spring boot mysql jdbcTemplate

pom依赖

<?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.6.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.nenu</groupId>
    <artifactId>mysql_module</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mysql_module</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>

        <!-- web 程序 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--  mysql连接Java的驱动程序-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>


        <!-- 支持通过jdbc 连接数据库-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </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>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

application.yml

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&autoReconnect=true&failOverReadOnly=false
    username: root
    password: mysql
    driver-class-name: com.mysql.cj.jdbc.Driver

数据库名 test 建表userinfo_1 语句如下：

DROP TABLE IF EXISTS `userinfo_1`;
CREATE TABLE `userinfo_1` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(50) DEFAULT NULL COMMENT '用户姓名',
  `gender` tinyint DEFAULT NULL COMMENT '性别',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

UserInfo.java

package com.nenu.domain;

/**
 * @author ：lichuankang
 * @date ：Created in 2022/2/25 11:12
 * @description ：
 */
public class UserInfo {

    /**
     * 主键 自增
     */
    private Long id;

    /**
     * 姓名
     */
    private String username;

    /**
     * 性别
     * 1：男  0：女
     */
    private Integer gender;

    /**
     * 年龄
     */
    private Integer age;


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "UserInfo{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", gender=" + gender +
                ", age=" + age +
                '}';
    }
}

Page.java

package com.nenu.domain;

import java.io.Serializable;
import java.util.List;

/**
 * @author ：lichuankang
 * @date ：Created in 2022/2/25 15:07
 * @description ：分页对象
 */
public class Page<T> implements Serializable {


    private static final long serialVersionUID = -7939777997786639124L;
    /**
     * 页码
     */
    private int pageNum = 1;

    /**
     * 页数
     */
    private int pageSize = 20;

    /**
     * 总页数
     */
    private Long countPage;

    /**
     * 总条数
     */
    private Long totalCount;

    /**
     * 查询出的对象
     */
    private List<T> list;



    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public Long getCountPage() {
        return countPage;
    }

    public void setCountPage(Long countPage) {
        this.countPage = countPage;
    }

    public Long getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(Long totalCount) {
        this.totalCount = totalCount;
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    @Override
    public String toString() {
        return "Page{" +
                "pageNum=" + pageNum +
                ", pageSize=" + pageSize +
                ", countPage=" + countPage +
                ", totalCount=" + totalCount +
                ", list=" + list +
                '}';
    }
}

UserInfoController

package com.nenu.controller;

import com.alibaba.fastjson.JSON;
import com.nenu.domain.Page;
import com.nenu.domain.UserInfo;
import jdk.nashorn.internal.ir.CallNode;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author ：lichuankang
 * @date ：Created in 2022/2/25 11:17
 * @description ：
 */
@RestController
@RequestMapping(value = "/jdbc")
public class UserInfoController {

    @Resource
    private JdbcTemplate jdbcTemplate;


    /**
     * 条件查询  根据名称查询
     * @param username
     * @return
     */
    @RequestMapping(value = "/getUserInfoByName")
    public String getUserInfoByUsername(@RequestParam String username) {
        String sql = "select * from userinfo_1 where username = '" + username + "'";
        List<UserInfo> userInfoList = jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserInfo.class));
        return JSON.toJSONString(userInfoList);
    }

    /**
     * 查询列表所有
     * @return
     */
    @RequestMapping(value = "/list")
    public String getAllUserInfo() {
        String sql = "select * from userinfo_1";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        System.out.println("maps:" + maps);

        List<UserInfo> userInfoList = jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserInfo.class));
        System.out.println("userInfoList:" + userInfoList);
        return JSON.toJSONString(userInfoList);
    }

    /**
     * 插入数据
     * @param info
     * @return
     */
    @RequestMapping(value = "/insert")
    public String insertUser(@RequestBody UserInfo info) {
        String sql  = "insert into userinfo_1(username,gender,age) values (?,?,?)";
        int update = jdbcTemplate.update(sql, info.getUsername(), info.getGender(), info.getAge());
        System.out.println("update--" + update);
        return "success";
    }

    /**
     * 删除数据  根据主键删除
     * @param id
     * @return
     */
    @RequestMapping(value = "/delete")
    public String deleteUser(@RequestParam Long id) {
        String sql  = "delete from userinfo_1 where id = ?";
        int update = jdbcTemplate.update(sql, id);
        System.out.println("update--" + update);
        return "success";
    }


    /**
     * 简单分页查询  不返回总页数  页数从1开始
     * @return
     */
    @RequestMapping(value = "/page")
    public Page getPageInfo(@RequestParam Integer pageNum, @RequestParam Integer pageSize) {
        String sql = "select * from userinfo_1 limit " + (pageNum - 1) * pageSize +  "," + pageSize;

        List<UserInfo> userInfoList = jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(UserInfo.class));
        System.out.println("userInfoList:" + userInfoList);

        Page<UserInfo> page = new Page<>();
        page.setList(userInfoList);
        page.setPageNum(pageNum);
        page.setPageSize(pageSize);
        return page;
    }
}

操作截图1：插入用户

操作截图2：查询所有用户列表

操作截图3：删除主键ID为3的用户

操作截图4: 查询第一页数据，页码大小为10

操作截图5：根据用户姓名查询用户信息