POM.xml

提供druid的pom坐标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>

<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version >1.2.17</version>
</dependency>

application.properties

配置文件,只有基础的配置信息

1
2
3
4
5
6
7
##数据库连接信息
spring.datasource.url=jdbc:mysql://localhost:3306/film?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# 德鲁伊配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

使用JdbcTemplate进行curd操作

1.创建数据库并建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- ----------------------------
-- Table structure for film_info
-- ----------------------------
DROP TABLE IF EXISTS `film_info`;
CREATE TABLE `film_info` (
`film_id` int(11) NOT NULL AUTO_INCREMENT,
`film_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`film_type` int(255) NULL DEFAULT NULL,
`film_duration` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`release_date` date NULL DEFAULT NULL,
`film_remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`film_id`) USING BTREE,
INDEX `film_type`(`film_type`) USING BTREE,
CONSTRAINT `film_info_ibfk_1` FOREIGN KEY (`film_type`) REFERENCES `type_info` (`type_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for type_info
-- ----------------------------
DROP TABLE IF EXISTS `type_info`;
CREATE TABLE `type_info` (
`type_id` int(255) NOT NULL AUTO_INCREMENT,
`type_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`type_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

2.创建一个叫file的pojo(其实该叫film)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
int film_id;
String film_name;
String film_type;
String film_duration;
String release_date;
String film_remark;

public int getFilm_id() {
return film_id;
}

public void setFilm_id(int film_id) {
this.film_id = film_id;
}

public String getFilm_name() {
return film_name;
}

public void setFilm_name(String film_name) {
this.film_name = film_name;
}

public String getFilm_type() {
return film_type;
}

public void setFilm_type(String film_type) {
this.film_type = film_type;
}

public String getFilm_duration() {
return film_duration;
}

public void setFilm_duration(String film_duration) {
this.film_duration = film_duration;
}

public String getRelease_date() {
return release_date;
}

public void setRelease_date(String release_date) {
this.release_date = release_date;
}

public String getFilm_remark() {
return film_remark;
}

public void setFilm_remark(String film_remark) {
this.film_remark = film_remark;
}

@Override
public String toString() {
return "file{" +
"film_id=" + film_id +
", film_name='" + film_name + '\'' +
", film_type='" + film_type + '\'' +
", film_duration='" + film_duration + '\'' +
", release_date='" + release_date + '\'' +
", film_remark='" + film_remark + '\'' +
'}';
}

3.先创建一个执行的类 ,我给sql语句写死了,演示下怎么执行的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
package com.example.druid.pojo;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

@Repository("get")
public class get {
@Resource()
JdbcTemplate jdbcTemplate;

public void update(){
String sql = "UPDATE `film`.`film_info` SET `film_type` = 2 WHERE `film_id` = 5";
System.out.println("修改");
jdbcTemplate.update(sql);
}

public List<file> select(){
String sql = "SELECT * FROM `film_info`";
System.out.println("查询");
return jdbcTemplate.query(sql,new BeanPropertyRowMapper<file>(file.class));
}

public void delete(){
String sql = "DELETE FROM `film`.`film_info` WHERE `film_id` = 3";
jdbcTemplate.update(sql);
System.out.println("删除");
}

public void insert(){
String sql = "INSERT INTO `film`.`film_info`(`film_name`, `film_type`, `film_duration`, `release_date`, `film_remark`) VALUES ('test', 2, '3', '2020-07-01', '4')";
jdbcTemplate.update(sql);
System.out.println("插入");
}

}

4.写个测试单元测试一下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
package com.example.druid;

import com.example.druid.pojo.file;
import com.example.druid.pojo.get;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class DruidApplicationTests {
@Autowired
get ge;

@Test
void contextLoads() {
ge.update();
ge.delete();
ge.insert();
List<file> fm = ge.select();
for (file f:fm
) {
System.out.println(f.toString());
}

}

}

5.输出结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
  .   ____          _            __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.3.0.RELEASE)

2020-07-01 23:18:03.298 INFO 17068 --- [ main] com.example.druid.DruidApplicationTests : Starting DruidApplicationTests on DESKTOP-NO6I8UL with PID 17068 (started by 86177 in E:\ideaProject\druid)
2020-07-01 23:18:03.300 INFO 17068 --- [ main] com.example.druid.DruidApplicationTests : No active profile set, falling back to default profiles: default
2020-07-01 23:18:04.540 INFO 17068 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2020-07-01 23:18:04.844 INFO 17068 --- [ main] com.example.druid.DruidApplicationTests : Started DruidApplicationTests in 1.832 seconds (JVM running for 2.667)

修改
2020-07-01 23:18:05.151 INFO 17068 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
删除
插入
查询
file{film_id=1, film_name='一路向南', film_type='1', film_duration='90', release_date='0020-12-12', film_remark='一路向南'}
file{film_id=2, film_name='一路向北', film_type='1', film_duration='90', release_date='0020-12-12', film_remark='一路向北'}
file{film_id=5, film_name='僵尸道长', film_type='2', film_duration='10', release_date='2012-12-12', film_remark='林正英'}
file{film_id=12, film_name='test', film_type='2', film_duration='3', release_date='2020-07-01', film_remark='4'}
file{film_id=13, film_name='test', film_type='2', film_duration='3', release_date='2020-07-01', film_remark='4'}

PS:这篇博客是记录一下当时咋操作的,以后在遇见的时候有地方查,还是mybatis得劲。。。

更新于

请我喝[茶]~( ̄▽ ̄)~*

Fanxing 微信支付

微信支付

Fanxing 支付宝

支付宝

Fanxing 贝宝

贝宝