八、调用存储过程
8.1 . 提出需求:
查询得到男性或女性的数量, 如果传入的是0 就女性否则是男性
8.2 . 准备数据库表和存储过程:
create table p_user(
id int primary key auto_increment,
name varchar(10 ),
sex char (2 )
);
insert into p_user(name,sex) values('赵英龙' ,"男" );
insert into p_user(name,sex) values('杨帆' ,"男" );
insert into p_user(name,sex) values('王爽' ,"女" );
#创建存储过程(查询得到男性或女性的数量, 如果传入的是0就女性否则是男性)
DELIMITER $
CREATE PROCEDURE mybatis.get_user_count(IN sex_id INT, OUT user_count INT)
BEGIN
IF sex_id=0 THEN
SELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex='女' INTO user_count;
ELSE
SELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex='男' INTO user_count;
END IF;
END
$
#调用存储过程
DELIMITER ;
SET @user_count = 0 ;
CALL mybatis.get_user_count(1 , @user_count);
SELECT @user_count;
8.3 . 创建表的实体类
public class User {
private String id;
private String name;
private String sex;
}
8.4 创建保存输入参数和输出参数的实体类
package com.vo;
public class ProcedurePOJO {
private int sex;
private int userCount;
public int getUserCount () {
return userCount;
}
public void setUserCount (int userCount) {
this .userCount = userCount;
}
public int getSex () {
return sex;
}
public void setSex (int sex) {
this .sex = sex;
}
}
8.5 . userMapper.xml
<mapper namespace="com.po.userMapper" >
<!--调用完毕后返回值回自动回填到pojo中的userCount属性里-->
<select id="getCount" statementType="CALLABLE" parameterType="com.vo.ProcedurePOJO" >
{call get_user_count(
#{sex,mode=IN,jdbcType=INTEGER},
#{userCount,mode=OUT,jdbcType=INTEGER}
)}
</select >
</mapper>
statementType取值可以是STATEMENT,PREPARED 或 CALLABLE 的一种。这会让 MyBatis 使用选择使用 Statement,PreparedStatement 或 CallableStatement。默认值:PREPARED。
8.5 . 测试调用:
public void executeProcedure () {
try {
String statement = "com.po.userMapper.getCount" ;
ProcedurePOJO procedurePOJO = new ProcedurePOJO();
procedurePOJO.setSex(0 );
sqlSession.selectOne(statement, procedurePOJO);
System.out .println(procedurePOJO.getUserCount());
} finally {
SessionFactoryUtil.closeSqlSession(sqlSession);
}
}
九、Mybatis缓存
9.1 . 理解MyBatis缓存
正如大多数持久层框架一样,MyBatis 同样提供了一级缓存和二级缓存的支持
1. 一级缓存: 基于PerpetualCache 的 HashMap本地缓存,其存储作用域为Session,当Session flush 或 close之后,该Session中的所有 Cache 就将清空。
2. 二级缓存与一级缓存其机制相同,默认也是采用 PerpetualCache,HashMap存储,不同在于其存储作用域为Mapper(Namespace),并且可自定义存储源,如 Ehcache。
3. 对于缓存数据更新机制,当某一个作用域(一级缓存Session/二级缓存Namespaces)的进行了 C/U/D 操作后,默认该作用域下所有 select 中的缓存将被clear。
9.2 . Mybatis一级缓存
1 ) 提出需求:
根据id查询对应的用户记录对象
2 ). 准备数据库表和数据
CREATE TABLE c_user(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20 ),
age INT
);
insert into c_user(name, age) values('赵英龙' , 22 );
insert into c_user(name, age) values('杨帆' , 23 );
3 ). 创建表的实体类
public class User implements Serializable{
private int id;
private String name;
private int age;
}
4 ). 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" >
<!-- namespace 是该mapper文件的一个唯一表示 -->
<mapper namespace="com.po.userMapper" >
<select id="getUser" parameterType="int" resultType="User" >
select * from c_user where id=#{id}
</select >
<update id="updateUser" parameterType="User" >
update c_user set
name=#{name}, age=#{age} where id=#{id}
</update>
</mapper>
5 ). 测试:
public User testCache () {
User user = null ;
String statement = "com.po.userMapper.getUser" ;
try {
user = sqlSession.selectOne(statement, 1 );
System.out .println(user);
System.out .println("****************************" );
user = sqlSession.selectOne(statement, 1 );
System.out .println(user);
} finally {
SessionFactoryUtil.closeSqlSession(sqlSession);
}
return user;
}
public void testCache1 () {
User user = null ;
User user2 = null ;
String statement = "com.po.userMapper.getUser" ;
String statement2 = "com.po.userMapper.updateUser" ;
try {
user = sqlSession.selectOne(statement, 1 );
user2 = sqlSession.selectOne(statement, 2 );
System.out .println(user);
User updatedUser = new User();
updatedUser.setId(1 );
updatedUser.setName("赵小龙" );
updatedUser.setAge(30 );
sqlSession.update(statement2,updatedUser);
System.out .println("*****************************" );
user2 = sqlSession.selectOne(statement, 2 );
System.out .println(user2);
} finally {
sqlSession.close();
}
}
9.3 . Mybatis二级缓存
Mapper(namespace)级别的缓存
1 ). 添加一个<cache>在userMapper.xml中
<mapper namespace="com.vo.userMapper" >
<cache/>
2 ). 测试
@Override
public User getUserById (Integer userid) {
User user = null ;
String statement = "com.po.userMapper.getUser" ;
try {
user = sqlSession.selectOne(statement, userid);
} finally {
SessionFactoryUtil.closeSqlSession(sqlSession);
}
return user;
}
public static void main (String[] args) {
UserDaoImpl dao = new UserDaoImpl();
User user = dao.getUserById(1 );
System.out .println(user);
System.out .println("**********************" );
UserDaoImpl dao2 = new UserDaoImpl();
User user2 = dao2.getUserById(1 );
System.out .println(user2);
}
注意:
1. 必须关闭session二级缓存才生效,关闭的时候把session中的数据保存到二级缓存中
2. User类必须实现Serializable接口
3 ). 补充说明
1. 映射语句文件中的所有select 语句将会被缓存。
2. 映射语句文件中的所有insert,update和delete语句会刷新缓存。
3. 缓存会使用Least Recently Used(LRU,最近最少使用的)算法来收回。
4. 缓存会根据指定的时间间隔来刷新。
5. 缓存会存储1024 个对象
<cache
eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true" />
十、spring集成mybatis
10.1. 添加Jar包
【mybatis】
mybatis-3.2.0.jar
mybatis-spring-1.1.1.jar
log4j-1.2.17.jar
【spring】
spring-aop-3.2.0.RELEASE.jar
spring-beans-3.2.0.RELEASE.jar
spring-context-3.2.0.RELEASE.jar
spring-core-3.2.0.RELEASE.jar
spring-expression-3.2.0.RELEASE.jar
spring-jdbc-3.2.0.RELEASE.jar
spring-test-3.2.4.RELEASE.jar
spring-tx-3.2.0.RELEASE.jar
aopalliance-1.0.jar
cglib-nodep-2.2.3.jar
commons-logging-1.1.1.jar
【MYSQL驱动包】
mysql-connector-java-5.0.4-bin.jar
10.2. 数据库表
CREATE TABLE s_user(
user_id INT AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(30),
user_birthday DATE,
user_salary DOUBLE
)
10.3. 实体类: User
public class User {
private int id;
private String name;
private Date birthday;
private double salary;
//set,get方法
}
10.4. DAO接口: UserMapper (XXXMapper)
public interface UserMapper {
public void save(User user);
public void update(User user);
public void delete(int id);
public User findById(int id);
public List<User > findAll();
}
10.5. SQL映射文件: 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">
<!—namespace 一定要和接口的类全名一致-- >
<mapper namespace ="com.lanqiao.mybatis.test9.UserMapper" >
<resultMap type ="User" id ="userResult" >
<result column ="user_id" property ="id" />
<result column ="user_name" property ="name" />
<result column ="user_birthday" property ="birthday" />
<result column ="user_salary" property ="salary" />
</resultMap >
<insert id ="save" keyColumn ="user_id" keyProperty ="id" useGeneratedKeys ="true" >
insert into s_user(user_name,user_birthday,user_salary)
values(#{name},#{birthday},#{salary})
</insert >
<update id ="update" >
update s_user
set user_name = #{name},
user_birthday = #{birthday},
user_salary = #{salary}
where user_id = #{id}
</update >
<delete id ="delete" >
delete from s_user
where user_id = #{id}
</delete >
<select id ="findById" resultMap ="userResult" >
select *
from s_user
where user_id = #{id}
</select >
<select id ="findAll" resultMap ="userResult" >
select *
from s_user
</select >
</mapper >
10.6. spring的配置文件: beans.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns ="http://www.springframework.org/schema/beans"
xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p ="http://www.springframework.org/schema/p"
xmlns:context ="http://www.springframework.org/schema/context"
xmlns:tx ="http://www.springframework.org/schema/tx"
xsi:schemaLocation ="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.2.xsd" >
<bean id ="dataSource"
class ="org.springframework.jdbc.datasource.DriverManagerDataSource" >
<property name ="driverClassName" value ="com.mysql.jdbc.Driver" />
<property name ="url" value ="jdbc:mysql://localhost:3306/mybatis" />
<property name ="username" value ="root" />
<property name ="password" value ="root" />
</bean >
<bean id ="sqlSessionFactory" class ="org.mybatis.spring.SqlSessionFactoryBean" >
<property name ="dataSource" ref ="dataSource" />
<property name ="typeAliasesPackage" value ="com.atuigu.spring_mybatis2.domain" />
</bean >
<bean class ="org.mybatis.spring.mapper.MapperScannerConfigurer" >
<property name ="basePackage" value ="com.atuigu.spring_mybatis2.mapper" />
<property name ="sqlSessionFactory" ref ="sqlSessionFactory" />
</bean >
<bean id ="txManager" class ="org.springframework.jdbc.datasource.DataSourceTransactionManager" >
<property name ="dataSource" ref ="dataSource" />
</bean >
<tx:annotation-driven transaction-manager ="txManager" />
</beans >
10.7. mybatis的配置文件: 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 >
</configuration >
10.8. 测试
@RunWith(SpringJUnit4ClassRunner.class) //使用Springtest测试框架
@ContextConfiguration("/beans.xml") //加载配置
public class SMTest {
@Autowired //注入
private UserMapper userMapper;
@Test
publicvoid save() {
User user = new User();
user.setBirthday(new Date());
user.setName("marry");
user.setSalary(300);
userMapper.save(user);
System.out.println(user.getId());
}
@Test
publicvoid update() {
User user = userMapper.findById(2);
user.setSalary(2000);
userMapper.update(user);
}
@Test
publicvoid delete() {
userMapper.delete(3);
}
@Test
publicvoid findById() {
User user = userMapper.findById(1);
System.out.println(user);
}
@Test
publicvoid findAll() {
List<User > users = userMapper.findAll();
System.out.println(users);
}
}