PS: You should never be afraid of change, because that is the path to becoming better.
JDBC (Java Data Base Connectivity) is mainly used for connecting to databases, executing SQL statements, processing SQL execution results, etc. 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/
Here, select the installation package corresponding to the Windows operating system for download, as shown in the figure below:
Then select next to install, and after 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 a database named db_student as follows:
The 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 dependencies for JDBC and MySQL driver 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 the configuration is complete, you can use IDEA's Database tool to test whether the configuration is correct. The test is successful as 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 Return a single record
*/
Student findByName(String name);
/**
* Query all data
* @return Return 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 automatic 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 for testing. Here, we take 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("Gongxingzhi", "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 follows:
At this point, the data insertion operation is successful, and the delete, update, and query operations are the same.
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.