(go) Spring Boot JDBC Connection Database

The text will be introduced to several database connection methods based on MySQL database in the Web application built in spring boot.
Includes JDBC, JPA, MyBatis, multiple data sources, and transactions.

1 JDBC Connection Database1.1 Property configuration file (application.properties)

Spring.datasource.url=jdbc:mysql://localhost:3306/testspring.datasource.username= Rootspring.datasource.password=123456spring.datasource.driver-class-name=com.mysql.jdbc.driver

If you use Jndi, you can override Spring.datasource URLs, username, password, such as:(not understood)

It is worth mentioning that either the spring boot default datasource configuration or your own DataSource Bean will refer to the attribute configuration in the external subordination file. So suppose you customize the DataSource bean, you can set the property when you define the bean, or in the properties file, you can "spring.datasource.*" the property in such a way that it is externally configured.

1.2 pom.xml configuring MAVEN dependencies

 <!--MySQL driver -        <Dependency>            <groupId>Mysql</groupId>            <Artifactid>Mysql-connector-java</Artifactid>         </Dependency>         <!--Spring Boot JDBC -         <Dependency>            <groupId>Org.springframework.boot</groupId>            <Artifactid>Spring-boot-starter-jdbc</Artifactid>          </Dependency>

1.3 Java code example

Studentservice.java

 PackageOrg.springboot.sample.service;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.util.List;Importorg.springboot.sample.entity.Student;Importorg.springframework.beans.factory.annotation.Autowired;Importorg.springframework.jdbc.core.JdbcTemplate;ImportOrg.springframework.jdbc.core.RowMapper;ImportOrg.springframework.stereotype.Service;/*** Studeng Service * *@authorTan Hongyu (365384722) * @mybloghttp://blog.csdn.net/catoop/* @create January 12, 2016*/@Service Public classStudentservice {@AutowiredPrivateJdbcTemplate JdbcTemplate;  PublicList<student>getList () {String SQL= "Select Id,name,score_sum,score_avg, age from STUDENT"; return(list<student>) jdbctemplate.query (SQL,NewRowmapper<student>() {@Override PublicStudent Maprow (ResultSet RS,intRowNum)throwsSQLException {Student stu=NewStudent (); Stu.setid (Rs.getint ("ID")); Stu.setage (Rs.getint ("Age")); Stu.setname (Rs.getstring ("NAME")); Stu.setsumscore (Rs.getstring ("Score_sum")); Stu.setavgscore (Rs.getstring ("Score_avg")); returnStu;    }        }); }}

Student.java entity class

 Packageorg.springboot.sample.entity;Importjava.io.Serializable;/*** Student Entity * *@authorTan Hongyu (365384722) * @mybloghttp://blog.csdn.net/catoop/* @create January 12, 2016*/ Public classStudentImplementsserializable{Private Static Final LongSerialversionuid = 2120869894112984147L; Private intID; PrivateString name; PrivateString Sumscore; PrivateString Avgscore; Private intAge ; //Save article length, get Set method omitted}

Studentcontroller.java

 PackageOrg.springboot.sample.controller;Importjava.util.List;ImportOrg.slf4j.Logger;Importorg.slf4j.LoggerFactory;Importorg.springboot.sample.entity.Student;ImportOrg.springboot.sample.service.StudentService;Importorg.springframework.beans.factory.annotation.Autowired;Importorg.springframework.web.bind.annotation.RequestMapping;ImportOrg.springframework.web.bind.annotation.RestController, @RestController @requestmapping ("/stu") Public classStudentcontroller {Private Static FinalLogger Logger = Loggerfactory.getlogger (Studentcontroller.class); @AutowiredPrivateStudentservice Studentservice; @RequestMapping ("/list")     PublicList<student>Getstus () {Logger.info ("Read student collection from database"); returnstudentservice.getlist (); }}

This article adds a file to the project after the project structure diagram:

Then start the project, Access address: http://localhost:8080/myspringboot/stu/list response results are as follows:

[{ID:1, Name:"Xiao Ming ",Sumscore:"252 ",Avgscore:"",Age1},{id:2, Name:"Xiao Wang ",Sumscore:"187 ",Avgscore:"62.3 ",Age1},{id:3, Name:"Lily, "Sumscore:"",Avgscore:"",Age0},{id:4, Name:"pillars ",Sumscore:"",Avgscore:"76.7 ",Age1},{id:5, Name:"Mao ",Sumscore:"",Avgscore:"",Age0},{id:6, Name:"bright Son ",Sumscore:"0 ",Avgscore:"0 ",Age1}]

2 Connection Pool Description

Before TOMCAT7, Tomcat essentially applied the JDBC data source implemented by DBCP connection pooling technology, but after TOMCAT7, Tomcat provided a new JDBC connection pooling scheme as a replacement or alternative to DBCP, addressing many of the disadvantages of using dbcp before, and improves performance. For more information, please refer to: http://wiki.jikexueyuan.com/project/tomcat/tomcat-jdbc-pool.html

Spring Boot prepares us for the best database connection pool scenario by configuring the required connection pool parameters in the properties file (for example, application.properties).
We use the Tomcat data source connection pool, we need to rely on TOMCAT-JDBC, as long as the application has added SPRING-BOOT-STARTER-JDBC or SPRING-BOOT-STARTER-DATA-JPA dependencies, there is no need to worry about this, Because TOMCAT-JDBC dependencies will be added automatically.
If we want to use the other way of connection pooling technology, as long as the configuration of their own datasource bean, you can override the spring boot automatic configuration.

Please see my data source configuration:

Spring.datasource.url=jdbc:mysql://localhost:3306/testspring.datasource.username= Rootspring.datasource.password=123456spring.datasource.driver-class-name= com.mysql.jdbc.driverspring.datasource.max-idle=10spring.datasource.max-wait=10000spring.datasource.min-idle= 5spring.datasource.initial-size=5spring.datasource.validation-query=select 1spring.datasource.test-on-borrow= falsespring.datasource.test-while-idle=truespring.datasource.time-between-eviction-runs-millis= 18800spring.datasource.jdbc-interceptors=connectionstate; Slowqueryreport (threshold=0)

Developers who have configured connection pooling are aware of the implications of these attributes.

We open the debug log output and add the Logback.xml:

<name= "Org.springframework.boot"  level= "DEBUG"/> 

Then start the project and watch the log output, as shown in the Automatically enabled connection pooling:

I added a filter to the data source configuration above and set the delay time to 0 (deliberately set very low, modify in the actual project):

Spring.datasource.jdbc-interceptors=connectionstate; Slowqueryreport (threshold=0)

At this point, when we visit the Http://localhost:8080/myspringboot/stu/list observation log, we find that the framework automatically warns the output of data queries that are larger than that time, as follows:

2016-01-12 23:27:06.710  WARN 17644---[nio-8080-exec-1] o.a.t.j.p.interceptor.slowqueryreport    : Slow Query Report Sql=select Id,name,score_sum,score_avg, age from   STUDENT; time=3 ms;

(go) Spring Boot JDBC Connection Database