Of Stack

Spring Boot How to use JDBC to obtain relevant data details

  • 2021-07-03 00:01:52
  • OfStack

What is JDBC

Java Database Connectivity is a kind of Java API used to execute SQL statement, establish connection with database, send statement to operate database and process result.

Spring Boot Use JDBC

Increase dependency

Modify pom. xml: Modify dependecies to read the following two 


<dependencies>
 <dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
 </dependency>
 <dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
 </dependency>
 </dependencies>

Creating the Customer. java class 


package com.example.kane.Model;

public class Customer {
 private long id;
 private String firstName, lastName;

 public Customer(long id, String firstName, String lastName) {
 this.id = id;
 this.firstName = firstName;
 this.lastName = lastName;
 }

 @Override
 public String toString() {
 return String.format(
  "Customer[id=%d, firstName='%s', lastName='%s']",
  id, firstName, lastName);
 }

 // getters & setters omitted for brevity
}

Modify the Application class 


package com.example.kane;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.client.RestTemplateBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.EnableScheduling;

import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.client.RestTemplate;

import com.example.kane.Model.Customer;

@SpringBootApplication
//@EnableScheduling
public class RestfulWebService1Application implements CommandLineRunner{
 
 private static final Logger log = LoggerFactory.getLogger(RestfulWebService1Application.class);

 public static void main(String args[]) {
 SpringApplication.run(RestfulWebService1Application.class, args);
 }

 @Autowired
 JdbcTemplate jdbcTemplate;

 @Override
 public void run(String... strings) throws Exception {

 log.info("Creating tables");

 jdbcTemplate.execute("DROP TABLE customers IF EXISTS");
 jdbcTemplate.execute("CREATE TABLE customers(" +
  "id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))");

 // Split up the array of whole names into an array of first/last names
 List<Object[]> splitUpNames = Arrays.asList("John Woo", "Jeff Dean", "Josh Bloch", "Josh Long").stream()
  .map(name -> name.split(" "))
  .collect(Collectors.toList());

 // Use a Java 8 stream to print out each tuple of the list
 splitUpNames.forEach(name -> log.info(String.format("Inserting customer record for %s %s", name[0], name[1])));

 // Uses JdbcTemplate's batchUpdate operation to bulk load data
 jdbcTemplate.batchUpdate("INSERT INTO customers(first_name, last_name) VALUES (?,?)", splitUpNames);

 log.info("Querying for customer records where first_name = 'Josh':");
 jdbcTemplate.query(
  "SELECT id, first_name, last_name FROM customers WHERE first_name = ?", new Object[] { "Josh" },
  (rs, rowNum) -> new Customer(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name"))
 ).forEach(customer -> log.info(customer.toString()));
 }
}

Run the project to see the results

2019-03-01 14:19:52.078 INFO 7436 --- [ restartedMain] c.e.kane.RestfulWebService1Application : Creating tables
2019-03-01 14:19:52.086 INFO 7436 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2019-03-01 14:19:52.392 INFO 7436 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2019-03-01 14:19:52.429 INFO 7436 --- [ restartedMain] c.e.kane.RestfulWebService1Application : Inserting customer record for John Woo
2019-03-01 14:19:52.430 INFO 7436 --- [ restartedMain] c.e.kane.RestfulWebService1Application : Inserting customer record for Jeff Dean
2019-03-01 14:19:52.430 INFO 7436 --- [ restartedMain] c.e.kane.RestfulWebService1Application : Inserting customer record for Josh Bloch
2019-03-01 14:19:52.430 INFO 7436 --- [ restartedMain] c.e.kane.RestfulWebService1Application : Inserting customer record for Josh Long
2019-03-01 14:19:52.461 INFO 7436 --- [ restartedMain] c.e.kane.RestfulWebService1Application : Querying for customer records where first_name = 'Josh':
2019-03-01 14:19:52.480 INFO 7436 --- [ restartedMain] c.e.kane.RestfulWebService1Application : Customer[id=3, firstName='Josh', lastName='Bloch']
2019-03-01 14:19:52.480 INFO 7436 --- [ restartedMain] c.e.kane.RestfulWebService1Application : Customer[id=4, firstName='Josh', lastName='Long']
2019-03-01 14:20:01.122 INFO 7436 --- [nio-8080-exec-5] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2019-03-01 14:20:01.123 INFO 7436 --- [nio-8080-exec-5] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2019-03-01 14:20:01.146 INFO 7436 --- [nio-8080-exec-5] o.s.web.servlet.DispatcherServlet : Completed initialization in 22 ms

Description

In the example of official website, Datasource with JDBC and Template is not configured. By default, the database stored in memory of H2 is used, which can only be used as a test. How to change DataSource is described below

Introduction to CommandLineRunner

Function

After the project starts, execute the execution function. We can set a class to implement CommandLineRunner interface, rewrite run method and execute 1 part of operation. It should be noted that the definition class must be marked as a component managed by Spring

Test class 


package com.example.kane.Model;

import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
@Component
@Order(value=1) // Because there may be many things to do, Order  You can interpret the order of execution according to the size 
public class run_after_application implements CommandLineRunner{

