JavaWeb: MyBatis-CURD

1. Review

The last article JavaWeb advanced road: MyBatis initial experience with you to MyBatis for a simple start, the next article will take you to continue to learn MyBatis commonly used functions!

2. XML mapper

The real power of MyBatis is its statement mapping, which is where its magic lies. Because of its extraordinary power, the MAPper’s XML file is relatively simple. If you compare it to JDBC code that does the same thing, you’ll immediately see that you’re saving nearly 95% of your code. MyBatis is committed to reducing the cost of use, so that users can focus more on SQL code.

An XML mapper is an XML file in which we write SQL statements, usually named xxxMapper.xml.

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xxx.xxx.xxx.mapper.xxxMapper">
    <cache></cache>
    <cache-ref></cache-ref>
    <resultMap></resultMap>
    <parameterMap></parameterMap>
    <sql></sql>
    <insert></insert>
    <update></update>
    <delete></delete>
    <select></select>
</mapper>
Copy the code

The SQL mapping file has only a few top-level elements (listed in the order they should be defined) :

3. SELECT (SELECT)

The select tag is one of the most commonly used elements in MyBatis.

The select element for a simple query is very simple, as follows:

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lmx.demo.mapper.UserMapper">
    <select id="selectUserById" parameterType="int" resultType="com.lmx.demo.pojo.User">
        select * from user where id = #{id};
    </select>
</mapper>
Copy the code

The corresponding usermapper. Java file:

pulic interface UserMapper {
    User selectUserById(int id);
} 
Copy the code

The test class:

public class UserMapperTest {
    
    @Test
    public void selectUserById(a) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        // Query the user whose ID is 3
        User user = userMapper.selectUserById(3); System.out.println(user); sqlSession.close(); }}Copy the code

Running result:

This statement is called selectUserById, which takes an int and returns an object of type User.

Is #{id} in JDBC PreparedStatement equivalent? Placeholder parameter.

There are many attributes in the select tag to configure each SQL statement in detail:

• id– A unique identifier in the namespace that can be used to refer to the statement. This corresponds to the method name in the object to which the namespace is mapped.
• parameterType– Class fully qualified name or alias of the parameters passed to the SQL statement. This attribute is optional and Mybatis will automatically determine the parameters of the incoming statement. The default value is unset.
• parameterMap– This attribute is used to reference an external parameterMap and has been deprecated. Use the inline parameter mapping and parameterType attribute.
• resultType– The fully qualified name or alias of the class returned in the query result. Note that if a collection is returned, it should be set to the type contained in the collection, not the type of the collection itself. Only one resultType and resultMap can be used at the same time.
• resultMap– A named reference to an external resultMap. The resulting mapping is the most powerful feature of MyBatis, and if you understand it well, many complex mapping problems can be solved easily. Only one resultType and resultMap can be used at the same time.
• flushCache– When set to true, the local and secondary caches will be cleared whenever the statement is called. Default: false.
• useCache– Setting this to true will cause the result of this statement to be cached in the second-level cache. Default: true for select elements.
• timeout– This setting is the number of seconds the driver waits for the database to return the result of the request before throwing the exception. Default is unset (dependent on database driver).
• fetchSize– This is a recommended value for the driver, try to make the number of rows returned by the driver in each batch equal to this setting. Default value is unset (dependent driver).
• statementType– STATEMENT, PREPARED, or CALLABLE are optional. This will cause MyBatis to use Statement, PreparedStatement, or CallableStatement, respectively. The default is PREPARED.
• resultSetType– One of FORWARD_ONLY, SCROLL_SENSITIVE, SCROLL_INSENSITIVE, or DEFAULT (equivalent to unset). The DEFAULT is unset (database-driven).
• databaseId– If the database vendor is configured, MyBatis loads all statements that do not have a databaseId or match the current databaseId; If there are both statements with and without, the unaccompanied statements are ignored.
• resultOrdered– This setting only applies to nested result select statements: If true, nested result sets or groups are assumed to be included, and when a primary result row is returned, no reference to the previous result set is generated. This makes it possible to get nested result sets without running out of memory. Default value:false.
• resultSets– This setting applies only to multiple result sets. It lists the result sets returned after the execution of the statement and gives each result set a name separated by commas.

Consider: If you need to query a user based on the name and password, how to query?

Idea 1:

Note @param(value) in the interface parameter identification SQL statement placeholder identification name.

pulic interface UserMapper {
    /** * Query users by name and password *@paramName Name, comment here@paramThe value in (value) corresponds to the placeholder identifier * of the SQL parameter@param"Password," password *@returnThe queried user */
    User selectUserByNameAndPassword(@Param("name") String name, @Param("password") String password);
} 
Copy the code

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lmx.demo.mapper.UserMapper">
    <! -->
	<select id="selectUserByNameAndPassword" resultType="com.lmx.demo.pojo.User">
        select * from user where name = #{name} and password = #{password} ;
    </select>
</mapper>
Copy the code

The test class:

public class UserMapperTest {
    
    @Test
    public void selectUserByNameAndPassword(a) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.selectUserByNameAndPassword("Zhang"."654321"); System.out.println(user); sqlSession.close(); }}Copy the code

Running result:

Idea 2:

Parameters are passed in Map key-value pairs.

