jun - 云代码空间
—— 相信 ，梦
<?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/jdbc</property>
<property name="user">root</property>
<property name="password">password</property>
<property name="acquireIncrement">50</property>
<property name="initialPoolSize">100</property>
<property name="minPoolSize">50</property>
<property name="maxPoolSize">1000</property>
</default-config>
<!--
<named-config name="oracle">
<property name="driverClass">oracle.jdbc.OracleDriver</property>
<property name="jdbcUrl">jdbc:oracle:thin:@localhost:1521/orcl</property>
<property name="user">scott</property>
<property name="password">tiger</property>
<property name="acquireIncrement">50</property>
<property name="initialPoolSize">100</property>
<property name="minPoolSize">50</property>
<property name="maxPoolSize">1000</property>
</named-config>
-->
</c3p0-config>
package cn.itcast.jdbc.dbutils.utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBCUtils {
//创建一个基于配置文件的C3P0数据库连接池
private static ComboPooledDataSource ds = new ComboPooledDataSource("");
//连接oracle
//private static ComboPooledDataSource ds = new ComboPooledDataSource("oracle");
public static ComboPooledDataSource getDataSource() {
return ds;
}
//维护一个容器，保存当前执行线程上的connection
public static ThreadLocal<Connection> container = new ThreadLocal<Connection>();
/*
* 作用:
1. 得到connection
* 2. connection.setAutoCommit(false);
* 3. 将该connection放到ThreadLocal上去
*/
public static void startTransaction(){
//从当前执行的线程上去连接
Connection conn = null;
try{
conn = container.get();
if(conn == null){
conn = getConnection();
}
//开启事务
conn.setAutoCommit(false);
//放到当前执行的线程上
container.set(conn);
}catch(Exception ex){
throw new RuntimeException(ex);
}
}
//利用这个方法提交事务
public static void commitTransaction(){
//从当前执行的线程上取出connection
Connection conn = container.get();
if(conn == null){
return;
}
try {
//事务提交
conn.commit();
//从当前执行的线程上去掉该连接
container.remove();
} catch (Exception e) {
throw new RuntimeException(e);
}finally{
release(conn, null, null);
}
}
public static void rollbackTransaction(){
//从当前执行的线程上取出connection
Connection conn = container.get();
if(conn == null){
return;
}
try {
//事务回滚
conn.rollback();
//从当前执行的线程上去掉该连接
container.remove();
} catch (Exception e) {
throw new RuntimeException(e);
}finally{
release(conn, null, null);
}
}
public static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
//释放资源
public static void release(Connection conn,Statement st,ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
}
}
package cn.itcast.jdbc.dbutils.demo;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.InputStream;
import java.io.Reader;
import java.sql.SQLException;
import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialClob;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
import cn.itcast.jdbc.dbutils.utils.JDBCUtils;
/*
* 使用DBUtils的update方法完成insert,update,delete,batch,clob,blob
* 步骤:
* 1. 创建QueryRunner
* private QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
* 2. SQL语句
* 3. 构造参数值
* 4. 调用QueryRunner.update(batch)方法
*/
public class Demo1 {
//定义DBUtils的QueryRunner
private QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
@Test
public void testInsert() throws SQLException{
String sql = "insert into account values(?,?,?)";
Object[] params = new Object[]{3,"Mary",3000};
//调用QueryRunner.update插入数据
runner.update(sql, params);
}
@Test
public void testUpdate() throws SQLException{
String sql = "update account set name=?,money=? where id=?";
Object[] params = new Object[]{"Mary123",1000,3};
runner.update(sql, params);
}
@Test
public void testDelete() throws SQLException{
String sql = "delete from account where id=?";
runner.update(sql, 3);
}
@Test
public void testBatch() throws SQLException{
String sql = "insert into account values(?,?,?)";
//插入10条记录到account表中，需要构造一个二维数组
Object[][] params = new Object[10][];
for(int i=0;i<10;i++){
params[i] = new Object[]{10+i,"Name"+i,1000};
}
//调用DBUtils的batch执行
runner.batch(sql, params);
}
@Test
public void testClob() throws Exception{
//使用DBUtils的update方法将一个大文本插入的数据库中
String sql = "insert into testclob(resume) values(?)";
//使用javax.sql.rowset.serial.SerialClob构造一个大文本对象，
//该文本对象代表了我们要插入的大文本
File file = new File("d:\\temp\\resume.txt");
Reader reader = new FileReader(file);
//构造一个缓存区
char[] buffer = new char[(int) file.length()];
reader.read(buffer);
reader.close();
//构造javax.sql.rowset.serial.SerialClob的对象
SerialClob clob = new SerialClob(buffer);
//调用QueryRunner.update将大文本插入到表中
runner.update(sql, clob);
}
@Test
public void testBlob() throws Exception{
//使用DBUtils.update方法将一张照片插入到表中
String sql = "insert into testblob(image) values(?)";
//使用javax.sql.rowset.serial.SerialBlob类的一个实例代表要插入的照片
//构造字节数组
File file = new File("d:\\temp\\wo.jpg");
InputStream in = new FileInputStream(file);
//字节缓存区，保存照片
byte[] buffer = new byte[(int) file.length()];
in.read(buffer);
in.close();
//构造javax.sql.rowset.serial.SerialBlob类的一个实例
SerialBlob blob = new SerialBlob(buffer);
//调用QueryRunner.update将大二进制插入到表中
runner.update(sql, blob);
}
}