PS: You should never be afraid of change, as it is the path to becoming better.
JDBC (Java Data Base Connectivity) is primarily used for connecting to databases, executing SQL statements, and processing SQL execution results. The learning of JDBC in Spring Boot from scratch includes the following main content:
- Install MySQL
- Connect to MySQL
- Create a database
- Dependencies and configuration
- Entity class
- Implement CRUD operations
- Test the effect
- Multi-data source configuration
Install MySQL#
Visit the official website to download the corresponding version of MySQL:
https://dev.mysql.com/downloads/
Select the installation package corresponding to the Windows operating system for download, as shown in the figure below:
Then select next to install, and once the installation is complete, you can start MySQL.
Connect to MySQL#
After installing MySQL, start MySQL, and then use Navicat to connect to MySQL. Create a new connection as follows:
Enter the username, password, etc., and click to test the connection. If the configuration is correct, it will prompt that the connection is successful.
Create Database and Table#
After connecting to MySQL, right-click on the connection to create the database db_student as follows:
The corresponding command to create the database is as follows:
CREATE DATABASE `db_student` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci';
After creating the database, create a table named student. You can create it using Navicat or use the command. The command to create the table is as follows:
CREATE TABLE `student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key id',
`name` varchar(32) DEFAULT NULL COMMENT 'Username',
`password` varchar(32) DEFAULT NULL COMMENT 'Password',
`age` int DEFAULT NULL COMMENT 'Age',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Dependencies and Configuration#
Create a Spring Boot project and add the JDBC and MySQL driver dependencies in its build.gradle file as follows:
dependencies {
// jdbc dependency
implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'
// mysql driver
runtime("mysql:mysql-connector-java")
// ...
}
Then, configure the basic database settings in the project's application.properties file as follows:
# Database username
spring.datasource.username=root
# Database password
spring.datasource.password=admin
# JDBC Driver
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# JDBC URL
spring.datasource.url=jdbc:mysql://localhost:3306/db_student?serverTimezone=Asia/Shanghai
After completing the configuration, you can use IDEA's Database tool to test whether the configuration is correct. The successful test is shown in the figure below:
Entity Class#
Create the data entity class corresponding to the data table student as follows:
/**
* Entity class
*/
public class Student {
private long id;
private String name;
private String password;
private int age;
public Student() {
}
public Student(String name, String password, int age) {
this.name = name;
this.password = password;
this.age = age;
}
public Student(long id,String name, String password, int age) {
this.id = id;
this.name = name;
this.password = password;
this.age = age;
}
// setter and getter methods
}
Implement CRUD Operations#
Define the CRUD interface IStudentRepository as follows:
/**
* @Desc: Define the CRUD interface
* @Author: jzman
*/
public interface IStudentRepository {
/**
* Save data
* @param student Single student record
*/
void save(Student student);
/**
* Delete data
* @param id Student id
*/
void delete(long id);
/**
* Update data
* @param student Single student record
*/
void update(Student student);
/**
* Query data
* @param name Name
* @return Returns a single record
*/
Student findByName(String name);
/**
* Query all data
* @return Returns all records
*/
List<Student> findAll();
}
Create StudentRepositoryImpl to implement the IStudentRepository interface for CRUD operations:
/**
* @Desc: Specific implementation
* @Author: jzman
*/
@Repository
public class StudentRepositoryImpl implements IStudentRepository {
public JdbcTemplate mJdbcTemplate;
/**
* Constructor for auto-wiring
* @param jdbcTemplate JdbcTemplate
*/
public StudentRepositoryImpl(JdbcTemplate jdbcTemplate) {
this.mJdbcTemplate = jdbcTemplate;
}
@Override
public void save(Student student) {
mJdbcTemplate.update("INSERT INTO student(name,password,age) values(?,?,?) ",
student.getName(), student.getPassword(), student.getAge());
}
@Override
public void delete(long id) {
mJdbcTemplate.update("DELETE FROM student where id=?", id);
}
@Override
public void update(Student student) {
mJdbcTemplate.update("UPDATE student SET name=?,password=?,age=? WHERE id=?",
student.getName(), student.getPassword(), student.getAge(), student.getId());
}
@Override
public Student findByName(String name) {
Object[] args = {name};
return mJdbcTemplate.queryForObject("SELECT * FROM student WHERE name=?", args,
new BeanPropertyRowMapper<Student>(Student.class));
}
@Override
public List<Student> findAll() {
return mJdbcTemplate.query("SELECT * FROM student",new BeanPropertyRowMapper<>(Student.class));
}
}
Test the Effect#
Write a test program to test, taking adding data as an example, inserting two records as follows:
* @Desc: StudentRepositoryTests
* @Author: jzman
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class StudentRepositoryTests {
@Autowired
private IStudentRepository mStudentRepository;
@Test
public void testSave(){
Student student1 = new Student("躬行之", "111",3);
Student student2 = new Student(2,"jzman", "123",20);
mStudentRepository.save(student1);
mStudentRepository.save(student2);
}
}
After running testSave, use the Database tool provided by IDEA to double-click the student table to view the contents of the data table, as shown below:
At this point, the data insertion operation is successful, and the delete, update, and query operations are similar.
Multi-Data Source Configuration#
The configuration of multiple data sources mainly involves configuring the corresponding DataSource and JdbcTemplate. Define multiple data sources as follows:
/**
* @Desc: Data source configuration
* @Author: jzman
*/
@Configuration
public class DataSourceConfig {
@Primary
@Bean(name = "primaryDataSource")
@Qualifier("primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "secondaryDataSource")
@Qualifier("secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "primaryJdbcTemplate")
public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource dataSource){
return new JdbcTemplate(dataSource);
}
@Bean(name = "secondaryJdbcTemplate")
public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource dataSource){
return new JdbcTemplate(dataSource);
}
}
Then, configure multiple database connections in the application.properties file as follows:
# dataSource1
spring.datasource.primary.username=root
spring.datasource.primary.password=admin
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/data_source_one?serverTimezone=Asia/Shanghai
# dataSource2
spring.datasource.secondary.username=root
spring.datasource.secondary.password=admin
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/data_source_two?serverTimezone=Asia/Shanghai
Different data sources correspond to different JdbcTemplate, allowing you to operate on the data of the corresponding data source. For specific details, please refer to the source code at the end of the document.