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 目录下的。

---