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

查询语句是使用 MyBatis 时最常用的元素之一

select元素配置细节如下

 

属性 描述 取值 默认
id 在这个模式下唯一的标识符，可被其它语句引用    
parameterType 传给此语句的参数的完整类名或别名    
resultType 语句返回值类型的整类名或别名。注意，如果是集合，那么这里填写的是集合的项的整类名或别名，而不是集合本身的类名。（resultType 与resultMap 不能并用）    
resultMap 引用的外部resultMap 名。结果集映射是MyBatis 中最强大的特性。许多复杂的映射都可以轻松解决。（resultType 与resultMap 不能并用）    
flushCache 如果设为true，则会在每次语句调用的时候就会清空缓存。select 语句默认设为false true|false false
useCache 如果设为true，则语句的结果集将被缓存。select 语句默认设为false true|false false
timeout 设置驱动器在抛出异常前等待回应的最长时间，默认为不设值，由驱动器自己决定		 true|false false
timeout 设置驱动器在抛出异常前等待回应的最长时间，默认为不设值，由驱动器自己决定 正整数 未设置
fetchSize 设置一个值后，驱动器会在结果集数目达到此数值后，激发返回，默认为不设值，由驱动器自己决定 正整数 驱动器决定
statementType statement，preparedstatement，callablestatement。
预准备语句、可调用语句		 STATEMENT
PREPARED
CALLABLE		 PREPARED
resultSetType forward_only，scroll_sensitive，scroll_insensitive
只转发，滚动敏感，不区分大小写的滚动		 FORWARD_ONLY
SCROLL_SENSITIVE
SCROLL_INSENSITIVE		 驱动器决定

例子：

 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）configuration.xml     ， MyBatis主配置文件

 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>

 2)jdbc.properties

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

3)log4j.properties

 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

4）User .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 }

5)UserSqlMap.xml

 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>

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

 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 }

7)UserDao interface

 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 }

8)UserDaoImpl

 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 }

9)测试类：

 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 }

10)执行testFindByName()：

MyBatis3 之增删改查操作

 

参考至：http://www.open-open.com/lib/view/open1349622424072.html

郁极风在此感谢这位同僚的帮助。

posted @ 2014-02-28 03:07  小风_  阅读(5801)  评论(0)  编辑  收藏  举报