mybatis3 增删改查额

MyBatis3 之增删改查操作           

• 博客分类：
• MyIbatis

 

configuration.xml     ， MyBatis主配置文件

Java代码  

1. <?xml version="1.0" encoding="UTF-8"?>  
2. <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"   
3. "http://mybatis.org/dtd/mybatis-3-config.dtd">  
4.   
5. <!-- 注意：每个标签必须按顺序写，会提示错误：-->  
6. <configuration>  
7.     <!-- 属性配置 -->  
8.     <properties resource="jdbc.properties"/>  
9.       
10.     <!-- 设置缓存和延迟加载等等重要的运行时的行为方式 -->  
11.     <settings>  
12.         <!-- 设置超时时间，它决定驱动等待一个数据库响应的时间  -->  
13.         <setting name="defaultStatementTimeout" value="25000"/>  
14.     </settings>  
15.       
16.     <!-- 别名 -->  
17.     <typeAliases>  
18.         <typeAlias alias="User" type="com.mybatis.model.User"/>  
19.     </typeAliases>  
20.       
21.     <environments default="development">  
22.         <!-- environment 元素体中包含对事务管理和连接池的环境配置 -->  
23.         <environment id="development">  
24.             <transactionManager type="JDBC" />  
25.             <dataSource type="POOLED">  
26.                 <property name="driver" value="${driver}" />  
27.                 <property name="url" value="${url}" />  
28.                 <property name="username" value="${username}" />  
29.                 <property name="password" value="${password}" />  
30.             </dataSource>  
31.         </environment>  
32.     </environments>  
33.       
34.     <!-- ORM映射文件 -->  
35.     <mappers>  
36.         <mapper resource="com/mybatis/model/UserSqlMap.xml" />  
37.     </mappers>  
38. </configuration>   

<?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"/>
	
	<!-- 设置缓存和延迟加载等等重要的运行时的行为方式 -->
	<settings>
		<!-- 设置超时时间，它决定驱动等待一个数据库响应的时间  -->
		<setting name="defaultStatementTimeout" value="25000"/>
	</settings>
	
	<!-- 别名 -->
	<typeAliases>
		<typeAlias alias="User" type="com.mybatis.model.User"/>
	</typeAliases>
	
	<environments default="development">
		<!-- environment 元素体中包含对事务管理和连接池的环境配置 -->
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${driver}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	</environments>
	
	<!-- ORM映射文件 -->
	<mappers>
		<mapper resource="com/mybatis/model/UserSqlMap.xml" />
	</mappers>
</configuration> 

 jdbc.properties

Java代码  

1. driver=com.mysql.jdbc.Driver  
2. url=jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=UTF-8  
3. username=root  
4. password=123456  

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
username=root
password=123456

 log4j.properties

Java代码  

1. log4j.rootLogger=debug, stdout, R  
2.   
3. log4j.appender.stdout=org.apache.log4j.ConsoleAppender  
4. log4j.appender.stdout.layout=org.apache.log4j.PatternLayout  
5.   
6. log4j.appender.stdout.layout.ConversionPattern=%5p - %m%n  
7.   
8. log4j.appender.R=org.apache.log4j.RollingFileAppender  
9. log4j.appender.R.File=firestorm.log  
10.   
11. log4j.appender.R.MaxFileSize=100KB  
12. log4j.appender.R.MaxBackupIndex=1  
13.   
14. log4j.appender.R.layout=org.apache.log4j.PatternLayout  
15. log4j.appender.R.layout.ConversionPattern=%p %t %c - %m%n  
16.   
17. log4j.logger.com.codefutures=DEBUG  

log4j.rootLogger=debug, stdout, R

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout

log4j.appender.stdout.layout.ConversionPattern=%5p - %m%n

log4j.appender.R=org.apache.log4j.RollingFileAppender
log4j.appender.R.File=firestorm.log

log4j.appender.R.MaxFileSize=100KB
log4j.appender.R.MaxBackupIndex=1

log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%p %t %c - %m%n

log4j.logger.com.codefutures=DEBUG

 User .java

Java代码  

