Mybatis动态sql开发之纯xml的简单CRUD实现

Mybatis 是一个ORM框架，MyBatis作为持久层框架，其主要思想是将程序中的大量sql语句剥离出来，配置在配置文件中，实现sql的灵活配置。这样做的好处是将sql与程序代码分离，可以在不修改程序代码的情况下，直接在配置文件中修改sql。下面给个简单的入门例子。
Mybatis总配置文件,主要功能，提供连库四个基本信息，加载mapper（映射文件），此处用dept(部门)表：

<?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>
        <!-- 给实体类起一个别名 user -->
        <typeAlias type="com.mybatis.mapper.Dept" alias="dept" />
    </typeAliases>
   <!--  数据源配置  这块用 oracle数据库 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="jdbc" />
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.OracleDriver" />
                <property name="url" value="jdbc:oracle:thin:@192.168.1.187:1521:xe" />
                <property name="username" value="deng" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--userMapper.xml装载进来  同等于把“dao”的实现装载进来 -->
        <mapper resource="dept.xml" />
    </mappers>
</configuration>

dept.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="dept">
    <!--动态查询sql语句  之if-->
    <select id="if" resultType="dept" parameterType="dept"> 
        select d.deptno,d.dname,d.loc from dept d where 1=1
        <if test="deptno!=0">
            and d.deptno = #{deptno}
        </if>
        <if test="dname!=null">
            and d.dname = #{dname}
        </if>
    </select>
    <!-- 动态查询sql之  choose -->
    <select id = "choose" resultType="dept" parameterType="dept">
        select d.deptno,d.dname,d.loc from dept d where 1=1 
        <choose>
            <when test="loc != null">
                and d.loc = #{loc}
            </when>
            <when test="dname != null">
                and d.dname = #{dname}
            </when>
            <otherwise>
                and d.deptno = 10
            </otherwise>
        </choose>
    </select>

    <!--动态查询sql 之where  -->
    <select id="where" resultType="dept" parameterType="dept">
        select d.deptno,d.dname,d.loc from dept d 
        <where>
            <if test="deptno != 0">
                d.deptno = #{deptno}
            </if>
            <if test="dname != null">
                d.dname = #{dname}
            </if>
            <if test="loc != null">
                d.loc = #{loc}
            </if>
        </where> 
    </select>
    <!-- 动态查询sql 之trim-->
    <select id="trim" resultType="dept" parameterType="dept">
        select d.deptno,d.dname,d.loc from dept d 
        <trim prefix="where" prefixOverrides="and | or">
            <if test="deptno != 0">
                d.deptno = #{deptno}
            </if>
            <if test="dname != null">
                and d.dname = #{dname}
            </if>
            <if test="loc != null">
                or d.loc = #{loc}
            </if>
        </trim>
    </select>
    <insert id="save" parameterType="dept">
        insert into dept(deptno,dname,loc) values(#{deptno},#{dname},#{loc})
    </insert>
    <delete id="delete" parameterType="dept">
        delete from dept where deptno = #{deptno}
    </delete>
    <!--动态更新sql 之set  --ame != null">
                d.dname = #{dname}
            </if>
            <if test="loc != null">
                d.loc #{loc}
            </if>
        </set>
        where d.deptno = #{deptno}
    </update>
</mapper>

java核心代码
package com.mybatis.util;

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

import javax.annotation.Resource;

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

/**
* 获得sqlSession缓存封装类
* @author 登祥
*
*/
public class MybatisUtil {
static SqlSession sqlSession = null;
static SqlSessionFactory sqlSessionFactory = null;

/**
 * 获得SqlSession
 * @param resouce 配置文件
 * @return SqlSession
 */
public static SqlSession getSqlSession(String resouce){
    //Reader reader = null;
    try {
        //开始读取
        sqlSessionFactory =  new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader(resouce));  
        //获得sqlSession
        sqlSession = sqlSessionFactory.openSession();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return sqlSession;
} 

/**
 * 可追加映射文件
 * @param resource 总配置文件
 * @param clazz 有注解方法的类或接口
 * @return SqlSession
 */
@SuppressWarnings("unchecked")
public static SqlSession getSqlSession(String resource,Class clazz){
    SqlSession sqlSession = null;
    SqlSessionFactory sqlSessionFactory = null;
    try {
        sqlSessionFactory= new SqlSessionFactoryBuilder().build(Resources
                .getResourceAsReader(resource));
        sqlSessionFactory.getConfiguration().addMapper(clazz);
        sqlSession = sql

SessionFactory.openSession();
} catch (IOException e) {
e.printStackTrace();
}
return sqlSession;
}
}

dao层
package com.mybatis.testdemo;

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

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.transaction.Transaction;

import com.mybatis.mapper.Dept;
import com.mybatis.util.MybatisUtil;

public class DeptDao {

private SqlSession sqlSession;

{
    sqlSession  = MybatisUtil.getSqlSession("mybatis_config.xml");
}

/**
 * 条件查询
 * @param sql 
 * @param obj
 * @return List<Dept>
 */
public List<Dept> query(String sql, Dept dept){
    return sqlSession.selectList(sql, dept);
}

/**
 * 保存记录
 * @param sql 
 * @param dept 
 * @return int
 */
public int save(String sql,Dept dept){
    int i = 0;
    try {
        i = sqlSession.insert(sql, dept);
        sqlSession.commit();
    } catch (Exception e) {
        if(null != sqlSession){
            sqlSession.rollback();
        }
    }
    return i;
}

/**
 * 更新记录
 * @param sql
 * @param dept
 * @return int
 */
public int update(String sql,Dept dept){
    return sqlSession.update(sql, dept);
}

/**
 * 删除记录
 * @param sql
 * @param dept
 * @return int
 */
public int delete(String sql,Dept dept){
    return sqlSession.delete(sql, dept);
}

}

测试方法，此处就一个例子，其余都一样。
package com.mybatis.service;

import java.util.List;

import org.apache.ibatis.transaction.Transaction;
import org.junit.Test;

import com.mybatis.mapper.Dept;
import com.mybatis.testdemo.DeptDao;

public class DeptService extends DeptDao{

/**
 * 测试动态sql查询 
 */
@Test
public void testQuery(){
    Dept dept = new Dept();
    //dept.setDname("SALES");
    dept.setDeptno(33);
    //dept.setLoc("12");
    List<Dept> depts = super.query("dept.choose", dept);
    for (Dept dept2 : depts) {
        System.out.println(dept2);
    }
}

/**
 * 测试保存单条记录
 */
@Test
public void testSave(){
    Dept dept = new Dept();
    dept.setDeptno(44);
    dept.setDname("测试");
    dept.setLoc("测试");
    super.save("dept.save", dept);
}

}
完结，对大家有益的话，谢谢点赞！