catalogue
1. Write yaml configuration file to connect to database
3. Binding and configuring data sources
4. Customize the DruidDataSource component and configure Druid data source monitoring
5. Configure Druid web monitoring filter
1. Dependencies required to import MyBatis
2. Configure database information
5. Create the corresponding UserMapper.xml under resources
6. Bind the UserMapper.xml file path in the configuration file
7. Write test class UserController
8. Currently, Druid database connection pool is used, and DruidConfig is customized
1, Integrated JDBC
1. Write yaml configuration file to connect to database
spring: datasource: username: root password: luolin123 url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8 driver-class-name: com.mysql.jdbc.Driver
2,JdbcTemplate
1. With the data source (com.zaxxer.hikari.HikariDataSource), you can get the data. With the connection, you can use the native JDBC statement to operate the database;
2. Even without using a third-party database operation framework, such as MyBatis, Spring itself makes a lightweight encapsulation of the native JDBC, that is, JdbcTemplate.
3. All CRUD methods for database operations are in the JdbcTemplate.
4. Spring Boot not only provides the default data source, but also the configured JdbcTemplate is placed in the container by default. Programmers only need to inject it themselves
5. The automatic configuration of JdbcTemplate depends on the JdbcTemplateConfiguration class under the org.springframework.boot.autoconfigure.jdbc package
JdbcTemplate mainly provides the following methods:
Execute method: it can be used to execute any SQL statement, generally used to execute DDL statements;
Update method and batchUpdate method: the update method is used to execute new, modify, delete and other statements; The batchUpdate method is used to execute batch related statements;
Query method and queryForXXX method: used to execute query related statements;
call method: used to execute stored procedures, functions and related statements
3. Testing
package com.rk.controller; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.Map; @RestController//Returns a string without using the view parser public class JDBCController { @Autowired JdbcTemplate jdbcTemplate; //Query all data in the user table and return to the front page @GetMapping("/userList") public List<Map<String,Object>> userList(){ String sql="select * from user"; List<Map<String, Object>> list_maps = jdbcTemplate.queryForList(sql); return list_maps; } //Add a user @GetMapping("/addUser") public String addUser(){ String sql="insert into user(id,name,pwd) values (4,'Wang Min','123456')"; jdbcTemplate.update(sql); return "add-ok"; } //Modify information @GetMapping("/updateUser/{id}") public String updateUser(@PathVariable("id") int id){ String sql="update user set name=?,pwd=? where id="+id; //Packaging parameters Object[] objects=new Object[2]; objects[0]="Rowling"; objects[1]="123456"; jdbcTemplate.update(sql,objects); return "update-ok"; } //delete user @GetMapping("/deleteUser/{id}") public String deleteUser(@PathVariable("id") int id){ String sql="delete from user where id=?"; jdbcTemplate.update(sql,id); return "delete-ok"; } }
2, Integrated Druid
1. Introduction to Druid
1. A large part of Java programs need to operate the database. In order to improve the performance, they have to use the database connection pool when operating the database. Druid is a database connection pool implementation on Alibaba's open source platform. It combines the advantages of C3P0, DBCP and other DB pools, and adds log monitoring.
2. Druid can well monitor DB pool connections and SQL execution. It is naturally a DB connection pool for monitoring.
3. Druid has deployed more than 600 applications in Alibaba, which has been severely tested by large-scale deployment in the production environment for more than a year.
4. For Spring Boot 2.0 and above, Hikari data source is used by default. It can be said that Hikari and Driud are the best data sources on the current Java Web. Let's focus on how Spring Boot integrates Druid data source and how to realize database monitoring.
2. Add dependency
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.21</version> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency>
3. Binding and configuring data sources
spring: datasource: username: root password: luolin123 url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8 driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource #specify data source #Spring Boot does not inject these attribute values by default and needs to bind itself #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 for monitoring statistics interception, stat: monitoring statistics, log4j: logging, wall: defending sql injection #If the error is allowed, java.lang.ClassNotFoundException:org.apache.log4j.Priority will be reported #Then import the log4j dependency. Maven address: https://mvnrepository.com/artifact/log4j/log4j filters: stat,wall,log4j maxPoolPreparedStatementPerConnectionSize: 20 useGlobalDataSourceStat: true connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
4. Customize the DruidDataSource component and configure Druid data source monitoring
package com.rk.config; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.support.http.StatViewServlet; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.web.servlet.ServletRegistrationBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import java.util.HashMap; import java.util.Map; @Configuration public class DruidConfig { @ConfigurationProperties(prefix = "spring.datasource") @Bean public DruidDataSource druidDataSource() { return new DruidDataSource(); } ///Configure the Servlet of Druid monitoring management background; //There is no web.xml file in the built-in Servlet container, so the Servlet registration method of Spring Boot is used @Bean public ServletRegistrationBean statViewServlet() { ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); // These parameters can be found in com.alibaba.druid.support.http.StatViewServlet // Found in the parent class of com.alibaba.druid.support.http.ResourceServlet Map<String, String> initParams = new HashMap<>(); initParams.put("loginUsername", "admin"); //Login account of background management interface initParams.put("loginPassword", "123456"); //Login password of background management interface //Who is allowed to access in the background //initParams.put("allow", "localhost"): indicates that only the local machine can access it //initParams.put("allow", ""): when it is empty or null, it means that all accesses are allowed initParams.put("allow", ""); //deny: Druid, who is denied access in the background //initParams.put("rk", "192.168.1.20"); Indicates that this ip access is prohibited //Set initialization parameters bean.setInitParameters(initParams); return bean; } }
Start: Enter druid/login.html page (druid is automatically generated)
After entering
5. Configure Druid web monitoring filter
//Configure Druid monitoring function //WebStatFilter: used to configure management association monitoring statistics between Web and Druid data sources @Bean public FilterRegistrationBean webStatFilter(){ FilterRegistrationBean bean=new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); //exclusions: sets which requests are filtered and excluded so that statistics are not performed Map<String, String> initParams = new HashMap<>(); initParams.put("exclusions", "*.js,*.css,/druid/*,/jdbc/*"); bean.setInitParameters(initParams); //"/ *" means to filter all requests bean.setUrlPatterns(Arrays.asList("/*")); return bean; }
3, Integrate Mybatis
1. Dependencies required to import MyBatis
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.1</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.21</version> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.elasticsearch</groupId> <artifactId>elasticsearch</artifactId> <version>2.4.0</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>5.3.12</version> </dependency> </dependencies>
2. Configure database information
spring: datasource: username: root password: luolin123 url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8 driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource #specify data source #Spring Boot does not inject these attribute values by default and needs to bind itself #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 for monitoring statistics interception, stat: monitoring statistics, log4j: logging, wall: defending sql injection #If the error is allowed, java.lang.ClassNotFoundException:org.apache.log4j.Priority will be reported #Then import the log4j dependency. Maven address: https://mvnrepository.com/artifact/log4j/log4j filters: stat,wall,log4j maxPoolPreparedStatementPerConnectionSize: 20 useGlobalDataSourceStat: true connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
3. Create entity class
package com.rk.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @AllArgsConstructor @NoArgsConstructor public class User { private int id; private String name; private String pwd; }
4. Create UserMapper
package com.rk.mapper; import com.rk.pojo.User; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import org.springframework.stereotype.Repository; import java.util.List; @Mapper//Indicates that this is a mapper class of mybatis @Repository public interface UserMapper { List<User> queryUserList();//Query all users void deleteUser(@Param("uid") int id);//Delete by id void updateUser(@Param("uid") int id);//Modify according to id void addUser(User user);//Add user User queryByid(@Param("uid") int id);//Query user by id }
5. Create the corresponding UserMapper.xml under resources
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.rk.mapper.UserMapper"> <select id="queryUserList" resultType="User"> select * from user </select> <delete id="deleteUser" parameterType="int"> delete from user where id=#{uid} </delete> <update id="updateUser" parameterType="int"> update user set name='Rowling' where id=#{uid} </update> <insert id="addUser" parameterType="User"> insert into user (id,name, pwd) values (#{id},#{name},#{pwd}) </insert> <select id="queryByid" resultType="User" parameterType="int"> select * from user where id=#{uid} </select> </mapper>
If you create mapper.xml directly under the pojo file directory, you need to filter the resource path in < build > of pom.xml
<resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> </resources>
6. Bind the UserMapper.xml file path in the configuration file
#Specify the core configuration file and Mapper mapping file for myBatis mybatis.mapper-locations=classpath:mapper/*.xml # Note: the path of the corresponding entity class mybatis.type-aliases-package=com.rk.pojo
7. Write test class UserController
package com.rk.controller; import com.rk.mapper.UserMapper; import com.rk.pojo.User; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController public class UserController { @Autowired private UserMapper userMapper; @GetMapping("/query") public List<User> query(){ List<User> users = userMapper.queryUserList(); return users; } @GetMapping("/update/{id}") public void updateUser(@PathVariable("id") int id){ userMapper.updateUser(id); } @GetMapping("/delete/{id}") public void deleteUser(@PathVariable("id") int id){ userMapper.deleteUser(id); } @GetMapping("/add") public void AddUser(){ User user=new User(); user.setId(4); user.setName("Luo min"); user.setPwd("luolin"); userMapper.addUser(user); } @GetMapping("/querybyid/{id}") public User queryUser(@PathVariable("id") int id){ return userMapper.queryByid(id); } }
8. Currently, Druid database connection pool is used, and DruidConfig is customized
package com.rk.config; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.web.servlet.FilterRegistrationBean; import org.springframework.boot.web.servlet.ServletRegistrationBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import java.util.Arrays; import java.util.HashMap; import java.util.Map; @Configuration//Represents a spring configuration class, equivalent to bean.xml public class DruidConfig { @ConfigurationProperties(prefix = "spring.datasource") @Bean public DruidDataSource druidDataSource() { return new DruidDataSource(); } ///Configure the Servlet of Druid monitoring management background; //There is no web.xml file in the built-in Servlet container, so the Servlet registration method of Spring Boot is used @Bean public ServletRegistrationBean statViewServlet() { ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); // These parameters can be found in com.alibaba.druid.support.http.StatViewServlet // Found in the parent class of com.alibaba.druid.support.http.ResourceServlet Map<String, String> initParams = new HashMap<>(); initParams.put("loginUsername", "admin"); //Login account of background management interface initParams.put("loginPassword", "123456"); //Login password of background management interface //Who is allowed to access in the background //initParams.put("allow", "localhost"): indicates that only the local machine can access it //initParams.put("allow", ""): when it is empty or null, it means that all accesses are allowed initParams.put("allow", ""); //deny: Druid, who is denied access in the background //initParams.put("rk", "192.168.1.20"); Indicates that this ip access is prohibited //Set initialization parameters bean.setInitParameters(initParams); return bean; } //Configure Druid monitoring function //WebStatFilter: used to configure management association monitoring statistics between Web and Druid data sources @Bean public FilterRegistrationBean webStatFilter(){ FilterRegistrationBean bean=new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); //exclusions: sets which requests are filtered and excluded so that statistics are not performed Map<String, String> initParams = new HashMap<>(); initParams.put("exclusions", "*.js,*.css,/druid/*,/jdbc/*"); bean.setInitParameters(initParams); //"/ *" means to filter all requests bean.setUrlPatterns(Arrays.asList("/*")); return bean; } }