1. package com.mybatis.model;  
2.   
3. import java.io.Serializable;  
4.   
5. @SuppressWarnings("serial")  
6. public class User implements Serializable {  
7.       
8.     private int id;  
9.     private String userName;  
10.     private String password;  
11.       
12.     public User(){  
13.     }  
14.       
15.     public int getId() {  
16.         return id;  
17.     }  
18.     public void setId(int id) {  
19.         this.id = id;  
20.     }  
21.     public String getUserName() {  
22.         return userName;  
23.     }  
24.     public void setUserName(String userName) {  
25.         this.userName = userName;  
26.     }  
27.     public String getPassword() {  
28.         return password;  
29.     }  
30.     public void setPassword(String password) {  
31.         this.password = password;  
32.     }  
33.   
34. }  

package com.mybatis.model;

import java.io.Serializable;

@SuppressWarnings("serial")
public class User implements Serializable {
	
	private int id;
	private String userName;
	private String password;
	
	public User(){
	}
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}

}

 UserSqlMap.xml

Java代码  

1. <?xml version="1.0" encoding="UTF-8"?>  
2. <!DOCTYPE mapper       
3. PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"       
4. "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">       
5.      
6. <!-- namespace用于java代码调用时识别指定xml的mapper文件 -->  
7. <mapper namespace="com.mybatis.model.User">  
8.     <!-- 配置ORM映射 -->  
9.     <resultMap type="User" id="user_orm">  
10.         <id property="id" column="id"/>  
11.         <result property="userName" column="userName"/>  
12.         <result property="password" column="password"/>  
13.     </resultMap>  
14.       
15.     <!-- 用来定义可重用的SQL代码段 -->  
16.     <sql id="demo_sql">  
17.        userName,password  
18.     </sql>  
19.       
20.     <insert id="inser_user" parameterType="User">  
21.         <!-- include 引用可重用的SQL代码段 -->  
22.         INSERT INTO USER(<include refid="demo_sql"/>) VALUES(#{userName},#{password})  
23.     </insert>  
24.       
25.     <update id="update_user" parameterType="User">  
26.         UPDATE USER SET userName=#{userName} ,password=#{password}  WHERE id=#{id}  
27.     </update>  
28.       
29.     <update id="delete_user" parameterType="int">  
30.         DELETE FROM USER WHERE id=#{id}  
31.     </update>  
32.     
33.     <select id="selectAll_user" useCache="false" flushCache="true" resultMap="user_orm">  
34.         SELECT * FROM USER  
35.     </select>  
36.       
37.     <!-- 使用map传人多个参数 -->  
38.     <select id="selectList_user" useCache="false" flushCache="true" parameterType="map" resultMap="user_orm">  
39.         SELECT * FROM USER LIMIT #{pageNow},#{pageSize}   
40.     </select>  
41.       
42.     <select id="selectById_user" parameterType="int" resultType="User">  
43.         SELECT * FROM USER WHERE id= #{id}  
44.     </select>  
45.       
46.     <select id="selectCount_user"  resultType="int">  
47.         SELECT count(*) FROM USER   
48.     </select>  
49.       
50.     <select id="selectByName_user" parameterType="String" resultType="User">  
51.         SELECT * FROM USER WHERE userName= #{userName}  
52.     </select>  
53.   
54.     <!--模糊 MyIbatis 3.2.0-->  
55.     <select id="selectUserByName" parameterType="String" resultMap="user_orm">  
56.          SELECT * FROM USER WHERE userName like "%"#{userName}"%"  
57.     </select>  
58.       
59. </mapper>  

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper     
PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"     
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">     
   
<!-- namespace用于java代码调用时识别指定xml的mapper文件 -->
<mapper namespace="com.mybatis.model.User">
	<!-- 配置ORM映射 -->
	<resultMap type="User" id="user_orm">
		<id property="id" column="id"/>
		<result property="userName" column="userName"/>
		<result property="password" column="password"/>
	</resultMap>
	
	<!-- 用来定义可重用的SQL代码段 -->
	<sql id="demo_sql">
       userName,password
	</sql>
	
	<insert id="inser_user" parameterType="User">
		<!-- include 引用可重用的SQL代码段 -->
		INSERT INTO USER(<include refid="demo_sql"/>) VALUES(#{userName},#{password})
	</insert>
	
	<update id="update_user" parameterType="User">
		UPDATE USER SET userName=#{userName} ,password=#{password}  WHERE id=#{id}
	</update>
	
	<update id="delete_user" parameterType="int">
		DELETE FROM USER WHERE id=#{id}
	</update>
  
    <select id="selectAll_user" useCache="false" flushCache="true" resultMap="user_orm">
    	SELECT * FROM USER
  	</select>
  	
  	<!-- 使用map传人多个参数 -->
    <select id="selectList_user" useCache="false" flushCache="true" parameterType="map" resultMap="user_orm">
    	SELECT * FROM USER LIMIT #{pageNow},#{pageSize} 
  	</select>
  	
  	<select id="selectById_user" parameterType="int" resultType="User">
    	SELECT * FROM USER WHERE id= #{id}
  	</select>
  	
  	<select id="selectCount_user"  resultType="int">
    	SELECT count(*) FROM USER 
  	</select>
  	
  	<select id="selectByName_user" parameterType="String" resultType="User">
    	SELECT * FROM USER WHERE userName= #{userName}
  	</select>

    <!--模糊 MyIbatis 3.2.0-->
    <select id="selectUserByName" parameterType="String" resultMap="user_orm">
	     SELECT * FROM USER WHERE userName like "%"#{userName}"%"
	</select>
  	
</mapper>

  SessionFactoryUtil.java  MyBatis工具类，用于创建SqlSessionFactory

Java代码  

1. package com.mybatis.sessionfactory;  
2.   
3. import java.io.IOException;  
4. import java.io.Reader;  
5.   
6. import org.apache.ibatis.io.Resources;  
7. import org.apache.ibatis.session.SqlSession;  
8. import org.apache.ibatis.session.SqlSessionFactory;  
9. import org.apache.ibatis.session.SqlSessionFactoryBuilder;  
10.   
11. public class SessionFactoryUtil {  
12.       
13.     private static final String RESOURCE = "Configuration.xml";  
14.     private static SqlSessionFactory sqlSessionFactory = null;  
15.     private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();  
16.   
17.     static {  
18.         Reader reader = null;  
19.         try {  
20.             reader = Resources.getResourceAsReader(RESOURCE);  
21.         } catch (IOException e) {  
22.             throw new RuntimeException("Get resource error:"+RESOURCE, e);  
23.         }  
24.   
25.         sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);  
26.     }  
27.       
28.     /** 
29.      * Function  : 获得SqlSessionFactory 
30.      */  
31.     public static SqlSessionFactory getSqlSessionFactory(){     
32.         return sqlSessionFactory;     
33.     }  
34.       
35.     /** 
36.      * Function  : 重新创建SqlSessionFactory 
37.      */  
38.     public static void rebuildSqlSessionFactory(){  
39.         Reader reader = null;  
40.         try {  
41.             reader = Resources.getResourceAsReader(RESOURCE);  
42.         } catch (IOException e) {  
43.             throw new RuntimeException("Get resource error:"+RESOURCE, e);  
44.         }  
45.   
46.         sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);  
47.     }  
48.       
49.     /** 
50.      * Function  : 获取sqlSession 
51.      */  
52.     public static SqlSession getSession(){  
53.         SqlSession session = threadLocal.get();  
54.           
55.         if(session!=null){  
56.             if(sqlSessionFactory == null){  
57.                 getSqlSessionFactory();  
58.             }  
59.             //如果sqlSessionFactory不为空则获取sqlSession，否则返回null  
60.             session = (sqlSessionFactory!=null) ? sqlSessionFactory.openSession(): null;  
61.         }  
62.           
63.         return session;  
64.     }  
65.       
66.     /** 
67.      * Function  : 关闭sqlSession 
68.      */  
69.     public static void closeSession(){  
70.         SqlSession session = threadLocal.get();  
71.         threadLocal.set(null);  
72.         if(session!=null){  
73.             session.close();  
74.         }  
75.     }  
76. }  

