Mybatis第二篇【CRUD、分页】

Bertha 。 2021-09-17 07:14 350阅读 0赞

# 完成CRUD操作 #

我们在上一篇中已经简单知道了Mybatis是怎么使用的以及工作流程了，这次我们使用Mybatis来完成CRUD的操作，再次巩固Mybatis的开发步骤以及一些细节

包与类之间的结构

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

## 增加学生 ##

配置文件

<?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>
        
        <properties resource="db.properties"/>
    
        
        <environments default="mysql_developer">
            
            <environment id="mysql_developer">
                
                <transactionManager type="jdbc"/>
                
                <dataSource type="pooled">
                    
                    <property name="driver" value="${mysql.driver}"/>
                    <property name="url" value="${mysql.url}"/>
                    <property name="username" value="${mysql.username}"/>
                    <property name="password" value="${mysql.password}"/>
                </dataSource>
            </environment>
    
    
            
            <environment id="oracle_developer">
                
                <transactionManager type="jdbc"/>
                
                <dataSource type="pooled">
                    
                    <property name="driver" value="${oracle.driver}"/>
                    <property name="url" value="${oracle.url}"/>
                    <property name="username" value="${oracle.username}"/>
                    <property name="password" value="${oracle.password}"/>
                </dataSource>
            </environment>
        </environments>
        <mappers>
            <mapper resource="zhongfucheng/StudentMapper.xml"/>
        </mappers>
    </configuration>

映射文件

