MyBatis Getting Started to CURD

As a happy little farmer, we often repeat different versions at every stage, student management, user management, registered logins, from the console version of JavaSE, or GUI version, to the JSP version of JavaWeb, to the version that just uses HTML as a front-end presentation, and to a newer technology where we've used TXTMaking a database, using XML, is also possible, now commonly used MySQL, add or delete check has always been an essential part of our content, even if you do not understand the principles, even if you do not have a deep understanding of the technology, take out your add or delete check, cracking is a random tap, the good things can still let you start (of course, the understanding of technology is still important), today I would like to chat with you about MyBatis technology.CURD (add delete check)

Optimize test methods

In test methods, reading configuration files, producing SqlSession, releasing resources, and so on, are duplicated in each test method, so we can completely propose this part to prevent a lot of duplicate code

@Before
    public  void init() throws Exception{
        //Read Configuration File
        inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //Create SqlSessionFactory Factory
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);

        //Use factory to produce SqlSession object
        sqlSession= factory.openSession();
        //Creating a proxy object for the Mapper interface using SqlSession
        userMapper = sqlSession.getMapper(UserMapper.class);
    }

 @After
    public void destroy() throws Exception{
        sqlSession.close();
        inputStream.close();
    }

Increase on both methods @Before The @Aftrer comment guarantees that the init() and destory() methods are executed before and after the method we are actually testing

(1) Additional operations

(1) Writing code

First, add a corresponding method to the UserMapper interface

public interface UserMapper {
    /**
     * Increase Users
     * @param user
     */
    void addUser(User user);
}

Next, in the SQL mapping file, add the new mapping configuration, which is placed in the <insert></insert>tag pair with the following code

