©2020-2024 ioDraw All rights reserved,
Privacy Policy
1.复习JDBC
//第一步，注册驱动程序 Class.forName("数据库驱动的完整类名"); //第二步，获取一个数据库的连接 Commection conn =
DriverManager.getConnection("数据库地址","用户名","密码"); //第三步，创建一个会话 Statement stmt =
conn.createStatement(); //第四步，执行SOL语句 stmt.executeUpdate("SQL语句"); //或者查询记录
ResultSet rs = stmt.executeQuery("查询记录的SQL语句"); //第五步，对查询的结果进行处理
while(rs.next()){ //操作 } //第六步，关闭连接 rs.close(); stmt.close(); conn.close();
2.引入JDBC依赖
在pom.xml文件中添加jdbc的场景启动器spring-boot-starter-jdbc和mysql-connector-java
<dependency> <groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency>
<groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version> <scope>runtime</scope> </dependency>
3.填写配置文件application.properties
#jdbc spring.datasource.name==newbee-mall-datasource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test_db?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=utf8&autoReconnect=true&useSSL=false&allowMultiQueries=true&useAffectedRows=true
spring.datasource.username=root spring.datasource.password=123456
4.编写测试类测试数据库是否连接成功
在test文件下编写测试并运行，在其他地方编写测试类需要在配置文件中去掉
spring-boot-starter-test的scope package ltd.newbee.mall; import
org.junit.jupiter.api.Test; import
org.springframework.beans.factory.annotation.Autowired; import
org.springframework.boot.test.context.SpringBootTest; import
javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException;
@SpringBootTest class NewbeeMallApplicationTests { //注入数据源对象 @Autowired private
DataSource defaultDataSource; @Test public void datasourceTest() throws
SQLException{ //获取数据库连接对象 Connection connection =
defaultDataSource.getConnection(); System.out.print("获取连接:"); //判断连接对象是否为空
System.out.println(connection !=null); connection.close(); } @Test void
contextLoads() { } }
5. JDBCTemplate
5.1创建测试表
DROP TABLE IF EXISTS jdbc_test; CREATE TABLE jdbc_test ( ds_id int(11) NOT
NULL AUTO_INCREMENT COMMENT '主键id', ds_type varchar(100) DEFAULT NULL COMMENT
'数据源类型', ds_name varchar(100) DEFAULT NULL COMMENT '数据源名称', PRIMARY KEY (ds_id)
USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8; /* Data for the table 'jdbc
test' */ insert into jdbc_test (ds_id,ds_type,ds_name) value
(1,'com.java','java'), (2,'com.jdbc','jdbc');
5.2对测试表的数据进行增删改查
public class JdbcController { //已经自动配置，因此可以直接通过@Autowired 注入进来 @Autowired
JdbcTemplate jdbcTemplate; //新增一条记录 @GetMapping("/insert") public String
insert(String type,String name){ if
(StringUtils.isEmpty(type)||StringUtils.isEmpty(name)){ return "参数异常"; }
jdbcTemplate.execute("insert into jdbc_test('da_type','ds_name') value
(\""+type+"\",\""+name+"\")"); return "SQL执行完毕"; } //删除一条记录
@GetMapping("/delete") public String delete(int id){ if (id < 0){ return
"参数异常"; } List<Map<String,Object>> result = jdbcTemplate.queryForList("select *
from jdbc_test where ds_id = \"" + id +"\""); if
(CollectionUtils.isEmpty(result)){ return"不存在该记录，删除失败"; }
jdbcTemplate.execute("delete from jdbc_test where ds_id=\"" + id + "\"");
return "SQL执行完毕"; } //修改一条记录 @GetMapping("/update") public String update(int id
,String type,String name){ if
(id<0||StringUtils.isEmpty(type)||StringUtils.isEmpty(name)){ return "参数异常"; }
List<Map<String,Object>>result = jdbcTemplate.queryForList("select * from
jdbc_test where ds_id = \"" + id + "\""); if (CollectionUtils.isEmpty(result)){
return "不存在该记录，无法修改"; } jdbcTemplate.execute("update jdbc_test set ds_type=\""+
type + "\",ds_name = \"" + "\" where ds_id=\"" + id + "\""); return "SQL执行完毕";
} //查询所有记录 @GetMapping("/queryALL") public List<Map<String,Object>> queryALL(){
List<Map<String,Object>> list = jdbcTemplate.queryForList("select * from
jdbc_test"); return list; } }