package com.mybatis.sessionfactory;

import java.io.IOException;
import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class SessionFactoryUtil {
	
	private static final String RESOURCE = "Configuration.xml";
	private static SqlSessionFactory sqlSessionFactory = null;
	private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();

	static {
		Reader reader = null;
		try {
			reader = Resources.getResourceAsReader(RESOURCE);
		} catch (IOException e) {
			throw new RuntimeException("Get resource error:"+RESOURCE, e);
		}

		sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
	}
	
	/**
	 * Function  : 获得SqlSessionFactory
	 */
	public static SqlSessionFactory getSqlSessionFactory(){   
        return sqlSessionFactory;   
    }
	
	/**
	 * Function  : 重新创建SqlSessionFactory
	 */
	public static void rebuildSqlSessionFactory(){
		Reader reader = null;
		try {
			reader = Resources.getResourceAsReader(RESOURCE);
		} catch (IOException e) {
			throw new RuntimeException("Get resource error:"+RESOURCE, e);
		}

		sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
	}
	
	/**
	 * Function  : 获取sqlSession
	 */
	public static SqlSession getSession(){
		SqlSession session = threadLocal.get();
		
		if(session!=null){
			if(sqlSessionFactory == null){
				getSqlSessionFactory();
			}
			//如果sqlSessionFactory不为空则获取sqlSession，否则返回null
			session = (sqlSessionFactory!=null) ? sqlSessionFactory.openSession(): null;
		}
		
		return session;
	}
	
	/**
	 * Function  : 关闭sqlSession
	 */
	public static void closeSession(){
		SqlSession session = threadLocal.get();
		threadLocal.set(null);
		if(session!=null){
			session.close();
		}
	}
}

 UserDao interface