<insert id="addUser" parameterType="cn.ideal.domain.User">
	insert into 	 				     user(username,telephone,birthday,gender,address)values(#{username},#	{telephone},#{birthday},#{gender},#{address})
</insert>

(2) Description:

1. The id attribute, of course, is the corresponding method name. Since here we don't need to get the return information, we don't return the resultType parameter. The parameter in the method is a JavaBean class, also known as the User entity class. Therefore, we need to add a parameterType attribute to the tag attribute, which needs to specify this entity class.

2. Write the inserted SQL statement in the text. Since the corresponding get set method has been generated quickly in the entity class, one can use #{} to represent the corresponding value.

3. Hint, the id in the database increases by itself, so it is not necessary to set the id

(3) Note:

Since the addition is a statement of the update class, after the insertion statement is executed, the transaction is committed, that is, the corresponding commit method is executed to commit the update operation. Without this sentence, it cannot be saved properly even without error, is rolled back, and the id is occupied

(4) Test code:

/**
     * Testing for new users
     * @throws Exception
     */
    @Test
    public void testUpdateUser() throws Exception{
        User user = new User();
        user.setId(17);
        user.setUsername("modify");
        user.setTelephone("18899999999");
        user.setBirthday(new Date());
        user.setGender("female");
        user.setAddress("Guangzhou");

        //Execution Method
        userMapper.updateUser(user);

    }

(5) Execution results:

Console:

(6) Get the id value of the new user

For MySQL self-increasing primary keys, MySQL automatically generates a self-increasing primary key before insert statements are executed. After insert execution, the self-increasing primary key for the newly inserted record can be obtained by SELECT LAST_INSERT_ID().

In the SQL mapping configuration file, you need to use the <selectKey></selectKey> tag. There is a special attribute, the order attribute, which represents the execution time relative to the insert operation, before-after-after

Note: The label is inserted in <select></select>

<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
	SELECT LAST_INSERT_ID();
</selectKey>

Test it

@Test
public void testAddUser() throws Exception{
    User user = new User();
    user.setUsername("increase");
    user.setTelephone("12266660000");
    user.setBirthday(new Date());
    user.setGender("male");
    user.setAddress("Zhuhai");
    System.out.println("Before insertion" + user);
    //Execution Method
    userMapper.addUser(user);
    System.out.println("After insertion" + user);
}

Execution effect

(2) Modification

(1) Writing code

Add a modification method to the UserMapper interface

public interface UserMapper {
    /**
     * Update User
     * @param user
     */
    void updateUser(User user);
}

Adding statements to the SQL mapping file, including <update></update>, is essentially the same as adding

<update id="updateUser" parameterType="cn.ideal.domain.User">
	update user set username=#{username},telephone=#{telephone},birthday=#{birthday},gender=#{gender},address=#{address} where id=#{id}
</update>

(2) Test code

/**
     * Testing for new users
     * @throws Exception
     */
    @Test
    public void testAddUser() throws Exception{
        User user = new User();
        user.setUsername("increase");
        user.setTelephone("12266668888");
        user.setBirthday(new Date());
        user.setGender("female");
        user.setAddress("Chengdu");

        //Execution Method
        userMapper.addUser(user);

    }

(3) Effect of execution

(3) Delete operation

(1) Writing code

Add Delete Method to Interface

public interface UserMapper {
    /**
     * delete user
     * @param uid
     */
    void deleteUser(Integer uid);
}

In the SQL mapping file, use the <delete></delete>tag to write the content, it is important to note that since the parameter we passed in is an Integer type user id, the value of the parameter type is parameterType

<delete id="deleteUser" parameterType="java.lang.Integer">
	delete from user where id=#{id}
</delete>

(2) Test code

/**
 * Test Delete User
 * @throws Exception
 */
@Test
public void testDeleteUser() throws Exception{
    //Execution Method
    userMapper.deleteUser(17);
}

(3) Effect of execution

(4) Fuzzy Query

Since queries are all very simple, they are not shown here, and the basic process is the same

(1) Writing code

Writing methods in the UserMapper interface

public interface UserMapper {
    /**
     * Fuzzy query by name
     * @param username
     * @return
     */
    List<User> findByName(String username);
}

New Query Statement in SQL Mapping File

<select id="findByName" parameterType="java.lang.String" resultType="cn.ideal.domain.User">
	select * from user where username like #{username}
</select>

(2) Test code

/**
 * Test Fuzzy Query
 * @throws Exception
 */
@Test
public void testFindByName() throws Exception{
	List<User> users = userMapper.findByName("%Zhang%");
	for (User user : users){
    	System.out.println(user);
    }
}

(3) Attention

When using fuzzy queries, we need to stitch two'%'strings on both sides of the query criteria. There are two solutions at this time, one is to complete the strings in the test like in my code above, the other is to use ${}, which represents a stitching symbol in the SQL configuration file, that is, you can write SQL like thissentence

select * from user where username like '%{value}'

Acceptable types are common (in this case {} can only write value internally), JavaBean, HashMap

But using%{} to stitch strings can cause SQL injection, so it is not recommended.

(4) Effect of execution

(5) Custom packing classes as query criteria

In Mapper's input mapping example, we have some knowledge of basic data types and basic data wrapping classes, but let's talk about a relatively complex case: custom wrapping classes

Let's start with a requirement: Or a query about the user, but the query conditions are more complex, not only limited to the user's information, but may also include orders, shopping carts, or information related to the user's behavior. How do you achieve such a requirement?

Then we want to see if we can add some information we need to the User class

• From the code point of view, the fields added in User do not necessarily correspond to the database. Modifying on the original basis will affect User's function as a database mapping object, so we can create a UserInstance class, inherit the User class and add some fields that are not database for some business.

(1) Define packaging classes

package cn.ideal.domain;

public class QueryUserVo {
    private UserInstance userInstance;

    public UserInstance getUserInstance() {
        return userInstance;
    }

    public void setUserInstance(UserInstance userInstance) {
        this.userInstance = userInstance;
    }

    //Other query criteria, such as orders, shopping carts, etc.
}

(2) Configure Mapper files

We use the user's gender and fuzzy queries for names to write SQL here. Of course, you can also write SQL yourself with other information.

<select id="findUserByVo" parameterType="cn.ideal.domain.QueryUserVo" resultType="cn.ideal.domain.UserInstance">
	select * from user where user.gender=#{userInstance.gender} and user.username like #{userInstance.username}
</select>

In QueryUserVo, we encapsulate all kinds of objects that query information. Why can the above code take out the corresponding attributes directly through userInstance.gender? This is called OGNL expression. We usually write user.getUsername in class, but in writing, OGNL expression omits get

(3) Test code

/**
 * Wrapper object as query parameter
 * @throws Exception
 */
@Test
public void testFindUserByVo() throws Exception{
	//Create wrapper objects and set query criteria
    QueryUserVo queryUserVo = new QueryUserVo();
    UserInstance userInstance = new UserInstance();
    userInstance.setGender("female");
    userInstance.setUsername("%Zhang%");
    queryUserVo.setUserInstance(userInstance);

    //Call UserMapper's method
    List<UserInstance> userInstances 
      	= userMapper.findUserByVo(queryUserVo);
    for (UserInstance u : userInstances){
		System.out.println(u);
    }
}

(4) Effect of execution

Ending

If there are any deficiencies in the article, please leave a message to correct them and thank you for your support!

If you can help, pay attention to me!If you prefer the way WeChat articles are read, you can focus on my public number