一、mybatis入门
1、引入相关的依赖:
这里不作介绍,可以参考ssm整的所需依赖
2、关于mybatis-config.xml:
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 引入jdbc.properties配置文件,获取连接数据库信息,推荐 --> <properties resource="jdbc.properties"/> <!-- 连接数据库信息也可以直接写这里,如以下写法,不推荐 --> <!-- <properties> <property name="jdbc.driverClassName" value="com.mysql.jdbc.Driver"/> <property name="jdbc.url" value="jdbc:mysql://localhost:3306/db_mybatis"/> <property name="jdbc.username" value="root"/> <property name="jdbc.password" value="123456"/> </properties> --> <!-- 取别名,在StudentMapper.xml的parameterType属性中就不用写全类名 --> <!-- 方式一,不推荐 --> <!-- <typeAliases> <typeAlias alias="Student" type="com.java1234.model.Student"/> </typeAliases> --> <!-- 方式二,推荐,扫描此包下所有实体 --> <typeAliases> <package name="com.zhu.entity"/> </typeAliases> <!-- 配置环境 --> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driverClassName}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> </dataSource> </environment> <environment id="test"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driverClassName}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> </dataSource> </environment> </environments> <!-- 读取映射文件 --> <mappers> <!-- <mapper resource="com/zhu/mappers/StudentMapper.xml" /> 不推荐--> <!-- <mapper class="com.zhu.mappers.StudentMapper"/> 不推荐--> <!-- 推荐,扫描此包下所有映射文件 --> <package name="com.zhu.mappers"/> </mappers> </configuration>
以上大部分配置在做ssm整合时都会写在spring 配置文件中,这里只是让大家了解一下。
3、dao层以及实现:
public interface StudentMapper { public int add(Student student); }
StudentMapper.xml:
<mapper namespace="com.zhu.mappers.StudentMapper"> <insert id="add" parameterType="Student"> insert into t_student values(null,#{name},#{age}) </insert> </mapper>
再来个获取sqlSessionFactory的工具类:
public class SqlSessionFactoryUtil { private static SqlSessionFactory sqlSessionFactory; public static SqlSessionFactory getSqlSessionFactory(){ if(sqlSessionFactory==null){ InputStream inputStream=null; try{ inputStream = Resources .getResourceAsStream("mybatisconfig.xml"); sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream); }catch(Exception e){ e.printStackTrace(); } } return sqlSessionFactory; } public static SqlSession openSession(){ return getSqlSessionFactory() .openSession(); } }
4、测试:
public static void main(String[] args) { SqlSession sqlSession=SqlSessionFactoryUtil.openSession(); StudentMapper studentMapper=sqlSession.getMapper(StudentMapper.class); Student student=new Student("李四",11); int result=studentMapper.add(student); sqlSession.commit();
二、配置版CRUD:
1、dao接口定义CRUD方法:
public interface StudentMapper { public int add(Student student); public int update(Student student); public int delete(Integer id); public Student findById(Integer id); public List<Student> find(); }
2、StudentMapper.xml:
增加方法,id是接口中对应的方法名,parameterType就是方法的参数类型,因为在mybatis-config配置文件中配置了typeAliases,所以这里可以直接写Student,否则就要写全类名。
mybatis用 #{属性名} 获取实体类的属性值。
<insert id="add" parameterType="Student" > insert into t_student values(null,#{name},#{age}) </insert>
更新方法:
<update id="update" parameterType= "Student"> update t_student set name=#{name},age=#{age} where id=#{id} </update>
删除方法:
<delete id="delete" parameterType="Integer"> delete from t_student where id=#{id} </delete>
根据id查学生(1个):resultType是参数类型
<select id="findById" parameterType= "Integer" resultType="Student"> select * from t_student where id=#{id} </select>
查询所有学生:可以先定义一个resultMap来接收返回的学生。
<resultMap type="Student" id="StudentResult"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="age" column="age"/> </resultMap>
在这里引用刚才定义的resultMap:
<select id="find" resultMap= "StudentResult"> select * from t_student </select>
注意:
这里说一说resultType和resultMap,其实刚才那个方法也可以用resultType,如果查询结果字段与实体类字段名称完全一致,可以不映射;resultMap只有在select中才会用到。有三种情况会用到ResultMap:
1、如果查询结果字段与实体类的字段名称不对应;
2、查询结果的字段类型与实体类的字段类型不一致;
3、查询结果对应多个实体类(多表关联查询时)。
三、多表关联查询问题
1、一对一关联:
给student类增加一个address属性,一个学生对应一个地址
public class Student { private Integer id; private String name; private Integer age; private Address address; }
public class Address { private Integer id; private String sheng; private String shi; private String qu; }
根据student的id查询学生,查询结果带有地址信息
public interface StudentMapper { public Student findStudentWithAddress(Integer id); }
AddressMapper.xml
<select id="findById" parameterType="Integer" resultType="Address"> select * from t_address where id=#{id} </select>
StudentMapper.xml:
因为student新增了一个Address类型的属性,查询结果对应了student和Address两个类,属于复合类型,因此要定义resultMap来接收。
<resultMap type="Student" id="StudentResult"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="age" column="age"/> <--property表示实体类中的属性名,column是数据库中对应的字段名--> <association property="address" column="addressId" select="com.zhu.mappers .AddressMapper.findById"> </association> </resultMap>
然后在select标签中引用这个resultMap:
<select id="findStudentWithAddress" resultMap="StudentResult" parameterType="Integer"> select * from t_student t1,t_address t2 where t1.addressId=t2.id and t1.id=#{id} </select>
2、一对多关联:
一对多,多个学生属于一个年级,即学生为n,年级为1
public interface StudentMapper { public Student findByGradeId(Integer gradeId); }
StudentMapper.xml:
<resultMap type="Student" id="StudentResult"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="age" column="age"/> <!--property="grade"表示学生的grade属性,column="gradeId"表示外键,select指查询年级方法的全类名--> <association property="grade" column="gradeId" select="com.zhu.mappers.GradeMapper.findById"></association> </resultMap> <select id="findByGradeId" resultMap="StudentResult" parameterType="Integer"> select * from t_student where gradeId=#{gradeId} </select>
public interface GradeMapper { public Grade findById(Integer id); }
GradeMapper.xml:
<resultMap type="Grade" id="GradeResult"> <result property="id" column="id"/> <result property="gradeName" column="gradeName"/> <!--property="students"表示grade的student属性,column="id",此ID是学生ID--> <collection property="students" column="id" select="com.zhu.mappers.StudentMapper.findByGradeId"></collection> </resultMap> <select id="findById" parameterType="Integer" resultMap="GradeResult"> select * from t_grade where id=#{id} </select>
关于一对一以及一对多关联查询详解,请参见mybatis关联查询之association和collection
四、mybatis的注解形式
mybatis也可以把sql语句直接把注解形式写在dao层的接口方法上:
1、注解版CRUD:
@Insert("insert into t_student values(null,#{name},#{age})") public int insertStudent(Student student); @Update("update t_student set name=#{name},age=#{age} where id=#{id}") public int updateStudent(Student student); @Delete("delete from t_student where id=#{id}") public int deleteStudent(int id); @Select("select * from t_student where id=#{id}") public Student getStudentById(Integer id); @Select("select * from t_student") @Results( { @Result(id=true,column="id",property="id"), @Result(column="name",property="name"), @Result(column="age",property="age") } ) public List<Student> findStudents();
2、注解版关联查询:
①、一对一关联查询:
studentMapper.java
@Select("select * from t_student where gradeId=#{gradeId}") @Results( { @Result(id=true,column="id",property="id"), @Result(column="name",property="name"), @Result(column="age",property="age"), @Result(column="addressId",property="address",one=@One(select="com.zhu.mappers.AddressMapper.findById")) } ) public Student selectStudentByGradeId(int gradeId);
gradeMapper.java
public interface GradeMapper { @Select("select * from t_grade where id=#{id}") @Results( { @Result(id=true,column="id",property="id"), @Result(column="gradeName",property="gradeName"), @Result(column="id",property="students",many=@Many(select="com.java1234.mappers.StudentMapper.selectStudentByGradeId")) } ) public Grade findById(Integer id); }
②、一对一和一对多:
@Select("select * from t_student where id=#{id}") @Results( { @Result(id=true,column="id",property="id"), @Result(column="name",property="name"), @Result(column="age",property="age"), @Result(column="addressId",property="address",one=@One(select="com.zhu.mappers.AddressMapper.findById")), @Result(column="gradeId",property="grade",one=@One(select="com.zhu.mappers.GradeMapper.findById")) } ) public Student selectStudentWithAddressAndGrade(int id);
五、sql语句构建器版CRUD:
sql语句构建器,就是把sql语句写在一个类中,然后在接口方法上引用这个类,请看下面的代码:
1、构建器类:
public class StudentDynaSqlProvider { public String insertStudent(final Student student){ return new SQL(){ { INSERT_INTO("t_student"); if(student.getName()!=null){ VALUES("name", "#{name}"); } if(student.getAge()!=null){ VALUES("age", "#{age}"); } } }.toString(); } public String updateStudent(final Student student){ return new SQL(){ { UPDATE("t_student"); if(student.getName()!=null){ SET("name=#{name}"); } if(student.getAge()!=null){ SET("age=#{age}"); } WHERE("id=#{id}"); } }.toString(); } public String deleteStudent(){ return new SQL(){ { DELETE_FROM("t_student"); WHERE("id=#{id}"); } }.toString(); } public String getStudentById(){ return new SQL(){ { SELECT("*"); FROM("t_student"); WHERE("id=#{id}"); } }.toString(); } public String findStudents(final Map<String,Object> map){ return new SQL(){ { SELECT("*"); FROM("t_student"); StringBuffer sb=new StringBuffer(); if(map.get("name")!=null){ sb.append(" and name like '"+map.get("name")+"'"); } if(map.get("age")!=null){ sb.append(" and age="+map.get("age")); } if(!sb.toString().equals("")){ WHERE(sb.toString().replaceFirst("and", "")); } } }.toString(); } }
2、在接口方法上引用:
public interface StudentMapper { @InsertProvider(type=StudentDynaSqlProvider.class,method="insertStudent") public int insertStudent(Student student); @UpdateProvider(type=StudentDynaSqlProvider.class,method="updateStudent") public int updateStudent(Student student); @DeleteProvider(type=StudentDynaSqlProvider.class,method="deleteStudent") public int deleteStudent(int id); @SelectProvider(type=StudentDynaSqlProvider.class,method="getStudentById") public Student getStudentById(Integer id); @SelectProvider(type=StudentDynaSqlProvider.class,method="findStudents") public List<Student> findStudents(Map<String,Object> map); }
3、junit测试:
@Test public void testInsert() { Student student=new Student("琪琪",11); studentMapper.insertStudent(student); sqlSession.commit(); }
六、动态sql的应用:
1、mybatis带条件分页查询
mybatis分页并不难,只要传入rowIndex和pageSize,然后用limit语句即可;关于带条件查询,要查哪个对象就把条件封装成那个对象的一个实体,然后在xml中通过where标签解析出来即可。话不多说,看如下代码:
User.java
public class User { private Integer userId; private String userName; private Integer age; private Card card;//一个人一张身份证,1对1 private List<MobilePhone> mobilePhone;//土豪,多个手机,1对多 }
Card.java
public class Card { private Integer cardId; private String cardNum;//身份证号 private String address;//地址 }
MobilePhone.java
private Integer mobilePhoneId; private String brand;//品牌 private double price;//价格 private User user;//主人 }
UserDao.java
/** * 带条件分页查询: * 可输入的条件:名字(模糊),cardId,age, * @param userCondition 把条件封装成一个user对象 * @param rowIndex 表示从第几行开始取数据 * @param pageSize 表示要返回多少行数据 * @return 返回user列表 */ List<User> queryUserList(@Param("userCondition") User userCondition, @Param("rowIndex") int rowIndex, @Param("pageSize") int pageSize);
UserDao.xml
<!--定义resultMap--> <resultMap type="User" id="userMap"> <id property="userId" column="user_id"/> <result property="userName" column="user_name"/> <result property="age" column="age"/> <association property="card" column="card_id" javaType="Card"> <id property="cardId" column="card_id"/> <result property="cardNum" column="card_num"/> <result property="address" column="address"/> </association> <collection property="mobilePhone" column="user_id" ofType="MobilePhone"> <id column="mobile_phone_id" property="mobilePhoneId" /> <result column="brand" property="brand" /> <result column="price" property="price" /> </collection> </resultMap> <!--带条件分页查询--> <select id="queryUserList" resultMap="userMap"> SELECT u.user_name,u.age,u.card_id,c.card_num,c.address,m.brand,m.price FROM tb_user u,tb_card c,tb_mobile_phone m <where> <if test="userCondition.card != null and userCondition.card.cardId != null"> and u.card_id = #{userCondition.card.cardId} </if> <if test="userCondition.userName != null"> and u.user_name like '%${userCondition.userName}%' </if> <if test="userCondition.age != null"> and u.age = #{userCondition.age} </if> AND u.card_id = c.card_id AND u.user_id = m.user_id </where> LIMIT #{rowIndex},#{pageSize} </select>
junit测试:
@Test public void testQueryUserList() { User userCondition = new User(); /*Card c = new Card(); c.setCardId(2); userCondition.setCard(c);*/ //userCondition.setAge(22); userCondition.setUserName("菲"); List<User> ul = userDao.queryUserList(userCondition, 1, 99); for(User user : ul) { System.out.println(user.getUserName()); /*List<MobilePhone> list = new ArrayList<>(); list = user.getMobilePhone(); for(MobilePhone mp : list) { System.out.println(mp.getBrand()); }*/ } }
以上代码便完成了带条件分页查询,整个过程并不难,只是在select中用了where标签以及用where的子标签if判断传入的条件是否为空,不为空就赋值。
2、mybatis的动态更新:
上面CRUD案例中的更新,name和age必须传入值,没有传入的话那就会更新成null或0,而这里所说的动态更新就是传了值的才更新,没传值的字段保留原来的值。看如下案例(实体类同带条件分页查询的三个实体类):
UserDao.java
int updateUser(User user);
UserDao.xml
<!-- 更新user --> <update id="updateUser" parameterType="User"> UPDATE tb_user <set> <if test="userName!=null">user_name=#{userName},</if> <if test="age!=null">age=#{age},</if> <if test="card!=null">card_id=#{card.cardId},</if> </set> where user_id=#{userId} </update>
junit测试:
@Test public void testUpdateUser() { User user = new User(); user.setUserId(4); user.setAge(22); Card c = new Card(); c.setCardId(1); user.setCard(c); int result = userDao.updateUser(user); assertEquals(1, result); }
动态更新就是在select标签中添加了一个set,然后再用if去判断传入的字段是否为空,不为空就更新。
更多动态sql的应用,请参考mybatis的动态sql
总结:
mybatis相对于hibernate来说,优点在于支持sql语句,而hibernate使用的是hql语句。在业务逻辑简单不需要编写很多hql语句时可能使用hibernate更加快捷,因为它封装了一些对数据库的基本操作比如save、update等,直接调用就行;当业务逻辑比较复杂,那就选用mybatis更好。
以上内容属于个人笔记整理,如有错误,欢迎批评指正!