 @Override
 public void run(String... args) throws Exception {
 // TODO Auto-generated method stub
 System.out.println("-----------------------");
 }
 
}

Introduction to JdbcTempalte

In the JDBC core package, JdbcTemplate is the main class, which simplifies the use of JDBC and avoids some common errors. It can execute JDBC core process, provide SQL statements on application code, and export results. This class performs SQL queries, updates, and catches JDBC exceptions by repeating operations on the result set. And translate it into the basic, more informative exception hierarchy defined in the org. springframework. dao package.

JDBC construction method

JdbcTemplate() 


// For Bean Create 1 A JdbcTemplate For use 
// No more configuration DataSource Under the condition of  springboot Provides  1 Some embedded database support, the above example uses is H2 Database, yes 1 A database with a memory

JdbcTemplate(javax.sql.DataSource dataSource) 


// Pass in at the time of construction 1 A  DataSource To get the link 
//JdbcTemplate Spring boot The default link is H2 database ,

Configuring the mysql database in spring boot

Database configuration class db_config 


package com.example.kane.config;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class db_config {
 // This class is 1 A Config Class 
 @Value("${db.driver}")
 private String DRIVER;

 @Value("${db.password}")
 private String PASSWORD;

 @Value("${db.url}")
 private String URL;

 @Value("${db.username}")
 private String USERNAME;
 @Bean
 public DataSource dataSource1() {
  BasicDataSource dataSource = new BasicDataSource();
  dataSource.setDriverClassName(DRIVER);
  dataSource.setUrl(URL);
  dataSource.setUsername(USERNAME);
  dataSource.setPassword(PASSWORD);
  return dataSource;
 }
}

application.properties 


# Database
# mysqljdbc Connection driver 
db.driver:com.mysql.cj.jdbc.Driver
db.url:jdbc:mysql://localhost:3306/test
db.username:root
db.password:root

pom.xml 


<dependency>
 <groupId>commons-dbcp</groupId>
 <artifactId>commons-dbcp</artifactId>
 <version>1.4</version>
</dependency>
<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <scope>runtime</scope>
</dependency>
<!--  Need to use commons-dbcp Connection pooling, and connection mysql Used drver-->

application Startup Class Modification 


 @Autowired
 JdbcTemplate jdbcTemplate;
 // The following is the configuration with the database loaded. Just add this 
 @Autowired
 db_config db_config;

After running the program, you will find that the data is stored in the local database 


package com.example.kane.Model;

public class Customer {
 private long id;
 private String firstName, lastName;

 public Customer(long id, String firstName, String lastName) {
 this.id = id;
 this.firstName = firstName;
 this.lastName = lastName;
 }

 @Override
 public String toString() {
 return String.format(
  "Customer[id=%d, firstName='%s', lastName='%s']",
  id, firstName, lastName);
 }

 // getters & setters omitted for brevity
}
0

Another simple way to configure the mysql database

Modify application. properties directly 


package com.example.kane.Model;

public class Customer {
 private long id;
 private String firstName, lastName;

 public Customer(long id, String firstName, String lastName) {
 this.id = id;
 this.firstName = firstName;
 this.lastName = lastName;
 }

 @Override
 public String toString() {
 return String.format(
  "Customer[id=%d, firstName='%s', lastName='%s']",
  id, firstName, lastName);
 }

 // getters & setters omitted for brevity
}
1

Change properties to yml file application. yml 


package com.example.kane.Model;

public class Customer {
 private long id;
 private String firstName, lastName;

 public Customer(long id, String firstName, String lastName) {
 this.id = id;
 this.firstName = firstName;
 this.lastName = lastName;
 }

 @Override
 public String toString() {
 return String.format(
  "Customer[id=%d, firstName='%s', lastName='%s']",
  id, firstName, lastName);
 }

 // getters & setters omitted for brevity
}
2

Note: These two methods return to the configuration file mode.

JDBC Template Common Methods

execute method: It can be used to execute any SQL statement, and 1 is generally used to execute DDL statement; update method and batchUpdate method: update method is used for executing statements such as adding, modifying and deleting; The batchUpdate method is used to execute batch-related statements; query method and queryForXXX method: for executing query related statements; call method: Used to execute stored procedures, function-related statements. Refer to official website https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html

One thing about connection pooling

Why use database connection pooling?

Because establishing a database connection is a very time-consuming process, connection pooling can be used to establish a connection with the database in advance and put it in memory. When the application needs to use the database, it can directly use the connections in the connection pool.

Current 3 mainstream connection pools

DBCP: Provides the maximum number of free connections, over which all connections are automatically disconnected, and the other two are not. C3P0: Provides the maximum idle connection time, so that the mechanism of automatically retracting idle connections can be achieved Druid: Made by Ali, it also provides the maximum idle connection time

Summarize

Related articles: