Mybatis implements data addition, deletion, modification and query (detailed explanation)

#Create a data called mybatis
CREATE DATABASE `mybatis`;

#Using the mybatis database 
USE `mybatis`;
 
#Create a user table
DROP TABLE IF EXISTS `user`;
 
#insert data
CREATE TABLE `user` (
  `id` int(20) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `pwd` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
insert  into `user`(`id`,`name`,`pwd`) values (1,'Wang Yi','123456'),(2,'Huang er','456789'),(3,'Zhang San','987654');

2. Import relevant jar packages

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.2</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>
#If MySQL is version 8, use the following
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.22</version>
</dependency>

3. Create a configuration file for mybatis

<?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"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&amp;useUnicode=true&amp;characterEncoding=utf8"/>
                <property name="username" value="MySQL User name for"/>
                <property name="password" value="MySQL Password for"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/UserDaoMapper.xml"/>
    </mappers>

</configuration>

4. Create a tool class for the key objects SqlSessionFactory and SqlSession of mybatis

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 java.io.IOException;
import java.io.InputStream;
 
public class MybatisUtils {
 
    private static SqlSessionFactory sqlSessionFactory;
 
    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
 
    //Get SqlSession connection
    public static SqlSession getSession(){
        return sqlSessionFactory.openSession();
    }
 
}

5. Build entity class

public class User {
    private int id;
    private String name;
    private String pwd;
}
//get, set and tostring methods are required

6. Interface

public interface UserDao {

}

7. mapper file

<?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.dao.UserDao">
    

</mapper>

8. Directory structure diagram

Now our project is set up. Next, let's query, add, modify and delete.

"Check" operation

Query all User data

First, define a query method in our interface class. The parameter class is list < user > because we query all user data, the returned result must not be just one user's data, so it is defined as a collection.

import java.util.List;
import java.util.Map;

public interface UserDao {
    List<User> getUserList();
    
}

After defining, we need to configure our methods and SQL statements in the mapper file

<?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.dao.UserDao">
    <!--  there id Is the method name we defined on the interface before, resultType It refers to the type of returned results. We query user data, so the return interface is User -->
    <select id="getUserList" resultType="com.pojo.User">
        select * from user
    </select>

</mapper>

Test it

import com.pojo.User;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;


public class UserTest {
    @Test
    public void getUserList(){
        //Create a SqlSession object using our tool class 
        SqlSession sqlSession = MybatisUtils.getSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.getUserList();
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }
}

result

Process: interface class writing method - > mapper file configuration - > test

Query user by id

Interface class writing method

import com.pojo.User;

import java.util.List;
import java.util.Map;

public interface UserDao {
    List<User> getUserList();
    User getUserById( int ids);
}

mapper file

<?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.dao.UserDao">

    <select id="getUserList" resultType="com.pojo.User">
        select * from user
    </select>
    <select id="getUserById" resultType="com.pojo.User" parameterType="int">
        select * from user where id=#{ids}
    </select>


</mapper>

Test it

@Test
public void getUserByIdTest(){
        SqlSession sqlSession = MybatisUtils.getSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = userDao.getUserById(2);
        System.out.println(user);
        sqlSession.close();
    }

result

"Add" operation

Interface class addition method

public interface UserDao {
    List<User> getUserList();
    User getUserById( int ids);
    //Insert statements only succeed or fail, so we use boolean here 
    boolean addUser(User user);
}

mapper file

<!--be careful insert No resultType Return type-->
<insert id="addUser" parameterType="com.pojo.User">
    insert into user (id,name,pwd)values (#{id},#{name},#{pwd});
</insert>

Test it

 @Test
    public void addUserTest(){
        SqlSession sqlSession = MybatisUtils.getSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setId(4);
        user.setName("Li Si");
        user.setPwd("123456");
        if (userDao.addUser(user)) {
            System.out.println("User added successfully");
        }else{
            System.out.println("Failed to add user");
        }
        // Note that to commit a transaction using the commit method, you need to commit a transaction to change the data, otherwise the executed statement will be invalid
        sqlSession.commit();
        sqlSession.close();
    }

"Change" operation

If you follow the above operation, I believe everyone should and should be able to do it. I'll use other methods for this operation. Although it's not a formal method, it's very practical. Don't say much and get to the point

Interface class

public interface UserDao {
    List<User> getUserList();
    User getUserById( int ids);
    boolean addUser(User user);
    //Please note that the parameter of our change method is Map
    boolean updataUser(Map<String,Object> map);
}

mapper file

<!---->Because our parameter is Map，So we mapper The parameters of the file are also map
<update id="updataUser" parameterType="map">
    update user set name = #{name},pwd=#{pwd} where id=#{id};
</update>

Test it

@Test
    public void updataUserTest(){
        SqlSession sqlSession = MybatisUtils.getSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Map<String,Object> map = new HashMap<String,Object>();
        //The advantage of the map method is that it can be added directly in this way. It is applicable to the four operations of adding, deleting, modifying and querying
        map.put("id",1);
        map.put("name","Ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha ha");
        map.put("pwd","aaaaaaaa");
        if (userDao.updataUser(map)) {
            System.out.println("User changed successfully");
        }else{
            System.out.println("Failed to change user");
        }
        // Note that to commit a transaction using the commit method, you need to commit a transaction to change the data, otherwise the executed statement will be invalid
        sqlSession.commit();
        sqlSession.close();
    }

Results

Delete operation

Interface class

import com.pojo.User;

import java.util.List;
import java.util.Map;

public interface UserDao {
    List<User> getUserList();
    User getUserById( int ids);
    boolean addUser(User user);
    boolean updataUser(Map<String,Object> map);
    boolean deleteUser(Map<String,Object> map);
}

mapper file

<delete id="deleteUser" parameterType="map">
    delete * from user where id=#{id};
</delete>

Test it

 @Test
    public void deleteUserTest(){
        SqlSession sqlSession = MybatisUtils.getSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("id",1);
        if (userDao.updataUser(map)) {
            System.out.println("User deleted successfully");
        }else{
            System.out.println("Failed to delete user");
        }
        // Note that to commit a transaction using the commit method, you need to commit a transaction to change the data, otherwise the executed statement will be invalid
        sqlSession.commit();
        sqlSession.close();
    }

result

Source code

Baidu network disk, please enter the extraction codezce5

Topics: Java Database Mybatis

Programmer Think

Mybatis implements data addition, deletion, modification and query (detailed explanation)

1. Construction project

1. Build experimental database

2. Import relevant jar packages

3. Create a configuration file for mybatis

4. Create a tool class for the key objects SqlSessionFactory and SqlSession of mybatis

5. Build entity class

6. Interface

7. mapper file

8. Directory structure diagram

"Check" operation

Query all User data

Query user by id

"Change" operation

Delete operation

Source code

Hot Topics