目录
1.MyBatis支持的事务管理器:JDBC,MANAGED(托管)
1.MyBatis支持的事务管理器:JDBC,MANAGED(托管)
1.JDBC 应用程序负责管理数据库连接的生命周期
2.MANAGED 由应用服务器负责管理数据库连接的生命周期(一般用于商业服务器JBoss,WebLogic)
2.MyBatis配置数据库
1.UNPOOLED 没有数据库连接池
2.POOLED 数据库连接池，配置jdbc.properties文件
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/zhaoyh01
jdbc.username=root
jdbc.password=root3.JNDI 使用应用服务器配置JNDI数据源获取数据库链接
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>
<!-- 配置属性:数据库链接地址、账号、密码 -->
<properties resource="jdbc.properties"/>
<!-- typeAliases：别名处理器，可以为java类型（resultType）起别名。 -->
<typeAliases>
<package name="com.zhaoyh.model"/>
</typeAliases>
<!-- 配置MyBatis支持的环境 -->
<environments default="development">
<environment id="development">
<!-- 配置事务管理器:JDBC -->
<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>
<package name="com.zhaoyh.mappers"/>
</mappers>
</configuration>
3.MyBatis查询
1.association 一对一查询
<resultMap type="Student" id="StudentResult">
<id property="id" column="id"></id>
<result property="name" column="name"/>
<result property="age" column="age"/>
<!-- 配置一对一关系:column对应外键id,select子查询,一个学生有一个班级,一个地址 -->
<association property="address" column="addressId" select="com.zhaoyh.mappers.AddressMapper.findById"></association>
<association property="grade" column="gradeId" select="com.zhaoyh.mappers.GradeMapper.findById"></association>
</resultMap>
2.collection 一对多查询
<!-- 定义一对多的关系,一个班级对应多个学生 -->
<resultMap type="Grade" id="GradeResult">
<result property="id" column="id"/>
<result property="gradeName" column="gradeName"/>
<collection property="students" column="id" select="com.zhaoyh.mappers.StudentMapper.findByGradeId"></collection>
</resultMap>4.MyBatis动态SQL
1. if 标签
<!-- 列表查询:if标签,当标签中的内容不为空时,查询条件增加标签中的内容 -->
<select id="searchStudents" parameterType="Map" resultMap="StudentResult">
select * from t_student where 1=1 and gradeId=${gradeId}
<if test="name != null">
and name=#{name}
</if>
<if test="age != null">
and age=#{age}
</if>
</select>
2. choose,when,otherwise ,类似与Java 中的 switch 语句
<!-- 列表查询: choose,when,otherwise -->
<select id="searchStudentsTwo" parameterType="Map" resultMap="StudentResult">
select * from t_student
<choose>
<when test="searchBy=='gradeId'">
where gradeId=#{gradeId}
</when>
<when test="searchBy=='name'">
where name=#{name}
</when>
<otherwise>
where age=#{age}
</otherwise>
</choose>
</select>3. where 自动加上where,如果where字句以and或者or开头，则自动删除第的一个and或者or;
<!-- 自动加上where,如果where字句以and或者or开头，则自动删除第的一个and或者or -->
<select id="searchStudentsThree" parameterType="Map" resultMap="StudentResult">
select * from t_student
<where>
<if test="gradeId != null">
gradeId=#{gradeId}
</if>
<if test="name != null">
and name=#{name}
</if>
<if test="age != null">
and age=#{age}
</if>
</where>
</select>4.trim
常用的属性：
prefix=”where”//给第一个符合条件的语句 加上前缀where
prefixOverrides=”and/or” //去掉第一个and或者是or
suffix=”and” //给最后一个符合条件的语句 加上后缀 and
suffixOverrides=”and”//去掉最后一条语句的后缀 and
<!-- 列表查询:trim -->
<select id="searchStudentsFour" parameterType="Map" resultMap="StudentResult">
select * from t_student
<trim prefix="where" prefixOverrides="and|or">
<if test="gradeId != null">
and gradeId=#{gradeId}
</if>
<if test="name != null">
and name=#{name}
</if>
<if test="age != null">
and age=#{age}
</if>
</trim>
</select>5.foreach 循环
常用的属性：
item 要遍历的元素；
collection 要遍历的集合；
index 元素在集合中的索引；
open 遍历以什么开头，例 open=” (“;
seprator 遍历出来的元素以什么分隔；
end 遍历以什么结束 end=”)”
<!-- 列表查询:foreach -->
<select id="searchStudentsFive" parameterType="Map" resultMap="StudentResult">
select * from t_student
<if test="gradeIds != null">
<where>
gradeId in
<foreach item="gradeId" collection="gradeIds" open="(" separator="," close=")">
#{gradeId}
</foreach>
</where>
</if>
</select>6. set 条件 自动加上set,自动删除最后一个逗号','
<!-- 更新:set -->
<update id="updateStudent" parameterType="Student" >
update t_student
<set>
<if test="name != null">
name=#{name},
</if>
<if test="age != null">
age=#{age},
</if>
</set>
where id=#{id}
</update>5.MyBatis杂项
1.处理CLOB、BLOB类型数据
student.java类
package com.zhaoyh.model;
public class Student {
private Integer id;
private String name;
private Integer age;
private byte[] picture;//BLOB类型的(MySql中的longblob)
private String remark;//CLOB类型(MySql中的longtext)
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(Integer id, String name, Integer age) {
super();
this.id = id;
this.name = name;
this.age = age;
}
public Student(String name, Integer age) {
super();
this.name = name;
this.age = age;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public byte[] getPicture() {
return picture;
}
public void setPicture(byte[] picture) {
this.picture = picture;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + ", remark=" + remark + "]";
}
}
student接口方法
package com.zhaoyh.mappers;
import java.util.List;
import java.util.Map;
import com.zhaoyh.model.Student;
public interface StudentMapper {
//处理Clob/Blob类型的数据
public int insertStudent(Student student);
public Student getStudentById(Integer id);
}
mapper映射文件
<?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.zhaoyh.mappers.StudentMapper">
<!-- 定义学生表的属性 -->
<resultMap type="Student" id="StudentResult">
<id property="id" column="id"></id>
<result property="name" column="name"/>
<result property="age" column="age"/>
</resultMap>
<!-- 处理Clob/Blob类型数据 -->
<insert id="insertStudent" parameterType="Student">
insert into t_student values(null,#{name},#{age},'1','1',#{picture},#{remark})
</insert>
<select id="getStudentById" parameterType="Integer" resultType="Student" >
select * from t_student where id=#{id}
</select>
</mapper> junittest测试类
/*@Test
public void testInsertStudent() throws IOException {
log.info("存入Clob/Blob类型的数据...");
Student student = new Student();
student.setName("test01");
student.setAge(22);
byte[] picture=null;
try {
File file = new File("E://Picture//kong.png");
InputStream is = new FileInputStream(file);
picture = new byte[is.available()];
is.read(picture);
is.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
student.setPicture(picture);
student.setRemark("12312312312312312312312312312123123");
studentMapper.insertStudent(student);
sqlSession.commit();
}
@Test
public void testGetStudentById() throws IOException {
log.info("查找Clob/Blob类型的数据...");
Student student = studentMapper.getStudentById(9);
System.out.println("student="+student);
byte []picture = student.getPicture();
try {
File file = new File("E://Picture//kong2.jpg");
OutputStream os = new FileOutputStream(file);
os.write(picture);
os.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}6.MyBatis分页查询
1.逻辑分页-RowBounds
通过RowBounds类可以实现Mybatis逻辑分页，原理是首先将所有结果查询出来，然后通过计算offset和limit，只返回部分结果，操作在内存中进行，所以也叫内存分页，当数据量比较大时，查询速度很慢。
student接口类
package com.zhaoyh.mappers;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.RowBounds;
import com.zhaoyh.model.Student;
public interface StudentMapper {
//分页查询
public List<Student> findStudents(RowBounds rowBounds);
}
Mapper映射文件
<!-- 分页查询 -->
<select id="findStudents" resultMap="StudentResult">
select * from t_student
</select>JUnitTest类
@Test
public void testFindStudent() {
log.info("分页查询...");
int offset=0,limit=3;//offset从第几页查询，limit每次查询条数
RowBounds rb = new RowBounds(offset, limit);
List<Student> studentList = studentMapper.findStudents(rb);
for(Student student : studentList){
System.out.println(student);
}
}2.物理分页
student接口
package com.zhaoyh.mappers;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.RowBounds;
import com.zhaoyh.model.Student;
public interface StudentMapper {
//分页查询-物理分页
public List<Student> findStudentsTwo(Map<String,Object> map);
}mapper映射文件
<!-- 分页查询:物理分页 -->
<select id="findStudentsTwo" parameterType="Map" resultMap="StudentResult">
select * from t_student
<if test="start != null and size != null">
limit #{start},#{size}
</if>
</select>JUnitTest测试类
@Test
public void testFindStudentTwo() {
log.info("分页查询-物理分页...");
HashMap map = new HashMap<String, Object>();
map.put("start", 0);
map.put("size", 3);
List<Student> studentList = studentMapper.findStudentsTwo(map);
for(Student student : studentList){
System.out.println(student);
}
}7.MyBatis缓存
Mybatis 默认情况下，MyBatis 启用一级缓存，即同一个 SqlSession 接口对象调用了相同的 select 语句，则直接会从缓存中返回结果，而不是再查询一次数据库；开发者可以自己配置二级缓存，二级缓存是全局的；默认情况下，select 使用缓存的，insert update delete 是不使用缓存的；
<?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.zhaoyh.mappers.StudentMapper">
<!--
1，size:表示缓存cache中能容纳的最大元素数。默认是1024；
2，flushInterval:定义缓存刷新周期，以毫秒计；
3，eviction:定义缓存的移除机制；默认是LRU(least recently userd，最近最少使用),还有FIFO(first in first out，先进先出)
4，readOnly:默认值是false，假如是true的话，缓存只能读。
-->
<cache size="1024" flushInterval="60000" eviction="LRU" readOnly="false"/>
<!-- 列表查询: flushCache="false" 默认不清空缓存 useCache="true" 默认使用缓存查询-->
<select id="searchStudents" parameterType="Map" resultMap="StudentResult" flushCache="false" useCache="true">
select * from t_student where 1=1 and gradeId=${gradeId}
<if test="name != null">and name=#{name}</if>
<if test="age != null">and age=#{age}</if>
</select>
</mapper> 8.使用注解配置 SQL 映射器
1，@Insert
2，@Update
3，@Delete
4，@Select
student.java实体类
package com.zhaoyh.model;
public class Student {
private Integer id;
private String name;
private Integer age;
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(Integer id, String name, Integer age) {
super();
this.id = id;
this.name = name;
this.age = age;
}
public Student(String name, Integer age) {
super();
this.name = name;
this.age = age;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
student接口类
package com.zhaoyh.mappers;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.zhaoyh.model.Student;
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 deleteStudentById(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();//查询所有
}
JUnitTest测试类
package com.zhaoyh.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.zhaoyh.mappers.StudentMapper;
import com.zhaoyh.model.Student;
import com.zhaoyh.service.StudentService;
import com.zhaoyh.util.SqlSessionFactoryUtil;
/**
* @author Administrator
*
*/
public class StudentServiceTest {
private static Logger log = Logger.getLogger(StudentService.class);
private SqlSession sqlSession = null;
private StudentMapper studentMapper = null;
/**
* 测试方法前调用
*
*/
@Before
public void setUp() throws Exception {
sqlSession = SqlSessionFactoryUtil.openSession();
studentMapper = sqlSession.getMapper(StudentMapper.class);
}
/**
* 测试方法前调用
*
*/
@After
public void tearDown() throws Exception {
sqlSession.close();
}
@Test
public void testInsertStudent() {
log.info("使用@Insert注解方式添加学生信息...");
Student student = new Student("test03",22);
studentMapper.insertStudent(student);
sqlSession.commit();
}
@Test
public void testUpdateStudent(){
log.info("使用@Update注解方式修改学生信息");
Student student = new Student(12,"test032",25);
studentMapper.updateStudent(student);
sqlSession.commit();
}
@Test
public void testDeleteStudentById(){
log.info("使用@Delete注解方式删除学生信息");
studentMapper.deleteStudentById(13);
sqlSession.commit();
}
@Test
public void testGetStudentById(){
log.info("使用@Select注解方式查询学生信息");
Student student = studentMapper.getStudentById(12);
System.out.println("Student="+student);
}
@Test
public void testFindStudents(){
log.info("使用@Select注解方式查询学生信息");
List<Student> studentList = studentMapper.findStudents();
for(Student student:studentList){
System.out.println("Student="+student);
}
}
}
9.关系映射:一对一映射
一个学生信息对应一个地址信息
student.java
package com.zhaoyh.model;
public class Student {
private Integer id;
private String name;
private Integer age;
private Address address;//地址信息
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(Integer id, String name, Integer age) {
super();
this.id = id;
this.name = name;
this.age = age;
}
public Student(String name, Integer age) {
super();
this.name = name;
this.age = age;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + ", address=" + address + "]";
}
}
Address.java
package com.zhaoyh.model;
public class Address {
private Integer id;
private String sheng;
private String shi;
private String qu;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getSheng() {
return sheng;
}
public void setSheng(String sheng) {
this.sheng = sheng;
}
public String getShi() {
return shi;
}
public void setShi(String shi) {
this.shi = shi;
}
public String getQu() {
return qu;
}
public void setQu(String qu) {
this.qu = qu;
}
@Override
public String toString() {
return "Address [id=" + id + ", sheng=" + sheng + ", shi=" + shi + ", qu=" + qu + "]";
}
}
Student接口方法
package com.zhaoyh.mappers;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.zhaoyh.model.Student;
public interface StudentMapper {
@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.zhaoyh.mappers.AddressMapper.findById"))
})
public Student findStudentWithAddress(int id);
}
Address接口方法
package com.zhaoyh.mappers;
import org.apache.ibatis.annotations.Select;
import com.zhaoyh.model.Address;
public interface AddressMapper {
@Select("select * from t_address where id=#{id}")
public Address findById(Integer id);//根据主键查询
}
JUnitTest测试类
package com.zhaoyh.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.zhaoyh.mappers.StudentMapper;
import com.zhaoyh.model.Student;
import com.zhaoyh.service.StudentService;
import com.zhaoyh.util.SqlSessionFactoryUtil;
/**
* @author Administrator
*
*/
public class StudentServiceTest {
private static Logger log = Logger.getLogger(StudentService.class);
private SqlSession sqlSession = null;
private StudentMapper studentMapper = null;
/**
* 测试方法前调用
*
*/
@Before
public void setUp() throws Exception {
sqlSession = SqlSessionFactoryUtil.openSession();
studentMapper = sqlSession.getMapper(StudentMapper.class);
}
/**
* 测试方法前调用
*
*/
@After
public void tearDown() throws Exception {
sqlSession.close();
}
@Test
public void testFindStudentWithAddress(){
log.info("一对一映射:学生信息包含地址信息...");
Student student = studentMapper.findStudentWithAddress(2);
System.out.println("Student="+student);
}
}
扫一扫
658
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