pulic interface UserMapper {
    /** * Query users by name and password *@paramThe param parameter, which is passed as a key-value pair. * Key indicates the placeholder identifier of the parameter in the SQL statement. * Value Indicates the value of the parameter. * Map Indicates that there is no order for passing the parameter@returnThe queried user */
    User selectUserByNameAndPassword(Map<String, Object> param);
} 
Copy the code

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lmx.demo.mapper.UserMapper">
	<! -->
    <select id="selectUserByNameAndPassword" parameterType="map" resultType="com.lmx.demo.pojo.User">
        select * from user where name = #{name} and password = #{password} ;
    </select>
</mapper>
Copy the code

The test class:

public class UserMapperTest {
    
    @Test
    public void selectUserByNameAndPassword(a) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Object> param = new HashMap<>();
        param.put("name"."Zhang");
        param.put("password"."654321"); User user = userMapper.selectUserByNameAndPassword(param); System.out.println(user); sqlSession.close(); }}Copy the code

Running result:

From the above two ideas, we can see that if there are more parameters to be passed, we can use the map method, and when there are fewer parameters, we can choose the annotation @param method.

4. INSERT

Querying the database is definitely not enough. If we want to insert data into the database, we can use the INSERT tag. It’s similar to the properties of the select tag.

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lmx.demo.mapper.UserMapper">
	<insert
            id=""
            parameterType=""
            flushCache="true"
            statementType="PREPARED"
            keyProperty=""
            keyColumn=""
            useGeneratedKeys="false"
            timeout="20">
    </insert>
</mapper>
Copy the code

As you can see from the above, the insert tag attributes have three new additions, which also apply only to the INSERT and Update tags.

• useGeneratedKeysThis causes MyBatis to use JDBC’s getGeneratedKeys method to fetch the primary key generated internally by the database (for example: Auto-increment field for relational database management systems such as MySQL and SQL Server), default: false.
• keyProperty– (insert and Update only) specifies an attribute that uniquely identifies an object. MyBatis sets it using the return value of getGeneratedKeys or the selectKey child of the INSERT statement. Default: not set (unset). If more than one column is generated, you can separate multiple attribute names with commas.
• keyColumn– (for INSERT and Update only) Sets the name of the column in the table where the key is generated. In some databases (like PostgreSQL), this is mandatory if the primary key column is not the first column in the table. If more than one column is generated, you can separate multiple attribute names with commas.

The use of the insert tag is also very simple:

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lmx.demo.mapper.UserMapper">
	<insert id="addUser" parameterType="com.lmx.demo.pojo.User">
        insert into user (name, password) VALUES (#{name}, #{password});
    </insert>
</mapper>
Copy the code

public interface UserMapper {

    int addUser(User user);
    
}
Copy the code

The test class:

public class UserMapperTest {
    
    @Test
    public void addUser(a) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setName("Fifty");
        user.setPassword("123123");
        int result = userMapper.addUser(user);
        System.out.println(result > 0 ? "Added successfully!" : "Add failed!");
        // Insert, UPDATE, and DELETE require transaction commit.sqlSession.commit(); sqlSession.close(); }}Copy the code

Running result:

Note: Mybatis insert, UPDATE and delete operations are required to manually commit transactions, otherwise the data will not be committed to the database. This is the same as in JDBC, but without calling the setAutoCommit() method.

5. UPDATE

The use of the update tag is similar and requires the transaction to commit, otherwise the data will not be committed to the database.

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lmx.demo.mapper.UserMapper">
	<insert id="addUser" parameterType="com.lmx.demo.pojo.User">
        insert into user (name, password) VALUES (#{name}, #{password});
    </insert>
</mapper>
Copy the code

public interface UserMapper {

    int modifyUserById(User user);
    
}
Copy the code

The test class:

public class UserMapperTest {
    
	@Test
    public void modifyUserById(a) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User(7."Wang Xiao Wu"."654321");
        int result = userMapper.modifyUserById(user);
        System.out.println(result > 0 ? "Modification succeeded!" : "Modify failed!"); sqlSession.commit(); sqlSession.close(); }}Copy the code

Running result:

6. DELETE

After the three operations above, you should know how to use the delete tag, right? The usage is the same.

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lmx.demo.mapper.UserMapper">
	<delete id="deleteUserById" parameterType="int">
        delete from user where id = #{id};
    </delete>
</mapper>
Copy the code

public interface UserMapper {

    int deleteUserById(int id);
    
}
Copy the code

The test class:

public class UserMapperTest {
    
	@Test
    public void deleteUserById(a) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int result = userMapper.deleteUserById(7);
        System.out.println(result > 0 ? "Deletion succeeded!" : "Deletion failed!"); sqlSession.commit(); sqlSession.close(); }}Copy the code

Running result:

7. Precautions

I believe that through the use of the above four tags, I have a little understanding of the addition, deletion, modification and check of Mybatis. But in our daily use of the process will inevitably because of some oversight as the code run times error. Here are some things to keep in mind when writing code:

1. All add, delete, modify and check operations are required to commit transactions!
2. All common parameters of the interface, as many as possible@ParamArguments, especially if there are multiple arguments, must be written!
3. In order to standardize the operation, in the SQL configuration file, we try toparameterTypeParameters andresultTypeAll write!

References:

• 【 crazy god say Java】Mybatis latest complete tutorial IDEA version easy to understand – p3
• Mybatis reference documentation