MyBatis中的事务以及动态SQL语句
1.什么是事务?
事务(Transaction),一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元( unit) 。事务应该具有4 个属性:原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
1 、原子性。一个事务是一个不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做。
2 、一致性。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
3 、隔离性。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
4 、持久性。指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
2.MyBatis中的事务
1. 当我们在增删改操作时, 如果不进行事务提交, 数据库中的数据是不会发生变化的, 由于查询不会改变数据库,是不用进行提交事务的.
package org. best. dao;
import org. best. bean. User;
import java. util. List;
public interface UserDao {
void deleteById ( Integer id) ;
}
< ? 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= "org.best.dao.UserDao" >
< ! -- id为方法名 resultType 为返回值类型 parameterType为参数类型 -- >
< delete id= "deleteById" parameterType= "java.lang.Integer" >
delete from user where id= #{ id}
< / delete>
< / mapper>
import org. apache. ibatis. io. Resources;
import org. apache. ibatis. session. SqlSession;
import org. apache. ibatis. session. SqlSessionFactory;
import org. apache. ibatis. session. SqlSessionFactoryBuilder;
import org. best. bean. User;
import org. best. dao. UserDao;
import org. junit. After;
import org. junit. Before;
import org. junit. Test;
import java. io. IOException;
import java. io. InputStream;
import java. util. List;
public class TestUser {
private InputStream resourceAsStream;
private SqlSession sqlSession;
@Before
public void init ( ) throws IOException {
resourceAsStream = Resources. getResourceAsStream ( "SqlMapperConfig.xml" ) ;
SqlSessionFactory build = new SqlSessionFactoryBuilder ( ) . build ( resourceAsStream) ;
sqlSession = build. openSession ( ) ;
}
@Test
public void TestDelete ( ) {
UserDao mapper = sqlSession. getMapper ( UserDao. class ) ;
mapper. deleteById ( 42 ) ;
sqlSession. commit ( ) ;
}
@After
public void destroy ( ) throws IOException {
resourceAsStream. close ( ) ;
sqlSession. close ( ) ;
}
}
3.if标签
package org. best. dao;
import org. best. bean. User;
import java. util. List;
public interface UserDao {
List< User> findLike ( User user) ;
}
< ? 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= "org.best.dao.UserDao" >
< select id= "findLike" parameterType= "user" resultType= "user" >
select * from user where 1 = 1
< if test= "username!=null and username!=''" >
and username like #{ username}
< / if >
< if test= "address!=null and address!=''" >
and address like #{ address}
< / if >
< / select>
< / mapper>
import org. apache. ibatis. io. Resources;
import org. apache. ibatis. session. SqlSession;
import org. apache. ibatis. session. SqlSessionFactory;
import org. apache. ibatis. session. SqlSessionFactoryBuilder;
import org. best. bean. User;
import org. best. dao. UserDao;
import org. junit. After;
import org. junit. Before;
import org. junit. Test;
import java. io. IOException;
import java. io. InputStream;
import java. util. List;
public class TestUser {
private InputStream resourceAsStream;
private SqlSession sqlSession;
@Before
public void init ( ) throws IOException {
resourceAsStream = Resources. getResourceAsStream ( "SqlMapperConfig.xml" ) ;
SqlSessionFactory build = new SqlSessionFactoryBuilder ( ) . build ( resourceAsStream) ;
sqlSession = build. openSession ( ) ;
}
@Test
public void TestFindLike ( ) {
UserDao mapper = sqlSession. getMapper ( UserDao. class ) ;
User user = new User ( ) ;
user. setAddress ( "%西%" ) ;
user. setUsername ( "%沈%" ) ;
List< User> like = mapper. findLike ( user) ;
for ( User user1 : like) {
System. out. println ( user1) ;
}
}
@After
public void destroy ( ) throws IOException {
resourceAsStream. close ( ) ;
sqlSession. close ( ) ;
}
}
4.foreach标签
package org. best. dao;
import org. best. bean. User;
import java. util. List;
public interface UserDao {
List< User> findByIds ( List< Integer> list) ;
}
< ? 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= "org.best.dao.UserDao" >
< select id= "findByIds" resultType= "user" parameterType= "java.util.List" >
select * from user where 1 = 1
< if test= "list.size>0" >
foreach标签中的属性说明:
collection: 代表要遍历的集合元素,注意编写时不要写#{ }
open: 代表语句的开始部分
close: 代表结束部分
item:当前遍历的集合中的元素
separator:分隔符
< foreach collection= "list" open= "and id in(" close= ")" item= "id" separator= "," >
#{ id}
< / foreach>
< / if >
< / select>
< / mapper>
import org. apache. ibatis. io. Resources;
import org. apache. ibatis. session. SqlSession;
import org. apache. ibatis. session. SqlSessionFactory;
import org. apache. ibatis. session. SqlSessionFactoryBuilder;
import org. best. bean. User;
import org. best. dao. UserDao;
import org. junit. After;
import org. junit. Before;
import org. junit. Test;
import java. io. IOException;
import java. io. InputStream;
import java. util. ArrayList;
import java. util. List;
public class TestUser {
private InputStream resourceAsStream;
private SqlSession sqlSession;
@Before
public void init ( ) throws IOException {
resourceAsStream = Resources. getResourceAsStream ( "SqlMapperConfig.xml" ) ;
SqlSessionFactory build = new SqlSessionFactoryBuilder ( ) . build ( resourceAsStream) ;
sqlSession = build. openSession ( ) ;
}
@Test
public void TestFindIds ( ) {
UserDao mapper = sqlSession. getMapper ( UserDao. class ) ;
List< Integer> list = new ArrayList < Integer> ( ) ;
list. add ( 41 ) ;
list. add ( 55 ) ;
List< User> byIds = mapper. findByIds ( list) ;
for ( User byId : byIds) {
System. out. println ( byId) ;
}
}
@After
public void destroy ( ) throws IOException {
resourceAsStream. close ( ) ;
sqlSession. close ( ) ;
}
}
5.set标签
package org. best. dao;
import org. best. bean. User;
import java. util. List;
public interface UserDao {
void update ( User user) ;
}
< ? 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= "org.best.dao.UserDao" >
< update id= "update" parameterType= "user" >
update user
< set>
< if test= "username!=null and username!=''" >
username = #{ username} ,
< / if >
< if test= "address!=null and address !=''" >
address= #{ address} ,
< / if >
< / set>
where 1 = 1
< if test= "id!=null and id!=''" >
and id= #{ id}
< / if >
< / update>
< / mapper>
import org. apache. ibatis. io. Resources;
import org. apache. ibatis. session. SqlSession;
import org. apache. ibatis. session. SqlSessionFactory;
import org. apache. ibatis. session. SqlSessionFactoryBuilder;
import org. best. bean. User;
import org. best. dao. UserDao;
import org. junit. After;
import org. junit. Before;
import org. junit. Test;
import java. io. IOException;
import java. io. InputStream;
import java. util. ArrayList;
import java. util. List;
public class TestUser {
private InputStream resourceAsStream;
private SqlSession sqlSession;
@Before
public void init ( ) throws IOException {
resourceAsStream = Resources. getResourceAsStream ( "SqlMapperConfig.xml" ) ;
SqlSessionFactory build = new SqlSessionFactoryBuilder ( ) . build ( resourceAsStream) ;
sqlSession = build. openSession ( ) ;
}
@Test
public void TestUpdate ( ) {
UserDao mapper = sqlSession. getMapper ( UserDao. class ) ;
User user = new User ( ) ;
user. setUsername ( "李哥" ) ;
user. setId ( 43 ) ;
mapper. update ( user) ;
sqlSession. commit ( ) ;
}
@After
public void destroy ( ) throws IOException {
resourceAsStream. close ( ) ;
sqlSession. close ( ) ;
}
}