mybaties中select用法，以及常用增删改查

 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 <mapper namespace="com.luwu.witcomm.bean.mapper.User">  6 <insert id="add" parameterType="User" useGeneratedKeys="true"  7 keyProperty="classId">  8  INSERT INTO users(username,PASSWORD)  9  VALUES(#{username},#{password}); 10 </insert> 11 <select id="selectAll" parameterType="Admin" useCache="true" 12 resultMap="adminResultMap"> 13 SELECT * FROM t_admin; 14 </select> 15 16 <resultMap id="adminResultMap" type="com.luwu.witcomm.bean.Admin"> 17 <id column="adminid" property="adminid" jdbcType="INTEGER" /> 18 <result column="loginname" property="loginname" jdbcType="VARCHAR" /> 19 <result column="loginpass" property="loginpass" jdbcType="VARCHAR" /> 20 <!-- 一下是映射关系集合 --> 21 <!-- 22 <collection property="type" ofType="com.luwu.witcomm.bean.Type"> 23 <id column="id" property="id"></id> 24 <result column="name" property="name" jdbcType="VARCHAR"></result> 25 <result column="remark" property="remark" jdbcType="VARCHAR"></result> 26 </collection> 27 --> 28 </resultMap> 29 </mapper>

 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 39 </configuration> 

1 driver=com.mysql.jdbc.Driver 2 url=jdbc:mysql://localhost:3306/keith 3 username=keith 4 password=keith

 1 log4j.rootLogger=DEBUG,CONSOLE,FILEOUT  2 log4j.addivity.org.apache=true  3  4 # CONSOLE  5 log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender  6 log4j.appender.Threshold=DEBUG  7 log4j.appender.CONSOLE.Target=System.out  8 log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout  9 #log4j.appender.CONSOLE.layout.ConversionPattern=[framework] %d - %c -%-4r [%t] %-5p %c %x - %m%n 10 log4j.appender.CONSOLE.layout.ConversionPattern=[%-5p] %d{yyyy-MM-dd HH\:mm\:ss} \:%m%n 11 12 # 13 # FILEOUT 14 log4j.appender.FILEOUT=org.apache.log4j.RollingFileAppender 15 log4j.appender.FILEOUT.File=${catalina.home}\\file.log 16 log4j.appender.fileout.MaxFileSize=100000KB 17 # default is true，append to the file; if false, the replace the log file whenever restart system 18 log4j.appender.FILEOUT.Append=true 19 #RollingFileAppender没有DatePattern这个属性 20 log4j.appender.FILEOUT.layout=org.apache.log4j.PatternLayout 21 #log4j.appender.CONSOLE.layout.ConversionPattern=[framework] %d - %c -%-4r [%t] %-5p %c %x - %m%n 22 log4j.appender.FILEOUT.layout.ConversionPattern=[%-5p]_%d{yyyy-MM-dd HH:mm:ss} :%m%n

 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 }

 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 </mapper>  

 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 }

 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 }

 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 }

 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 }