Data
1. Configuration file
When creating a new project, select mysql and jdbc Driver to import dependencies
Configure the user name, password, url, and Driver in the configuration file
spring: datasource: username: root password: 123456 url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai driver-class-name: com.mysql.cj.jdbc.Driver
Note that since mysql used in SpringBoot is version 8.0 +, the url must contain time zone information
serverTimezone=Asia/Shanghai
2. Test use
package com.wang; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; @SpringBootTest class Springboot04DataApplicationTests { //As long as the DataSource is automatically assembled, we can use it @Autowired DataSource dataSource; @Test void contextLoads() throws SQLException { //View the default data source: Hikari HikariDataSource System.out.println(dataSource.getClass()); //Get database connection Connection connection = dataSource.getConnection(); System.out.println(connection); //Close database connection connection.close(); } }
be careful
- Bind DataSource using auto assembly
- Call jdbc with getConnection method
- The default data source is Hikari HikariDataSource
3. CRUD
package com.wang.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 public class jdbcController { //XXXX template: springboot's configured template bean is ready to use and encapsulates CRUD //jdbc //redis @Autowired JdbcTemplate jdbcTemplate; //Query all information in the database //Without entity classes, how to get things in the database? = = > Map @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; } @GetMapping("/addUser") public String addUser() { String sql = "insert into mybatis.user(id, name, pwd) values(5, 'Xiao Ming', '123456')"; int update = jdbcTemplate.update(sql); if (update == 1) { return "addUser-OK"; } else { return "addUser-Fail"; } } //Splice SQL, pass parameters in RestFul style, and annotate with @ PathVariable @GetMapping("/updateUser/{id}") public String updateUser(@PathVariable("id") int id) { String sql = "update mybatis.user set name = ?, pwd = ? where id=" + id; //encapsulation Object[] objects = new Object[2]; objects[0] = "Xiaoming 2"; objects[1] = "1233211234567"; //update in jdbcTemplate overloads prepareStatement, which can be directly transferred to the required object int update = jdbcTemplate.update(sql, objects); if (update == 1) { return "updateUser-OK"; } else { return "updateUser-Fail"; } } @GetMapping("/deleteUser/{id}") public String deleteUser(@PathVariable("id") int id) { String sql = "delete from mybatis.user where id = ?"; //update in jdbcTemplate overloads prepareStatement, which can be directly transferred to the required object //int is also an object. You can pass it in directly int update = jdbcTemplate.update(sql, id); if (update == 1) { return "deleteUser-OK"; } else { return "deleteUser-Fail"; } } }
be careful
- XXXX template: springboot's configured template bean is ready to use and encapsulates CRUD
- Except for queries, update statements are used
- update in jdbcTemplate overloads prepareStatement, which can be directly transferred to the required object
- Query for query
- Without entity classes, how to get things in the database? = = > Map = = > the map is stored in the list, and the field name and value in the database are stored in the form of K-V in the map. The field name is of String type and the value is of Object type
4. Druid
1. Import dependency
<!-- https://mvnrepository.com/artifact/com.alibaba/druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.24</version> </dependency>
2. Specify the data source and configure Druid
Specify the type in the configuration file and configure Druid
spring: datasource: username: root password: 123456 url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource #Spring Boot does not inject these attribute values by default and needs to bind by 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: defensive sql injection #If allowed, an error will be reported in Java lang.ClassNotFoundException: org. apache. log4j. Priority #Then import 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. Bind Druid to spring datasource
package com.wang.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 javax.sql.DataSource; import java.util.HashMap; @Configuration public class DruidConfig { //Combine the custom Bean with spring Datasource binding @ConfigurationProperties(prefix = "spring.datasource") //Register DruidDataSource with Bean @Bean public DataSource druidDataSource() { return new DruidDataSource(); } }
be careful
- @ConfigurationProperties binding configuration, and prefix can be specified later
4. Enable background monitoring
package com.wang.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 javax.sql.DataSource; import java.util.HashMap; @Configuration public class DruidConfig { //Combine the custom Bean with spring Datasource binding @ConfigurationProperties(prefix = "spring.datasource") //Register DruidDataSource with Bean @Bean public DataSource druidDataSource() { return new DruidDataSource(); } //Background monitoring, you can access / druid to fix the written code //Because spring boot has a built-in servlet container, there is no web XML, alternative: ServletRegistrationBean @Bean public ServletRegistrationBean statViewServlet() { ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*"); //Someone needs to log in at the background and configure the account and password HashMap<String, String> initParameters = new HashMap<>(); //Add configuration //The login key is a fixed loginUsername loginPassword initParameters.put("loginUsername", "admin"); initParameters.put("loginPassword", "123456"); //Who is allowed to access? If the value is blank, everyone can access initParameters.put("allow", ""); //IP whitelist //initParameters.put("allow","192.168.1.12,127.0.0.1"); //IP blacklist //initParameters.put("deny","192.168.4.23"); //Can I reset the data initParameters.put("resetEnable","false"); bean.setInitParameters(initParameters); //Set initialization parameters return bean; } }
The key is as follows
public static final String SESSION_USER_KEY = "druid-user"; public static final String PARAM_NAME_USERNAME = "loginUsername"; public static final String PARAM_NAME_PASSWORD = "loginPassword"; public static final String PARAM_NAME_ALLOW = "allow"; public static final String PARAM_NAME_DENY = "deny"; public static final String PARAM_REMOTE_ADDR = "remoteAddress";
To use log4j, configure log4j under resources properties
#Output the log information with the level of DEBUG to the two destinations of console and file. The definitions of console and file are in the following code log4j.rootLogger=DEBUG,console,file #Relevant settings of console output log4j.appender.console = org.apache.log4j.ConsoleAppender log4j.appender.console.Target = System.out log4j.appender.console.Threshold=DEBUG log4j.appender.console.layout = org.apache.log4j.PatternLayout log4j.appender.console.layout.ConversionPattern=[%c]-%m%n #Relevant settings for file output log4j.appender.file = org.apache.log4j.RollingFileAppender log4j.appender.file.File=./log/wang.log log4j.appender.file.MaxFileSize=10mb log4j.appender.file.Threshold=DEBUG log4j.appender.file.layout=org.apache.log4j.PatternLayout log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n #Log output level log4j.logger.org.mybatis=DEBUG log4j.logger.java.sql=DEBUG log4j.logger.java.sql.Statement=DEBUG log4j.logger.java.sql.ResultSet=DEBUG log4j.logger.java.sql.PreparedStatement=DEBUG
be careful
- After configuring with methods in JavaConfig, be sure to register beans
- Through servletregistrationbean = new servletregistrationbean < > (New statviewservlet(), "/ Druid / *"); Configure stat and set path
- We need to register stat with web XML, because spring boot has built-in servlet container, so there is no web XML, alternative: ServletRegistrationBean
- Put it into the setInitParameters method in the form of key value pairs (map)
- The key is fixed. Don't Scribble
5. Set the Filter for background monitoring
//filter //We need to register the filter. Similarly, use the filter registration bean to register @Bean public FilterRegistrationBean webStatFilter() { FilterRegistrationBean bean = new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); //What requests can be filtered HashMap<String, String> initParameters = new HashMap<>(); //These things are not counted initParameters.put("exclusions", "*.js,*.css,/druid/*"); bean.setInitParameters(initParameters); return bean; }
The key is as follows
public final static String PARAM_NAME_PROFILE_ENABLE = "profileEnable"; public final static String PARAM_NAME_SESSION_STAT_ENABLE = "sessionStatEnable"; public final static String PARAM_NAME_SESSION_STAT_MAX_COUNT = "sessionStatMaxCount"; public static final String PARAM_NAME_EXCLUSIONS = "exclusions"; public static final String PARAM_NAME_PRINCIPAL_SESSION_NAME = "principalSessionName"; public static final String PARAM_NAME_PRINCIPAL_COOKIE_NAME = "principalCookieName"; public static final String PARAM_NAME_REAL_IP_HEADER = "realIpHeader";
be careful
- Similar to stat, we need to register filter and use FilterRegistrationBean
- Don't forget to register Bean
5. MyBatis
1. Import dependency
mybatis-spring-boot-starter
<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency>
2. Write entity class
package com.wang.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; }
3. Write Dao layer
package com.wang.mapper; import com.wang.pojo.User; import org.apache.ibatis.annotations.Mapper; import org.springframework.stereotype.Repository; import java.util.List; //This annotation indicates that this is a Mapper class of MyBatis //You can also add @ MapperScan("") scanning package to the startup class @Mapper //This annotation indicates that it is Dao layer @Repository public interface UserMapper { List<User> queryUserList(); User queryUserById(int id); int addUser(User user); int updateUser(User user); int deleteUser(int id); }
be careful
- @Mapper
- This annotation indicates that this is a Mapper class of MyBatis
- You can also add @ MapperScan("") scanning package to the startup class
- @Repository
- This annotation indicates that it is Dao layer
- The above two notes are indispensable
4. Integrate Mybatis
Add to profile
# Integrate Mybatis mybatis: type-aliases-package: com.wang.pojo mapper-locations: classpath:MyBatis/mapper/*.xml
Set alias and make xxxmapper XML path
be careful
- In the path setting, there is no path after classpath:/
5. Write xxxmapper xml
Create a new folder under the path corresponding to the configuration file (resources path)
UserMapper.xml
<?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.wang.mapper.UserMapper"> <select id="queryUserList" resultType="User"> select * from user; </select> <select id="queryUserById" resultType="User"> select * from user where id = #{id}; </select> <insert id="addUser" parameterType="User"> insert into user (id, name, pwd) VALUES (#{id}, #{name}, #{pwd}); </insert> <update id="updateUser" parameterType="User"> update user set name = #{name}, pwd = #{pwd} where id = #{id}; </update> <delete id="deleteUser" parameterType="_int"> delete from user where id = #{id}; </delete> </mapper>
6. Write controller
package com.wang.controller; import com.wang.mapper.UserMapper; import com.wang.pojo.User; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController public class UserController { @Autowired private UserMapper userMapper; @GetMapping("/userList") public List<User> queryUserList() { List<User> userList = userMapper.queryUserList(); return userList; } @GetMapping("/addUser") public String addUser() { int i = userMapper.addUser(new User(6, "Xiao Ming", "123456")); return i == 1 ? "ok" : "fail"; } @GetMapping("/updateUser") public String updateUser() { int i = userMapper.updateUser(new User(6, "Xiaoming 2", "654321")); return i == 1 ? "ok" : "fail"; } @GetMapping("/deleteUser") public String deleteUser() { int i = userMapper.deleteUser(6); return i == 1 ? "ok" : "fail"; } }
be careful
- In SpringBoot, automatic assembly @ AutoWired should be used for all introductions. Dao's interface should be introduced here. In the actual project, Service layer classes should be introduced (lazy and not written here)