mybatis一些简单的基本用法

一、jar包导入：

二、数据库：

      数据库很简单，只有一张表tbl_user,三个字段，user_id为主键。

   

三、包的结构：

  

四、具体class及配置文件：

MybatisUtil.java

package cn.ibm.com.util;

import java.io.Reader;

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

public class MybatisUtil {
    private static final ThreadLocal<SqlSession> tl = new ThreadLocal<SqlSession>();
    
    public static SqlSession getSqlSession() throws Exception {
        String resource = "cn/ibm/com/config/sqlMapConfig.xml";
        Reader reader = Resources.getResourceAsReader(resource);
        return new SqlSessionFactoryBuilder().build(reader).openSession();
    }
    
    public static SqlSession getSameSession() throws Exception{
        SqlSession session = tl.get();
        if(session==null){
            session= getSqlSession();
            tl.set(session);
        }
        return session;
    }
}

UserDao.java

package cn.ibm.com.dao;

import java.util.List;
import cn.ibm.com.domain.User;

public interface UserDao {

    int saveUser(User user);
    int saveUser02(User user);
    int deleteUserByUserId(String userId);
    int updateUser(User user);
    User findUserByUserId(String userId);
    List<User> findAllUser();
    List<User> findUserByConditions(User user);
    List<User> findUserByIds(List<String> ids);
}

 UserDaoImpl.java

package cn.ibm.com.dao.impl;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.session.SqlSession;

import cn.ibm.com.dao.UserDao;
import cn.ibm.com.domain.User;
import cn.ibm.com.util.MybatisUtil;

public class UserDaoImpl implements UserDao{
private static Log log = LogFactory.getLog(UserDaoImpl.class);
private static SqlSession session;
static{
    try {
        session = MybatisUtil.getSameSession();
    } catch (Exception e) {
        log.debug("获取session出错。。",e);
    }
}
    @Override//如果保存成功则返回1，保存失败，且没有进行以下if的判断处理，则会抛出异常。使用parameterType
    public int saveUser(User user) {
        if(session.selectOne("cn.ibm.com.domain.User.selectUserByUserId", user.getUserId())!=null){
            log.info(user.getUserId()+" : Id已经存在。请重新输入。");
            return 0;
        }
        int ret = session.insert("cn.ibm.com.domain.User.insertUser", user);
        log.info("save user return : " +ret);
        session.commit();
        return ret;
    }

    @Override//删除成功返回1，删除失败返回0，不会抛出异常
    public int deleteUserByUserId(String userId) {
        int ret = session.delete("cn.ibm.com.domain.User.deleteUserByUserId", userId);
        log.info("delete user return : "+ret);
        session.commit();
        return ret;
    }

    @Override//更新失败返回0，更新成功返回1
    public int updateUser(User user) {
        int ret = session.update("cn.ibm.com.domain.User.updateUser", user);
        log.info("update user return : "+ret);
        session.commit();
        return ret;
    }

    @Override//
    public User findUserByUserId(String userId) {
        User user = session.selectOne("cn.ibm.com.domain.User.selectUserByUserId", userId);
        log.info("find user return : "+user);
        return user;
    }

    @Override//查找所有用户
    public List<User> findAllUser() {
        List<User> users = session.selectList("cn.ibm.com.domain.User.selectAllUser");
        log.info("find all user return : "+users);
        log.info("users size : "+users.size());
        return users;
    }

    @Override//使用parameterMap
    public int saveUser02(User user) {
        if(session.selectOne("cn.ibm.com.domain.User.selectUserByUserId", user.getUserId())!=null){
            log.info(user.getUserId()+" : Id已经存在。请重新输入。");
            return 0;
        }
        int ret = session.insert("cn.ibm.com.domain.User.insertUser02", user);
        log.info("save user return : " +ret);
        session.commit();
        return ret;
    }

    @Override//多条件模糊查询
    public List<User> findUserByConditions(User user) {
        List<User> users = session.selectList("cn.ibm.com.domain.User.selectUserByConditions", user);
        log.info("find by conditions return : "+users);
        log.info("users size : "+users.size());
        return users;
    }

