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
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 timeSummarize