Interaction between front end and back end and database operation

1. Query operation

• In mapper, select tag is used to complete the query operation. The sql statement used is select *from data table name. Or change * to the specific field value to be accepted, such as id, username and password. Then set the corresponding method in controller and service, and write the corresponding code in the method. The return value of the lookup method.

<select id="[mapper Corresponding dao Method name]" parameterType="Incoming parameter type" resultType="Data type returned">
        select * from tb_user where username=#{username}
</select>

2. Modify operation

• In mapper, update is used to complete the modification operation, and the user ID, username and password transmitted from jsp are used to modify. We find the corresponding user through the user ID and modify it. The sql statement is update data table name set username = {username}, Password = {password} where id = {ID} can be modified. After that, set the corresponding method in the controller and service, and write the corresponding code in the method.

<update id=""  parameterType="">
        update tb_user set username=#{username}, password=#{password} where id=#{id}</update>

3. Delete operation

• In mapper, you need to use the delete tag to complete the deletion. We also use the user ID from jsp to delete. The sql statement used is delete from data name where id = {ID}. Then set the corresponding method in the controller and service, and write the corresponding code in the method.

<delete id="" parameterType="">
        delete from tb_user where id=#{id}
</delete>

4. Add operation

• In mapper, you need to use the insert tag to add. You can add the corresponding fields through the user id, username and password from the jsp form. The sql statement is the insert into data table name
  (ID, username, password) values ({ID}, {username}, {password}) can be added to it. After that, set the corresponding method in the controller and service, and write the corresponding code in the method.

 <insert id="" parameterType="">
        insert into tb_user(id,username,password) values(#{id},#{username},#{password})
 </insert>

To paginate a query

First, we add the relevant paging jar package code in pom.xml,

 <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>5.1.2</version>
    </dependency>

After that, you can use the Alt + Enter shortcut key to import into the current project. We should also add the following paging configuration to sqlSessionFactory in the application.xml file.

 <!--   Paging configuration     -->
        <property name="plugins">
            <array>
                <bean class="com.github.pagehelper.PageInterceptor">
                    <property name="properties">
                        <props>
                            <prop key="helperDialect">mysql</prop>
                            <prop key="reasonable">true</prop>
                        </props>
                    </property>
                </bean>
            </array>
        </property>

Then use the following format in the controller to perform paging operation

@RequestParam(defaultValue = "1") [This is to set the default paging parameters]
public ModelAndView findAll(@RequestParam(defaultValue = "1") Integer page,
                                @RequestParam(defaultValue = "5") Integer size){
        // Call the method in service to get the query result
        List<UserInfo> userInfoList = userInfoService.findAll(page,size);
        // Store the obtained data in PageInfo
        PageInfo pageInfo = new PageInfo(userInfoList);
        ModelAndView mv = new ModelAndView();
        mv.addObject("Attribute name",Attribute value);
        mv.setViewName("[Corresponding jsp Filename]");
        return mv;
    }

We also need to change the parameters in the service to the corresponding page number and size. The return value of the corresponding method is a list.