spring + mybatis 的 crud

Love The Way You Lie 2021-09-26 08:42 401阅读 0赞

# 前提 #

要先搭建好Spring基础环境。[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&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>
    
        <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>
        
        <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    
                
                <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 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">
        
    <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包导入 ##

![这里写图片描述][SouthEast]  
如图，下载好后，将mybatis.jar还有 **lib** 文件中的依赖包全部导入。lib文件中包括了 log4j的jar包了。

# 文件目录结构图 #

![这里写图片描述][SouthEast 1]

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

--------------------

[spring]: http://blog.csdn.net/qinqigang/article/details/78594141
[SouthEast]: /images/20210923/255808e9cc914b3db3bd78682346d469.png
[SouthEast 1]: /images/20210923/2cfb5a1c878948e28431715c17fe47e3.png