    @Override//根据多个id值进行查找
    public List<User> findUserByIds(List<String> ids) {
        List<User> users = session.selectList("cn.ibm.com.domain.User.selectUsersByIds",ids);
        log.info("users size : "+users.size());
        log.info("users : "+users);
        return users;
    }

}

User.java

package cn.ibm.com.domain;

public class User {

    private String userId;
    private String userName;
    private String userPwd;
    
    public String getUserId() {
        return userId;
    }
    public void setUserId(String userId) {
        this.userId = userId;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getUserPwd() {
        return userPwd;
    }
    public void setUserPwd(String userPwd) {
        this.userPwd = userPwd;
    }
    @Override
    public String toString() {
        return "User [userId=" + userId + ", userName=" + userName
                + ", userPwd=" + userPwd + "]";
    }
    
}

mybatis核心配置文件sqlMapConfig.xml

<?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>
    <!-- 定义别名 -->
    <typeAliases>
        <typeAlias type="cn.ibm.com.domain.User" alias="User"/>
    </typeAliases>
    
    <environments default="development">
        <environment id="development">
            <!-- 事务管理器类型 -->
            <transactionManager type="JDBC" />
            <!-- 数据源信息 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/mybatisdb" />
                <property name="username" value="root" />
                <property name="password" value="root" />
            </dataSource>
        </environment>
    </environments>
    <!-- 注册SQL映射文件 -->
    <mappers>
        <mapper resource="cn/ibm/com/domain/UserMapper.xml" />
    </mappers>
</configuration>

实体映射文件UserMapper.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">
<!-- namespace为命名空间 ,要保证唯一-->
<mapper namespace="cn.ibm.com.domain.User">
    <!-- 提取公共字段 -->
    <sql id="someCols">
        user_name,user_pwd
    </sql>
    <sql id="allCols">
        user_id,user_name,user_pwd
    </sql>
    
    <parameterMap type="User" id="userMap">
        <parameter property="userId" resultMap="user_id"/>
        <parameter property="userName" resultMap="user_name"/>
        <parameter property="userPwd" resultMap="user_pwd"/>
    </parameterMap>
    
    <resultMap type="User" id="UserResult">
        <result property="userId" column="user_id" javaType="String" jdbcType="VARCHAR"/>
        <result property="userName" column="user_name"/>
        <result property="userPwd" column="user_pwd"/>
    </resultMap>
    
