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;
}
}