mybatis3常见数据操作

一、一般增删查改操作  

数据映射文件User.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">  
  
<mapper namespace="UserMapper">  
  
    <!-- select 语句，未传参，返回int值 。 -->  
    <select id="countAll" resultType="java.lang.Integer">    
    <![CDATA[ select count(*) from T_TEST_USER ]]>  
    </select>  
  
    <!-- select 语句，传参一个，返回User对象 。注：select id as id,name as name,pwd as pwd   
        的as后面的名称要与User类中属性名称要一一对应 -->  
    <select id="getUserByName" parameterType="String"  
        resultType="com.hsinghsu.testSSI.model.User">  
        select  
        id as id,name as name,pwd as pwd  
        from T_TEST_USER  
        WHERE NAME = #{name}  
    </select>  
  
    <!-- select 语句，传参多个，返回User对象。注：此时select * 语句中对应的表的列名要与User类中属性名称要一一对应 -->  
    <select id="getUserByNameAndAge" parameterType="Map"  
        resultType="com.hsinghsu.testSSI.model.User">  
        select  
        *  
        from T_TEST_USER  
        WHERE NAME = #{name} and AGE =  
        #{age}  
    </select>  
  
    <!-- select 语句，传参一个，返回User对象。 -->  
    <select id="getUserByAge" parameterType="String"  
        resultType="com.hsinghsu.testSSI.model.User">  
        select  
        *  
        from T_TEST_USER  
        WHERE AGE = #{age}  
    </select>  
  
    <!-- select 语句，传参一个，返回HashMap类型。 -->  
    <select id="getUserByPwd" parameterType="String" resultType="Map">  
        select  
        *  
        from T_TEST_USER  
        WHERE PWD = #{pwd}  
    </select>  
  
    <!-- select 语句，传参一个，返回HashMap类型。 -->  
    <select id="getUserBySex" parameterType="String" resultType="Map">  
        select  
        *  
        from T_TEST_USER  
        WHERE SEX = #{sex}  
    </select>  
  
    <insert id="insertUser" parameterType="com.hsinghsu.testSSI.model.User">  
        insert into  
        T_TEST_USER(ID,NAME,PWD,AGE,SEX)  
        values(#{id},#{name},#{pwd},#{age},#{sex})  
    </insert>  
  
    <update id="updateUser" parameterType="com.hsinghsu.testSSI.model.User">  
        UPDATE T_TEST_USER  
        SET NAME = #{name},  
        PWD = #{pwd},  
        AGE = #{age},  
        SEX = #{sex}  
        WHERE ID = #{id}  
    </update>  
      
    <delete id="deleteUserById" parameterType="int">  
        delete from T_TEST_USER where ID = #{id}  
    </delete>  
  
</mapper>  

执行文件UserDaoImpl.java代码如下：

 

package com.hsinghsu.testSSI.dao.impl;  
  
import java.util.ArrayList;  
import java.util.HashMap;  
import java.util.List;  
import java.util.Map;  
  
import org.mybatis.spring.support.SqlSessionDaoSupport;  
import org.springframework.stereotype.Repository;  
  
import com.hsinghsu.testSSI.dao.UserDao;  
import com.hsinghsu.testSSI.model.User;  
import com.hsinghsu.testSSI.util.SQLAdapter;  
  
@Repository(value = "userDao")  
public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao {  
  
    @Override  
    public User getUserByName(String name) {  
  
        User user = new User();  
  
        // select语句，传入一个参数，返回User对象  -------------------------------------------------------------------------  
        int count = (Integer) getSqlSession().selectOne("UserMapper.countAll");// select语句，未传入参数，返回int值  
        user = getSqlSession().selectOne("UserMapper.getUserByName", name);// select语句，传入一个参数，返回User对象  
  
        //select语句，传入两个参数，返回User对象  --------------------------------------------------------------------------  
        User usr = new User();  
        Map<String, String> map = new HashMap<String, String>();  
        map.put("name", "hsing");  
        map.put("age", "12");  
        usr = getSqlSession().selectOne("UserMapper.getUserByNameAndAge", map);// select语句，传入两个参数，返回User对象  
  
        //select语句，传入一个参数，返回List类型  --------------------------------------------------------------------------  
        List<User> userList = new ArrayList<User>();  
        userList = getSqlSession().selectList("UserMapper.getUserByAge", "12");// select语句，传入一个参数，返回List类型  
        for (User u : userList) {  
            System.out.println("--u--name:" + u.getName() + " pwd:" + u.getPwd());  
        }  
  
        //select语句，传入一个参数，返回HashMap类型 -----------------------------------------------------------------------  
        HashMap<String, Object> userMap = (HashMap<String, Object>) getSqlSession().selectOne("UserMapper.getUserByPwd", "gg");// select语句，传入一个参数，返回HashMap类型  
        if (userMap != null) {  
            System.out.println(userMap.toString() + "--m--name:" + userMap.get("NAME") + " pwd:" + userMap.get("PWD"));// 注:map的key为数据表的列，区分大小写  
        } else {  
            System.out.println("userMap is null");  
        }  
  
        //select语句，传入一个参数，返回List<HashMap>类型  -----------------------------------------------------------------  
        List<HashMap<String, Object>> userMapList = new ArrayList<HashMap<String, Object>>();  
        userMapList = getSqlSession().selectList("UserMapper.getUserBySex", "0");// select语句，传入一个参数，返回List<HashMap>类型  
        for (HashMap<String, Object> umap : userMapList) {  
            System.out.println(umap.toString() + "--map--name:" + umap.get("NAME") + " pwd:" + umap.get("PWD"));// 注:map的key为数据表的列，区分大小写  
        }  
          
        //insert语句，传入一个对象，成功返回1  -------------------------------------------------------------------------------------  
        User oneUser = new User();  
        oneUser.setId(Long.parseLong("6"));  
        oneUser.setName("go07");  
        oneUser.setPwd("pesd07");  
        oneUser.setAge(Long.parseLong("25"));  
        oneUser.setSex(Long.parseLong("1"));  
        int result = getSqlSession().insert("UserMapper.insertUser",oneUser);//insert语句，传入一个对象  
        System.out.println("insert result:"+result);  
          
        //update语句，传入一个对象，成功返回1  -------------------------------------------------------------------------------------  
        User oneUser2 = new User();  
        oneUser2.setId(Long.parseLong("6"));  
        oneUser2.setName("go07--");  
        oneUser2.setPwd("pesd07--");  
        oneUser2.setAge(Long.parseLong("26"));  
        oneUser2.setSex(Long.parseLong("0"));  
        int updateResult = getSqlSession().update("UserMapper.updateUser",oneUser2);//insert语句，传入一个对象  
        System.out.println("update result:"+updateResult);  
          
        //delete语句，传入一个值，成功返回1 -------------------------------------------------------------------------------------  
        int result3 = getSqlSession().delete("UserMapper.deleteUserById",7);//delete语句，传入一个参数  
        System.out.println("delete result:"+result3);  
          
          
        return user;  
    }  
  
}  

二、直接执行SQL语句  
在一般应用中，经常需要开发人员在java程序中写sql语句，同时需要动态的执行。在这里我们先定义一个SQLAdapter类，用户传递sql语句。  
SQLAdapter.java代码如下： 

package com.hsinghsu.testSSI.util;  
  
public class SQLAdapter {  
      
    String sql;    
        
    public SQLAdapter(String sql) {    
        this.sql = sql;    
    }    
    
    public String getSql() {    
        return sql;    
    }    
    
    public void setSql(String sql) {    
        this.sql = sql;    
    }    
  
}  

 

在映射文件User.xml中代码如下：

[html] view plain copy print ?

1. <?xml version="1.0" encoding="UTF-8"?>      
2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"      
3.      "http://mybatis.org/dtd/mybatis-3-mapper.dtd">    
4.     
5. <mapper namespace="UserMapper">    
6.     
7.     <select id="queryBySql" parameterType="com.hsinghsu.testSSI.util.SQLAdapter"    
8.         resultType="Map">    
9.         ${sql}    
10.     </select>    
11.     
12.     <insert id="insertBySql" parameterType="com.hsinghsu.testSSI.util.SQLAdapter">    
13.         ${sql}    
14.     </insert>    
15.         
16.     <update id="updateBySql" parameterType="com.hsinghsu.testSSI.util.SQLAdapter">    
17.         ${sql}    
18.     </update>    
19.         
20.     <delete id="deleteBySql" parameterType="com.hsinghsu.testSSI.util.SQLAdapter">    
21.         ${sql}    
22.     </delete>    
23.     
24. </mapper>  

在实现类中UserDaoImpl.java代码如下：   

[java] view plain copy print ?

1. package com.hsinghsu.testSSI.dao.impl;    
2.     
3. import java.util.ArrayList;    
4. import java.util.HashMap;    
5. import java.util.List;    
6. import java.util.Map;    
7.     
8. import org.mybatis.spring.support.SqlSessionDaoSupport;    
9. import org.springframework.stereotype.Repository;    
10.     
11. import com.hsinghsu.testSSI.dao.UserDao;    
12. import com.hsinghsu.testSSI.model.User;    
13. import com.hsinghsu.testSSI.util.SQLAdapter;    
14.     
15. @Repository(value = "userDao")    
16. public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao {    
17.     
18.     @Override    
19.     public User getUserByName(String name) {    
20.             
21.         //=====================================================直接执行sql语句==========================================================    
22.             
23.         //select语句 直接执行select sql语句 ---------------------------------------------------------------------------    
24.         String selectSql = "select ID,NAME,PWD,AGE,SEX from T_TEST_USER WHERE SEX = 1";    
25.         SQLAdapter selectSqlAdapter = new SQLAdapter(selectSql);    
26.         List<HashMap<String, Object>> userMapList2 = new ArrayList<HashMap<String, Object>>();    
27.         userMapList2 = getSqlSession().selectList("UserMapper.queryBySql", selectSqlAdapter);// select语句，传入一个参数，返回List<HashMap>类型    
28.         for (HashMap<String, Object> umap2 : userMapList2) {    
29.             System.out.println(umap2.toString() + "--map2--name:" + umap2.get("NAME") + " pwd:" + umap2.get("PWD"));// 注:map的key为数据表的列，区分大小写    
30.         }    
31.                     
32.         //insert语句 直接执行insert sql语句 ---------------------------------------------------------------------------    
33.         String insertSql = "insert into T_TEST_USER(ID,NAME,PWD,AGE,SEX) values(7,'gogo08','gogopwd08',24,1)";    
34.         SQLAdapter insertSqlAdapter = new SQLAdapter(insertSql);    
35.         int result2 = getSqlSession().insert("UserMapper.insertBySql",insertSqlAdapter);    
36.         System.out.println("insert result2:"+result2);    
37.             
38.         //update语句 直接执行update sql语句 ---------------------------------------------------------------------------    
39.         String updateSql = "UPDATE T_TEST_USER SET NAME = 'GSS00', PWD = 'GG007', AGE = 24, SEX = 1 WHERE ID = 6";    
40.         SQLAdapter updateSqlAdapter = new SQLAdapter(updateSql);    
41.         int updateSqlResult = getSqlSession().update("UserMapper.updateBySql",updateSqlAdapter);    
42.         System.out.println("updateSqlResult result:"+updateSqlResult);    
43.             
44.         //delete语句 直接执行delete sql语句 ---------------------------------------------------------------------------    
45.         String deleteSql = "delete from T_TEST_USER where ID = 5";    
46.         SQLAdapter deleteSqlAdapter = new SQLAdapter(deleteSql);    
47.         int deleteSqlResult = getSqlSession().delete("UserMapper.deleteBySql",deleteSqlAdapter);    
48.         System.out.println("deleteSqlResult result:"+deleteSqlResult);    
49.             
50.         //=============================================================================================================================    
51.     
52.         return null;    
53.     }    
54.     
55. }