spring + mybatis 的 crud

前提

要先搭建好Spring基础环境。spring搭建环境

实现步骤

实体类 model

User.class

package com.spring.handlers.model;
public class User {
    private int id;
    private int departmentId;
    private String userName;
    private String password;

    private Department department;

    //getter and setter 方法及toString()方法忽略，读者自己加上。
}

Department.class

package com.spring.handlers.model;

public class Department {

    private int id;

    private String departmentName;

    //getter and setter 方法及toString()方法忽略，读者自己加上。
}

在 src目录下，创建mybatis的配置文件，已经俩个实体类对应的xml配置文件

mybatis的配置文件： MyBatisConfig.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>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC">
      </transactionManager>
      <dataSource type="POOLED">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatis?                                        useUnicode=true&amp;characterEncoding=UTF-8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
        <mapper resource="User.xml"/>
        <mapper resource="Department.xml"/>
  </mappers>
</configuration>

User对应的User.xml

注意：里面包括crud操作，而且包含联合查询，还有动态的拼接mysql语句，批量删除操作等。

<?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="com.spring.handlers.dao.IUser">
<resultMap type="com.spring.handlers.model.User" id="baseMap">  
                <id column="id" property="id"/>  
                <result column="department_id" property="departmentId"/> 
                <result column="username" property="userName"/>   
                <result column="password" property="password"/>                 

                <association property="department" javaType="com.spring.handlers.model.Department">  
                        <id column="did" property="id"/>  
                        <result column="dname" property="departmentName"/>  
                </association>  
</resultMap>  

<sql id="WithDepartment_Column_List">
        u.id,u.department_id,u.password,u.username,d.id did,d.department_name dname
    </sql>
