Mybatis
1.建立项目
源码 下载地址:http://download.csdn.net/detail/dingsai88/8438185
参考资源:http://mybatis.github.io/mybatis-3/zh/index.html
2.建库
语句在源码SQL 文件夹下数据库mysql
createdatabase mybatis;
usemybatis;
createtable users(id int primary key auto_increment,name varchar(20),age int);
insertinto users(name,age)values('tom',12);
insertinto users(name,age)values('jack',11);
3.建配置文件
<?xml version="1.0"encoding="UTF-8"?>
<!DOCTYPEconfiguration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--
开发模式:developmen
工作模式:work
-->
<environmentsdefault="development">
<environmentid="development">
<transactionManagertype="JDBC" />
<!-- POOLED – 这是 JDBC 连接对象的数据源连接池的实现,用来避免创建新的连接实例
时必要的初始连接和认证时间。这是一种当前 Web 应用程序用来快速响应请求很流行的方
法。-->
<dataSourcetype="POOLED">
<propertyname="driver" value="com.mysql.jdbc.Driver" />
<propertyname="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<propertyname="username" value="root" />
<propertyname="password" value="1111" />
</dataSource>
</environment>
</environments>
</configuration>
4.建立userMapper.xml
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPEmapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mappernamespace="com.ding.test.User.userMapper">
<selectid="getUser"parameterType="int"
resultType="com.ding.test.User">
select * from users whereid=#{id}
</select>
</mapper>
5.Test
String resource ="conf.xml";
//加载mybatis的配置文件(它也加载关联的映射文件)
Reader reader = Resources.getResourceAsReader(resource);
//构建sqlSession的工厂
SqlSessionFactory sessionFactory = newSqlSessionFactoryBuilder().build(reader);
//创建能执行映射文件中sql的 sqlSession
SqlSession session = sessionFactory.openSession();
//映射sql的标识字符串
String statement ="com.ding.test.User.userMapper"+".selectUser";
//执行查询返回一个唯一 user对象的sql
User user = session.selectOne(statement, 1);
System.out.println(user);
6.CRUD
userMapper.xml
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPEmapperPUBLIC "-//mybatis.org//DTDMapper3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mappernamespace="com.ding.test.User.userMapper">
<insertid="addUser"parameterType="com.ding.test.User">
insert into users(name,age)values(#{name},#{age})
</insert>
<deleteid="deleteUser"parameterType="int">
delete from users where id=#{id}
</delete>
<updateid="updateUser"parameterType="com.ding.test.User">
update users set name=#{name},age=#{age} where id=#{id}
</update>
<!-- parameterType是传入参数类型 ;resultType返回结果集类型-->
<selectid="getUser"parameterType="int"resultType="com.ding.test.User">
select *
from users where id=#{id}
</select>
<selectid="getAllUser"resultType="com.ding.test.User">
select * from users
</select>
</mapper>
Test2.java
package com.ding.test2;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Ignore;
import org.junit.Test;
import com.ding.test.User;
import com.ding.util.MybatisUtil;
public class Test2 {
/**
* 测试insert方法
*/
@Test
@Ignore
// 忽视
public void testAdd() {
SqlSessionFactory factory = MybatisUtil.getFactory();
// 默认是手动提交事务
// SqlSession session =factory.openSession();// session.commit();
// 是否自动提交
SqlSession session = factory.openSession(true);
//com.ding.test.User.userMapper是userMapper文件的的namespace命名空间
// .addUser是命名空间下的ID
String statment = "com.ding.test.User.userMapper.addUser";
int insert = session.insert(statment,new User(-1,"dingsai3", 23));
session.close();
System.out.println("insert:" + insert);
}
@Test
@Ignore
public void testUpdate() {
SqlSessionFactory factory = MybatisUtil.getFactory();
SqlSession session = factory.openSession(true);
String statement = "com.ding.test.User.userMapper.updateUser";
int update = session.update(statement,
new User(4,"dingsaiUpdate4", 11));
session.close();
System.out.println("update:" + update);
}
@Test
@Ignore
public void testDelete() {
SqlSessionFactory factory = MybatisUtil.getFactory();
SqlSession session = factory.openSession(true);
int delete = session.delete("com.ding.test.User.userMapper.deleteUser",1);
session.close();
System.out.println("delete:" + delete);
}
@Test
@Ignore
public void testGetUser() {
SqlSessionFactory factory = MybatisUtil.getFactory();
SqlSession session = factory.openSession(true);
User user = session.selectOne("com.ding.test.User.userMapper.getUser",2);
session.close();
System.out.println("user:" + user);
}
@Test
@Ignore
public void testGetAllUser(){
SqlSessionFactory factory=MybatisUtil.getFactory();
SqlSession session=factory.openSession(true);
List<User> users=session.selectList("com.ding.test.User.userMapper.getAllUser", 1);
for(User user:users){
System.out.println("AllUser:" + user);
}
}
}
7.基于annotation的CRUD
UserMapperAnnation.java
packagecom.ding.test2;
importjava.util.List;
importorg.apache.ibatis.annotations.Delete;
importorg.apache.ibatis.annotations.Insert;
importorg.apache.ibatis.annotations.Select;
importorg.apache.ibatis.annotations.Update;
importcom.ding.test.User;
/**
* 基于注解的mapper接口
* @author Daniel
*
*/
publicinterface UserMapperAnnotation {
@Insert("insert into users(name,age)values(#{name},#{age})")
public int addUser(User user);
@Delete("delete from users whereid=#{id}")
public int deleteById(int id);
@Update("update users setname=#{name},age=#{age} where id=#{id}")
public int update(User user);
@Select("select * from users whereid=#{id}")
public User getById(int id);
@Select("select * from users ")
public List<User> getAll();
}
TestAnnotation.java
packagecom.ding.test2;
importjava.util.List;
importorg.apache.ibatis.session.SqlSession;
importorg.apache.ibatis.session.SqlSessionFactory;
importorg.junit.Ignore;
importorg.junit.Test;
importcom.ding.test.User;
importcom.ding.util.MybatisUtil;
/**
* 测试基于注解的mybatis
*
* @author Daniel
*
*/
publicclass TestAnnotation {
/**
* 测试insert方法
*/
@Test
@Ignore
// 忽视
public void testAdd() {
SqlSessionFactory factory =MybatisUtil.getFactory();
// 默认是手动提交事务
// SqlSession session =factory.openSession();// session.commit();
// 是否自动提交
SqlSession session =factory.openSession(true);
//反射生成UserMapperAnnotation对象
UserMapperAnnotation mapper = session
.getMapper(UserMapperAnnotation.class);
int insert = mapper.addUser(new User(-1,"dingsai6", 66));
session.close();
System.out.println("insert:" +insert);
}
@Test
@Ignore
public void testUpdate() {
SqlSessionFactory factory =MybatisUtil.getFactory();
SqlSession session =factory.openSession(true);
UserMapperAnnotation mapper = session
.getMapper(UserMapperAnnotation.class);
int update = mapper.update(new User(6,"dingsaiupdaete", 56));
session.close();
System.out.println("update:" +update);
}
@Test
@Ignore
public void testDelete() {
SqlSessionFactory factory =MybatisUtil.getFactory();
SqlSession session =factory.openSession(true);
UserMapperAnnotation mapper = session
.getMapper(UserMapperAnnotation.class);
int delete = mapper.deleteById(5);
session.close();
System.out.println("delete:" +delete);
}
@Test
@Ignore
public void testGetUser() {
SqlSessionFactory factory =MybatisUtil.getFactory();
SqlSession session =factory.openSession(true);
UserMapperAnnotation mapper = session
.getMapper(UserMapperAnnotation.class);
User user = mapper.getById(6);
session.close();
System.out.println("user:" +user);
}
@Test
public void testGetAllUser() {
SqlSessionFactory factory =MybatisUtil.getFactory();
SqlSession session =factory.openSession(true);
UserMapperAnnotation mapper = session
.getMapper(UserMapperAnnotation.class);
List<User> users = mapper.getAll();
for (User user : users) {
System.out.println("AllUser:"+ user);
}
}
}
Con.xml
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPEconfigurationPUBLIC"-//mybatis.org//DTDConfig 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environmentsdefault="development">
<environmentid="development">
<transactionManagertype="JDBC"/>
<dataSourcetype="POOLED">
<propertyname="driver"value="com.mysql.jdbc.Driver"/>
<propertyname="url"value="jdbc:mysql://localhost:3306/mybatis"/>
<propertyname="username"value="root"/>
<propertyname="password"value="111"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 导入xml映射 -->
<mapperresource="com/ding/test2/userMapper.xml"/>
<!-- 导入类映射 -->
<mapperclass="com.ding.test2.UserMapperAnnotation"/>
</mappers>
</configuration>
8.添加DTD让XML自动完成提示
<!DOCTYPEmapper PUBLIC "-//mybatis.org//DTDMapper3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
Preferences --XML catalog-- Add
location=mybatis-3-mapper.dtd
key=-//mybatis.org//DTDMapper3.0//EN
9.数据库字段和bean字段不对应,修改别名
语句别名
<!-- 别名对应bean和xml -->
<select id="getOrder" parameterType="int" resultType="Order">
select order_id id, order_noorderNo,order_price price from orders where order_id=#{id}
</select>
Order类
private Integer id;
private String orderNo;
private float price;
xml映射别名
注意返回值变成resultMap类型
<mappernamespace="com.ding.bean.orderMapper">
<!--配置字段属性映射-->
<resultMap id="orderResultMap"type="Order">
<id property="id"column="order_id" />
<result property="orderNo"column="order_no" />
<result property="price"column="order_price" />
</resultMap>
<!-- 调用映射map -->
<select id="getOrderMap"parameterType="int" resultMap="orderResultMap">
select *
from orders where order_id=#{id}
</select>
</mapper>
10.联表查询一对一association:
1.联表查询一对一(1)
通过resultMap映射sql语句结果集为Bean
select* from class c ,teacher t where c.teacher_id=t.t_id and c.c_id=1;
Classes.java
private int id;
private Stringname;
private Teacher teacher;
Teacher.java
private int id;
private String name;
mysql>show create table teacher \G
***************************1. row ***************************
Table: teacher
CreateTable: CREATE TABLE `teacher` (
`t_id` int(11) NOT NULL auto_increment,
`t_name` varchar(20) default NULL,
PRIMARY KEY (`t_id`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql>show create table class \G
***************************1. row ***************************
Table: class
CreateTable: CREATE TABLE `class` (
`c_id` int(11) NOT NULL auto_increment,
`c_name` varchar(20) default NULL,
`teacher_id` int(11) default NULL,
PRIMARY KEY (`c_id`),
KEY `fk_teacher_id` (`teacher_id`),
CONSTRAINT `fk_teacher_id` FOREIGN KEY(`teacher_id`) REFERENCES `teacher` (`t
_id`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1
<!--一对一返回结果集1.一条语句 ;resultMap 通过association把Teacher类映射成Classes类的属性-->
<resultMap type="Classes"id="getClassMap">
<id property="id"column="c_id" />
<result property="name"column="c_name" />
<!-- 类属性是Teacher类association联合社团 -->
<associationproperty="teacher" javaType="Teacher">
<id property="id"column="t_id" />
<result property="name"column="t_name" />
</association>
</resultMap>
<!-- 返回resultMap类型 -->
<select id="getClass" parameterType="int"resultMap="getClassMap">
SELECT *
FROM class c ,teacher t WHEREc.teacher_id=t.t_id AND c.c_id=#{id};
</select>
2.联表查询一对一(2)
把第一条语句的结果当成条件传给第二条语句,第二条语句的结果集通过resultMap映射给 bean的属性类
<!--一对一返回结果集2.两条语句;把第一条语句的结果通过association传给另一条语句作为参数;还是通过association映射成Classes类-->
<select id="getClass2"parameterType="int" resultMap="getClass2Map">
SELECT * FROM class WHERE c_id=#{id}
</select>
<!-- 映射出返回的结果集Classes类-->
<resultMap id="getClass2Map"type="Classes" >
<id property="id"column="c_id" />
<result property="name"column="c_name" />
<!-- 把第一条语句返回的teacher_id字段的值,当成条件传给getTeacher语句,返回的结果注入给Classes类的teacher属性 -->
<associationproperty="teacher" column="teacher_id"
select="getTeacher">
</association>
</resultMap>
<!-- 第二条语句,用于返回结果注入到Classes类的teacher属性 -->
<select id="getTeacher" parameterType="int"resultType="Teacher">
SELECT t_id id , t_name name FROMteacher WHERE t_id=#{id}
</select>
字段说明:
association:用于一对一的关联查询
property:对象属性的名称
javaType:对象属性的类型
column:所对应的数据库字段名
select:另一个查询的结果集
11.联表查询一对多
1.联表查询一对多(1)
Classes.java
private int id;
private Stringname;
private Teacherteacher;
privateList<Student> students;
oneToManyMapper.xml
<!--One To Many1 一条语句的映射 -->
<select id="oneToMany1"parameterType="int" resultMap="getClassesMapOneToMany">
SELECT *
FROM class c, student s ,teacher t WHEREc.teacher_id=t.t_id AND
c.c_id=s.class_id AND c.c_id=#{id};
</select>
<resultMap type="Classes"id="getClassesMapOneToMany">
<id property="id"column="c_id" />
<result property="name"column="c_name" />
<associationproperty="teacher" javaType="Teacher">
<id property="id"column="t_id" />
<result property="name"column="t_name" />
</association>
<collection property="students"ofType="Student">
<id property="id"column="s_id" />
<result property="name"column="s_name" />
</collection>
</resultMap>
1.联表查询一对多(2)
<!-- One To Many2 多条语句的映射 -->
<select id="oneToMany2"parameterType="int" resultMap="getClassMapOneToMany2">
SELECT * FROM class WHERE c_id=#{id};
</select>
<resultMap type="Classes"id="getClassMapOneToMany2">
<id property="id"column="c_id" />
<result property="name"column="c_name" />
<associationproperty="teacher" select="getTeacher"
column="teacher_id" />
<collection property="students"select="getStudent"ofType="Student"column="c_id"></collection>
</resultMap>
<select id="getTeacher"parameterType="int" resultType="Teacher">
SELECT t_id id ,t_name name FROM teacher WHERE t_id=#{id};
</select>
<select id="getStudent"parameterType="int" resultType="Student">
SELECT s_id id,s_name name FROM student WHERE class_id=#{id};
</select>
12.动态模糊查询
User.java
private int id;
private String name;
ConditionUser.java
private Stringname;
private int minAge;
private int maxAge;
classMapperLike.xml
大于等于between and
Like用like
<select id="getUser" parameterType="ConditionUser"resultType="com.ding.bean.User">
select * from d_user where age between#{minAge} and #{maxAge}
<iftest="name!=null">
and name like #{name}
</if>
</select>
调用:
"com.ding.bean.classMapperLike.getUser",new ConditionUser("%Tom%",0, 50));
choose,when, otherwise,trim, where, set 等在:
http://mybatis.github.io/mybatis-3/zh/dynamic-sql.html
13.调用存储过程
#创建存储过程(查询得到男性或女性的数量, 如果传入的是 0 就女性否则是男性)
DELIMITER$
CREATEPROCEDURE mybatis.ges_user_count(IN sex_id INT, OUT user_count INT)
BEGIN
IFsex_id=0 THEN
SELECTCOUNT(*) FROM mybatis.p_user WHERE p_user.sex='女' INTO user_count;
ELSE
SELECTCOUNT(*) FROM mybatis.p_user WHERE p_user.sex='男' INTO user_count;
ENDIF;
END
$
#调用存储过程
DELIMITER;
SET@user_count = 0;
CALLmybatis.ges_user_count(1, @user_count);
SELECT@user_count;
procedureMapper.xml配置:
传入parameterMap参数 需要statementType=”CALLABLE”
<!-- 调用存储过程procedure -->
<select id="getUser" parameterMap="getUserCountMap"statementType="CALLABLE">
CALLmybatis.ges_user_count(?,?);
</select>
<parameterMap type="java.util.Map"id="getUserCountMap">
<parameter property="sexid" mode="IN" jdbcType="INTEGER" />
<parameter property="usercount" mode="OUT" jdbcType="INTEGER" />
</parameterMap>
调用方式:放入map的需要和配置的parameterMap定义的一样
Map<String,Integer> parameterMap=newHashMap<String,Integer>();
//传入的参数
parameterMap.put("sexid",1);
//返回值参数
parameterMap.put("usercount",-1);
session.selectList(
"com.ding.bean.classMapperProcedure.getUser",parameterMap);
session.close();
System.out.println(parameterMap.get("usercount"));
14.缓存
cacheMapper.xml
一级缓存:
默认开启:session级别
更新情况:
1.参数值改变,2.调用session.clearCache();3.调用增删改CUD 4.不是同一个session对象不会用一级缓存
<mappernamespace="com.ding.bean.cache.cacheMapper">
<!--开启二级缓存-->
<cache/>
<!—一级缓存默认开启 -->
<select id="getUser"parameterType="int"resultType="com.ding.bean.cache.CUser">
select * from c_user where id=#{id}
</select>
<update id="updateUser"parameterType="com.ding.bean.cache.CUser">
update c_user set
name=#{name}, age=#{age} where id=#{id}
</update>
</mapper>
调用:
/**
* 一级缓存:session级别 默认开启
*
* 1.参数值改变,2.调用session.clearCache();3.调用增删改CUD
*/
@Test
@Ignore
public void testOneCache() {
SqlSessionFactory factory =MybatisUtil.getFactory();
SqlSession session =factory.openSession(true);
CUser user = session.selectOne(
"com.ding.bean.cache.cacheMapper.getUser",1);
System.out.println(user);
// 1.参数值改变查询语句只执行一次,第二次不会执行,直接调用session里的值
// user =
//session.selectOne("com.ding.bean.cache.cacheMapper.getUser",2);
// 2.调用清理cache
// session.clearCache();
// 3.调用增删改 cud
//session.update("com.ding.bean.cache.cacheMapper.updateUser", new
// CUser(1,"TOM update",33));
// 4.不是同一个session对象不会用一级缓存
// SqlSession session1 =factory.openSession(true);
// user =
//session1.selectOne("com.ding.bean.cache.cacheMapper.getUser",1);
user =session.selectOne("com.ding.bean.cache.cacheMapper.getUser", 1);
System.out.println(user);
}
二级缓存:
在映射文件里加入:
<cache/>
/**
* 二级缓存:对应映射文件级别sessionFactory级别
*/
@Test
public void testTwoCache() {
SqlSessionFactory factory =MybatisUtil.getFactory();
SqlSession session =factory.openSession(true);
SqlSession session1 =factory.openSession();
CUser user = session.selectOne(
"com.ding.bean.cache.cacheMapper.getUser",1);
System.out.println(user);
//必须commit不然不会命中二级缓存
session.commit();
// 4.不是同一个session对象不会用一级缓存 Cache Hit Ratio[com.ding.bean.cache.cacheMapper]: 0.5
user =session1.selectOne("com.ding.bean.cache.cacheMapper.getUser", 1);
System.out.println("TEST:" +user);
}
不同的sqlSession会有缓存,session.commit增加缓存命中率
二级缓存其他配置:
http://mybatis.github.io/mybatis-3/zh/sqlmap-xml.html#cache