Article directory
- 1. Data SourceDataSource
- 2. SpringBoot’s embedded data source object
- 3. Spring’s built-in persistence solution JdbcTemplate
- 4. SpringBoot embedded database
- 5. Access to embedded database H2
1. Data sourceDataSource
Before understanding the concept of data source, let’s take a look at the basic steps of native JDBC:
Connection conn= null;
Statement statement = null;
try{<!-- -->
//Register driver
Class.forName("com.mysql.cj.jdbc.Driver");
//Get connection
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDB","root","95279527");
//Get the database operation object
statement = conn.createStatement();
//Execute SQL statement
String sql = "UPDATE dept SET dname='Intelligent Research and Development' WHERE deptno=1";
int count = statement.executeUpdate(sql);
System.out.println(count == 1 ? "Update successful" : "Update failed");
}catch(Exception e){<!-- -->
e.printStackTrace();
} finally{<!-- -->
//Close the resource
}
Data source, that is, the source of data. All information for establishing a database connection is stored in the data source object, such as driver type, host, port, username, password, to build a database connection and connection pool (note that not all data sources provide connection pools), this connection pool, What I understand is a collection of Connection objects above.
Ordinary connection to the database is to establish a connection. After executing the SQL, it will be closed, that is, the connection object is destroyed. You need to repeat the above steps to connect again. This pattern can severely impact program performance when interacting with the database is frequent. Most of the time and space consumption is consumed in connection and destruction, rather than database processing. The data source connection pool will establish multiple database connections based on your parameters and cache them. Every time you connect to the database, you can get a connection from the connection pool to execute SQL, and it will be released after use. Enter the connection pool. In this way, the overhead of connecting to the database is avoided and the pressure on the database is reduced.
2. SpringBoot’s embedded data source object
In the previous project, the technical selection of the data layer solution was:
- Use DruidDataSource as data source
- Database uses MySQL
- Persistence framework: MyBatis-plus
The configuration format of the data source is:
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/ssm_db?serverTimezone=UTC
username: root
password: root
type:xxx
When Druid’s starting dependencies are introduced, this configuration can also be written as:
SpringBoot provides 3 built-in data source objects for developers to choose from:
- Hikari CP: Default built-in data source object
- Tomcat provides DataSource: When HikariCP is unavailable and in a web environment, the data source object configured by the tomcat server will be used
- Commons DBCP: Hikari is not available, and the tomcat data source is not available. The dbcp data source will be used.
Note that in addition to the general configuration, the specific connection configuration must be set in the next-level configuration depending on the specific data source type, such as maximum-pool-size:
Comment out Druid’s starting dependency and restart the module. You can see that the previous Druid information is no longer in the log, but Hikaricp is output.
3. Spring’s built-in persistence solution JdbcTemplate
Next, instead of using the previous MyBatis or MyBatisPlus, use Spring’s JdbcTemplate to complete persistence and introduce the starting dependencies:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--Don’t forget the MySQL driver-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
In fact, if you look at the dependencies of mybatis or mybatisplus, you will find that they contain the starting dependency of jdbc, spring-boot-starter-jdbc, because of course these two frameworks are also encapsulated based on jdbc. The relationship between them, what I understand is Native JDBC => JDBC Template => MyBatis => MyBatisPlus
Regarding the use of JDBC Template, the rough query is as follows. At this time, the type of the returned result is List, which may be inconvenient to use.
@Test
void testJdbcTemplate(@Autowired JdbcTemplate jdbcTemplate){<!-- -->
String sql = "select * from tbl_book where id = 1";
List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
}
The following writing method returns a List of entity classes. The RowMapper interface is used for conversion in the middle:
@SpringBootTest
class Springboot15SqlApplicationTests {<!-- -->
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
void testJdbc(){<!-- -->
String sql = "select * from tbl_book where id = 1";
List<Book> query = jdbcTemplate.query(sql, new RowMapper<Book>() {<!-- -->
@Override
public Book mapRow(ResultSet rs, int rowNum) throws SQLException {<!-- -->
Book temp = new Book();
temp.setId(rs.getInt("id"));
temp.setName(rs.getString("name"));
temp.setType(rs.getString("type"));
temp.setDescription(rs.getString("description"));
return temp;
}
});
System.out.println(query);
}
}
About the configuration of JdbcTemplate:
spring:
jdbc:
template:
query-timeout: -1 # Query timeout time
max-rows: 500 # Maximum number of rows
fetch-size: -1 # Number of cache lines
This number of cache lines means that all rows of the query will not be loaded in at one time, but fetch-size rows will be stored first each time.
4. SpringBoot embedded database
SpringBoot provides 3 built-in databases for developers to choose from to improve development and testing efficiency:
- H2
- HSQL
- Derby
H2 is an embedded relational database written in Java language. It is just a class library (that is, there is only one jar file) and can be directly embedded into application projects without being restricted by the platform.
Taking the H2 database as an example, first import the dependent coordinates:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <scope>runtime</scope> </dependency>
Then set the current project as a web project (just introduce the spring-boot-starter-web startup dependency directly), and configure the H2 management console parameters:
spring:
h2:
console:
enabled: true # Enable visual console
path: /h2 #Configure console access path
# Access username sa, default password 123456
The first time you start a test connection on the console, you may fail. There is no such library (there is no such folder in the corresponding disk ~ directory). Saving the C drive is a bit annoying. You can change it as needed, such as:
jdbc:h2:D:/software/h2/data/test
Change the configuration and paste the configuration of the above form into the datasource:
Restart the service and then log in again to succeed:
H2 started successfully.
5. Access to embedded database H2
server:
port: 80
spring:
datasource:
driver-class-name: org.h2.Driver
url: jdbc:h2:~/test
username: sa
password: 123456
h2:
console:
path: /h2
enabled: true
SpringBoot can automatically identify the database type according to the URL address. If the driver class exists, the configuration can be omitted, that is, the driver-class-name: org.h2.Driver line can be commented out. Use JDBC to operate the H2 database:
String sql = "inser into xxx"; jdbcTemplate.update(sql);
An error may be reported here:
The file is locked：nio:C:/Users/yourname/test.mv.db
Database may be already in use:null. Close all other connections
This is because the default connection method is embedded. By default, this connection methodonly allows one client to connect to the H2 database. After a client connects to the H2 database, the database file will be locked< /strong>, then other clients will no longer be able to connect. Finally, the H2 database console is only used in the development stage. Please be sure to turn off the console function for online projects. (Or separate configuration files, dev, pro)
For more information about H2 database, such as whether the running mode is embedded mode, server mode, mixed mode, whether the connection mode is memory mode, etc., please refer to this article: https://blog.csdn.net/qq_34845394/article/details/107190256
At this point, there are many technical choices and combinations for the data layer:
