This method is a minimal configuration, supports MySQL database multi-store connection, supports Hikari connection pooling, and supports MyBatis (including configurations for Dao class and xml file locations).
1. Introduce dependencies in pom.xml:
<!-- Begin of DB related --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.1.1</version> <exclusions> <exclusion> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-jdbc</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!-- End of DB related -->
We used mybatis-spring-boot-starter and let it exclude the tomcat-jdbc connection pool, so spring-boot will look for whether HikariCP is available, the second dependency is found, and then mysql-connector is also available.
2. Relevant configurations in application.yml:
spring: profiles: active: dev datasource: driver-class-name: com.mysql.jdbc.Driver username: root password: 123456 hikari: maxLifetime: 1765000 #The life time of a connection (milliseconds), timeout and not used will be released (retired). Default: 30 minutes. It is recommended to set more than 30 seconds less than the database timeout maximumPoolSize: 15 #The maximum number of connections allowed in the connection pool. Default value: 10; Recommended formula: ((core_count * 2) + effective_spindle_count) mybatis: mapperLocations: classpath:mapper/*.xml --- # Development environment configuration spring: profiles: dev datasource: url: jdbc:mysql://localhost:3306/ --- # Test environment configuration spring: profiles: test datasource: url: jdbc:mysql://192.168.0.12:3306/ --- # Production environment configuration spring: profiles: prod datasource: url: jdbc:mysql://192.168.0.13:3306/
Among them, the end of datasource.url does not include dbName, so that multiple dbs can be supported. When using it, you only need to specify the db name in front of the table name of the SQL statement.
3. Dao interface code:
package com.xjj.dao; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Select; import com.xjj.entity.Person; @Mapper public interface PersonDAO { @Select("SELECT id, first_name AS firstName, last_name AS lastName, birthday_date AS birthDate, sex, phone_no AS phoneNo" + " FROM test.t_person WHERE id=#{0};") public Person getPersonById(int id); public int insertPerson(Person person); public int updatePersonById(Person person); public int updatePersonByPhoneNo(Person person); }Just use @Mapper annotation to support finding by Mybatis and support writing SQL statements on methods.
4. XML file:
Create the mapper directory in the resources directory, and then create the xml file as follows:
<?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.xjj.dao.PersonDAO"> <!-- Insert database user table--> <insert id="insertPerson"> INSERT INTO test.t_person(first_name,last_name,birth_date,sex,phone_no,update_dt) VALUES(#{firstName},#{lastName},#{birthDate},#{sex},#{phoneNo},NOW()) </insert> <update id="updatePersonById"> UPDATE test.t_person SET first_name=#{firstName}, last_name=#{lastName}, birth_date=#{birthDate}, sex=#{sex}, phone_no=#{phoneNo} WHERE id=#{id} </update> <update id="updatePersonByPhoneNo"> UPDATE test.t_person SET first_name=#{firstName}, last_name=#{lastName}, birth_date=#{birthDate}, sex=#{sex} WHERE phone_no=#{phoneNo} </update> </mapper>5. Test:
@Test public void dbTest() throws JsonProcessingException{ Person person2 = personDAO.getPersonById(2); logger.info("person no 2 is: {}", objectMapper.writeValueAsString(person2)); person2.setFirstName("eight"); personDAO.updatePersonById(person2); person2 = personDAO.getPersonById(2); logger.info("person no 2 after update is: {}", objectMapper.writeValueAsString(person2)); assertThat(person2.getFirstName(), equalTo("8")); }Summarize
The above is the simple configuration method for spring boot to configure MySQL database connection, Hikari connection pool and Mybatis introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!