我要评论mysql 8.0及对应驱动包匹配注意点
mysql 数据库更新为8.0及以上后，对应的应用程序数据库链接驱动包也要更新为8.0版本。否则会报驱动异常。
需要注意以下几点：
1.最新驱动包maven下载配置如下：
<dependency>
<groupid>mysql</groupid>
<artifactid>mysql-connector-java</artifactid>
<version>8.0.11</version>
</dependency>
<dependency>
<groupid>org.apache.commons</groupid>
<artifactid>commons-pool2</artifactid>
<version>${common.pool.version}</version>
</dependency>
2.jdbc配置表更新如下：
以前版本
jdbc.driver=com.mysql.jdbc.driver jdbc.url=jdbc:mysql://xxx.xx.xx.xxx:3306/db?characterencoding=utf-8 jdbc.username=root jdbc.password=admin
现在版本
jdbc.driver=com.mysql.cj.jdbc.driver jdbc.url=jdbc:mysql://xxx.xx.xx.xxx:3306/db?useunicode=true&characterencoding=utf-8&usessl=false&servertimezone=gmt%2b8 jdbc.username=root jdbc.password=admin ---------全局如下配置 spring.datasource.url = jdbc:mysql://127.0.0.1:3306/cim?useunicode=true&characterencoding=utf8&servertimezone=utc spring.datasource.username = cim spring.datasource.password = f8hypmssxl6xmzek spring.datasource.type=com.zaxxer.hikari.hikaridatasource spring.datasource.driver-class-name = com.mysql.cj.jdbc.driver spring.datasource.hikari.minimum-idle=5 spring.datasource.hikari.maximum-pool-size=10 spring.datasource.hikari.auto-commit=true spring.datasource.hikari.idle-timeout=30000 spring.datasource.hikari.pool-name=master_hikari_pool spring.datasource.hikari.max-lifetime=120000 spring.datasource.hikari.connection-timeout=30000 spring.datasource.hikari.connection-test-query=select 1 spring.datasource.hikari.validation-timeout=600000
使用jdbctemplate
①、依赖
<dependency> <groupid>mysql</groupid> <artifactid>mysql-connector-java</artifactid> </dependency> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter-jdbc</artifactid> </dependency>
maven编译项目时报错，信息如下：
解决方式一：在pom.xml文件增加如下配置信息
<plugin>
<groupid>org.apache.maven.plugins</groupid>
<artifactid>maven-surefire-plugin</artifactid>
<configuration>
<skip>true</skip>
</configuration>
</plugin>
解决方式二：使用命令参数，如下所示：
②、在resource文件夹下application.properties配置文件中增加数据库参数
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/dba spring.datasource.username=root spring.datasource.password=123456 spring.datasource.driver-class-name=com.mysql.cj.jdbc.driver spring.datasource.max-idle=10 spring.datasource.max-wait=1000 spring.datasource.min-idle=5 spring.datasource.initial-size=5
③、测试
@restcontroller
@requestmapping("/jte")
public class jdbctemplatecontroller {
@autowired
private jdbctemplate jdbctemplate;
@requestmapping("/getusers")
public list<map<string, object>> getdbtype(){
string sql = "select * from boot_user";
list<map<string, object>> list = jdbctemplate.queryforlist(sql);
for (map<string, object> map : list) {
set<entry<string, object>> entries = map.entryset( );
if(entries != null) {
iterator<entry<string, object>> iterator = entries.iterator( );
while(iterator.hasnext( )) {
entry<string, object> entry =(entry<string, object>) iterator.next( );
object key = entry.getkey( );
object value = entry.getvalue();
system.out.println(key+":"+value);
}
}
}
return list;
}
}
启动maven项目，输入地址：
http://localhost:8080/jte/getusers
通过浏览器访问后，返回参数如下：
集成mybatis
①、添加依赖
<dependency> <groupid>org.mybatis.spring.boot</groupid> <artifactid>mybatis-spring-boot-starter</artifactid> <version>1.0.0</version> </dependency>
②、在resource文件夹下application.properties配置文件中增加数据库参数
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/dba spring.datasource.username=root spring.datasource.password=123456 spring.datasource.driver-class-name=com.mysql.cj.jdbc.driver spring.datasource.max-idle=10 spring.datasource.max-wait=1000 spring.datasource.min-idle=5 spring.datasource.initial-size=5
③、mapper接口类和xml文件
public interface bootusermapper {
list<bootuser> selectall();
}
<?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.yoodb.study.demo02.mapper.bootusermapper" >
<resultmap id="baseresultmap" type="com.yoodb.study.demo02.bean.bootuser" >
<id column="id" property="id" jdbctype="varchar" />
<result column="user_name" property="name" jdbctype="varchar" />
<result column="detail" property="detail" jdbctype="varchar" />
</resultmap>
<select id="selectall" resultmap="baseresultmap">
select
id, user_name, detail
from boot_user order by detail asc
</select>
</mapper>
public class bootuser {
private string id;
private string name;
private string detail;
public string getid() {
return id;
}
public void setid(string id) {
this.id = id;
}
public string getname() {
return name;
}
public void setname(string name) {
this.name = name;
}
public string getdetail() {
return detail;
}
public void setdetail(string detail) {
this.detail = detail;
}
}
④业务类
@service
public class bootuserservice {
@autowired
private bootusermapper mapper;
public list<bootuser> getusers(){
return mapper.selectall();
}
}
@restcontroller
@requestmapping("/myt")
public class bootusercontroller {
@autowired
private bootuserservice service;
@requestmapping("/getusers")
public list<bootuser> getusers() {
list<bootuser> list = service.getusers();
return list;
}
}
启动maven项目，通过浏览器访问http://localhost:8080/myt/getusers
解决方案：
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
重新启动maven项目，输入地址：
http://localhost:8080/myt/getusers
多数据源配置
一、spring boot集成mybatis和使用jdbctemplate配置公共文件
有两个数据源来配置实现，分别对应的名字是onedatasource和twodatasource
在application.properties文件中配置数据源信息如下：
spring.datasource.one.url=jdbc:mysql://123.57.47.154:3306/springboot1 spring.datasource.one.username=root spring.datasource.one.password=wangyoodb spring.datasource.one.driverclassname=com.mysql.cj.jdbc.driver spring.datasource.two.url=jdbc:mysql://123.57.47.154:3306/springboot2 spring.datasource.two.username=root spring.datasource.two.password=wangyoodb spring.datasource.two.driverclassname=com.mysql.cj.jdbc.driver
二、配置类进行加载datasource
- @configuration：用于定义配置类，可替换xml配置文件，被注解的类内部包含有一个或多个被@bean注解的方法。这些方法将会被annotationconfigapplicationcontext或annotationconfigwebapplicationcontext类进行扫描，并用于构建bean定义，初始化spring容器
- @mapperscan：指定要扫描mapper类包的路径，配置mybatis的接口存放位置
- @bean：用于告诉方法产生一个bean对象，然后这个bean对象交给spring管理
- @configurationproperties：是spring boot提供的类型安全的属性绑定，以第一个bean为例。@configurationproperties(prefix = “spring.datasource.one”)表示使用spring.datasource.one前缀的数据库配置去创建一个datasource
- @primary：可以理解为默认优先选择，不可以同时设置多个，必须增加此注解用于区分主数据库（默认数据库）。
- @qualifier：来达到注入某个特指bean的作用，表示查找spring容器中指定名字的对象
【bean.setmapperlocations(new pathmatchingresourcepatternresolver().getresources(“***”)) mapper的xml形式文件位置必须要配置，不然项目会报no statement错误信息。】
@configuration
@mapperscan(basepackages = "com.yoodb.study.demo03.mapper.one", sqlsessionfactoryref = "onesqlsessionfactory")
public class datasourceoneconfig{
@bean(name = "onedatasource")
@primary
@configurationproperties(prefic = "spring.datasource.one")
public datasource getdatesourceone(){
return datasourcebuilder.create().build();
}
@bean(name="onesqlsessionfactory")
@primary
public sqlsessionfactory onesqlsessionfactory(@qualifier("onedatasource") datasource datasource) throws exception{
sqlsessionfactorybean bean = new sqlsessionfactorybean();
bean.setdatasource(datasource);
bean.setmapperlocations(new pathmatchingresourcepatternresolver().getresources("classpath*:mapper/one/*.xml"));
return bean.getobject();
}
@bean("onesqlsessiontemplate")
@primary
public sqlsessiontemplate onesqlsessiontemplate(@qualifier("onesqlsessionfactory") sqlsessionfactory sessionfactory){
return new sqlsessiontemplate(sessionfactory);
}
}
@configuration
@mapperscan(basepackages = "com.yoodb.study.demo03.mapper.two", sqlsessionfactoryref = "twosqlsessionfactory")
public class datasourcetwoconfig {
@bean(name = "twodatasource")
@configurationproperties(prefix = "spring.datasource.two")
public datasource getdatesourcetwo(datasourceproperties properties) {
return datasourcebuilder.create(properties.getclassloader())
.type(hikaridatasource.class)
.driverclassname(properties.determinedriverclassname())
.url(properties.determineurl())
.username(properties.determineusername())
.password(properties.determinepassword())
.build();
}
@bean(name = "twosqlsessionfactory")
public sqlsessionfactory twosqlsessionfactory(@qualifier("twodatasource") datasource datasource)
throws exception {
sqlsessionfactorybean bean = new sqlsessionfactorybean();
bean.setdatasource(datasource);
bean.setmapperlocations(
new pathmatchingresourcepatternresolver().getresources("classpath*:mapper/two/*.xml"));
return bean.getobject();
}
@bean("twosqlsessiontemplate")
public sqlsessiontemplate twosqlsessiontemplate(
@qualifier("twosqlsessionfactory") sqlsessionfactory sessionfactory) {
return new sqlsessiontemplate(sessionfactory);
}
}
三、实体类
两个数据源公用一个实体类
public class bootuser {
private string id;
private string name;
private string detail;
public string getid() {
return id;
}
public void setid(string id) {
this.id = id;
}
public string getname() {
return name;
}
public void setname(string name) {
this.name = name;
}
public string getdetail() {
return detail;
}
public void setdetail(string detail) {
this.detail = detail;
}
}
四、新增mapper.xml文件
数据源一：在src/main/resources/mapper/one（不存在文件加新建）创建bootuseronemapper.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.yoodb.study.demo03.mapper.one.bootuseronemapper" >
<resultmap id="baseresultmap" type="com.yoodb.study.demo03.bean.bootuser" >
<id column="id" property="id" jdbctype="varchar" />
<result column="user_name" property="name" jdbctype="varchar" />
<result column="detail" property="detail" jdbctype="varchar" />
</resultmap>
<select id="selectall" resultmap="baseresultmap">
select
id, user_name, detail
from boot_user order by detail asc
</select>
</mapper>
数据源二：在src/main/resources/mapper/two（不存在文件加新建）创建bootusertwomapper.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.yoodb.study.demo03.mapper.two.bootusertwomapper" >
<resultmap id="baseresultmap" type="com.yoodb.study.demo03.bean.bootuser" >
<id column="id" property="id" jdbctype="varchar" />
<result column="user_name" property="name" jdbctype="varchar" />
<result column="detail" property="detail" jdbctype="varchar" />
</resultmap>
<select id="selectall" resultmap="baseresultmap">
select
id, user_name, detail
from boot_user order by detail asc
</select>
</mapper>
五、新增接口文件
数据源一：mapper接口类文件，具体代码如下：
public interface bootuseronemapper {
list<bootuser> selectall();
}
数据源二：mapper接口类文件，具体代码如下：
public interface bootusertwomapper {
list<bootuser> selectall();
}
六、创建service/controller
@service
public class bootuserservice {
@autowired
private bootuseronemapper onemapper;
@autowired
private bootusertwomapper twomapper;
public list<bootuser> getusers(){
list<bootuser> listone = onemapper.selectall();
list<bootuser> listtwo = twomapper.selectall();
listone.addall(listtwo);
return listone;
}
}
@restcontroller
@requestmapping("/myt")
public class bootusercontroller {
@autowired
private bootuserservice service;
@requestmapping("/getusers")
public list<bootuser> getusers() {
list<bootuser> list = service.getusers();
return list;
}
}
项目启动过程中控制台报错，错误信息如下：
解决方法：就是在pom.xml文件中增加如下配置信息：
<resources> <resource> <!-- 指定resources插件处理哪个目录下的资源文件 --> <directory>src/main/resources</directory> <includes> <include>**/**</include> </includes> </resource> </resources>
http://localhost:8080/myt/getusers
解决方案
将application.properties文件中spring.datasource…url和spring.datasource…driverclassname分别替换成spring.datasource…jdbc-url和spring.datasource…driver-class-name。
spring boot源码分析：
1）spring.datasource..url替换成spring.datasource..jdbcurl也没有任何问题。
源码org.springframework.boot.jdbc包中databasedriver.class文件反编译后，发现url参数必须以“jdbc”开始，具体源码如下：
public static databasedriver fromjdbcurl(string url) {
if (stringutils.haslength(url)) {
assert.istrue(url.startswith("jdbc"), "url must start with 'jdbc'");
string urlwithoutprefix = url.substring("jdbc".length()).tolowercase(locale.english);
databasedriver[] var2 = values();
int var3 = var2.length;
for(int var4 = 0; var4 < var3; ++var4) {
databasedriver driver = var2[var4];
iterator var6 = driver.geturlprefixes().iterator();
while(var6.hasnext()) {
string urlprefix = (string)var6.next();
string prefix = ":" + urlprefix + ":";
if (driver != unknown && urlwithoutprefix.startswith(prefix)) {
return driver;
}
}
}
}
return unknown;
}
2）spring.datasource.*.driverclassname不替换也没有影响。
源码org.springframework.boot.autoconfigure.jdbc包中configurationproperties.class文件反编译后，发现属性即为driverclassname字段，具体源码如下：
public string determinedriverclassname() {
if (stringutils.hastext(this.driverclassname)) {
assert.state(this.driverclassisloadable(), () -> {
return "cannot load driver class: " + this.driverclassname;
});
return this.driverclassname;
} else {
...
修改application.properties文件配置，参考信息如下：
spring.datasource.one.jdbc-url=jdbc:mysql://123.57.47.154:3306/springboot1 spring.datasource.one.username=root spring.datasource.one.password=wangyoodb spring.datasource.one.driver-class-name=com.mysql.cj.jdbc.driver spring.datasource.two.jdbc-url=jdbc:mysql://123.57.47.154:3306/springboot2 spring.datasource.two.username=root spring.datasource.two.password=wangyoodb spring.datasource.two.driver-class-name=com.mysql.cj.jdbc.driver
使用jdbctemplate
@restcontroller
@requestmapping("/jte")
public class jdbctemplatecontroller {
@autowired
private jdbctemplate onesqlsessiontemplate;
@autowired
private jdbctemplate twosqlsessiontemplate;
@requestmapping("/getoneusers")
public list<map<string, object>> getoneusers(){
string sql = "select * from boot_user";
list<map<string, object>> list = twosqlsessiontemplate.queryforlist(sql);
for (map<string, object> map : list) {
set<entry<string, object>> entries = map.entryset( );
if(entries != null) {
iterator<entry<string, object>> iterator = entries.iterator( );
while(iterator.hasnext( )) {
entry<string, object> entry =(entry<string, object>) iterator.next( );
object key = entry.getkey( );
object value = entry.getvalue();
system.out.println(key+":"+value);
}
}
}
return list;
}
@requestmapping("/gettwousers")
public list<bootuser> gettwousers() {
list<bootuser> list = twosqlsessiontemplate.query("select id,user_name " +
"name,detail from boot_user", new beanpropertyrowmapper<>(bootuser.class));
return list;
}
}
多数据源配置文件、多数据源类文件、实体类文件、controller层创建完成后，目录如图：
项目启动后访问多数据源一请求地址：
http://localhost:8080/jte/getoneusers
通过浏览器访问输出如下信息：
[{"id":1,"user_name":"素文宅博客","password":"e10adc3949ba59abbe56e057f20f883e","role_name":"素文宅博客","detail":"欢迎关注“java精选”微信公众号，专注程序员推送一些java开发知识，包括基础知识、各大流行框架（mybatis、spring、spring boot等）、大数据技术（storm、hadoop、mapreduce、spark等）、数据库（mysql、oracle、nosql等）、算法与数据结构、面试专题、面试技巧经验、职业规划以及优质开源项目等。"}]
项目启动后访问多数据源二请求地址：
http://localhost:8080/jte/gettwousers
通过浏览器访问输出如下信息：
[{"id":"1","name":"素文宅博客","detail":"欢迎关注“java精选”微信公众号，专注程序员推送一些java开发知识，包括基础知识、各大流行框架（mybatis、spring、spring boot等）、大数据技术（storm、hadoop、mapreduce、spark等）、数据库（mysql、oracle、nosql等）、算法与数据结构、面试专题、面试技巧经验、职业规划以及优质开源项目等。"}]
spring容器中jdbctemplate提供了两种注入方式，一种是使用@resource注解，直接通过byname的方式注入进来，另外一种就是@autowired注解加上@qualifier注解，两者联合起来，实际上也是byname。
将jdbctemplate注入成功后，onesqlsessiontemplate和twosqlsessiontemplate此时就代表操作不同的数据源，使用不同的jdbctemplate操作不同的数据源，实现了多数据源配置。
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论