Spring Boot JDBC示例
时间：2020-02-23 14:35:48 来源:igfitidea点击:
我们已经知道springboot做了很多自动配置，这帮助我们避免了很多样板代码。在JDBC的情况下，springboot提供了自动配置，比如基于xml自动创建DataSource和JDBCTemplate对象application.properties.
所以你只需要在应用程序中自动配置 JdbcTemplateBean代码如下所示。
@Repository
public class StudentRepositoryImpl implements StudentRepository {
//Just need to autowire JdbcTemplate, spring boot will
//do auto configure
@Autowired
private JdbcTemplate jdbcTemplate;
让我们创建一个简单的Spring Boot JDBC示例。
使用的工具
- Spring Boot 2.2.2.Release.
- Spring JDBC 5.2.2.1.Release.
- Hikaricp 3.4.1.
- Maven 3.
- Java 8.
项目结构
创建新的Spring Boot项目
第1步：去 https://start.spring.io并创建具有以下依赖项的项目
- Spring-Boot-Starter-JDBC
这是相同的屏幕截图。
maven配置
添加MySQL驱动器依赖项，我们需要添加MySQL JDBC驱动器依赖项，以便连接到MySQL。
<!-- MySQL JDBC driver --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency>
你的 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.2.2.RELEASE</version>
<relativePath <!-- lookup parent from repository -->
</parent>
<groupId>com.theitroad</groupId>
<artifactId>SpringBootJDBCExample</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>SpringBootJDBCExample</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<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>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
在application.properties中配置数据源
#mysql properties spring.datasource.url=jdbc:mysql://localhost:3306/StudentData spring.datasource.username=root spring.datasource.password=test1234 spring.datasource.platform=mysql
请按照方式更改用户名和密码 mysql本地设置。
创建数据库表
让我们创建一个名为学生的数据库表，我们将在此示例中使用。
创建表学生(ID int(11)not null auto_increment，studentname varchar(255)默认为空，年龄int(3)默认为null，主键(ID));
创建模型类
让我们创造简单 Student.java。
package org.igi.theitroad.model;
public class Student {
private int studentId;
private String studentName;
private int age;
public Student(int studentId, String studentName, int age) {
super();
this.studentId = studentId;
this.studentName = studentName;
this.age = age;
}
public int getStudentId() {
return studentId;
}
public void setStudentId(int studentId) {
this.studentId = studentId;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Student [studentId=" + studentId + ", StudentName=" + studentName + ",age =" + age + "]";
}
}
创建存储库界面和实现
package org.igi.theitroad.repository;
import java.util.List;
import java.util.Optional;
import org.igi.theitroad.model.Student;
public interface StudentRepository{
int save(Student student);
int update(Student student);
int deleteById(int id);
List findAll();
Optional findById(Long id);
}
创建它的欧式命名 StudentRepositoryImpl.java
package org.igi.theitroad.repository;
import java.util.List;
import java.util.Optional;
import org.igi.theitroad.model.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class StudentRepositoryImpl implements StudentRepository {
//Just need to autowire JdbcTemplate, spring boot will
//do auto configure
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int save(Student student) {
return jdbcTemplate.update(
"insert into students (studentName, age) values(?,?)",
student.getStudentName(), student.getAge());
}
@Override
public int update(Student student) {
return jdbcTemplate.update(
"update students set age = ? where id = ?",
student.getAge(), student.getStudentId());
}
@Override
public int deleteById(int id) {
return jdbcTemplate.update(
"delete from students where id = ?",
id);
}
@Override
public List findAll() {
return jdbcTemplate.query(
"select * from students",
(rs, rowNum) ->
new Student(
rs.getInt("id"),
rs.getString("studentName"),
rs.getInt("age")
)
);
}
@Override
public Optional findById(Long id) {
return jdbcTemplate.queryForObject(
"select * from students where id = ?",
new Object[]{id},
(rs, rowNum) ->
Optional.of(new Student(
rs.getInt("id"),
rs.getString("studentName"),
rs.getInt("age")
))
);
}
}
我们使用Spring的JDBCtemplate与数据库交互。
JDBCTemplate有助于我们避免锅炉板代码并提供方便的方法来检索和插入数据库中的数据。
创建服务接口和实现
创建服务界面 StudentService.java
package org.igi.theitroad.service;
import java.util.List;
import java.util.Optional;
import org.igi.theitroad.model.Student;
public interface StudentService {
int save(Student student);
int update(Student student);
int deleteById(int id);
List findAll();
Optional findById(Long id);
}
创建它的实施命名 StudentRepositoryImpl.java
package org.igi.theitroad.service;
import java.util.List;
import java.util.Optional;
import org.igi.theitroad.model.Student;
import org.igi.theitroad.repository.StudentRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class StudentServiceImpl implements StudentService{
@Autowired
StudentRepository studentRepository;
@Override
public int save(Student student) {
return studentRepository.save(student);
}
@Override
public int update(Student student) {
return studentRepository.update(student);
}
@Override
public int deleteById(int id) {
return studentRepository.deleteById(id);
}
@Override
public List findAll() {
return studentRepository.findAll();
}
@Override
public Optional findById(Long id) {
return studentRepository.findById(id);
}
}
请注意，我们已经注释了学生iserviceimpl @Service.spring将根据此注释自动创建一个bean。 StudentRepository被注射到 StudentServiceImpl类和 StudentServiceImpl正在将所有Crud Operatons委派给StudentRepository，其实际与Spring Jdbectemplate与数据库进行交互。
创建应用程序类
package org.igi.theitroad;
import java.util.Arrays;
import java.util.List;
import org.igi.theitroad.model.Student;
import org.igi.theitroad.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class SpringBootJdbcExampleApplication implements CommandLineRunner{
@Autowired
StudentService studentService;
public static void main(String[] args) {
SpringApplication.run(SpringBootJdbcExampleApplication.class, args);
}
@Override
public void run(String... args) {
System.out.println("StartApplication...");
testStudentData();
}
void testStudentData() {
List students = Arrays.asList(
new Student(1,"John", 16),
new Student(2,"Martin", 18),
new Student(3,"Mary", 20),
new Student(4,"Ricky", 15)
);
System.out.println("[SAVE]");
students.forEach(student -> {
System.out.println("Saving student with name: "+student.getStudentName() );
studentService.save(student);
});
//find all
System.out.println("get All students: "+studentService.findAll());
//find by id
System.out.println("Find Student with id 2");
Student student = studentService.findById(2L).orElseThrow(IllegalArgumentException::new);
System.out.println("Student with id 2: "+student);
//update
System.out.println("Update age of Martin to 19");
student.setAge(19);
System.out.println("Rows affected: "+studentService.update(student));
//delete
System.out.println("Delete Student with id 4");
System.out.println("Rows affected: "+ studentService.deleteById(4));
//find all
System.out.println("get updated list of Students: "+studentService.findAll());
}
}
我们已经注射了 StudentServiceSpring的类唱片队在Spring的jdebcexamplapplication和使用 StudentService在这里做颤音的操作。
在 testStudentData()，我们创建了4个学生对象并在学生服务方法的帮助下执行了Crud操作。
运行应用程序
当我们运行应用程序时，我们将得到以下输出：
StartApplication... [SAVE] Saving student with name: John 2019-12-25 00:09:09.183 INFO 18336 — [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2019-12-25 00:09:10.190 INFO 18336 — [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. Saving student with name: Martin Saving student with name: Mary Saving student with name: Ricky get All students: [Student [studentId=1, StudentName=John,age =16], Student [studentId=2, StudentName=Martin,age =18], Student [studentId=3, StudentName=Mary,age =20], Student [studentId=4, StudentName=Ricky,age =15]] Find Student with id 2 Student with id 2: Student [studentId=2, StudentName=Martin,age =18] Update age of Martin to 19 Rows affected: 1 Delete Student with id 4 Rows affected: 1 get updated list of Students: [Student [studentId=1, StudentName=John,age =16], Student [studentId=2, StudentName=Martin,age =19], Student [studentId=3, StudentName=Mary,age =20]]
验证数据库输出
让我们将查询运行到Datbase并检查我们的更改是否反映在 Students table
如我们所见，表执行CRUD操作后，表显示更新数据。
