Spring Boot集成MyBatis实现分页查询

小咪咪 2023-10-07 11:57 23阅读 0赞

**目录**

前言

1、配置pom.xml---文件导入jar

2、配置application.properties

3、在springboot 主入口添加注解，里面值为Mapper对应的Java接口类

4、持久化接口

5、mapper文件配置

6、service层

7、service实现

8、Controller层

9、数据库实体类

10、项目结构图

11、测试

--------------------

### **前言** ###

使用pagehelper插件。它非常的方便，自动拼接mapper.xml配置里的对应的sql语句，也就是说不用我们去修改或新增mapper.xml里的sql语句配置，直接使用原来的查询所有的sql语句就行了，只会查询页面当前显示的页数的数据

### **1、配置pom.xml---文件导入jar** ###

有关mysql，dbcp，mybatis，spring-boot-starter-web相关的jar包

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>2.2.6.RELEASE</version>
    		<relativePath/> 
    	</parent>
    	<groupId>com.dyt.server</groupId>
    	<artifactId>dataServer</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<name>dataServer</name>
    	<description>数据监测服务</description>
    
    	<properties>
    		<java.version>1.8</java.version>
    		<fastjson.version>1.2.44</fastjson.version>
    	</properties>
    
    	<dependencies>
    	    <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-aop</artifactId>
            </dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-jdbc</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>org.mybatis.spring.boot</groupId>
    			<artifactId>mybatis-spring-boot-starter</artifactId>
    			<version>2.1.2</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-devtools</artifactId>
    			<optional>true</optional>
    		</dependency>
    		
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.5</version>
            </dependency>
            
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid-spring-boot-starter</artifactId>
    			<version>1.1.10</version>
    		</dependency>
    		<dependency>
    			<groupId>com.oracle.ojdbc</groupId>
    			<artifactId>ojdbc8</artifactId>
    			<scope>runtime</scope>
    		</dependency>
    		<dependency>
    			<groupId>org.projectlombok</groupId>
    			<artifactId>lombok</artifactId>
    			<optional>true</optional>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    			<exclusions>
    				<exclusion>
    					<groupId>org.junit.vintage</groupId>
    					<artifactId>junit-vintage-engine</artifactId>
    				</exclusion>
    			</exclusions>
    		</dependency>
    		<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>fastjson</artifactId>
                <version>${fastjson.version}</version>
            </dependency>
    		<dependency>
    			<groupId>cn.easyproject</groupId>
    			<artifactId>orai18n</artifactId>
    			<version>12.1.0.2.0</version>
    		</dependency>
    	</dependencies>
    
    	<build>
    		<plugins>
    			<plugin>
    				<groupId>org.springframework.boot</groupId>
    				<artifactId>spring-boot-maven-plugin</artifactId>
    			</plugin>
    		</plugins>
    	</build>
    
    </project>

### **2、配置**application.properties ###

application.properties文件中配置mybatis的mapper文件位置，实体类的包路径，还有最好加上驼峰命名规范，如果单独配置了mybatis的配置文件，在application.properties中不能在同时写入mybatis的配置

