PS: the original text was first published on wechat official account: Jingxing zhi (Jzman-blog)

Read the previous ones before reading this article:

  • Spring Boot series to develop an interface
  • Spring Boot series Thymeleaf template introduction
  • Spring Boot series Thymeleaf common syntax
  • Spring Boot series Thymeleaf template layout
  • Spring Boot series project internationalization

JDBC (Java Data Base Connectivity, Java database connection), the main purpose is used for database connection, SQL statement execution, PROCESSING SQL execution results, etc., from scratch in Spring Boot JDBC learning, the main content is as follows:

  1. MySQL installation
  2. Connect the MySQL
  3. Creating a database
  4. Dependencies and Configuration
  5. Entity class
  6. Add, delete, modify and check
  7. The test results
  8. Multi-data Source Configuration

MySQL installation

Visit the official website to download the corresponding version of MySQL:

https://dev.mysql.com/downloads/
Copy the code

Here, select the installation package corresponding to Windows operating system to download, as shown below:

Select Next to install MySQL, and then start MySQL.

Connect the MySQL

MySQL > install MySQL from navicat;

Enter the user name, password, and click Test connection. If the configuration is correct, the connection will be successful.

Create databases and tables

MySQL > create database db_student

The command for creating a database is as follows:

CREATE DATABASE `db_student` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci';
Copy the code

Create a table named student after the database is created, either using navicat or using the following command:

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;
Copy the code

Dependencies and Configuration

Create the Spring Boot project and add the JDBC and MySQL driver dependencies to 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")
    // ...
}
Copy the code

Then perform the basic database configuration in the project’s application.properties file as follows:

# database username spring. The datasource. The username = # root database password spring. The 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/ShanghaiCopy the code

After the configuration is complete, the Database tool of IDEA can be used to test whether the configuration is correct. The test is successful, as shown in the following figure:

Entity class

Create table student; create table student;

/** * Entity class */
public class Student {
    private long id;
    private String name;
    private String password;
    private int age;

    public Student(a) {}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
}
Copy the code

Add, delete, modify and check

Define IStudentRepository as follows:

/ * * *@Desc: defines the add, delete, check and change interface *@Author: jzman
 */
public interface IStudentRepository {
    /** * Save data *@paramStudent Single student record */
    void save(Student student);

    /** * delete data *@paramId Student ID */
    void delete(long id);

    /** * Update data *@paramStudent Single student record */
    void update(Student student);

    /** * query data *@paramThe name name *@returnReturns a single record */
    Student findByName(String name);

    /** * query all data *@returnReturn all records */
    List<Student> findAll(a);
}
Copy the code

Create repository interface iStudentrePositoryImpl create repository interface IStudentRepository

/ * * *@Desc: Implementation *@Author: jzman
 */
@Repository
public class StudentRepositoryImpl implements IStudentRepository {

    public JdbcTemplate mJdbcTemplate;

    /** * the constructor is automatically assembled@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(a) {
        return mJdbcTemplate.query("SELECT * FROM student".newBeanPropertyRowMapper<>(Student.class)); }}Copy the code

The test results

Write a test program to test. Here, take adding data as an example, insert two data as follows:

 * @Desc: StudentRepositoryTests
 * @Author: jzman
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class StudentRepositoryTests {
    @Autowired
    private IStudentRepository mStudentRepository;

    @Test
    public void testSave(a){
        Student student1 = new Student("Practice."."111".3);
        Student student2 = new Student(2."jzman"."123".20); mStudentRepository.save(student1); mStudentRepository.save(student2); }}Copy the code

After running testSave and using the Database tool provided by IDEA, double-click table Student to view the contents of the table as follows:

At this point, the data insert is successful, as are the delete, modify, and query operations.

Multi-data Source Configuration

Configure the DataSource and JdbcTemplate for multiple data sources. Define the 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(a) {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource(a) {
        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 newJdbcTemplate(dataSource); }}Copy the code

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 # dataSource1 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/ShanghaiCopy the code

Different data sources correspond to different JdbcTemplate to manipulate the data of the corresponding data source. For details, see the corresponding source code at the end of this article.

Can pay attention to the public number [practice] exchange learning, reply keyword [Spring Boot] to obtain the corresponding case source link.