Complete CRUD operation

In the previous article, we had a brief idea of how Mybatis works and how it works. This time, we used Mybatis to complete the CRUD, reinforcing Mybatis development steps and some details

Structure between Package and Class

Increase Students

configuration file

<?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>
    <!--Load the properties file under the class path-->
    <properties resource="db.properties"/>

    <!--Set a defau lt connection environment information-->
    <environments default="mysql_developer">
        <!--Connect the environment information and give it any unique name-->
        <environment id="mysql_developer">
            <! -- mybatis uses jdbc transaction management -->
            <transactionManager type="jdbc"/>
            <!-- mybatis uses connection pooling to get connections-->
            <dataSource type="pooled">
                <!--Configure the four necessary attributes for interacting with the database-->
                <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>


        <!--Connect the environment information and give it any unique name-->
        <environment id="oracle_developer">
            <! -- mybatis uses jdbc transaction management -->
            <transactionManager type="jdbc"/>
            <!-- mybatis uses connection pooling to get connections-->
            <dataSource type="pooled">
                <!--Configure the four necessary attributes for interacting with the database-->
                <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>

Mapping File

<!-- namespace Attributes are namespaces and must be unique -->
<mapper namespace="StudentID">

    <!-- resultMap Label:Mapping Entities and Tables 
         type Attribute: Represents the entity full path name
         id Attribute: Give an arbitrary unique name to the entity-table mapping
    -->
    <resultMap type="zhongfucheng.Student" id="studentMap">
        <!-- id Label:Mapping Primary Key Properties
             result Label: Map non-primary key properties
             property attribute:Attribute name of entity
             column Attribute: Field name of table
        -->
        <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>

insert data

public class StudentDao {

    public void add(Student student) throws Exception {
        //Get Connected Object
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{
            //Namespace of the mapping file. ID of the SQL fragment, you can call the SQL in the corresponding mapping file
            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);
    }
}

Query data based on ID

Add select tag

    <!--
        Query by id
        The property resultMap represents the return value type, the return value type is Student, which is the entity type above
    -->
    <select id="findById" parameterType="int" resultMap="studentMap">
        SELECT * FROM STUDENTS WHERE id = #{id};
    </select>

The result of the query is a Student object, and we call the SelectOne method

    public Student findById(int id) throws Exception {
        //Get Connected Object
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{
            //Namespace of the mapping file. ID of the SQL fragment, you can call the SQL in the corresponding mapping file
            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());

    }

Query all data

    <!--
        Query all data
        The return value type makes sense in List<Student>but we just need to write the type in the set
    -->
    <select id="findAll" resultMap="studentMap">
        SELECT * FROM STUDENTS;
    </select>

The result we queried was not just one object, so we used the SelectList method

  public List<Student> findAll() throws Exception {
        //Get Connected Object
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{
            //Namespace of the mapping file. ID of the SQL fragment, you can call the SQL in the corresponding mapping file
            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());

    }

Delete by id

    <!--according to id delete-->
    <delete id="delete" parameterType="int">
        DELETE FROM STUDENTS WHERE id=#{id};

    </delete>

Call delete method to delete

    public void delete(int id ) throws Exception {
        //Get Connected Object
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{
            //Namespace of the mapping file. ID of the SQL fragment, you can call the SQL in the corresponding mapping file
            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);

    }

modify

    <!--To update-->
    <update id="update" parameterType="zhongfucheng.Student">

        update students set name=#{name},sal=#{sal} where id=#{id};

    </update>

Query out the corresponding object and modify it

    public void update(Student student ) throws Exception {
        //Get Connected Object
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{
            //Namespace of the mapping file. ID of the SQL fragment, you can call the SQL in the corresponding mapping file
            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);

    }

Small details

    <!-- 
        Note: This insert/update/delete tag is only a template and actually takes the SQL statement as the core when doing operations
             That is, insert/update/delete tags are generic when adding/deleting/
             But only select tags can be used when making queries
             What label do we advocate
    --> 

Mybatis Paging

Paging is a very useful technique, so let's also learn how to use Mybatis to paginate...

Our paging requires multiple parameters, not just one, as in our previous example.When multiple parameters need to be received, we use the Map collection to load!

    public List<Student>  pagination(int start ,int end) throws Exception {
        //Get Connected Object
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try{
            //Namespace of the mapping file. ID of the SQL fragment, you can call the SQL in the corresponding mapping file


            /**
             * Since we have more than two parameters, there is only one Object parameter collection in the method
             * So we use the Map collection to load our parameters
             */
            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());

        }

    }

So in the entity and table mapping files, the parameter we receive is the map collection

    <!--Paging Query-->
    <select id="pagination" parameterType="map" resultMap="studentMap">

        /*Automatically find the value of the corresponding Map collection based on the key*/
        select * from students limit #{start},#{end};

    </select>