Interested friends can go to know about the other several articles have been published, your praise is the biggest support for me, thank you!
- The article directories
SpringBoot takeoff road -HelloWorld
(two) SpringBoot takeoff road – analysis of the principle of entry
(three) SpringBoot takeoff road -YAML configuration summary (entry must know will)
(4) SpringBoot takeoff road – static resource processing
(five) SpringBoot takeoff road -Thymeleaf template engine
(6) SpringBoot takeoff road – integration jdbctemplate-druid-mybatis
(7) SpringBoot launch road – integration of SpringSecurity (Mybatis, JDBC, memory)
(eight) SpringBoot take-off path – integration Shiro detailed tutorial (MyBatis, Thymeleaf)
SpringBoot -Swagger 2 And 3
Description:
- SpringBoot takeoff road series of articles involved in the source code, are synchronized to github, there is a need for small partners, go down at will
- Github.com/ideal-20/Sp…
- Untalented and uneducated, will point to shallow knowledge, we should be a tool to see it, do not like to spray ha ~
The introduction
The Thymeleaf template engine has been introduced, and some small demos will start to involve data, so today we will talk about how to integrate common data related techniques in SpringBoot: JdbcTemplate, Druid, MyBatis
The content introduced in this article is not new and does not involve too much grammar. The key is integration. I will briefly mention the three kinds of introduction
And finally, before we start, there’s one more thing to mention
SpringBoot on the database related processing, all use Spring Data, it is a sub-project in the Spring family bucket, can support the operation of relational/non-relational database, can greatly simplify the operation of database access
For more information, you can go to the official website:
https://spring.io/projects/spring-data
(1) Integrate JdbcTemplate
Although it is called integrating JdbcTemplate, it is essentially integrating JDBC, but the JdbcTemplate simplifies native JDBC
(1) Introducing dependencies
We first create a Project or Module, then initialize a SpringBoot Project, in addition to the basic Web, and select the JDBC API from the SQL option on the left, which introduces a dependency on integrating JDBC
Take a look at POM, which introduces spring-boot-starter-JDBC as an initiator, and encapsulates some of its dependencies
Details can refer to the official website documents, I posted two versions, more versions, you can go to read
https://docs.spring.io/spring-boot/docs/2.3.0.RELEASE/reference/html/using-spring-boot.html#using-boot-structuring-your- code
https://docs.spring.io/spring-boot/docs/2.2.7.RELEASE/reference/html/using-spring-boot.html#using-boot-starter
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
Copy the codeThe previous version of mysql-connector-Java should also be introduced. If not, don’t worry, we will introduce it later when we write database configuration
(2) Modify the configuration
Next, we need to configure some information about the database in the configuration file, such as username, password and so on. We use YML for this, and the same with properties
One more word about the URL option, my table name is springboot_mybatis_test, but if you do not set the time zone or some encoding, there may be some errors in the use of different versions.
When setting the time zone, as I set serverTimezone=UTC, it will be 8 hours earlier than China time, so China can choose Asia/Shanghai or Asia/Hongkong
spring:
datasource:
username: root
password: root99
# serverTimezone=Asia/Shanghai
url: jdbc:mysql://localhost:3306/springboot_mybatis_test? serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
driver-class-name: com.mysql.cj.jdbc.Driver
Copy the codeIf mysql-connector-java is not added to the mysql-connector-java dependencies, the configuration will be displayed in red. If mysql-connector-java is not added to the connector-java dependencies, the configuration will be displayed in red. If mysql-connector-java is not added to the connector-java dependencies, the configuration will be displayed in red. The default version-free dependency is the 8.x version of mysql-connector-Java
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
Copy the codeTherefore, select com.mysql.cj.jdbc.driver during configuration
Com.mysql.jdbc.driver is out of date
Loading class
com.mysql.jdbc.Driver'. This is deprecated. The new driver class iscom.mysql.cj.jdbc.Driver’. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
You can also explicitly specify the mysql-connector-Java version if you want to use older versions
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.26</version>
</dependency>
Copy the code(3) Use JdbcTemplate for CURD
After simple configuration, it is already possible to add, delete, change and check the native JDBC. The native JDBC is a bit cumbersome, Spring has given us a certain simplification, namely the JdbcTemplate, which we should have used for a long time
A: the query
As for the query, I show you two ways, one is if we have an entity corresponding to the content of the database, and one is if there is no entity corresponding to the query, and don’t forget to inject the JdbcTemplate
- There are entities: New a BeanPropertyRowMapper in the query, which asked JdbcTemplate help us put the query result set. Each row of the ResultSet results use BeanPropertyRowMapper mapRow () method, To the Java class object we want
- No entities: Use the queryForList method to return a collection of individual maps
@Controller
@RequestMapping("/jdbc")
public class JdbcTemplateController {
@Autowired
JdbcTemplate jdbcTemplate;
// There are corresponding entities
@RequestMapping("/userList")
public void userList(a){
String sql = "select * from user";
ArrayList<User> users = (ArrayList<User>) jdbcTemplate.query(sql,
new BeanPropertyRowMapper<User>(User.class));
for(User user : users){ System.out.println(user); }}// There is no corresponding entity
@RequestMapping("/userList")
@ResponseBody
public List<Map<String, Object>> userList(){
String sql = "select * from user";
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
returnmaps; }}Copy the codeThis is the collection of objects that we found
User{uid=1, username='zhangsan', password='666', nickname='The Flying Penguin'}
User{uid=2, username='lisi', password='666', nickname='Sad Little Boy'}
Copy the codeThis is the set of maps that we found
[{"uid":1."username":"zhangsan"."password":"666"."nickname":"The Flying Penguin"}, {"uid":2."username":"lisi"."password":"666"."nickname":"Sad little Boy"}]
Copy the codeB: insert
@RequestMapping("/addUser")
@ResponseBody
public String addUser(a){
// Insert statement, pay attention to time problem
String sql = "Insert into user (uid, username, password and nickname) values (NULL, 'wangwu', '888', 'Cathy nickname')";
jdbcTemplate.update(sql);
/ / query
return "User added successfully";
}
Copy the codeC: modify
// Modify user information
@RequestMapping("/updateUser/{uid}")
@ResponseBody
public String updateUser(@PathVariable("uid") int uid){
// Insert statement
String sql = "update user set username=? ,password=? ,nickname=? where uid="+ uid;
/ / data
String[] s = new String[3];
s[0] = "jack";
s[1] = "666";
s[2] = "Jack";
jdbcTemplate.update(sql,s);
/ / query
return "User information modified successfully";
}
Copy the codeD: delete
// Delete the user
@RequestMapping("/deleteUser/{uid}")
@ResponseBody
public String delUser(@PathVariable("uid") int uid){
// Insert statement
String sql = "delete from user where uid=?";
jdbcTemplate.update(sql,uid);
/ / query
return "User deleted successfully";
}
Copy the code(2) Integrate Druid
(1) SpringBoot default connection pool
Druid is an open source database connection pool that can be used to create and close a database. The connection pool can be used to create and close a database
SpringBoot, by default, has a configuration data source, which we can look at briefly, just like in the JDBC example above, in the test class
@SpringBootTest
class Springboot08JdbcApplicationTests {
@Autowired
DataSource dataSource;
@Test
void contextLoads(a) throws SQLException {
/ / the data source
System.out.println(dataSource.getClass());
// Get the connectionConnection connection = dataSource.getConnection(); System.out.println(connection); connection.close(); }}Copy the codeTake a look at the print
class com.zaxxer.hikari.HikariDataSource
HikariProxyConnection@118492650 wrapping com.mysql.cj.jdbc.ConnectionImpl@712cfb63
Copy the codeAs you can see, SpringBoot uses Hikari connection pooling by default. If you want to take a look at the source code, you can take a look at these two classes
DataSourceAutoConfiguration, DataSourceConfiguration
Hikari and Druid are excellent database connection pools. Druid has a lot more functionality than Hikari and Druid. It also has some features such as statistics or SQL blocking
(2) Introducing dependencies
To start, add Druid’s dependencies to your Pom. Check out the latest version at the following url
https://mvnrepository.com/artifact/com.alibaba/druid
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
Copy the code(3) Modify the configuration
Then use the spring in the configuration. The datasource. Type to specify a custom data source types, the value with the connection pool to realize the fully qualified name, for example: com. Alibaba. Druid. Pool. DruidDataSource
Druid = yML; Druid = yML; Druid = yML
spring:
datasource:
username: root
password: root99
url: jdbc:mysql://localhost:3306/springboot_mybatis_test? serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
driver-class-name: com.mysql.cj.jdbc.Driver
# Custom data source
type: com.alibaba.druid.pool.DruidDataSource
#druid Data source proprietary configuration
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
Configure filters to monitor statistical intercepts
#stat: monitor statistics, log4j: log, wall: defend against SQL injection
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=500
Copy the codeIf the run time error Java. Lang. ClassNotFoundException: org.. Apache log4j. Priority import log4j rely on
Address: https://mvnrepository.com/artifact/log4j/log4j
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
Copy the code(4) Bind custom configuration parameters
Some privatized Settings such as the following initialization size or Max/min connection count will not take effect because Spring Boot does not inject these values by default, so we need to bind them ourselves
Create a package called config and create the DruidConfig class
We first have to do is to bind the Druid configuration of global configuration files to com. Alibaba. Druid. Pool. DruidDataSource, it can take effect (@ ConfigurationProperties)
The custom Druid data source is then added to the container so that SpringBoot does not need to create it itself
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druidDataSource(a) {
return newDruidDataSource(); }}Copy the codeIt’s a simple test to see if it works
@SpringBootTest
class Springboot08JdbcApplicationTests {
@Autowired
DataSource dataSource;
@Test
void contextLoads(a) throws SQLException {
/ / the data source
System.out.println(dataSource.getClass());
// Get the connection
Connection connection = dataSource.getConnection();
System.out.println(connection);
DruidDataSource druidDataSource = (DruidDataSource) dataSource;
System.out.println("Maximum number of connections to data source:" + druidDataSource.getMaxActive());
System.out.println("Data source initialization number of connections:" + druidDataSource.getInitialSize());
// Close the connectionconnection.close(); }}Copy the codeThe run shows that the basic configuration is in effect
class com.alibaba.druid.pool.DruidDataSource com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@74e6094b druidDataSource Maximum number of connections to a data source: 20 druidDataSource Number of initial connections to a data source: 5Copy the code(5) Configure data source monitoring
Another feature of Druid is data source monitoring, which provides a background management page
Continue adding to the new DruidConfig configuration class above
-
Spring Boot is used to register servlets, because the built-in Servlet container does not have a web. XML file, it is some fixed writing method
-
Parameters such as user names can be found in the ResourceServlet parent of StatViewServlet under Druid
-
You can also set access personnel to be associated with access prohibited personnel
@Bean
public ServletRegistrationBean statViewServlet(a) {
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
Map<String, String> initParams = new HashMap<>();
initParams.put("loginUsername"."admin"); // Account used to log in to the background management interface
initParams.put("loginPassword"."admin888"); // Password for logging in to the background GUI
// initparams. put("allow", "localhost") : indicates that only the localhost is accessible
// initparams. put("allow", ""): indicates that the background allows all access
initParams.put("allow"."");
// Set the initialization parameters
bean.setInitParameters(initParams);
return bean;
}
Copy the codeTake a look:
We enter http://localhost:8080/druid/ in the address bar of any path to access the login page background, after login, can see some such as SQL monitoring etc. Function
(3) Integration of MyBatis
So this is basically a normal user table, so let’s just test it out, and just to make it look a little bit more comfortable, I’m not specifying Druid here, so if you want to, you can just click on it, ok
(1) Introducing dependencies
Introduce mybatis initiator and connect to MySQL driver
<! -- Mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<! MySQL connection driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
Copy the code(2) Configure the database
Mybatis = mybatis = mybatis = Mybatis = Mybatis = Mybatis = Mybatis = Mybatis = Mybatis = Mybatis Type-aliases-package configures the package where we put our entity, that is, the scan package
spring:
datasource:
username: root
password: root99
url: jdbc:mysql://localhost:3306/springboot_mybatis_test? serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8
driver-class-name: com.mysql.cj.jdbc.Driver
# type: com. Alibaba. Druid. Pool. DruidDataSource # custom data source
mybatis:
mapper-locations: classpath:mapper/*Mapper.xml
type-aliases-package: cn.ideal.pojo
Copy the code(3) Create entities
public class User {
private Long uid;
private String username;
private String password;
privateString nickname; . get set toString }Copy the code(4) Write Mapper and mapper.xml
UserMapper
@Mapper
public interface UserMapper {
public List<User> queryAllUser(a);
}
Copy the codeUserMapper.xml
This file path SRC \main\resources\mapper\ usermapper.xml
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cn.ideal.mapper.UserMapper">
<select id="queryAllUser" resultType="cn.ideal.domain.User">
select * from user
</select>
</mapper>
Copy the code(5) test
@Controller
public class MybatisController {
@Autowired
private UserMapper userMapper;
@RequestMapping("/queryAllUser")
@ResponseBody
public List<User> queryUser(a) {
List<User> users = userMapper.queryAllUser();
returnusers; }}Copy the codeReturn result:
[{"uid":1."username":"zhangsan"."password":"666"."nickname":"The Flying Penguin"}, {"uid":2."username":"lisi"."password":"666"."nickname":"Sad little Boy"}, {"uid":3."username":"jack"."password":"666"."nickname":"Jack"}]
Copy the code(4) Redis integration
After writing the integrated MyBatis, I will briefly mention Redis, because I can use the integrated MyBatis to determine whether the data comes from the database or the cache when testing later
(1) Introducing dependencies
<! Redis initiator -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
Copy the code(2) Modify the configuration
You can use “properties” here
spring:
redis:
host: 127.0. 01.
port: 6379
Copy the codePort is the default Redis, at the same time you can open the Redis client, installed in the directory to find the good redis-server.exe
(3) Test Redis by RedisTemplate
So let’s go ahead and test it in the test class, and here’s what it looks like, the first time it looks up from the database, and then it writes to the cache, and the second time it looks up from the cache because it already has data in the cache
@RunWith(SpringRunner.class)
@SpringBootTest(classes = Springboot03MybatisApplication.class)
public class RedisTest {
@Autowired
private UserMapper userMapper;
@Autowired
private RedisTemplate<String, String> redisTemplate;
@Test
public void test(a) throws JsonProcessingException {
// check from redis
String userLists = redisTemplate.boundValueOps("user.queryAllUser").get();
// If there is no content in redis
if (null == userLists) {
// Query database data
List<User> all = userMapper.queryAllUser();
/ / json
ObjectMapper om = new ObjectMapper();
userLists = om.writeValueAsString(all);
// Store the data in Redis, and get the data directly from Redis next time
redisTemplate.boundValueOps("user.queryAllUser").set(userLists);
System.out.println("This data is from the database!!");
} else {
System.out.println("This data is retrieved from redis cache!!"); } System.out.println(userLists); }}Copy the codeThe execution result
For the first time,
This data is obtained from the database!! [{"uid":1."username":"zhangsan"."password":"666"."nickname":"The Flying Penguin"}, {"uid":2."username":"lisi"."password":"666"."nickname":"Sad little Boy"}, {"uid":3."username":"jack"."password":"666"."nickname":"Jack"}]
Copy the codeThe second time
This data is retrieved from redis cache!! [{"uid":1."username":"zhangsan"."password":"666"."nickname":"The Flying Penguin"}, {"uid":2."username":"lisi"."password":"666"."nickname":"Sad little Boy"}, {"uid":3."username":"jack"."password":"666"."nickname":"Jack"}]
Copy the code(5) Ending
If there is any deficiency in the article, you are welcome to leave a message to exchange, thank friends for their support!
If it helps you, follow me! If you prefer the way of reading articles on wechat, you can follow my official account
We don’t know each other here, but we are working hard for our dreams
A adhere to push original development of technical articles of the public number: ideal more than two days
