Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "what is the way SpringBoot uses SQL database". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "what is the way SpringBoot uses SQL database" can help you solve the problem.

I. configure the data source

Java's javax.sql.DataSource interface provides a standard way to handle database connections.

1.1. Embedded database support

It is usually convenient to develop applications using embedded databases in memory. Obviously, in-memory databases do not provide persistent storage. You need to populate the database when the application starts and be prepared to discard the data when the application ends.

Spring Boot can automatically configure embedded H2, HSQL, and Derby databases. You do not need to provide any connection URL. You only need to include build dependencies on the embedded database you want to use. If there are multiple embedded databases on the classpath, set the spring.datasource.embedded-database-connection configuration property to control which one to use. Set this property to none to disable automatic configuration of embedded databases.

If you use this feature in your tests, you may notice that the entire test suite reuses the same database no matter how many application contexts you use. If you want to ensure that each context has a separate embedded database, you should set spring.datasource.generate-unique-name to true.

Org.springframework.boot spring-boot-starter-data-jpa org.hsqldb hsqldb runtime1.2. Connect to the production database

Use the DataSource pool to automatically configure production database connections.

1.3. Data source configuration spring.datasource.url=jdbc:mysql://localhost/testspring.datasource.username=dbuserspring.datasource.password=dbpass

You should at least specify URL by setting the spring.datasource.url property. Otherwise, Spring Boot attempts to automatically configure the embedded database.

Spring Boot can infer the JDBC driver classes for most databases from URL. If you need to specify a specific class, you can use this spring.datasource.driver-class-name property.

# Tomcat connection pool configuration spring.datasource.tomcat.max-wait=10000spring.datasource.tomcat.max-active=50spring.datasource.tomcat.test-on-borrow=true1.4. Supported connection pooling

1. We prefer HikariCP because of its performance and concurrency. If HikariCP is available, we always choose it.

two。 Otherwise, if the Tomcat DataSource pool is available, we will use it.

3. Otherwise, if Commons DBCP2 is available, we will use it.

4. If HikariCP, Tomcat, and DBCP2 are not available, and Oracle UCP is available, we use it.

HikariCP

Tomcat pooling Datasource

Commons DBCP2

Oracle UCP & OracleDataSource

Spring Framework's SimpleDriverDataSource

H2 JdbcDataSource

PostgreSQL PGSimpleDataSource

1.5. Connect to the JNDI data source

If you deploy a Spring Boot application to an application server, you may want to configure and manage the data source using the built-in capabilities of the application server and access it using JNDI.

Spring.datasource.jndi-name=java:jboss/datasources/customers

Second, use JdbcTemplate

The Spring JdbcTemplate and NamedParameterJdbcTemplate classes are configured automatically. It can be referenced directly through @ Autowire.

@ Componentpublic class MyBean {private final JdbcTemplate jdbcTemplate; public MyBean (JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;} public void doSomething () {this.jdbcTemplate...}} spring.jdbc.template.max-rows=500 III, JPA and Spring Data JPA

Java Persistence API is a standard technology that allows you to "map" objects to relational databases. The spring-boot-starter-data-jpa POM provides a shortcut to get started. It provides the following key dependencies:

Hibernate: one of the most popular JPA implementations.

Spring Data JPA: helps you implement a JPA-based repository.

Spring ORM: core ORM support from the Spring framework.

