properties配置文件
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.username=root
jdbc.password=123456
jdbc.url=jdbc:mysql://127.1.1.1:3306/zhw_db
一、Spring默认数据源
Beans4.xml
导入依赖包:mysql-connector-java
<?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:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<!-- 启动spring注解 -->
<context:annotation-config/>
<!-- 自动扫包 -->
<context:component-scan base-package="cn.zhw.d4"/>
<!-- 导入properties文件 -->
<context:property-placeholder location="classpath:cn/zhw/d4/jdbc.properties" />
<!-- 配置数据源 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<!-- 数据库驱动 -->
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<!-- 用户名 -->
<property name="username" value="${jdbc.username}"/>
<!-- 密码 -->
<property name="password" value="${jdbc.password}"/>
<!-- 数据库地址 -->
<property name="url" value="${jdbc.url}"/>
</bean>
<!-- 配置jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
Spring给我们提供了一个万能的神器 JdbcTemplate
JdbcTemplate 很好用
二、DBCP数据源
DBCP.xml
导入依赖包:dbcp、pool
<?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:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<!-- 启动spring注解 -->
<context:annotation-config/>
<!-- 自动扫包 -->
<context:component-scan base-package="cn.zhw.d4"/>
<!-- 导入properties文件 -->
<context:property-placeholder location="classpath:cn/zhw/d4/jdbc.properties" />
<!-- 配置数据源 -->
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
<!-- 数据库驱动 -->
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<!-- 用户名 -->
<property name="username" value="${jdbc.username}"/>
<!-- 密码 -->
<property name="password" value="${jdbc.password}"/>
<!-- 数据库地址 -->
<property name="url" value="${jdbc.url}"/>
</bean>
<!-- 配置jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
三、c3p0数据源
c3p0.xml
导入依赖包:c3p0
<?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:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<!-- 启动spring注解 -->
<context:annotation-config/>
<!-- 自动扫包 -->
<context:component-scan base-package="cn.zhw.d4"/>
<!-- 导入properties文件 -->
<context:property-placeholder location="classpath:cn/zhw/d4/jdbc.properties" />
<!-- 配置数据源 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<!-- 数据库驱动 -->
<property name="driverClass" value="${jdbc.driverClassName}"/>
<!-- 用户名 -->
<property name="user" value="${jdbc.username}"/>
<!-- 密码 -->
<property name="password" value="${jdbc.password}"/>
<!-- 数据库地址 -->
<property name="jdbcUrl" value="${jdbc.url}"/>
</bean>
<!-- 配置jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
四、Druid数据源
DBCP.xml
导入依赖包:druid
<?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:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<!-- 启动spring注解 -->
<context:annotation-config/>
<!-- 自动扫包 -->
<context:component-scan base-package="cn.zhw.d4"/>
<!-- 导入properties文件 -->
<context:property-placeholder location="classpath:cn/zhw/d4/jdbc.properties" />
<!-- 配置数据源 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<!-- 数据库驱动 -->
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<!-- 用户名 -->
<property name="username" value="${jdbc.username}"/>
<!-- 密码 -->
<property name="password" value="${jdbc.password}"/>
<!-- 数据库地址 -->
<property name="url" value="${jdbc.url}"/>
</bean>
<!-- 配置jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
测试 不同的数据源 的使用 (可忽略)
项目结构
pojo --> Account.java
package cn.zhw.d4.dd1.pojo;
import lombok.Data;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
/**
* Day2019.3.1
* cn.zhw.d4.dd1
*
* @author Zhang Huiwen
* @date 2019-04-07 下午 8:58
*/
@Data
@Scope("prototype")
@Component("account")
public class Account {
private Integer id;
private String username;
private Double blance;
}
dao --> AccountDaoImpl.java
package cn.zhw.d4.dd1.dao.impl;
import cn.zhw.d4.dd1.dao.AccountDao;
import cn.zhw.d4.dd1.pojo.Account;
import lombok.Data;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
* Day2019.3.1
* cn.zhw.d4.dd1.dao.impl
* accountDao
* @author Zhang Huiwen
* @date 2019-04-07 下午 9:48
*/
@Data
@Scope("prototype")
@Repository("accountDao")
public class AccountDaoImpl implements AccountDao {
private final JdbcTemplate jdbcTemplate;
@Autowired
public AccountDaoImpl(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
/*
* 添加用户
* @param account pojo
* @return 成功 或 失败
*/
@Override
public boolean addAccount(Account account) {
String sql = "insert account(username, blance) VALUE (?,?);";
int isOK = jdbcTemplate.update(sql, account.getUsername(), account.getBlance());
return isOK >= 1;
}
/*
* 更新
* @param account pojo
* @return 成功 或 失败
*/
@Override
public boolean updateAccount(Account account) {
String sql = "update account set username = ?,blance = ? where id = ?;";
int isOK = jdbcTemplate.update(sql, account.getUsername(), account.getBlance(), account.getId());
return isOK >= 1;
}
/*
* 删除
* @param account pojo
* @return 成功 或 失败
*/
@Override
public boolean deleteAccount(Account account) {
String sql = "delete from account where id = ?;";
int isOK = jdbcTemplate.update(sql, account.getId());
return isOK >= 1;
}
/**
* 查询 单行
* @param id id
* @return 返回对象
*/
@Override
public Account findAccountByID(Integer id) {
String sql = "select * from account where id = ?;";
RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
return jdbcTemplate.queryForObject(sql,rowMapper,id);
}
/**
* 查询所有对象
* @return 返回list
*/
@Override
public List<Account> findAllAccount_1() {
String sql = "select * from account;";
RowMapper<Account> rowMapper = new BeanPropertyRowMapper<Account>(Account.class);
return jdbcTemplate.query(sql,rowMapper);
}
}
test 类 --> D4.java
import cn.zhw.d4.dd1.dao.AccountDao;
import cn.zhw.d4.dd1.dao.BaseDao;
import cn.zhw.d4.dd1.pojo.Account;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import javax.sql.DataSource;
import java.sql.SQLException;
/**
* Day2019.3.1
* PACKAGE_NAME
*
* @author Zhang Huiwen
* @date 2019-04-05 下午 10:06
*/
public class D4 {
/*
第1个 cn/zhw/d4/Beans4.xml 对应的是Spring 默认数据源
第2个 cn/zhw/d4/DBCP.xml 对用的是 DBCP 数据源
第3个 cn/zhw/d4/c3p0.xml 对应的是 c3p0 数据源
*/
// 下面的三个变量可以切换使用
private ApplicationContext ac = new ClassPathXmlApplicationContext("cn/zhw/d4/Beans4.xml"); // 默认数据源
// private ApplicationContext ac = new ClassPathXmlApplicationContext("cn/zhw/d4/DBCP.xml"); // DBCP 数据源
// private ApplicationContext ac = new ClassPathXmlApplicationContext("cn/zhw/d4/c3p0.xml"); // c3p0 数据源
// private ApplicationContext ac = new ClassPathXmlApplicationContext("cn/zhw/d4/druid.xml"); // druid 数据源
@Test
public void test_connection(){
/*
* 测试数据库的连接
*/
DataSource dataSource = (DataSource) ac.getBean("dataSource");
try {
System.out.println(dataSource.getConnection());
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void test_dao(){
/*
* 测试 jdbc模板
*/
BaseDao dao = (BaseDao) ac.getBean("baseDao");
System.out.println(dao.getJdbcTemplate());
}
@Test
public void test_CreateTable(){
/* 测试创建表
*
*/
BaseDao dao = (BaseDao) ac.getBean("baseDao");
dao.createTable();
}
@Test
public void test_dropTable(){
/*
* 测试删除表
*/
BaseDao dao = (BaseDao) ac.getBean("baseDao");
dao.dropTable();
}
@Test
public void test_addAccount(){
/*
测试添加Account
*/
AccountDao dao = ac.getBean("accountDao",AccountDao.class);
Account account = new Account();
account.setBlance(100D);
account.setUsername("zhw");
if (dao.addAccount(account)) {
System.out.println("添加成功! " + account);
}
else {
System.out.println("添加失败! " + account);
}
account.setUsername("sc");
account.setBlance(95D);
if (dao.addAccount(account)) {
System.out.println("添加成功! " + account);
}
else {
System.out.println("添加失败! " + account);
}
account.setUsername("zjf");
account.setBlance(90D);
if (dao.addAccount(account)) {
System.out.println("添加成功! " + account);
}
else {
System.out.println("添加失败! " + account);
}
}
@Test
public void test_updateAccount(){
/*
测试更新Account
*/
AccountDao dao = ac.getBean("accountDao",AccountDao.class);
Account account = new Account();
account.setId(1);
account.setUsername("Super-zhw");
account.setBlance(200.1D);
if (dao.updateAccount(account)) {
System.out.println("修改成功! " + account);
}
else {
System.out.println("修改失败! " + account);
}
}
@Test
public void test_deleteAccount(){
/*
测试删除Account
*/
AccountDao dao = ac.getBean("accountDao",AccountDao.class);
Account account = new Account();
account.setId(3);
if (dao.deleteAccount(account)) {
System.out.println("删除成功! " + account);
}
else {
System.out.println("删除失败! " + account);
}
}
@Test
public void test_queryByID_1(){
/*
测试 查询 findAccountByID
*/
AccountDao dao = ac.getBean("accountDao",AccountDao.class);
System.out.println(dao.findAccountByID(1));
}
@Test
public void test_queryAll_1(){
/*
测试 查询findAllAccount_1
*/
AccountDao dao = ac.getBean("accountDao",AccountDao.class);
for (Account account : dao.findAllAccount_1()) {
System.out.println(account);
}
}
@Test
public void test_berans_extension(){
/*
测试 使用 jdbc.properties 配置文件
*/
ApplicationContext ac = new ClassPathXmlApplicationContext("cn/zhw/d4/use_properties.xml");
System.out.println(ac.getBean("jdbcTemplate"));
}
@Test
public void test_DBCP(){
/*
测试 使用 DBCP 数据源
*/
ApplicationContext ac = new ClassPathXmlApplicationContext("cn/zhw/d4/DBCP.xml");
System.out.println(ac.getBean("jdbcTemplate"));
}
@Test
public void test_c3p0(){
/*
测试 使用 c3p0 数据源
*/
ApplicationContext ac = new ClassPathXmlApplicationContext("cn/zhw/d4/c3p0.xml");
System.out.println(ac.getBean("jdbcTemplate"));
}
}
其实你会发现没什么好测试的,都是基于JdbcTemplate来对数据库操作的,就是配置文件上有点不同而已,其他都是相同的..