MyBatis和Spring的整合：传统Dao方式的整合

Question

遇到了一个问题，报错如下
Mon Aug 10 10:58:32 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Exception in thread "main" org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:

Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (Unknown system variable 'lower_case_table_names')

The error may exist in com/wyy/po/CustomerMapper.xml

The error may involve com.wyy.po.CustomerMapper.findcustomerbyid

The error occurred while executing a query

Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (Unknown system variable 'lower_case_table_names')

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy2.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166)
at com.wyy.dao.impl.CustomerDaoimpl.findCustomerByid(CustomerDaoimpl.java:13)
at com.wyy.test.TestDao.main(TestDao.java:17)

Caused by: org.apache.ibatis.exceptions.PersistenceException:

Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (Unknown system variable 'lower_case_table_names')

The error may exist in com/wyy/po/CustomerMapper.xml

The error may involve com.wyy.po.CustomerMapper.findcustomerbyid

The error occurred while executing a query

Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (Unknown system variable 'lower_case_table_names')

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
... 4 more

Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (Unknown system variable 'lower_case_table_names')
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82)
at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)
at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:336)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:84)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
... 11 more
Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (Unknown system variable 'lower_case_table_names')
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2294)
at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2039)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1533)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
... 21 more
Caused by: java.sql.SQLException: Unknown system variable 'lower_case_table_names'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1369)
at com.mysql.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java:3833)
at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3283)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2297)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:39)
at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:256)
at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2304)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2290)
... 25 more

代码如下：

CustomerDao.java：

package com.wyy.dao;

import com.wyy.po.Customer;

public interface CustomerDao {
//通过id查询用户
public Customer findCustomerByid(Integer id);
}

CustomerDaoimpl.java

package com.wyy.dao.impl;

import org.mybatis.spring.support.SqlSessionDaoSupport;

import com.wyy.dao.CustomerDao;
import com.wyy.po.Customer;

public class CustomerDaoimpl extends SqlSessionDaoSupport implements CustomerDao {

@Override
public Customer findCustomerByid(Integer id) {
	
	return this.getSqlSession().selectOne("com.wyy.po."+"CustomerMapper.findcustomerbyid",id);
}

}

Customer.java：
package com.wyy.po;

import java.io.Serializable;

//实体类
public class Customer implements Serializable {

private Integer id;
private String username;
private String jobs;
private String phone;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getJobs() {
return jobs;
}
public void setJobs(String jobs) {
this.jobs = jobs;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Customer [id=" + id + ", username=" + username + ", jobs=" + jobs + ", phone=" + phone + "]";
}

}

CustomerMapper.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">

<!-- 下面几行都是自主编写的映射信息 -->
<!-- namespace表示命名空间 -->
<mapper namespace="com.wyy.po.CustomerMapper">
<!-- 根据id查询客户信息, mybatis-config类做了包扫描，所以resultType咱们写-->
<select id="findcustomerbyid" parameterType="Integer" resultType="customer">
select * from t_customer where id=#{id}
</select>
</mapper>

TestDao.java：
package com.wyy.test;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.wyy.dao.CustomerDao;
import com.wyy.po.Customer;

public class TestDao {
public static void main(String[] args) {
ApplicationContext app=new ClassPathXmlApplicationContext("applicationContext.xml");
//applicationContext.xml里的实例化Dao的bean的id
//CustomerDao cus=(CustomerDao)app.getBean("customerDao");
//获取bean的另一种方式
CustomerDao cus=app.getBean(CustomerDao.class);
Customer customer=cus.findCustomerByid(2);
System.out.println(customer);
}

applicationContext.xml：
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.3.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.3.xsd">

<!-- 导入db.properties文件 -->
<context:property-placeholder location="classpath:db.properties" />
<!-- 1.配置数据源 -->
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
	<!-- 数据库驱动 -->
	<property name="driverClassName" value="${jdbc.driver}"></property>
	<!-- 连接数据库的url -->
	<property name="url" value="${jdbc.url}"></property>
	<!-- 连接数据库的用户名 -->
	<property name="username" value="${jdbc.username}"></property>
	<!-- 连接数据库的密码 -->
	<property name="password" value="${jdbc.password}"></property>
	<!-- 配置最大连接数 -->
	<property name="maxTotal" value="${jdbc.maxTotal}"></property>
	<!-- 最大空闲连接 -->
	<property name="maxIdle" value="${jdbc.maxIdle}"></property>
	<!-- 初始化来连接数 -->
	<property name="initialSize" value="${jdbc.initialSize}"></property>
</bean>

<!-- 配置事物管理器，依赖于数据源 -->
<bean id="transactionManager"
	class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	<property name="dataSource" ref="dataSource"></property>
</bean>

<!-- 开启事物注解 -->
<tx:annotation-driven transaction-manager="transactionManager" />

<!-- MyBatis和Spring的整合需要配置 MyBatis工厂 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
	<!-- 注入数据源 -->
	<property name="dataSource" ref="dataSource"></property>
	<!-- 指定核心配置文件位置 -->
	<property name="configLocation" value="classpath:mybatis-config.xml"></property>
</bean>

<!-- 实例化Dao -->
<bean id="customerDao" class="com.wyy.dao.impl.CustomerDaoimpl">
<!-- 注入Sqlsession对象实例 -->
<!-- 这里的ref是指  MyBatis工厂里的id-->
<property name="sqlSessionFactory" ref="sqlSessionFactory"></property>
</bean>

</beans>

db.properties：
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/account
jdbc.username=root
jdbc.password=121
jdbc.maxTotal=30
jdbc.maxIdle=10
jdbc.initialSize=5

log4j.properties：

Global logging configuration

log4j.rootLogger=ERROR, stdout

MyBatis logging configuration...

log4j.logger.com.wyy=DEBUG

Console output...

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

mybatis-config.xml：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 别名,这里不设别名，那么就是class名 -->
<typeAliases>
<package name="com.wyy.po" />
</typeAliases>

<!--2.配置Mapper的位置 -->
<mappers>
	<mapper resource="com/wyy/po/CustomerMapper.xml" />	
</mappers>

</configuration>

Answer 1

额 用5.7以上版本需要关闭ssl啊

Answer 2

参考一下
https://www.52interview.com/book/15/167