3.1. Entity class @ Entitypublic class City implements Serializable {@ Id @ GeneratedValue private Long id; @ Column (nullable = false) private String name; @ Column (nullable = false) private String state; / /. Additional members, often include @ OneToMany mappings protected City () {/ / no-args constructor required by JPA spec / / this one is protected since it shouldn't be used directly} public City (String name, String state) {this.name = name; this.state = state;} public String getName () {return this.name;} public String getState () {return this.state } / /... Etc} 3.2.Spring Data JPA repository

Spring Data JPA repositories are interfaces that you can define to access data. The JPA query is automatically created based on your method name.

Public interface CityRepository extends Repository {Page findAll (Pageable pageable); City findByNameAndStateAllIgnoringCase (String name, String state);}

The Spring Data JPA repository supports three different boot modes: default, deferred, and and lazy.

To enable deferred or delayed booting, set the spring.data.jpa.repositories.bootstrap-mode property to deferred or lazy, respectively.

3.3. Create and delete JPA databases

By default, an JPA database is created automatically only if you use an embedded database (H2, HSQL, or Derby). You can explicitly configure JPA settings using the spring.jpa.* property.

Spring.jpa.hibernate.ddl-auto=create-drop

Spring.jpa.properties.hibernate [globally _ quoted_identifiers] = true

3.4. Open EntityManager in a view

If you are running a Web application, Spring Boot registers OpenEntityManagerInViewInterceptor by default to apply the Open EntityManager in View mode to allow delayed loading in the Web view. If you don't want this behavior, you should set spring.jpa.open-in-view=false.

IV. Spring Data JDBC

Spring Data includes repository support for JDBC and will automatically be CrudRepository. For more advanced queries, @ Query provides comments.

Spring Boot automatically configures Spring Data's JDBC repository when the necessary dependencies are on the classpath. They can be added to your project and depend only on spring-boot-starter-data-jdbc.

Use the Web console of H2

The H2 database provides a browser-based console that is automatically configured for you. The console is automatically configured when the following conditions are met:

You are developing servlet-based Web applications.

Com.h3database:h3 is on the classpath.

You are using Spring Boot's developer tools.

If you do not use Spring Boot's developer tools, but still want to use H2's console, you can configure the value of the spring.h3.console.enabled property to true.

The H2 console is only for use during development, so you should be careful to make sure that spring.h3.console.enabled does not set it to true in production.

By default, the console is located at / h3-console. You can use this spring.h3.console.path property to customize the path to the console.

VI. Use jOOQ

JOOQ object-oriented query (jOOQ) is a popular product of Data Geekery that generates Java code from your database and allows you to build type-safe SQL queries through its smooth API. Both commercial and open source versions can be used with Spring Boot.

6.1. Code generation

In order to use jOOQ type-safe queries, you need to generate Java classes from the database schema. You can follow the instructions in the jOOQ user's manual. If you use the jooq-codegen-maven plug-in and you also use the spring-boot-starter-parent "parent POM", you can safely omit the plug-in label. You can also use Spring Boot-defined version variables, such as h3.version, to declare the plug-in's database dependencies.

Org.jooq jooq-codegen-maven... Com.h3database h3 ${h3.version} org.h3.Driver jdbc:h3:~/yourdatabase... 6.2. Use DSLContext

The fluent API provided by jOOQ is initiated through the org.jooq.DSLContext interface. Spring Boot automatically configures DSLContext as Spring Bean and connects it to your application DataSource. To use DSLContext, you can inject it.

@ Componentpublic class MyBean {private final DSLContext create; public MyBean (DSLContext dslContext) {this.create = dslContext;} public List authorsBornAfter1980 () {return this.create.selectFrom (AUTHOR) .where (AUTHOR.DATE_OF_BIRTH.greaterThan (new GregorianCalendar (1980, 0, 1) .fetch (AUTHOR.DATE_OF_BIRTH);}} 6.3.jOOQ SQL dialect

Unless spring.jooq.sql-dialect has configured this property, Spring Boot determines the SQL dialect used for the data source. If Spring Boot cannot detect the dialect, it uses DEFAULT.

Spring Boot can only automatically configure dialects supported by the open source version of jOOQ.

6.4. Custom jOOQ

More advanced customization can be achieved by defining your own DefaultConfigurationCustomizer bean, which creates an org.jooq.Configuration. This takes precedence over automatically configuring anything in the application.

7. Use R2DBC

The Reactive Relational Database Connectivity (R2DBC) project brings reactive programming API to relational databases. R2DBC io.r2dbc.spi.Connection provides a standard way to use non-blocking database connections. The connection is a DataSource provided through ConnectionFactory, similar to jdbc. ConnectionFactory is configured by spring.r2dbc.*.

Spring.r2dbc.url=r2dbc:postgresql://localhost/testspring.r2dbc.username=dbuserspring.r2dbc.password=dbpass

You do not need to specify the driver class name because Spring Boot gets the driver from the connection factory discovery of R2DBC.

@ Configuration (proxyBeanMethods = false) public class MyR2dbcConfiguration {@ Bean public ConnectionFactoryOptionsBuilderCustomizer connectionFactoryPortCustomizer () {return (builder)-> builder.option (ConnectionFactoryOptions.PORT, 5432);} @ Configuration (proxyBeanMethods = false) public class MyPostgresR2dbcConfiguration {@ Bean public ConnectionFactoryOptionsBuilderCustomizer postgresCustomizer () {Map options = new HashMap (); options.put ("lock_timeout", "30s"); options.put ("statement_timeout", "60s") Return (builder)-> builder.option (PostgresqlConnectionFactoryProvider.OPTIONS, options); Embedded database support

Similar to JDBC support, Spring Boot can automatically configure embedded databases for responsive use. You do not need to provide any connection URL. You only need to include build dependencies on the embedded database you want to use.

Io.r2dbc r2dbc-h3 runtime

If you use this feature in your tests, you may notice that the entire test suite reuses the same database no matter how many application contexts you use. If you want to ensure that each context has a separate embedded database, you should set spring.r2dbc.generate-unique-name to true.

7.2. Use the database client @ Componentpublic class MyBean {private final DatabaseClient databaseClient; public MyBean (DatabaseClient databaseClient) {this.databaseClient = databaseClient;} public Flux someMethod () {return this.databaseClient.sql ("select * from user") .fetch () .all ();}} 7.3.Spring Data R2DBC repository

Spring Data R2DBC repositories are interfaces that you can define to access data. The query is automatically created based on your method name. For more complex queries, you can annotate your method using Spring Data's Query annotations.

Spring Data repositories are typically extended from Repository or CrudRepository interfaces.

This is the end of public interface CityRepository extends Repository {Mono findByNameAndStateAllIgnoringCase (String name, String state);} about "how SpringBoot uses SQL databases". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.