1、pom引入:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
2、bean.xml配置:
(1)配置QueryRunner的bean
(2)配置出c3p0数据源
(3)配置component-scan去主动加载注解方式的类进入bean容器
3、配置代码示例(增删盖查)
package com.fang.dao.Impl;
import com.fang.dao.AccountDao;
import com.fang.pojo.Account;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.util.List;
@Repository
public class AccountDaoImpl implements AccountDao {
public void saveAccount(){
System.out.println("保存账户");
}
@Resource(name = "runner")
private QueryRunner runner;
@Override
public Account findById(int id) {
try{
return runner.query("select * from account where id = ?",new BeanHandler<>(Account.class),id);
}catch (Exception e){
throw new RuntimeException(e);
}
}
@Override
public List<Account> findAll() {
try{
return runner.query("select * from account",new BeanListHandler<>(Account.class));
}catch (Exception e){
throw new RuntimeException(e);
}
}
@Override
public List<Account> findByName(String username) {
try{
return runner.query("select * from account where name = ?",new BeanListHandler<>(Account.class),username);
}catch (Exception e){
throw new RuntimeException(e);
}
}
@Override
public int insertOne(Account ac) {
try{
return runner.update("insert into account(name,count)values(?,?)",ac.getName(),ac.getAccount());
}catch (Exception e){
throw new RuntimeException(e);
}
}
@Override
public int updateOne(Account ac) {
try{
return runner.update("update account set name =?, count=? where id = ? ",ac.getName(),ac.getAccount(),ac.getId());
}catch (Exception e){
throw new RuntimeException(e);
}
}
@Override
public Boolean transfer(int sid, int toId, int count) {
return null;
}
}
4、测试:
import com.fang.dao.AccountDao;
import com.fang.pojo.Account;
import com.fang.service.IaccoutService;
import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.List;
public class ClientTest {
@Test
public void testFindAll(){
ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
IaccoutService iaccoutService = context.getBean("accountService",IaccoutService.class);
List<Account> accounts = iaccoutService.findAll();
for (Account account:accounts) {
System.out.println(account);
}
}
}
结果:
5、期间遇到的问题:
(1)问题1
Loading class com.mysql.jdbc.Driver'. This is deprecated. The new driver class is
com.mysql.cj.jdbc.Driver’. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
解决方法:将加载数据库驱动的语句更改 com.mysql.cj.jdbc.Driver
数据pom中配置的是8.0.20版本
配置bean类是不能使用(com.mysql.jdbc.Driver)必须如下:
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test"></property>
<property name="user" value="root"></property>
<property name="password" value="123456"></property>
2、数据时区导致dataSource容器实例化失败:
java.sql.SQLException: The server time zone value ‘�й���ʱ��‘ is unrecognized or represents more tha
解决
报错原因:时区问题
查看时区
show variables like ‘%time_zone%’;
修改时区为中国的中8时区
set global time_zone=’+8:00’;