Java代码  

1. package com.mybatis.dao;  
2.   
3. import java.util.List;  
4.   
5. import com.mybatis.model.User;  
6.   
7. public interface UserDao {  
8.       
9.     public User load(int id);  
10.     public void add(User user);  
11.     public void update(User user);  
12.     public void delete(int id);  
13.     public User findByName(String userName);  
14.     public List<User>  queryAllUser();  
15.     public List<User> list(int pageNow,int pageSize);  
16.     public int getAllCount();  
17.       
18. }  

package com.mybatis.dao;

import java.util.List;

import com.mybatis.model.User;

public interface UserDao {
	
	public User load(int id);
	public void add(User user);
	public void update(User user);
	public void delete(int id);
	public User findByName(String userName);
	public List<User>  queryAllUser();
	public List<User> list(int pageNow,int pageSize);
	public int getAllCount();
	
}

 UserDaoImpl

Java代码  

1. package com.mybatis.dao.implment;  
2.   
3. import java.util.HashMap;  
4. import java.util.List;  
5. import java.util.Map;  
6.   
7. import org.apache.ibatis.session.SqlSession;  
8.   
9. import com.mybatis.dao.UserDao;  
10. import com.mybatis.model.User;  
11. import com.mybatis.sessionfactory.SessionFactoryUtil;  
12.   
13.   
14. public class UserDaoImpl implements UserDao {  
15.       
16.     public User load(int id){  
17.         SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();  
18.         User user = (User) session.selectOne("com.mybatis.model.User.selectById_user", id);  
19.         session.close();  
20.         return user;  
21.     }  
22.   
23.     public void add(User user) {  
24.         SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();  
25.         session.insert("com.mybatis.model.User.inser_user", user);  
26.         session.commit();  
27.         session.close();  
28.     }  
29.       
30.     public void update(User user){  
31.         SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();  
32.         session.update("com.mybatis.model.User.update_user", user);  
33.         session.commit();  
34.         session.close();  
35.     }  
36.       
37.     public void delete(int id){  
38.         SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();  
39.         session.delete("com.mybatis.model.User.delete_user", id);  
40.         session.close();  
41.     }  
42.       
43.     public User findByName(String userName){  
44.         SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();  
45.         User user = (User)session.selectOne("com.mybatis.model.User.selectByName_user", userName);  
46.         session.close();  
47.         return user;  
48.     }  
49.       
50.     @SuppressWarnings("unchecked")  
51.     public List<User> queryAllUser() {  
52.         SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();  
53.         List<User> list = session.selectList("com.mybatis.model.User.selectAll_user");  
54.         session.close();  
55.         return list;  
56.     }  
57.       
58.     @SuppressWarnings("unchecked")  
59.     public List<User> list(int pageNow , int pageSize){  
60.         SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();  
61.         Map<String,Object> params = new HashMap<String ,Object>();  
62.         params.put("pageNow", pageNow);  
63.         params.put("pageSize", pageSize);  
64.         List<User> list = session.selectList("com.mybatis.model.User.selectList_user", params);  
65.         session.close();  
66.         return list;  
67.     }  
68.       
69.     public int getAllCount(){  
70.         SqlSession session =SessionFactoryUtil.getSqlSessionFactory().openSession();  
71.         int count = (Integer) session.selectOne("com.mybatis.model.User.selectCount_user");  
72.         session.close();  
73.         return count;  
74.     }  
75.       
76. }  

package com.mybatis.dao.implment;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import com.mybatis.dao.UserDao;
import com.mybatis.model.User;
import com.mybatis.sessionfactory.SessionFactoryUtil;


