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.模糊查询
防止sql注入：
<select id="findUserLikeName" parameterType="java.lang.String" resultMap="userMap"> select * from users where user_name like concat('%',#{name},'%') </select>
不防止sql注入：
<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 >=#{java} </if> <if test="web!=null"> and web >=#{web} </if> <if test="mysql!=null"> and mysql >=#{mysql} </if> </select>
b.choose
<select id="searchStudent2" parameterType="java.lang.String" resultMap="scoreMap"> select * from score <choose> <when test="course=='java'"> where java >=60 </when> <when test="course=='web'"> where web >=60 </when> <otherwise> where mysql >=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 >=#{java} </if> <if test="web!=null"> and web >=#{web} </if> <if test="mysql!=null"> and mysql >=#{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 >=#{java} </if> <if test="web!=null"> and web >=#{web} </if> <if test="mysql!=null"> and mysql >=#{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);
9.其他
a.添加后返回主键
<insert id="..." parameterType="..."> <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer"> SELECT LAST_INSERT_ID() </selectKey> insert into ... (...) values (...) </insert>
b.有则修改，无则添加
<insert id="addUserNum"> insert into user_record (user_id, user_num) values (#{userId}, 1) ON DUPLICATE key UPDATE user_num = user_num+1 </insert>
