一、搭建mybatis环境
-
创建一个动态web项目
-
引入mysql ,mybatis包
-
添加主配置文件mybatis-config.xml到src下
<?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> <!--加载配置文件--> <properties resource="jdbc.properties"/> <!--类型别名--> <typeAliases> <typeAlias alias="Student" type="com.yuanhuan.model.Student"/> </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> </environments> <!--引入映射文件--> <mappers> <mapper resource="com/yuanhuan/mappers/StudentMapper.xml" /> </mappers> </configuration>
-
添加jdbc.properties文件到src下(就是连接mysql的用户名密码,驱动信息)
在这里插入代码片jdbc.driverClassName=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mybatis jdbc.username=root jdbc.password=l1542735938
-
下面进行测试:
-
添加工具类SqlSessionFactoryUtil
public class SqlSessionFactoryUtil { private static SqlSessionFactory sqlSessionFactory; public static SqlSessionFactory getSqlSessionFactory() throws IOException { if (sqlSessionFactory == null) { InputStream inputStream=null; inputStream = Resources.getResourceAsStream("mybatis-config.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } return sqlSessionFactory; } public static SqlSession openSession() throws IOException { return getSqlSessionFactory().openSession(); } }
-
创建Student的model
package com.yuanhuan.model; public class Student { private int id; private String name; private int age; public Student() { super(); } public Student(String name, int age) { this.name = name; this.age = age; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } }
-
创建StudentMapper接口
public interface StudentMapper { public int add(Student student); }
-
添加StudentMapper配置StudentMapper.xml(与StudentMapper在同一目录)
<?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.yuanhuan.mappers.StudentMapper"> <!--因为在mybatis-config.xml中定义过Student类型别名,所以可以直接用Student--> <insert id="add" parameterType="Student" > insert into student values(null,#{name},#{age}) </insert> </mapper>
-
具体测试:
public class StudentService { public static void main(String[] args) throws IOException { SqlSession sqlSession= SqlSessionFactoryUtil.openSession(); //直接返回一个StudentMapper接口的实现类对象。 StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); int addnums=studentMapper.add(new Student("张三fe", 13)); //提交,不提交数据库是接收不到信息的 sqlSession.commit(); if (addnums > 0) { System.out.println("添加成功"); } else { System.out.println("添加失败"); } sqlSession.close(); } }
-
二、Mybatis主配置文件详解
上面配置环境也介绍过一些,这里再具体介绍一下:
<?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>
<!--加载配置文件,两种方法-->
<!--第一种,加载配置文件,方便-->
<properties resource="jdbc.properties"/>
<!--第二种,自己一个个配,不推荐使用-->
<!--<properties>
<property name="jdbc.username" value="root"/>
<property name="jdbc.password" value="123456"/>
</properties>-->
<!--给类的完成限定名取别名,方便使用-->
<!--第一种,一个个配置,优点就是可以自定义别名-->
<!--<typeAliases>
<typeAlias alias="Student" type="com.yuanhuan.model.Student"/>
</typeAliases>-->
<!--第二种,自动为指定包下全部class创建别名,名称就是类名,对于大项目,很多model时候可以用次方法,-->
<typeAliases>
<package name="com.yuanhuan.model"/>
</typeAliases>
<!--环境(此处为开发环境,还有测试环境test等)-->
<environments default="development">
<environment id="development">
<!--事务管理有两种JDBC,MANAGED,一般我们就用JDBC。-->
<!--(JDBC:应用程序负责管理数据库连接的生命周期;-->
<!--MANAGED:由应用服务器负责管理数据库连接的生命周期;(一般商业服务器才有此功能,如 JBOSS,WebLogic))-->
<transactionManager type="JDBC" />
<!--数据源,用来配置数据源;类型有:UNPOOLED,POOLED,JNDI;-->
<!-- UNPOOLED,没有连接池,每次数据库操作,MyBatis 都会创建一个新的连接,用完后,关闭;适合小并发 项目;-->
<!-- POOLED,用上了连接池;-->
<!-- JNDI,使用应用服务器配置 JNDI 数据源获取数据库连接;-->
<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>
<!--引入映射文件,两种方法-->
<!--第一种,一个个引入(可以通过resource,class,url,url不推荐使用,因为他配置的是根目录很麻烦),-->
<!--<mappers>
<mapper resource="com/yuanhuan/mappers/StudentMapper.xml" />
<mapper class="com.yuanhuan.mappers.StudentMapper"/>
</mappers>-->
<!--第二种,通过包名依次性将包种映射文件全部引入,推荐使用-->
<mappers>
<package name="com.yuanhuan.mappers"/>
</mappers>
</configuration>
三、配Log4j日志(方便记录打印一些信息,有它特定的格式,也可自己设置格式,便于查阅)
-
引入log4j 的jar包
-
在src下添加log4j的配置文件log4j.properties,内容如下(把注释删除掉):
//配置信息输出到appender1,appender2 log4j.rootLogger=info,appender1,appender2 //appender1为输出的到控制台 log4j.appender.appender1=org.apache.log4j.ConsoleAppender //appender2为输出的到文件夹 log4j.appender.appender2=org.apache.log4j.FileAppender log4j.appender.appender2.File=C:/logFile.txt //配置输出方式(有多种) log4j.appender.appender1.layout=org.apache.log4j.TTCCLayout log4j.appender.appender2.layout=org.apache.log4j.TTCCLayout
-
使用,在类中先定义变量,然后就可以使用了
//注意引包import org.apache.log4j.Logger; public static Logger logger = Logger.getLogger(StudentService.class);//StudentService为当前类名 logger.info("添加成功");
四、使用xml配置sql映射器(后面还有注解配置sql映射器,就是在接口上方加@xxx那种,但一般还是用xml配置多一些,灵活一些)
最开始的mybatis配置中介绍过一种add,下面具体介绍增删改查
-
首先定义一个接口StudentMapper:
public interface StudentMapper { public int add(Student student); public int delete(int id); public int update(Student student); public Student findById(int id); public List<Student> findAll(); }
-
定义对应的配置文件StudentMapper.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.yuanhuan.mappers.StudentMapper"> <!--id表示对应mapper类中的方法名,parametertype对应方法参数类型,resultType对应方法返回值类型,增删改自动返回Integer,不用设置--> <insert id="add" parameterType="Student" > insert into student values(null,#{name},#{age})/*这儿的name,age,从上面type中Student中自动获取,上面Student对应add方法的参数*/ </insert> <update id="update" parameterType="Student" > update student set name=#{name},age=#{age} where id=#{id} </update> <delete id="delete" parameterType="Integer" > delete from student where id=#{id} </delete> <select id="findById" parameterType="Integer" resultType="Student" > select * from student where id=#{id}/*这儿的id对应findById方法的参数名*/ </select> <!--返回集合时需要自定义返回类型--> <resultMap id="studentList" type="Student"> <!--id表示主键,property表示上面type中的属性,colunm表示对应数据库重的属性--> <id property="id" column="id"/> <result property="name" column="name"/> <result property="age" column="age"/> </resultMap> <!--resultMap对应上面自定义返回类型的id--> <select id="findAll" resultMap="studentList" > select * from student </select> </mapper>
-
写测试,先封装一个StudentService类,在定义个test类,直接在test中进行测试:
StudentService.java:
public class StudentService { //注意引包import org.apache.log4j.Logger; private SqlSession sqlSession=null; private StudentMapper studentMapper=null; private static Logger logger = Logger.getLogger(StudentService.class); public StudentService() throws IOException { sqlSession=SqlSessionFactoryUtil.openSession(); studentMapper = sqlSession.getMapper(StudentMapper.class); } public void sqlSessionClose() { sqlSession.close(); } public int add(Student student){ logger.info("添加学生"); int changenums=studentMapper.add(student); sqlSession.commit(); return changenums; } public int delete(int id){ logger.info("删除学生"); int changenums=studentMapper.delete(id); sqlSession.commit(); return changenums; } public int update(Student student){ logger.info("修改学生"); int changenums=studentMapper.update(student); sqlSession.commit(); return changenums; } public Student findById(int id){ logger.info("通过id查找学生"); Student student=studentMapper.findById(id); System.out.println(student); return student; } public List<Student> findAll(){ logger.info("查找全部学生"); List<Student> studentList=studentMapper.findAll(); for (Student student: studentList) { System.out.println(student); } return studentList; } }
test.java:
public class Test { public static void main(String[] args) throws IOException { StudentService studentService = new StudentService(); //studentService.add(new Student("袁欢", 22)); //studentService.update(new Student(14, "袁欢2", 23)); //studentService.delete(14); //studentService.findById(15); //studentService.findAll(); studentService.sqlSessionClose(); } }
五、Mybatis关系映射(就是查询时表之间的关联查询)
一对一关系实现
说明:两张表,一张学生,一张地址,学生中有外键addressId对应地址的主键Id,model学生类中有Address address属性,若要查找学生(包含地址信息)时,就要用到一对一关系实现,
步骤:1.先在StudentMapper.xml重新定义student返回类型,重定义的address属性中关联到AddressMapper.xml中(利用在StudentMapper.xml中查询得到的addressId 传送到AddressMapper.xml中查询对应的地址信息,赋值给student的address属性。)
StudentMapper.xml:
<resultMap type="Student" id="StudentResult">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<!--太坑了,这里column是传到后面select对应接口方法中的参数,-->
<association property="address" column="addressId" select="com.yuanhuan.mappers.AddressMapper.findAddressById"></association>
</resultMap>
<select id="findStudentWithAddressById" resultMap="StudentResult" parameterType="Integer">
select * from student t1,address t2 where t1.addressId=t2.id and t1.id=#{id}
</select>
AddressMapper.xml:
<select id="findAddressById" parameterType="Integer" resultType="Address">
select * from address where id=#{id}
</select>
一对多关系实现
将中association换位collection即可,但我测试都可以。
六、动态Sql
动态sql就是根据查询条件不同动态的拼接sql语句
1. if用法
例子:
<select id="searchStudent" resultMap="Student2" parameterType="Map">
select * from student
<--加这一行是无论有没有条件都能正常执行-->
where 1=1
<if test="name!=null">
and name like #{name}
</if>
<if test="age!=null">
and age=#{age}
</if>
</select>
2.choose、when、otherwise用法
例子:
<select id="searchStudent2" resultMap="Student2" parameterType="Map">
select * from student
<choose>
<when test="searchBy=='name'">
where name like #{name}
</when>
<when test="searchBy=='age'">
where age=#{age}
</when>
<otherwise>
where 1=1
</otherwise>
</choose>
</select>
3.where用法
有些语句后面没有条件有些有(所以不知道后面该不该加and),所以必须写一个 一定的条件,比如1=1,使用where会自动为我们检测后面子句是否为and 或or开头,是的话自动为我们删除and/or.
例子:
<select id="searchStudent2" resultMap="Student2" parameterType="Map">
select * from student
<choose>
<when test="searchBy=='name'">
where name like #{name}
</when>
<when test="searchBy=='age'">
where age=#{age}
</when>
<otherwise>
where 1=1
</otherwise>
</choose>
</select>
4.trim用法(他有四个属性,分别为在前加prefix,在后加suffix,在后面的首部删除prefixOverrides,在后面的尾部删除suffixOverrides)
例子:
<select id="searchStudent" resultMap="Student2" parameterType="Map">
select * from student
<--在前加where,在后面前面删除第一个and,这样就等同于where-->
<trim prefix="where" prefixOverrides="and">
<if test="name!=null">
and name like #{name}
</if>
<if test="age!=null">
and age=#{age}
</if>
</trim>
</select>
5.for each用法(用于传入一个集合,从集合中一个个取出拼接成sql语句)
例子
<select id="searchStudent3" resultMap="Student2" parameterType="Map">
select * from student
<where>
<if test="gradeIds!=null">
gradeId in
<--collection为传入集合,item为遍历时的临时单个对象,open为集合前加符号,separator为分割符,close为集合后加符号,拼接起来就为select * from student where grade in(1,2 ***)-->
<foreach collection="gradeIds" item="gradeId" open="(" separator="," close=")">
#{gradeId}
</foreach>
</if>
</where>
</select>
6.set用法(更新信息时,不确定更新哪些信息,所以可以用set,动态选择更新信息)
set会自动添加set
set会自动去掉最后一个逗号
例子:
<update id="updateStudent" parameterType="Student">
update student
<set>
<if test="name!=null">
name=#{name},
</if>
<if test="age!=null">
age=#{age},
</if>
</set>
where id=#{id}
</update>
七、处理大文本CLOB(小说)和二进制文件BLOB(图片,视频等)
在mysql中clob对应longtext, BLOB对应blob或longblob
例子:(插入,获得与普通属性类似,这里只是介绍如何操作图片)
//插入学生,带图片
Student student = new Student();
student.setName("欢");
student.setAge(15);
student.setRemark("很长很长的自我介绍。。。。。。。。。。。。。。。。");
byte[]pic =null;
InputStream inputStream = new FileInputStream(new File("E:\\你的名字4K壁纸\\687986.jpg"));
pic = new byte[inputStream.available()];
inputStream.read(pic);
inputStream.close();
student.setPic(pic);
studentService.insertStudent(student);
//获得学生(带图片)
Student student=studentService.getStudentById(18);
OutputStream outputStream=new FileOutputStream(new File("E:\\123.jpg"));
outputStream.write(student.getPic());
outputStream.flush();
outputStream.close();
八、多参数查询
在实际开发中一般传入mapper.xml中的参数用Map,不用多参数,这里只是介绍一些,多参数在mapper.xml中例子
<--开始不写参数类型,在里面直接使用#{param1},#{param2}....-->
<select id="test" resultType="Student">
select * from student where id=#{param1} and age=#{param2}
</select>
九、分页查找
1. 逻辑分页(取出全部数据到内存时,再取出分页数据,大项目对性能影响比较大,一般不使用)
mybatis 只支持逻辑分页。
实例:
//接口:
public List<Student> findStudent(RowBounds rowBounds);
//映射配置文件(不用写参数,mybatis自动操作)
<select id="findStudent" resultMap="StudentResult" >
select * from student
</select>
//测试
studentService类
public List<Student> findStudent(RowBounds rowBounds) {
logger.info("分页查找学生");
List<Student> studentList = studentMapper.findStudent(rowBounds);
for (Student student:
studentList) {
System.out.println(student);
}
return studentList;
}
test类
RowBounds rowBounds = new RowBounds(0,5);
studentService.findStudent(rowBounds);
studentService.sqlSessionClose();
2.物理分页(自己拼接sql实现真正的物理分页,常用)
实例:(传进去的就Map包含start 和size)
<select id="findStudent2" parameterType="Map" resultMap="StudentResult">
select * from student
<if test="start!=null and size!=null">
limit #{start},#{size}
</if>
</select>
十、Mybatis缓存
像数据库查询操作,大项目中,相同的操作很频繁,所以就可以使用缓冲(一般项目不使用)
-
Mybatis 默认情况下,MyBatis 启用一级缓存,即同一个 SqlSession 接口对象调用了相同的 select 语句,则直 接会从缓存中返回结果,而不是再查询一次数据库;(同一个用户)
-
开发者可以自己配置二级缓存,二级缓存是全局的;(所有用户)
-
默认情况下,select 使用缓存的,insert,update,delete 是不使用缓存的;
-
使用时只要在对应mapper.xml中加入:
<cache size="1024" flushInterval="60000" eviction="LRU" readOnly="false"/>
说明:(上面项目属性说明)
- size:表示缓存cache中能容纳的最大元素数。默认是1024;
- flushInterval:定义缓存刷新周期,以毫秒计;
- eviction:定义缓存的移除机制;默认是LRU(least recently userd,最近最少使用),还有FIFO(first in first out,先进先出)
- readOnly:默认值是false,假如是true的话,缓存只能读。
十一、使用注解配置SQL映射器
相对于xml配置来说优点就是更简洁,缺点就是不够灵活,企业开发一般还是用xmL配置。
1.使用注解配置普通增删改查
-
用法:直接将上面xml中的sql语句放到对应接口上,前面加上对应的@insert @update @delete @select即可。有集合返回值的也可以加上@resultMap等信息
-
实例:(下面列出了增删改查及查询带有集合数据的例子)
public interface StudentMapper { @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.使用注解配置关系映射(一对一,一对多)
代码大体跟用xml配置类时,都是移到接口方法前面,修改部分内容
实例:
//一对一(还需在地址接口中配置查询)
@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.java1234.mappers.AddressMapper.findById"))
}
)
public Student selectStudentWithAddress(int id);
//一对多(还需在班级接口中配置)
@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.java1234.mappers.AddressMapper.findById"))
}
)
public Student selectStudentByGradeId(int gradeId);
//一对多且把班级带上(还需在班级接口和地址接口中配置)
@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.java1234.mappers.AddressMapper.findById")),
@Result(column="gradeId",property="grade",one=@One(select="com.java1234.mappers.GradeMapper.findById"))
}
)
public Student selectStudentWithAddressAndGrade(int id);
3.使用注解配置动态Sql
-
先定义一个类,里面实现动态sql的语句,最后在到需要使用的接口方法上添加相关属性即可
-
实例:
StudentMapper 接口: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); }
StudentDynaSqlProvider 类:
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(); } }