JdbcTemplate基本使用
一、开发工具及环境二、Spring Boot基本配置
1、Spring initializr 设置2、pom.xml3、JdbcTemplateApplication类4、JdbcTemplateApplicationTests类5、MyMvcConfig类 三、数据库搭建
1、数据准备2、application.properties 配置数据库连接3、测试是否能连接上数据库 四、整合JdbcTemplate
1、创建实体类User.java2、创建UserDao.java3、创建JdbcController
JdbcTemplate基本使用 一、开发工具及环境
电脑操作系统：Win10
Java版本：JDK1.8
MySQL数据库版本：mysql-8.0.26-winx64
编辑器：IntelliJ IDEA 2021.2 企业版
SpringBoot版本：2.6.3
工作目录
二、Spring Boot基本配置 1、Spring initializr 设置 2、pom.xml3、JdbcTemplateApplication类4.0.0 org.springframework.boot spring-boot-starter-parent 2.6.3 com.example jdbcTemplate 0.0.1-SNAPSHOT jdbcTemplate Demo project for Spring Boot 1.8 org.springframework.boot spring-boot-starter-jdbc org.springframework.boot spring-boot-starter-web mysql mysql-connector-java runtime org.projectlombok lombok true org.springframework.boot spring-boot-starter-test test org.springframework.boot spring-boot-maven-plugin org.projectlombok lombok
package com.example.jdbcTemplate;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class JdbcTemplateApplication {
public static void main(String[] args) {
SpringApplication.run(JdbcTemplateApplication.class, args);
}
}
4、JdbcTemplateApplicationTests类
package com.example.jdbcTemplate;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
class JdbcTemplateApplicationTests {
@Test
void contextLoads() {
}
}
5、MyMvcConfig类
package com.example.jdbcTemplate.config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.View;
import org.springframework.web.servlet.ViewResolver;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
import java.util.Locale;
//全面扩展SpringMVC dispatcherServlet
@Configuration
@EnableWebMvc
public class MyMvcConfig implements WebMvcConfigurer {
//ViewResolver 实现了视图解析器接口的类，我们就可以把它看做视图解析器
@Bean
public ViewResolver myViewResolver(){
return new MyViewResolver();
}
public static class MyViewResolver implements ViewResolver{
@Override
public View resolveViewName(String viewName, Locale locale) throws Exception {
return null;
}
}
}
三、数据库搭建
1、数据准备
创建User表，并插入三行数据
CREATE TABLE `jdbctemplate`.`User`( `id` INT(4) NOT NULL, `username` VARCHAr(30) NOT NULL, `password` VARCHAr(30) NOT NULL, `email` VARCHAr(30), `gender` INT(1), `birth` DATE, PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('1', 'zhangsan', '123456', 'test01@qq.com', '1', '2000-06-14');
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('2', 'xiaoming', '654321', 'test02@qq.com', '1', '2001-07-03');
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('3', 'zhangcuihua', '123456789', 'test03@qq.com', '0', '1999-08-12');
2、application.properties 配置数据库连接
spring.datasource.username=root spring.datasource.password=123456 spring.datasource.url=jdbc:mysql://localhost:3306/jdbcTemplate?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver3、测试是否能连接上数据库
package com.example.jdbcTemplate;
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 JdbcTemplateApplicationTests {
//DI注入数据源
@Autowired
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
//看一下默认数据源
System.out.println(dataSource.getClass());
//获得连接
Connection connection = dataSource.getConnection();
System.out.println(connection);
//关闭连接
connection.close();
}
}
运行测试是否运行成功，如果成功，则说明成功连接数据库。
四、整合JdbcTemplate先创建pojo、dao、controller三个文件夹
1、创建实体类User.java在pojo文件夹中新建User类
package com.example.jdbcTemplate.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Integer id;
private String username;
private String password;
private String email;
private Integer gender;//0:女生;1:男生
private Date birth;
}
2、创建UserDao.java
在Dao文件夹中创建UserDao.java并整合JdbcTemplate
package com.example.jdbcTemplate.dao;
import com.example.jdbcTemplate.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
@Repository
public class UserDao {
@Autowired
JdbcTemplate jdbcTemplate;
public List> getUserList(){
String sql = "select * from `user`";
List> userList = jdbcTemplate.queryForList(sql);
return userList;
}
public User getUserById(int id){
String sql = "select * from `user` where id="+id;
User user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(User.class));
return user;
}
public String addUser(User user){
String sql = "insert into `user`(id,username,password,email,gender,birth) values(?,?,?,?,?,?)";
Object[] objects = new Object[6];
objects[0] = user.getId();
objects[1] = user.getUsername();
objects[2] = user.getPassword();
objects[3] = user.getEmail();
objects[4] = user.getGender();
objects[5] = user.getBirth();
jdbcTemplate.update(sql,objects);
return "add ok";
}
public String updateUser(User user){
String sql = "update `user` set username=?,password=?,email=?,gender=?,birth=? where id=?";
Object[] objects = new Object[6];
objects[0] = user.getUsername();
objects[1] = user.getPassword();
objects[2] = user.getEmail();
objects[3] = user.getGender();
objects[4] = user.getBirth();
objects[5] = user.getId();
jdbcTemplate.update(sql,objects);
return "update ok";
}
public String deleteUserById(int id){
String sql = "delete from `user` where id="+id;
jdbcTemplate.update(sql);
return "delete ok";
}
}
3、创建JdbcController
package com.example.jdbcTemplate.controller;
import com.example.jdbcTemplate.dao.UserDao;
import com.example.jdbcTemplate.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
import java.util.Date;
import java.util.List;
import java.util.Map;
@RestController
public class JdbcController {
@Autowired
UserDao userDao;
@GetMapping({"/","/userList"})
public String getUserList(){
List> userList = userDao.getUserList();
return userList.toString();
}
@GetMapping("/user/{id}")
public String getUserById(@PathVariable("id") Integer id){
User user = userDao.getUserById(id);
return user.toString();
}
@GetMapping("/addUser")
public String addUser(){
User user = new User(4, "chenyonwu", "xiangyue2035", "test04@qq.com", 1, new Date());
String s = userDao.addUser(user);
return s;
}
@GetMapping("/updateUser")
public String updateUser(){
User user = new User(4, "AAA", "123456", "test05@qq.com", 0, new Date());
String s = userDao.updateUser(user);
return s;
}
@GetMapping("/deleteUser/{id}")
public String deleteUser(@PathVariable("id") Integer id){
String s = userDao.deleteUserById(id);
return s;
}
}