#端口
    server.port=8091
    #数据库相关配置
    
    spring.datasource.url=jdbc:oracle:thin:@**.**.**.**:1521:ky
    spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
    spring.datasource.username=root
    spring.datasource.password=root
    
    ####mybatis配置
    # 这种方式需要自己在resources目录下创建mapper目录然后存放xml
    mybatis.mapper-locations=classpath:/mapper/*.xml 
    #mybatis.config-location=classpath:/mybatis/mybatis-config.xml
    # 指定POJO扫描包来让mybatis自动扫描到自定义POJO
    mybatis.type-aliases-package=com.ykx.transinfo.model
    # 驼峰命名规范 如：数据库字段是  order_id 那么 实体字段就要写成 orderId
    mybatis.configuration.map-underscore-to-camel-case=true
    # 这个配置会将执行的sql打印出来，在开发或测试的时候可以用
    mybatis.configuration.log-impl= org.apache.ibatis.logging.stdout.StdOutImpl

### **3、在springboot 主入口添加注解，里面值为Mapper对应的Java接口类** ###

**备注**：@SpringBootApplication表示自动装配；  
可以将你的pageHelper配置写在入口类里边，也可以像这样写到带有@Configuration的配置类中  
**注意**：所有的类要写在main方法所在的类的包下，或者子包下，不然不能扫描；

package com.ykx.transinfo;
    
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    @MapperScan("com.ykx.transinfo.mapper")
    public class DataServerApplication {
    
    	public static void main(String[] args) {
    		SpringApplication.run(DataServerApplication.class, args);
    	}
    
    }

**配置类**

/**   
     * Copyright © 2020 eSunny Info. Tech Ltd. All rights reserved.
     * 功能描述：
     * @Title: PageHelperConfig.java  
     * @Package com.ykx.transinfo.util  
     * @Description: TODO  
     * @author Administrator   
     * @date 2020年3月30日 上午11:34:22  
     * @version  
     */  
    
    package com.ykx.transinfo.util;
    
    /**   
     * 类名称：PageHelperConfig   
     * 类描述：   
     * 创建人：Administrator
     * 创建时间：2020年3月30日 上午11:34:22   
     * 修改人：Administrator
     * 修改时间：2020年3月30日 上午11:34:22   
     * 修改备注：   
     * @version       
     */
    
    import com.github.pagehelper.PageHelper;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import java.util.Properties;
    
    @Configuration//将该类加到spring容器里
    public class PageHelperConfig {
        @Bean//加上该注解spring容器自动配置
        public PageHelper pageHelper() {
            PageHelper pageHelper = new PageHelper();
            Properties properties = new Properties();
            properties.setProperty("offsetAsPageNum", "true");
            properties.setProperty("rowBoundsWithCount", "true");
            properties.setProperty("reasonable", "true");
            //配置mysql数据库的方言
            properties.setProperty("dialect","mysql");
    		// 表示支持从接口中读取pageNum和pageSize
            properties.setProperty("supportMethodsArguments", "true");
            pageHelper.setProperties(properties);
            return pageHelper;
        }
    }

### **4、持久化接口** ###

@Repository
    public interface TicketRealnameMapper {
    	 //	有多个参数时，需要使用@Param来指定参数，否则查询报错
    	 List<TicketRealname> selectUserByCustCerNo(@Param("custCerNo") String custCerNo,@Param("pageNum")int pageNum, @Param("pageSize")int pageSize);
    	 
    	 //	有多个参数时，需要使用@Param来指定参数，否则查询报错
    	 List<TicketRealname> selectUserByCustName(@Param("custName") String custName);
    }

### 5**、**mapper文件配置 ###

**resultType**：返回值类型，使用resultType进行输出映射，只有查询出来的列名和pojo中的属性名一致，该列才可以映射成功。

**resultMap：**如果查询出来的列名和pojo的属性名不一致，通过定义一个resultMap对列名和pojo属性名之间作一个映射关系。

<?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.ykx.transinfo.mapper.TicketRealnameMapper">
     
    <select id="selectUserByCustCerNo" parameterType="java.lang.String" resultType="com.ykx.transinfo.model.TicketRealname">
        SELECT *
        FROM CZJY.TICKET_REALNAME
        WHERE 1 = 1
        <if test="custCerNo != null and custCerNo !=''">
            AND CUST_CERNO = #{custCerNo,jdbcType=VARCHAR}
        </if>
        ORDER BY LINER_DATE
    </select>
    
     
    <select id="selectUserByCustName" parameterType="java.lang.String" resultType="com.ykx.transinfo.model.TicketRealname">
        SELECT *
        FROM CZJY.TICKET_REALNAME
        WHERE 1 = 1
        <if test="custName != null and custName !=''">
            AND CUST_NAME = #{custName,jdbcType=VARCHAR}
        </if>
        ORDER BY LINER_DATE
    </select>
    </mapper>

### 6、service层 ###

public interface TicketRealnameService {
    	
    	public List<TicketRealname> selectUserByCustCerNo(String custCerNo,int pageNum, int pageSize);
    	
    	public List<TicketRealname> selectUserByCustName(String custCerName);
    	
    }

### 7、service实现 ###

