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：根据用户姓名查询用户信息