<mapper namespace="StudentID">
    
        
        <resultMap type="zhongfucheng.Student" id="studentMap">
            
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="sal" column="sal"/>
        </resultMap>
    
        <insert id="add" parameterType="zhongfucheng.Student">
            INSERT INTO ZHONGFUCHENG.STUDENTS (ID, NAME, SAL) VALUES (#{id},#{name},#{sal});
        </insert>
    
    </mapper>

插入数据

public class StudentDao { 
    
        public void add(Student student) throws Exception {
            //得到连接对象
            SqlSession sqlSession = MybatisUtil.getSqlSession();
            try{
                //映射文件的命名空间.SQL片段的ID，就可以调用对应的映射文件中的SQL
                sqlSession.insert("StudentID.add", student);
                sqlSession.commit();
            }catch(Exception e){
                e.printStackTrace();
                sqlSession.rollback();
                throw e;
            }finally{
                MybatisUtil.closeSqlSession();
            }
        }
        public static void main(String[] args) throws Exception {
            StudentDao studentDao = new StudentDao();
            Student student = new Student(3, "zhong3", 10000D);
            studentDao.add(student);
        }
    }

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

## 根据ID查询数据 ##

增加select标签

<select id="findById" parameterType="int" resultMap="studentMap">
            SELECT * FROM STUDENTS WHERE id = #{id};
        </select>

查询出来的结果是一个Student对象，我们调用SelectOne方法

public Student findById(int id) throws Exception {
            //得到连接对象
            SqlSession sqlSession = MybatisUtil.getSqlSession();
            try{
                //映射文件的命名空间.SQL片段的ID，就可以调用对应的映射文件中的SQL
                return sqlSession.selectOne("StudentID.findById",id);
            }catch(Exception e){
                e.printStackTrace();
                sqlSession.rollback();
                throw e;
            }finally{
                MybatisUtil.closeSqlSession();
            }
        }
        public static void main(String[] args) throws Exception {
            StudentDao studentDao = new StudentDao();
            Student student = studentDao.findById(1);
            System.out.println(student.getName());
    
        }

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

## 查询所有数据 ##

<select id="findAll" resultMap="studentMap">
            SELECT * FROM STUDENTS;
        </select>

我们查询出来的结果不单单只有一个对象了，因此我们使用的是SelectList这个方法

public List<Student> findAll() throws Exception {
            //得到连接对象
            SqlSession sqlSession = MybatisUtil.getSqlSession();
            try{
                //映射文件的命名空间.SQL片段的ID，就可以调用对应的映射文件中的SQL
                return sqlSession.selectList("StudentID.findAll");
            }catch(Exception e){
                e.printStackTrace();
                sqlSession.rollback();
                throw e;
            }finally{
                MybatisUtil.closeSqlSession();
            }
        }
        public static void main(String[] args) throws Exception {
            StudentDao studentDao = new StudentDao();
            List<Student> students = studentDao.findAll();
            System.out.println(students.size());
    
        }

## 根据id删除 ##

<delete id="delete" parameterType="int">
            DELETE FROM STUDENTS WHERE id=#{id};
    
        </delete>

调用delete方法删除

public void delete(int id ) throws Exception { //得到连接对象 SqlSession sqlSession = MybatisUtil.getSqlSession(); try{ //映射文件的命名空间.SQL片段的ID，就可以调用对应的映射文件中的SQL sqlSession.delete("StudentID.delete", id); sqlSession.commit(); }catch(Exception e){ e.printStackTrace(); sqlSession.rollback(); throw e; }finally{ MybatisUtil.closeSqlSession(); } } public static void main(String[] args) throws Exception { StudentDao studentDao = new StudentDao(); studentDao.delete(1); }

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

## 修改 ##

<update id="update" parameterType="zhongfucheng.Student">
    
            update students set name=#{name},sal=#{sal} where id=#{id};
    
        </update>

查询出对应的对象，对其进行修改

public void update(Student student ) throws Exception { //得到连接对象 SqlSession sqlSession = MybatisUtil.getSqlSession(); try{ //映射文件的命名空间.SQL片段的ID，就可以调用对应的映射文件中的SQL sqlSession.update("StudentID.update", student); sqlSession.commit(); }catch(Exception e){ e.printStackTrace(); sqlSession.rollback(); throw e; }finally{ MybatisUtil.closeSqlSession(); } } public static void main(String[] args) throws Exception { StudentDao studentDao = new StudentDao(); Student student = studentDao.findById(2); student.setName("fucheng"); student.setSal(2000D); studentDao.update(student); }

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

## 小细节 ##

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

# Mybatis分页 #

分页是一个非常实用的技术点，我们也来学习一下使用Mybatis是怎么分页的…

我们的分页是需要多个参数的，并不是像我们之前的例子中只有一个参数。**当需要接收多个参数的时候，我们使用Map集合来装载**！

public List<Student>  pagination(int start ,int end) throws Exception {
            //得到连接对象
            SqlSession sqlSession = MybatisUtil.getSqlSession();
            try{
                //映射文件的命名空间.SQL片段的ID，就可以调用对应的映射文件中的SQL
    
    
                /** * 由于我们的参数超过了两个，而方法中只有一个Object参数收集 * 因此我们使用Map集合来装载我们的参数 */
                Map<String, Object> map = new HashMap();
                map.put("start", start);
                map.put("end", end);
                return sqlSession.selectList("StudentID.pagination", map);
            }catch(Exception e){
                e.printStackTrace();
                sqlSession.rollback();
                throw e;
            }finally{
                MybatisUtil.closeSqlSession();
            }
        }
        public static void main(String[] args) throws Exception {
            StudentDao studentDao = new StudentDao();
            List<Student> students = studentDao.pagination(0, 3);
            for (Student student : students) {
    
                System.out.println(student.getId());
    
            }
    
        }

那么在实体与表映射文件中，我们接收的参数就是map集合

<select id="pagination" parameterType="map" resultMap="studentMap"> /*根据key自动找到对应Map集合的value*/ select * from students limit #{start},#{end}; </select>

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

[SouthEast]: /images/20210827/d3143df3bc754eef9ffcbaabc9e78be9.png
[SouthEast 1]: /images/20210827/e99fbd959dac4418b3afd92bd8f761a8.png
[SouthEast 2]: /images/20210827/b021aebc85334a368aa7bf3d90f8e0da.png
[SouthEast 3]: /images/20210827/e5078be8e9474ebeb05d05dd548a19f9.png
[SouthEast 4]: /images/20210827/7f90005b00004acba73771e7a66e982b.png
[SouthEast 5]: /images/20210827/4d5b973eb9524f6eb10d3ee69058bd83.png