@Service
    public class TicketRealnameServiceImp implements TicketRealnameService{
    
    	@Autowired
    	public TicketRealnameMapper realnameMapper;
    	
    	/** 
    	* @see com.ykx.transinfo.service.TicketRealnameService#findAllUser(java.lang.String, int, int)  
    	* @Function: TicketServiceImp.java
    	* @Description: 该函数的功能描述
    	*
    	* @param:描述1描述
    	* @return：返回结果描述
    	* @throws：异常描述
    	*
    	* @version: v1.0.0
    	* @author: Administrator
    	* @date: 2020年3月30日 上午10:36:06 
    	*
    	* Modification History:
    	* Date         Author          Version            Description
    	*---------------------------------------------------------*
    	* 2020年3月30日     Administrator           v1.0.0               修改原因
    	*/
        @SuppressWarnings({ "null" })
        public List<TicketRealname> selectUserByCustCerNo(String custCerNo, int pageNum, int pageSize) {
            return realnameMapper.selectUserByCustCerNo(custCerNo,pageNum,pageSize);
        }
    
    	/** 
    	* @see com.ykx.transinfo.service.TicketRealnameService#selectUserByCustName(java.lang.String)  
    	* @Function: TicketRealnameServiceImp.java
    	* @Description: 该函数的功能描述
    	*
    	* @param:描述1描述
    	* @return：返回结果描述
    	* @throws：异常描述
    	*
    	* @version: v1.0.0
    	* @author: Administrator
    	* @date: 2020年3月30日 下午4:21:24 
    	*
    	* Modification History:
    	* Date         Author          Version            Description
    	*---------------------------------------------------------*
    	* 2020年3月30日     Administrator           v1.0.0               修改原因
    	*/
    	
    	@Override
    	public List<TicketRealname> selectUserByCustName(String custName) {
    		// TODO Auto-generated method stub
    		 return realnameMapper.selectUserByCustName(custName);
    	}
    
    
    }

### 8、Controller层 ###

@RestController
    public class TicketRealnameController {
    
    	@Autowired
        private TicketRealnameService service;
    	
    	@PostMapping("/find")
        public Object  selectUserByCustCerNo(
        @RequestParam(value = "custCerNo", required = false, defaultValue = "500221114615")String custCerNo,
        @RequestParam(name = "pageNum", required = false, defaultValue = "1")int pageNum, 
        @RequestParam(name = "pageSize", required = false, defaultValue = "10")int pageSize){
    		JSONObject map = new JSONObject();//创建JSONmap来存放JSON数据传到前台
    		PageHelper.startPage(pageNum, pageSize);//设置数据库分页查询的范围
    		try {
            	// 这里的custCerNo是指按证件号码查询，pageNum是指：页数 ，pageSize是指每页展示的数据
               List<TicketRealname> realnames = service.selectUserByCustCerNo(custCerNo,pageNum,pageSize);
               PageInfo<TicketRealname> pageInfo=new PageInfo<>(realnames);
               map.put("count",pageInfo.getTotal());//获取查询总条数，这里我会在后面解释
               map.put("data", realnames);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return map;
        }
    	
    	
    	@PostMapping("/name")
        public Object  selectUserByName(
        @RequestParam(name = "custName", required = false, defaultValue = "10")String custName){
    		List<TicketRealname> realnames = null;
    		try {
            	// 这里的custCerNo是指按证件号码查询，1是指：页数 ，2是指每页展示的数据
    			realnames = service.selectUserByCustName(custName);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return realnames;
        }
    }

### **9、数据库实体类** ###

**推荐引入lombok,少去写get、set的麻烦，**

/**   
     * Copyright © 2020 eSunny Info. Tech Ltd. All rights reserved.
     * 功能描述：
     * @Title: TicketRealname.java  
     * @Package com.ykx.transinfo.model  
     * @Description: TODO  
     * @author Administrator   
     * @date 2020年3月27日 下午4:33:03  
     * @version  
     */  
    
    package com.ykx.transinfo.model;
    
    import java.io.Serializable;
    
    import lombok.Data;
    /**   
     * 类名称：TicketRealname   
     * 类描述：   
     * 创建人：Administrator
     * 创建时间：2020年3月27日 下午4:33:03   
     * 修改人：Administrator
     * 修改时间：2020年3月27日 下午4:33:03   
     * 修改备注：   
     * @version       
     */
    @Data
    public class TicketRealname  implements Serializable{
    
    	private static final long serialVersionUID = 1L;
    	
    	private String realnameSeq = "";
    
    	private String linerDate = "";
    
    	private String ticketSeq = "";
    
    	private String telephone = "";
    
    	private String custName = "";
    
    	private String custCertype = "";
    
    	private String custCerno = "";
    
    	private String address = "";
    
    	private String remark = "";
    
    	private String lastupdateTime = "";
    
    }

### **10、项目结构图** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIwOTU3NjY5_size_16_color_FFFFFF_t_70][]

### **11、测试**  ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIwOTU3NjY5_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIwOTU3NjY5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200331162525612.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIwOTU3NjY5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIwOTU3NjY5_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2020033116405283.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIwOTU3NjY5,size_16,color_FFFFFF,t_70