<!-- resultType="com.spring.handlers.model.User" -->
    <select id="findById" parameterType="int" resultMap="baseMap">
        select <include refid="WithDepartment_Column_List"></include>
         from user u left JOIN department d on u.department_id = d.id where  u.id=#{id}      
    </select>
    <!-- findListByUser -->
    <select id="findListByUser" parameterType="com.spring.handlers.model.User" resultMap="baseMap">
        select <include refid="WithDepartment_Column_List"></include>
         from user u left join department d on u.department_id = d.id    
            <!-- <where></where>    <if test="user != null">
            </if> -->
            <trim prefix="where 1=1 " >
                <if test="id != null and id != '' ">
                    and  u.id=#{id}
                </if>
                <if test="departmentId != null and departmentId != '' ">
                    and u.department_id=#{departmentId}
                </if>
                <if test="userName != null and userName != '' ">
                    and u.username like '%${userName}%'
                </if>           
            </trim>
    </select>
    <insert id="addUser" parameterType="com.spring.handlers.model.User">
        INSERT into `user` VALUES(null,#{departmentId},#{userName},#{password});
    </insert>
    <!-- UPDATE `user` set username = '修改' WHERE id = 1; -->
    <update id="updateUser" parameterType="com.spring.handlers.model.User">
        UPDATE `user` 
        <trim prefix="set" suffixOverrides=",">
            <if test="departmentId != null and departmentId != ''">
             department_id = #{departmentId} ,
            </if>
            <if test="userName != null and userName != ''">
                username='${userName}',    
            </if>
            <if test="password != null and password != ''">
                password='${password}',    
            </if>
        </trim>

        WHERE id = #{id};
    </update>
    <update id="updateUserTwo" parameterType="com.spring.handlers.model.User">
        UPDATE `user` 
        <set>
            <if test="departmentId != null and departmentId != ''">
             department_id = #{departmentId} ,
            </if>
            <if test="userName != null and userName != ''">
                username='${userName}',    
            </if>
            <if test="password != null and password != ''">
                password='${password}',    
            </if>
        </set>

        WHERE id = #{id};
    </update>
    <delete id="deleteUserByIds" parameterType="java.util.List">
        delete from user 
        <trim prefix="where" >
            id in 
            <foreach collection="list" item="id"
                 open="(" close=")" index="index" separator=",">
                #{id}
            </foreach>
        </trim>
    </delete>
</mapper>

department对应的Department.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="com.spring.handlers.dao.IDepartment">
    <resultMap type="com.spring.handlers.model.Department" id="baseMaps">
        <id column="id" property="id"/>
        <result column="department_name" property="departmentName"/>
    </resultMap>
    <sql id="baseColumn">
        id,department_name
    </sql>
    <select id="findById" parameterType="int" resultMap="baseMaps">
        select <include refid="baseColumn"></include> from department where id=#{id}
    </select>
    <insert id="addDepartment" parameterType="com.spring.handlers.model.Department">
        INSERT into `department` VALUES(null,#{departmentName});
    </insert>
    <update id="updateDepartment">
        update department 
        <trim prefix="set" suffixOverrides=",">
            <if test="departmentName != null and departmentName != ''">
                department_name = '${departmentName}' ,
            </if>
        </trim>
        where id = #{id}
    </update>
    <delete id="deleteByList" > 
        delete from department 
        <trim prefix="where">
            id in
            <foreach collection="list" item="id"
                 open="(" close=")" index="index" separator=",">
                #{id}
            </foreach>
        </trim>
    </delete>
</mapper>

为了调试方便，加入log4j配置来查看调试结果：log4j.properties

log4j.rootLogger=DEBUG, Console  

#Console  
log4j.appender.Console=org.apache.log4j.ConsoleAppender  
log4j.appender.Console.layout=org.apache.log4j.PatternLayout  
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n  

log4j.logger.java.sql.ResultSet=INFO  
log4j.logger.org.apache=INFO  
log4j.logger.java.sql.Connection=DEBUG  
log4j.logger.java.sql.Statement=DEBUG  
log4j.logger.java.sql.PreparedStatement=DEBUG

编写接口，映射实体类对应的xml文件，先是IUser.java接口

package com.spring.handlers.dao;

import java.util.List;

import com.spring.handlers.model.User;

public interface IUser {

    public User findById(Integer id);

    public List<User> findListByUser(User user);

    public int addUser(User user);

    public int updateUser(User user);

    public int updateUserTwo(User user);

    public int deleteUserByIds(List<Integer> list);

}

IDepartment.java接口

package com.spring.handlers.dao;

import java.util.List;

import com.spring.handlers.model.Department;
import com.spring.handlers.util.SqlFactoryUtil;

public interface IDepartment {

    public Department findById(Integer id);

    public int addDepartment(Department department);

    public int updateDepartment(Department department);

    public int deleteByList(List<Integer> list);

}

工厂类 SqlFactoryUtil.java，读取mybatis的配置文件，创建操作数据库的session。

package com.spring.handlers.util;

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

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

import com.spring.handlers.model.Department;
import com.spring.handlers.model.User;

public class SqlFactoryUtil {

    public static final String resource = "MyBatisConfig.xml";
    Reader reader = null;
    SqlSession session = null;

    public SqlSession getSqlSession() {
        try {
            reader = Resources.getResourceAsReader(resource);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);
        return sessionFactory.openSession();
    }

    public static void main(String[] args) {
        SqlFactoryUtil util = new SqlFactoryUtil();
        SqlSession session  = util.getSqlSession();

        /*User user = session.selectOne("findById",3);      
        System.out.println(user.toString());*/      
        /*User user2 = new User();
        user2.setPassword("haha1");
        user2.setUserName("测试1");
        user2.setDepartmentId(2);
        int res = session.update("addUser",user2);*/

        User user = new User();
        user.setId(1);
        user = session.selectOne("findById", user);

        /*Department department = new Department();

        department.setDepartmentName("销售部");
        int res = session.update("addDepartment",department);*/

        session.commit();
        //System.out.println(user2.toString());
        System.out.println(user.toString());
        session.close();
    }   
}

测试类 test.java

package com.spring.handlers.dao;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.spring.handlers.model.Department;
import com.spring.handlers.model.User;
import com.spring.handlers.util.SqlFactoryUtil;

public class Test {

    public static void main(String[] args) {
        SqlFactoryUtil util = new SqlFactoryUtil();
        SqlSession session = util.getSqlSession();
        //SqlSession session1 = util.getSqlSession();
        IUser iUser = session.getMapper(IUser.class);
        User user = new User();
        user.setUserName("测试da");
        user.setId(7);
        //List<User> list = iUser.findListByUser(user);
        //System.out.println(iUser.addUser(user));
        //System.out.println(iUser.updateUser(user));
        List<Integer> list = new ArrayList<>();
        list.add(7);
        list.add(6);
        list.add(5);
        //System.out.println(iUser.deleteUserByIds(list));
        IDepartment iDepartment = session.getMapper(IDepartment.class);
        //Department department = iDepartment.findById(1);
        Department department2 = new Department();
        department2.setDepartmentName("产品部");
        department2.setId(4);
        //int s = iDepartment.updateDepartment(department2);
        int s = iDepartment.deleteByList(list);
        /*for(User u : list) {
            System.out.println(u.toString());
        }*/
        System.out.println(s);

        session.commit();
        session.close();
    }   
}

注意：使用mybatis需要导入相应的jar包

在mybatis的官网上下载jar包，全部导入即可。

mybatis的jar包导入

如图，下载好后，将mybatis.jar还有 lib 文件中的依赖包全部导入。lib文件中包括了 log4j的jar包了。

文件目录结构图

注意：图中红线划的都是不需要的包或者类。不用看。
最底下的配置文件都是在 src 目录下的。

---