MyBatis query and add, update delete operation

Debug console prints SQL

Use process of Mybatis

Let’s review the process of using Mybatis

• Create the mybatis-config. XML global configuration file
• Create xxxmapper.xml configuration file
• Create a SqlSessionFactory
• Create the SqlSession object with SqlSessionFactory
• Use SqlSession to add, delete, change and check CRUD

Print the SQL configuration

The built-in log factory provides logging capabilities, print SQL using log4J configuration, and add dependencies

Slf4j </groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.7.30</version> </dependency>Copy the code

Create a file named log4j.properties in your application’s classpath

log4j.rootLogger=ERROR, Stdout log4j.logger.cn.junko=DEBUG # print more TRACE contents #log4j.logger.cn.junko=TRACE log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%nCopy the code

This way, we will have logs printed out when we execute the operation database

The query

Multiple parameters

When multiple parameters are required for query, an alias is used to facilitate identification and use

<select id="selectByPointAndTitle" resultType="cn.junko.domain.Video">
        select * from video where point = #{point} and title like  concat('%',#{title},'%')
    </select>
Copy the code

List<Video> selectByPointAndTitle(@Param("point") int point,@Param("title") String title);
Copy the code

Hump mapping

As mentioned earlier, database fields are underlined, and Java properties are humped

• Methods:select cover_img as coverImg from video

But what about multi-field, as operations for each parameter? Mybatis configuration is used here

 <! -- Underline automatically maps hump field -->
 <settings>
 <setting name="mapUnderscoreToCamelCase" value="true"/>
 </settings>
Copy the code

This allows for automatic mapping, for example cover_img will automatically map to coverImg

Mybatis into parameterType refs

ParameterType parameterType

It can be a basic type

parameterType="java.lang.Long"
parameterType="java.lang.String"
Copy the code

It can also be a JAVA collection List or Map

parameterType="java.util.Map"
parameterType="java.util.List"
Copy the code

Or a custom object

parameterType="cn.junko.domain.Video"
Copy the code

• #{title, jdbcType=VARCHAR}
  • Most cases are normal, but if an error occurs: invalid column type, jdbcType is missing;
  • The jdbcType attribute is required only if the field can be NULL

Common database types and Java proxy comparison

insert

Write the mapper

<insert id="addVideo" parameterType="cn.junko.domain.Video" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
        INSERT INTO `video`(`title`, `summary`, `cover_img`, `price`, `create_time`, `c_id`, `point`)
        VALUES (#{title},#{summary},#{coverImg},#{price},#{createTime},#{cid},#{point});
    </insert>
Copy the code

UseGeneratedKeys =”true” keyProperty=”id” keyColumn=” ID “test useGeneratedKeys=”true” keyProperty=”id” key =”id” test

@Test
    public void addVideo(a){
        Video video = new Video();
        video.setTitle("Test Data 3");
        video.setSummary("nihao.com");
        video.setCoverImg("wangyi");
        video.setPrice(8777);
        video.setCreateTime(new Date());
        video.setCid(7);
        video.setPoint(5.7);
        int rows = videoMapper.addVideo(video);
        System.out.println("Autoincrement primary key:"+video.getId());
        System.out.println(video.toString());
    }
Copy the code

Foreach batch insert

Foreach Is used to insert multiple video records in batches. It is a built-in label used for circular stitching. It is often used for batch adding and IN query

Contains the following attributes: Collection: Mandatory. The value is the type of the collection to iterate through. In this case, there are many types of input arguments. Separator: the name of the property of the index. In the case of a collection array, this value is the key of the map. Open: the string at the beginning of the contents of the loop. The separator for each loopCopy the code

Note that the alias value of item is iteam.xxx

<insert id="addVideoList" parameterType="cn.junko.domain.Video" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
        INSERT INTO `video`(`title`, `summary`, `cover_img`, `price`, `create_time`, `c_id`, `point`)
        VALUES
        <foreach collection="list" separator="," item="video">
        (#{video.title},#{video.summary},#{video.coverImg},#{video.price},#{video.createTime},#{video.cid},#{video.point})
        </foreach>
    </insert>
Copy the code

list.add(video1);
        list.add(video2);
        int rows = videoMapper.addVideoList(list);
        System.out.println(list);
Copy the code

The same applies to primary key increment

update

Normal updates are simpler, using the if test tag

• Optionally update non-empty fields
• Introduction to the if test label
  • The if tag determines the query condition by judging the value passed in, and test specifies an OGNL expression
  • Common writing

// Update the value of the current field until the condition is met<if test='title ! = null and id == 87 '> title = #{title}, </if>
<if test="title! =null"> title = #{title}, </if>
Copy the code

code

<! -- /*suffixOverrides="," */-->
    <update id="updateVideo" parameterType="cn.junko.domain.Video">
        UPDATE `video`
        <trim prefix="set" >
            <if test="title ! = null">`title` = #{title},</if>
            <if test="summary ! = null">`summary` = #{summary},</if>
            <if test="coverImg ! = null">`cover_img` = #{coverImg},</if>
            <if test="price ! = 0">`price` = #{price},</if>
            <if test="createTime ! = null">`create_time` = #{createTime},</if>
            <if test="cid ! = 0">`c_id` = #{cid},</if>
            <if test="point > 7.0">`point` = #{point}</if>
        </trim>
        WHERE `id` = #{id};
    </update>
Copy the code

Be sure to see if the POJO class contains primitive data types or wrapper data types

delete

• Delete Delete syntax
  • Requirement: Delete the data after a certain period and the amount is greater than 10 yuan

<delete id="deleteByCreateTimeAndPrice" parameterType="java.util.Map">delete from video where create_time <! [CDATA[ > ]]> #{createTime} and price <! [CDATA[ >= ]]> #{price}</delete>
Copy the code

Why should I escape characters: because MyBatis SQL is written in XML, some SQL syntax symbols conflict with XML

Greater than or equal to <! [CDATA[>=]]> Less than or equal to <! [CDATA[ <= ]]>Copy the code