springboot整合jdbc

springboot整合jdbc

pom.xml:

<?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.4.3</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.godwin</groupId>
	<artifactId>demo01</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo01</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<!-- springboot整合jdbc -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
			<version>2.4.3</version>
		</dependency>
		<!-- mysql驱动8.0.23 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<!-- 单元测试 -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>		
		<!--web-->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</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>
			</plugin>
		</plugins>
	</build>

</project>

application.yml:

#定义端口
server:
  port: 8080

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver #8以上配serverTimezone
    url: jdbc:mysql://127.0.0.1:3306/stu?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
    username: root
    password: root

MyController.java:

@RestController
public class MyController {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @GetMapping("/user")
    public List<Map<String,Object>> map(){
        String sql = "select * from student";
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
        return result;
    }

    @GetMapping("/add")
    public String add(){
        String sql = "insert into stu.student(id,age,name,sex) values (8,18,'zs','nan')";
        jdbcTemplate.update(sql);
        return "add ok";
    }

    @GetMapping("/update/{id}")
    public String update(@PathVariable("id") int id){
        String sql = "update stu.student set age=?,name=?,sex=? where id=" + id;
        Object[] objects = new Object[3];
        objects[0] =  6;
        objects[1] = "小明";
        objects[2] = "男";
        jdbcTemplate.update(sql,objects);
        return "update ok";
    }

    @GetMapping("/delete/{id}")
    public String delete(@PathVariable("id") int id){
        String sql = "delete from stu.student where id = ?";
        jdbcTemplate.update(sql,id);
        return "delete ok";
    }
}

测试网站:

查询：http://localhost:8080/user

增加：http://localhost:8080/add

修改：http://localhost:8080/update/6

删除：http://localhost:8080/delete/6

总结：练习下原生sql语句，工作中用mybatis居多，且尽量不要拼接链接。