首页 > 代码库 > JavaEE 之 Mybatis

JavaEE 之 Mybatis

1.Mybatis

  a.定义:MyBatis 是支持普通 SQL查询,存储过程和高级映射的优秀持久层框架

  b.步骤:

    ①在src下创建 SqlMapConfig.xml 及 datasource.properties

    ②建UserMapper.java(相当于DAO)

public interface UserMapper {
    
    public int addUser(@Param("user")User user);
    public int delUserById(int userId);
    public int updateUser(@Param("user")User user);
    public User findUserById(int userId);
    public List<User> findAllUser();

}

    ③建UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.wode.mapper.UserMapper">
    <resultMap id="userMap" type="User">
        <id property="userId" column="user_id" />
        <result property="userName" column="user_name"/>
        <result property="userPwd" column="user_pwd"/>
        <result property="userType" column="user_type"/>
    </resultMap>
    
    <insert id="addUser"  parameterType="User">
        insert into users (user_id,user_name,user_pwd,user_type) values (null,#{user.userName},#{user.userPwd},#{user.userType})
    </insert>
    
    <delete id="delUserById" parameterType="int">
        delete from users where user_id=#{userId}
    </delete>
    
    <update id="updateUser" parameterType="User">
        update users set user_name = #{user.userName},user_pwd = #{user.userPwd},user_type = #{user.userType} where user_id = #{user.userId}
    </update>
    
    <select id="findUserById" parameterType="int" resultMap="userMap">
         select * from users where user_id = #{user.userId}
     </select>
     
     <select id="findAllUser" resultMap="userMap">
         select * from users
     </select>
    
</mapper>

    ④在Service中个使用

//一下代码可封装于另一个类中
InputStream  in = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession session = sessionFactory.openSession()

//使用
UserMapper mapper=session.getMapper(UserMapper.class);

//
int result=mapper.addUser(user);
session.commit();

//
int result=mapper.delUserById(id);
session.commit();

//
int result=mapper.updateUser(user);
session.commit();

//查单个对象
User user = mapper.findUserById(userId);

//查所有对象
List<User> users = mapper.findAllUser()

 

 

2.其他查询

  a.查询聚合函数

    UserMapper.xml中

     <select id="findCountUser" resultType="int">
         select count(*) from users
     </select>

    UserMapper.java中

public int findCountUser();

  b.模糊查询

     <select id="findUserLikeName" parameterType="java.lang.String" resultMap="userMap">
         select * from users where user_name like ‘%${name}%‘
     </select>
public List<User> findUserLikeName(@Param("name")String name);

  c.查询单个字段

     <select id="findPwdByName" parameterType="java.lang.String" resultType="java.lang.String">
         select user_pwd from users where user_name = #{userName}
     </select>
    public String findPwdByName(@Param("userName")String userName);

 

 

3.一对一(One2One)

  a.方法一:

    UserMapper.xml中

    <resultMap id="userAndInfoMap" type="User">
        <id property="userId" column="user_id" />
        <result property="userName" column="user_name"/>
        <result property="userPwd" column="user_pwd"/>
        <result property="userType" column="user_type"/>
        <association property="info" resultMap="com.wode.mapper.InfoMapper.InfoMapper"></association>
    </resultMap>

    <select id="findUserAndInfoById" parameterType="int" resultMap="userAndInfoMap">
         select * from users u left join infos i on u.user_id=i.user_id where u.user_id = #{userId}
     </select>

 

  b.方法二

    UserMapper.xml中

    <resultMap id="userAndInfoMap2" type="User">
        <id property="userId" column="user_id" />
        <result property="userName" column="user_name"/>
        <result property="userPwd" column="user_pwd"/>
        <result property="userType" column="user_type"/>
        <association property="info" column="user_id" javaType="Info" select="com.wode.mapper.InfoMapper.findInfoByUserId"></association>
    </resultMap>

     <select id="findUserAndInfoById2" parameterType="int" resultMap="userAndInfoMap2">
         select * from users u where u.user_id = #{userId}
     </select>

    InfoMapper.xml中

    <resultMap type="Info" id="InfoMapper">
        <id property="infoId" javaType="int" column="info_id" />
        <result javaType="java.lang.String" property="infoName" column="info_name" />
        <result javaType="java.lang.String" property="infoEmail" column="info_email" />
    </resultMap>

    <select id="findInfoByUserId" resultMap="InfoMapper">
        select * from infos where user_id=#{userId}
    </select>

 

 

4.一对多(One2Many)

  同One2One,仅在List<Info>部分将<association ...></association > 更换为<collection ...></collection>

 

 

5.多对多(Many2Many)

  a.方法一:同上

    <resultMap id="UserAndCourseMapper2" type="User">
        <id property="userId" column="user_id" />
        <result property="userName" column="user_name"/>
        <result property="userPwd" column="user_pwd"/>
        <result property="userType" column="user_type"/>
        <collection property="courses" resultMap="com.wode.mapper.CourseMapper.courseMap"></collection>
    </resultMap>

    <select id="findUserAndCourseById2" parameterType="Integer"
        resultMap="UserAndCourseMapper2">
            select * from users u,course c,user_course uc where u.user_id=uc.user_id and c.courseId = uc.course_id and u.user_id=#{userId}
    </select>

  b.方法二:在通过userId查Course时需用到子查询(即第二个select)

    <resultMap id="UserAndCourseMapper" type="User">
        <id property="userId" column="user_id" />
        <result property="userName" column="user_name"/>
        <result property="userPwd" column="user_pwd"/>
        <result property="userType" column="user_type"/>
        <collection property="courses" column="user_id" select="findCourseByUser"></collection>
    </resultMap>

    <select id="findUserAndCourseById" parameterType="Integer"
        resultMap="UserAndCourseMapper">
            select * from users where user_id=#{userId}
    </select>
    
    <select id="findCourseByUser" parameterType="Integer"
        resultMap="com.wode.mapper.CourseMapper.courseMap">
            select * from course where courseId in(select course_id from user_course where user_id=#{userId})
    </select>

 

 

6.二级缓存

  a.在SqlMapConfig.xml配置

<settings>
    <setting name="cacheEnabled" value="true" />
</settings>

  b.在UserMapper.xml中配置

<cache />

 

 

7.动态查询

  a.if

    <select id="searchStudent" parameterType="java.util.Map"  resultMap="scoreMap">
            select * from score  where 1=1
            <if test="java!=null">
                and java &gt;=#{java}
            </if>
            <if test="web!=null">
                and web &gt;=#{web}
            </if>
            <if test="mysql!=null">
                and mysql &gt;=#{mysql}
            </if>
    </select>

  b.choose

    <select id="searchStudent2" parameterType="java.lang.String" resultMap="scoreMap">
        select * from score 
        <choose> 
            <when test="course==‘java‘">
                where java &gt;=60
            </when>
            <when test="course==‘web‘">
                where web &gt;=60
            </when>
            <otherwise>
                where mysql &gt;=60
            </otherwise>
        </choose>
    </select>

  c.<where>

    <select id="searchStudent" parameterType="java.util.Map"  resultMap="scoreMap">
            select * from score 
            <where>
            <if test="java!=null">
                and java &gt;=#{java}
            </if>
            <if test="web!=null">
                and web &gt;=#{web}
            </if>
            <if test="mysql!=null">
                and mysql &gt;=#{mysql}
            </if>
            </where>
    </select>

  d.trim

    <select id="searchStudent" parameterType="java.util.Map"  resultMap="scoreMap">
            select * from score   
            <trim prefix="where" prefixOverrides="and|or">
            <if test="java!=null">
                and java &gt;=#{java}
            </if>
            <if test="web!=null">
                and web &gt;=#{web}
            </if>
            <if test="mysql!=null">
                and mysql &gt;=#{mysql}
            </if>
            </trim>
    </select>

  e.set

    <update id="updateScore" parameterType="java.util.Map">
        update score 
        <set>
            <if test="java != null">
                  java = #{java},
              </if>
              <if test="web != null">
                  web = #{web},
              </if>
              <if test="mysql != null">
                  mysql = #{mysql}
              </if>
        </set>
              where id = #{id}
    </update>

  f.foreach

    <select id="findUser"  parameterType="java.util.Map" resultMap="userMap">
        select * from users
        <where>
            user_id in
            <foreach collection="usersId" item="userId" separator="," open="(" close=")" index="">
                #{userId}            
            </foreach>
        </where>
    </select>

 

 

8.注解

  a.SqlMapConfig.xml中<mappers>只需配置

    <mappers>
        <package name="com/wode/mapper" />
    </mappers>

  b.普通注解

    //使用注解的方式新增用户
    @Insert("insert into users values(null,#{user.userName},#{user.userPwd},#{user.userType})")
    @Options(keyProperty="user.userId",useGeneratedKeys=true)
    public int addUser(@Param("user")User user);
    //注解的方式修改用户资料
    @Update("update users set user_name=#{name} where user_id=#{id}")
    public int updateUserNameById(@Param("name")String name,@Param("id")int id);
    //注解的方式删除用户
    @Delete("delete from users where user_id=#{id}")
    public int delById(@Param("id") int id);
    
    @Select("select * from users")
/**    @Results({
        @Result(id=true,property="userId",column="user_id",javaType=Integer.class),
        @Result(property="userName",column="user_name",javaType=String.class),
        @Result(property="userPwd",column="user_pwd",javaType=String.class),
        @Result(property="userType",column="user_type",javaType=Integer.class)
    })
    */
    @ResultMap("userMap")
    public List<User> findAllUser();

  c.一对多、多对一查询

    @Select("select * from users where user_id=#{id}")
    @Results({
        @Result(id=true,property="userId",column="user_id",javaType=Integer.class),
        @Result(property="userName",column="user_name",javaType=String.class),
        @Result(property="userPwd",column="user_pwd",javaType=String.class),
        @Result(property="userType",column="user_type",javaType=Integer.class),
        @Result(property="info",column="user_id",many=@Many(select="com.wode.mapper.UserInfoMapper.findByUser")) 
    })


    @Select("select * from userInfo where info_id=#{infoId}")
    @Results({
        @Result(id=true,column="info_id",property="infoId",javaType=Integer.class),
        @Result(column="nickName",javaType=String.class,property="nickName"),
        @Result(column="email",property="email",javaType=String.class),
        @Result(column="user_id",property="user",one=@One(select="com.wode.mapper.UserMapper.findUserById"))
    })
    public UserInfo findInfoAndUser(@Param("infoId")int infoId);

 

JavaEE 之 Mybatis