    <!-- select标签中是查询语句 -->
    <!--
        id:当前对应SQL的唯一标识
        parameterType：当前SQL接收参数的类型
        resultType:执行SQL后包装成什么类型返回
     -->
    <select id="selectUserByUserId" parameterType="int" resultMap="UserResult">
        select
             <include refid="allCols"/>
         from tbl_user where user_id = #{id}
    </select>
    <!-- 以下两种插入数据方式都可以正常运行 -->
    <insert id="insertUser" parameterType="User" >
        insert into tbl_user (<include refid="allCols"/>) values(#{userId},#{userName},#{userPwd})
    </insert>
    
    <insert id="insertUser02" parameterMap="userMap" >
        insert into tbl_user (<include refid="allCols"/>) values(#{userId},#{userName},#{userPwd})
    </insert>
    
    <delete id="deleteUserByUserId" parameterType="int" >
        delete from tbl_user where user_id = #{id}
    </delete>
    
    <update id="updateUser" parameterType="User" >
        update tbl_user
         <set>
                user_name=#{userName},
                user_pwd = #{userPwd}
         </set>
         <where>
                 user_id = #{userId}
         </where>
         <!-- 以上方式和下面的这种原始方式都可以实现update操作 -->
        <!-- update tbl_user set user_name=#{userName},user_pwd=#{userPwd} where user_id=#{userId} -->
    </update>
    
    <select id="selectAllUser" resultMap="UserResult" >
        select <include refid="allCols"/> from tbl_user
    </select>
    <!-- 多条件模糊查询,通过parameterType方式 -->
    <select id="selectUserByConditions" parameterType="User" resultMap="UserResult">
        select <include refid="allCols"/> from tbl_user
        <where>
           <where>
            <if test="userName!=null">
                user_name like concat('%',#{userName},'%')
            </if>
            <if test="userPwd!=null">
                and user_pwd like concat('%',#{userPwd},'%')
            </if>
        </where>
        </where>
    </select>
    <!-- 根据多个id进行查询 -->
    <!-- 在这种情况下， parameterType="" 可以省略不写，其值也可以为list,List,java.util.List或者String,string,java.lang.String都是正确的，都可以输出结果
    但是resultMap不能使用User，在本例中不可以使用，本例中User属性字段和数据库表字段是不一样的，不知道一样的话行不行，有待验证
    foreach中item的属性值要和foreach内部大括号内的值相同，否则会发生异常
    -->
    <select id="selectUsersByIds" parameterType="java.util.List" resultMap="UserResult">
    select <include refid="allCols"/> from tbl_user
        <where>
            user_id in
            <foreach item="userId" collection="list" separator="," open="(" close=")" >
            #{userId}
            </foreach>
        </where>
    </select>
</mapper>

日志文件log4j.properties

log4j.rootLogger=debug,my

### direct log messages to my ###
log4j.appender.my=org.apache.log4j.ConsoleAppender
log4j.appender.my.Target=System.out
log4j.appender.my.layout=org.apache.log4j.PatternLayout
log4j.appender.my.layout.ConversionPattern=%d %5p %c{1}:%L - %m%n

测试类UserDaoTest.java

package test;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import cn.ibm.com.dao.UserDao;
import cn.ibm.com.dao.impl.UserDaoImpl;
import cn.ibm.com.domain.User;
import cn.ibm.com.util.MybatisUtil;

public class UserDaoTest {
    
    private static Log logger = LogFactory.getLog(UserDaoTest.class);
    private UserDao userDao = new UserDaoImpl();
    
    @Test//test if get the same session
    public void testSameSession() throws Exception{
        SqlSession s0 = MybatisUtil.getSameSession();
        SqlSession s1 = MybatisUtil.getSameSession();
        System.out.println(s0==s1);         //true
        SqlSession s2 = MybatisUtil.getSqlSession();
        SqlSession s3 = MybatisUtil.getSqlSession();
        System.out.println(s2==s3);  //false
    }
    @Test
    public void testSaveUser(){
        User user = new User();
        int random = (int) ((Math.random()+1)*1000);
        user.setUserId(random+"");
        user.setUserName("name "+random);
        user.setUserPwd("name "+random);
        userDao.saveUser(user);
    }
    @Test
    public void testSaveUser01(){
        User user = new User();
        user.setUserId(200000+"");
        user.setUserName("name ");
        user.setUserPwd("name ");
        userDao.saveUser(user);
    }
    @Test
    public void testDeleteUser(){
        userDao.deleteUserByUserId("200000");
    }
    @Test
    public void testFindUserById(){
        userDao.findUserByUserId("200000");
    }
    @Test
    public void testSaveUser_parameterMap(){
        User user = new User();
        user.setUserId(200001+"");
        user.setUserName("name1 ");
        user.setUserPwd("name1 ");
        userDao.saveUser02(user);
    }
    @Test
    public void testFindAllUser(){
        userDao.findAllUser();
    }
    @Test
    public void testUpdateUser(){
        User user = new User();
        user.setUserId("200001");
        user.setUserName("updateName");
        user.setUserPwd("updatePwd");
        userDao.updateUser(user);
    }
    @Test
    public void testConditionsQuery(){
        User user = new User();
        user.setUserName("n");
        user.setUserPwd("n");
        userDao.findUserByConditions(user);
    }
    @Test
    public void testFindUserByIds(){
        List<String> ids = new ArrayList<String>();
        ids.add("58");
        ids.add("1862");
        userDao.findUserByIds(ids);
    }
}

注意：有的时候可能会因为一些很小的细节出现问题，比如insert into tbl_user (<include refid="allCols"/>) values(#{userId},#{userName},#{userPwd})，这个sql语句，如果不小心将前面的括号写丢了，肯定时会发生异常的，如果参数类型是String或者其他的基本数据类型，当通过#进行取值的时候{}里面的值可以随便填写，但是如果是实体类对象，则

{}中的值必须是实体类的属性，如果不是则会发生异常。