使用C3P0连接池和DButils进Dao模式的数据库操作
c3p0-config.xml
<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">root</property>
<!--连接数据库的初始连接数: 5 -->
<property name="initialPoolSize">5</property>
<!--数据库连接池中的最大连接数: 10 -->
<property name="maxPoolSize">10</property>
<!--数据库中的数据连接getConnection所用的时间 -->
<property name="checkoutTimeout">3000</property>
</default-config>
<named-config name="otherc3p0">
</named-config>
</c3p0-config>
Dao.java
package 使用C3P0连接池和DButils进Dao模式的数据库操作;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/*
* 访问数据的DAO接口
* 定义好访问数据表的各种方法
*/
/**
*
* @author Administrator
* @param <T>: DAO 处理的类的实体,类的类型:(学生类,老师类)
*/
public interface DAO<T> {
/**
* 批量处理的方法
* @param connection
* @param sql
* @param args: 填充占位符的Object数组类型的可变参数
*/
void batch(Connection connection, String sql, Object... args);
/**
* 返回class对象中的某一属性值。
* @param connection
* @param sql
* @param args
* @return
*/
<E> E getForValue(Connection connection, String sql, Object... args);
/**
* 返回一组DAO对象
* @param connection
* @param sql
* @param args
* @return
*/
List<T> getForList(Connection connection, String sql, Object... args);
/**
* 返回一个T 的对象
* @param connection:
* @param sql
* @param args
* @return
* @throws SQLException
*/
T get(Connection connection, String sql, Object... args) throws SQLException;
/**
* @param connection :数据库连接
* @param sql:SQL语句
* @param args: 填充占位符的可变参数
* @return
* @throws SQLException
*/
int update(Connection connection, String sql, Object... args) throws SQLException;
}
JDBC_tools
package 使用C3P0连接池和DButils进Dao模式的数据库操作;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBC_tools {
private static DataSource dataSource = null;
static {
dataSource = new ComboPooledDataSource();
}
// 处理数据库事务的
// 提交
public static void commit(Connection connection) {
if (connection != null) {
try {
connection.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 回滚事务
public static void rollback(Connection connection) {
if (connection != null) {
try {
connection.rollback();
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 开始事务
public static void start(Connection connection) {
if (connection != null) {
try {
connection.setAutoCommit(false);
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
// 关闭连接 和PreparedStatement(任何时候都不要使用 Statement!!!)
public static void close(Connection connection, PreparedStatement preparedStatement) {
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void close(Connection connection, Statement statement) {
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 关闭连接,权限, Resulset
public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
JDBCDaoImpl.java
package 使用C3P0连接池和DButils进Dao模式的数据库操作;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import Les_8_反射机制_JDBC_元数据.ReflectionUtils;
/**
* 使用QueryRunner提供具体实现
* @author Administrator
* @param <T>:子类需传入的
*
*/
public class JDBCDaoImpl<T> implements DAO<T> {
private QueryRunner queryrunner = null;
private Class<T> type;
public JDBCDaoImpl() {
queryrunner = new QueryRunner();
type = ReflectionUtils.getSuperGenericType(getClass());
}
@Override
public void batch(Connection connection, String sql, Object... args) {
}
@Override
public <E> E getForValue(Connection connection, String sql, Object... args) {
return null;
}
@Override
public List<T> getForList(Connection connection, String sql, Object... args) {
return null;
}
@Override
public T get(Connection connection, String sql, Object... args) throws SQLException {
return queryrunner.query(connection, sql, new BeanHandler<T>(type), args);
}
@Override
public int update(Connection connection, String sql, Object... args) throws SQLException {
return queryrunner.update(connection, sql, args);
}
}
```java
## User.java
```java
package 使用C3P0连接池和DButils进Dao模式的数据库操作;
public class User {
private int id;
private String username;
private int money;
@Override
public String toString() {
return "User [id=" + id + ", name=" + username + ", money=" + money + "]";
}
public User(int id, String username, int money) {
super();
this.id = id;
this.username = username;
this.money = money;
}
public User() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public int getMoney() {
return money;
}
public void setMoney(int money) {
this.money = money;
}
}
UserDao.java
package 使用C3P0连接池和DButils进Dao模式的数据库操作;
public class UserDao extends JDBCDaoImpl<User> {
}
UserDaoTest.java
package 使用C3P0连接池和DButils进Dao模式的数据库操作;
import java.sql.Connection;
import org.junit.Test;
public class UserDaoTest {
UserDao userdao = new UserDao();
@Test
public void testBatch() {
}
@Test
public void testGetForValue() {
}
@Test
public void testGetForList() {
}
@Test
public void testGet() {
Connection connection = null;
try {
connection = JDBC_tools.getConnection();
String sql = "select *from user where id =?";
User user = userdao.get(connection, sql, 1);
System.out.println(user);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBC_tools.close(connection, null);
}
}
/**
* 使用JDBC_tools连接C3P0数据库连接池实现数据的更新操作
* JDBCUtils:获取数据库连接getConnection()
*/
@Test
public void testUpdate() {
Connection connection = null;
try {
connection = JDBC_tools.getConnection();
String sql = "insert into user value(?,?,?)";
int num = userdao.update(connection, sql, 3, 333, 333);
System.out.println(num);
} catch (Exception e) {
e.printStackTrace();
}
}
}
源码下载地址:https://download.csdn.net/download/wangzijian121/10743546