public class UserDaoImpl implements UserDao {
	
	public User load(int id){
		SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();
		User user = (User) session.selectOne("com.mybatis.model.User.selectById_user", id);
		session.close();
		return user;
	}

	public void add(User user) {
		SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();
		session.insert("com.mybatis.model.User.inser_user", user);
		session.commit();
		session.close();
	}
	
	public void update(User user){
		SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();
		session.update("com.mybatis.model.User.update_user", user);
		session.commit();
		session.close();
	}
	
	public void delete(int id){
		SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();
		session.delete("com.mybatis.model.User.delete_user", id);
		session.close();
	}
	
	public User findByName(String userName){
		SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();
		User user = (User)session.selectOne("com.mybatis.model.User.selectByName_user", userName);
		session.close();
		return user;
	}
	
	@SuppressWarnings("unchecked")
	public List<User> queryAllUser() {
		SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();
		List<User> list = session.selectList("com.mybatis.model.User.selectAll_user");
		session.close();
		return list;
	}
	
	@SuppressWarnings("unchecked")
	public List<User> list(int pageNow , int pageSize){
		SqlSession session = SessionFactoryUtil.getSqlSessionFactory().openSession();
		Map<String,Object> params = new HashMap<String ,Object>();
		params.put("pageNow", pageNow);
		params.put("pageSize", pageSize);
		List<User> list = session.selectList("com.mybatis.model.User.selectList_user", params);
		session.close();
		return list;
	}
	
	public int getAllCount(){
		SqlSession session =SessionFactoryUtil.getSqlSessionFactory().openSession();
		int count = (Integer) session.selectOne("com.mybatis.model.User.selectCount_user");
		session.close();
		return count;
	}
	
}

 测试类：

Java代码  

1. package com.mybatis.dao.implment;  
2.   
3. import java.util.List;  
4.   
5. import org.junit.Test;  
6.   
7. import com.mybatis.dao.UserDao;  
8. import com.mybatis.model.User;  
9.   
10. public class UserDaoTest {  
11.       
12.     private UserDao userDao = new UserDaoImpl();  
13.       
14.     @Test  
15.     public void testLoad(){  
16.         User u = userDao.load(1);  
17.         if(u!=null){  
18.             System.out.println("UserId:"+u.getId()+"  UserName:"+u.getUserName()+"  Password:"+u.getPassword());  
19.         }  
20.         else{  
21.             System.out.println("id不存在！！");  
22.         }  
23.     }  
24.       
25.     @Test  
26.     public void testAdd(){  
27.         User user = new User();  
28.         user.setUserName("admin5");  
29.         user.setPassword("123456");  
30.         userDao.add(user);  
31.     }  
32.       
33.     @Test  
34.     public void testUpdate(){  
35.         User user = new User();  
36.         user.setId(2);  
37.         user.setUserName("manager");  
38.         user.setPassword("123456");  
39.         userDao.update(user);  
40.     }  
41.       
42.     @Test  
43.     public void testQueryAllUser(){  
44.         List<User> list = userDao.queryAllUser();  
45.         if(list!=null&list.size()>0){  
46.             for(User u:list){  
47.                 System.out.println("UserId:"+u.getId()+"  UserName:"+u.getUserName()+"  Password:"+u.getPassword());  
48.             }  
49.         }  
50.     }  
51.       
52.     @Test  
53.     public void testFindByName(){  
54.         User u = userDao.findByName("admin");  
55.         if(u!=null){  
56.             System.out.println("UserId:"+u.getId()+"  UserName:"+u.getUserName()+"  Password:"+u.getPassword());  
57.         }  
58.         else{  
59.             System.out.println("用户名不存在！！");  
60.         }  
61.     }  
62.       
63.     @Test  
64.     public void testList(){  
65.         List<User> list = userDao.list(1, 4);  
66.         if(list!=null&list.size()>0){  
67.             for(User u:list){  
68.                 System.out.println("UserId:"+u.getId()+"  UserName:"+u.getUserName()+"  Password:"+u.getPassword());  
69.             }  
70.         }  
71.     }  
72.       
73.     @Test  
74.     public void testGetAllCount(){  
75.             System.out.println("All Count : "+userDao.getAllCount());  
76.     }  
77.       
78.     @Test  
79.     public void testDelete(){  
80.         userDao.delete(3);  
81.     }  
82.   
83. }