crud of MyBatis single table

1. Create maven project and add dependency, junit, mysql, and mybatis(3.4.6)

2. Create the configuration file of mybatis under Resources

   <?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">
   
   <!--
       //The configuration file contains a configuration node
           //The configuration information is environment and mapping
           　//In the environment, there is datasource, which contains four familiar strings connecting to the database
   -->
   <configuration>
       <environments default="development">
           <environment id="development">
               <transactionManager type="JDBC"/>
               <dataSource type="POOLED">
                   <property name="driver" value="com.mysql.jdbc.Driver"/>
                   <property name="url" value="jdbc:mysql://localhost:3306/hello"/>
                   <property name="username" value="root"/>
                   <property name="password" value="123456"/>
               </dataSource>
           </environment>
       </environments>
       <mappers>
           <mapper resource="com/qfedu/pojo/UserMapper.xml"/>
       </mappers>
   </configuration>
   

3. db.sql

   CREATE TABLE `user` (
     `uid` int(11) NOT NULL AUTO_INCREMENT,
     `username` varchar(20) NOT NULL,
     `password` varchar(20) NOT NULL,
     `age` int(11) DEFAULT NULL,
     `addr` varchar(50) DEFAULT NULL,
     PRIMARY KEY (`uid`),
     UNIQUE KEY `username` (`username`)
   ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1
   

4. User.java

   package com.qfedu.pojo;
   
   public class User {
   
       private int uid;
       private String username;
       private String password;
       private int age;
       private String addr;
   
       @Override
       public String toString() {
           return "User{" +
                   "uid=" + uid +
                   ", username='" + username + '\'' +
                   ", password='" + password + '\'' +
                   ", age=" + age +
                   ", addr='" + addr + '\'' +
                   '}';
       }
   
       public int getUid() {
           return uid;
       }
   
       public void setUid(int uid) {
           this.uid = uid;
       }
   
       public String getUsername() {
           return username;
       }
   
       public void setUsername(String username) {
           this.username = username;
       }
   
       public String getPassword() {
           return password;
       }
   
       public void setPassword(String password) {
           this.password = password;
       }
   
       public int getAge() {
           return age;
       }
   
       public void setAge(int age) {
           this.age = age;
       }
   
       public String getAddr() {
           return addr;
       }
   
       public void setAddr(String addr) {
           this.addr = addr;
       }
   }
   

5. Create the UserMapper.xml file under the package of com.qfedu.pojo

   <?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">
   
   <!--
       //Each mapper file will have its own mapped namespace,
       //Each method corresponds to its own sql statement, and each sql statement corresponds to an id
       //All namespace.id s in the entire project must be unique
   -->
   <mapper namespace="com.qfedu.pojo.UserMapper">
       <select id="selectUser" resultType="com.qfedu.pojo.User">
         select * from user
       </select>
   
       <select id="selectUserCount" resultType="int">
           select count(1) from user;
       </select>
   
       <select id="selectUsersByPage1" resultType="com.qfedu.pojo.User">
           select * from user limit 3
       </select>
   
       <select id="selectUsersByPage2" resultType="com.qfedu.pojo.User">
           select * from user limit #{pageSize}
       </select>
   
       <select id="selectUsersByPage3" resultType="com.qfedu.pojo.User">
           select * from user limit #{startIndex}, #{pageSize}
       </select>
   
       <delete id="saveUser">
           insert into user values(null, #{username}, #{password}, #{age}, #{addr});
       </delete>
   
       <delete id="deleteUserByUid">
           delete from user where uid = #{uid}
       </delete>
   
       <update id="updateUser">
           update user set username= #{username}, password=#{password}, age = #{age}, addr = #{addr} where uid = #{uid};
       </update>
   </mapper>
   

6. TestUser.java

   package com.qfedu.test;
   
   import com.qfedu.pojo.User;
   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 org.junit.After;
   import org.junit.Before;
   import org.junit.Test;
   
   import java.io.IOException;
   import java.sql.Connection;
   import java.util.HashMap;
   import java.util.List;
   import java.util.Map;
   
   public class TestUser {
   
       private SqlSessionFactory sf = null;
       private SqlSession session = null;
   
       @Before
       public void setUp(){
   
           try {
               sf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
   
               session = sf.openSession();
           } catch (IOException e) {
               e.printStackTrace();
           }
       }
   
       @After
       public void tearDown(){
           if(session != null){
               session.close();
               session = null;
           }
       }
   
       @Test
       public void testGetAllUsers(){
   
           /**
            *  1.  Create SqlSessionFactory objects using mybatis's configuration file and SqlSessionFactoryBuilder builder mode
            *  2.  Use the openSession() method of SqlSessionFactory object to get a SqlSession object, and use the object to complete all crud operations of the object
            *  3.  Using SqlSession object to complete crud operation
            *  4.  close resource
            *
            */
   
           /**
            * session The crud method ("namespace. ID") of; the namespace.id must be unique throughout the project
            */
           List<User> users = session.selectList("com.qfedu.pojo.UserMapper.selectUser");
   
           for (User u : users) {
               System.out.println(u);
           }
       }
   
       @Test
       public void testGetAllUsersCount(){
   
           /**
            *  1.  Create SqlSessionFactory objects using mybatis's configuration file and SqlSessionFactoryBuilder builder mode
            *  2.  Use the openSession() method of SqlSessionFactory object to get a SqlSession object, and use the object to complete all crud operations of the object
            *  3.  Using SqlSession object to complete crud operation
            *  4.  close resource
            *
            */
           /**
            * session The crud method ("namespace. ID") of; the namespace.id must be unique throughout the project
            */
           Integer count = session.selectOne("com.qfedu.pojo.UserMapper.selectUserCount");
   
           System.out.println(count);
       }
   
       @Test
       public void testGetUsersByPage1(){
           List<User> users = session.selectList("com.qfedu.pojo.UserMapper.selectUsersByPage1");
   
           for (User u : users) {
               System.out.println(u);
           }
       }
   
       @Test
       public void testGetUsersByPage2(){
           List<User> users = session.selectList("com.qfedu.pojo.UserMapper.selectUsersByPage2", 9);
   
           for (User u : users) {
               System.out.println(u);
           }
       }
   
       @Test
       public void testGetUsersByPage3(){
   
           Map<String, Integer> map = new HashMap<>();
   
           map.put("startIndex", 10);
           map.put("pageSize", 5);
   
           List<User> users = session.selectList("com.qfedu.pojo.UserMapper.selectUsersByPage3", map);
   
           for (User u : users) {
               System.out.println(u);
           }
       }
   
       @Test
       public  void testSaveUser(){
           User u = new User();
   
           u.setUsername("lisi");
           u.setPassword("888888");
           u.setAddr("wuhan");
           u.setAge(20);
   
           int result = session.insert("com.qfedu.pojo.UserMapper.saveUser", u);
   
           System.out.println(result);
       }
   
       @Test
       public void testDelete(){
           Integer result = session.delete("com.qfedu.pojo.UserMapper.deleteUserByUid", 9);
   
   
           System.out.println(result);
       }
   
       @Test
       public void testUpdate(){
           User user = new User();
   
           user.setUid(8);
           user.setUsername("888");
           user.setPassword("lisixin");
   //        user.setAge(20);
   //        user.setAddr("nanjing");
   
           int result = session.update("com.qfedu.pojo.UserMapper.updateUser", user);
           session.commit();       //
   
           System.out.println(result);
       }
   }
   

Some problems should be noted in this case:

1. Map file of UserMapper:
  	1. namespace and id must be unique in the whole project
  	2. namespace+id finds a unique sql statement. The labels may not correspond to each other, but they do not affect the execution results. However, you still need to standardize each label
2.	TestUser.java
  	1. When creating a SqlSession object, if the openSession() method of the SqlSessionFactory object does not contain parameters, the transaction is used for manual submission. After the addition, deletion and modification, the commit() method of the SqlSession object needs to be called to complete the transaction submission. If the openSession() method contains a true value, it means that the automatic submission takes effect. When we finish the operation of adding, deleting and modifying, we have completed the corresponding function of adding, deleting and modifying. The default submission method of openSession() method is manual submission
  	2. This class makes use of junit's life cycle methods. Before each test method is executed, it will call the set() method marked with @ before annotation to complete the preparation of the environment. After each method is completed, it will automatically execute the tearDown() method marked with @ after annotation to complete the release of resources
  	3. SqlSession is the core object in mybatis, which can be used to complete the crud function for all operations. There are corresponding methods, selectList (), query list (set), selectOne () query single object, insert (), delete, update () corresponding to the function of adding, deleting and modifying. The return values of all three methods are the number of rows affected
  	4. After using SqlSession object, close SqlSession object with close() method. It is not recommended to close SqlSessionFactory object
  	5. About transfer parameters
       	1. Pass a single value, and the placeholders in the sql statement can be written at will
       	2. Multiple values. You can use map to transfer values. The key in the map should be consistent with the placeholder in the sql statement
       	3. Object passing value. The field value in sql statement is consistent with the property name of the object
       	4. There are two ways to transfer the scattered value: arg0, arg1,... Or param1, param2