package com.enhance.jdbc;
import java.beans.PropertyVetoException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
//c3p0 下载地址:http://sourceforge.net/projects/c3p0/
//需要 c3p0.jar mchange-commons-java.jar
public class C3p0ConnectionPool {
private static ComboPooledDataSource ds=new ComboPooledDataSource();
private PreparedStatement pstmt;
private ResultSet rs;
static{
Properties prop=new Properties();
try {
prop.load(new FileInputStream("src/mysql.ini"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
ds.setDriverClass(prop.getProperty("driver"));
} catch (PropertyVetoException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ds.setJdbcUrl(prop.getProperty("url"));
ds.setUser(prop.getProperty("user"));
ds.setPassword(prop.getProperty("pass"));
ds.setMaxPoolSize(Integer.parseInt(prop.getProperty("maxActive")));
ds.setMinPoolSize(Integer.parseInt(prop.getProperty("minIdle")));
ds.setInitialPoolSize(Integer.parseInt(prop.getProperty("initialSize")));
ds.setMaxStatements(Integer.parseInt(prop.getProperty("maxStatements")));
}
public Connection getConenction() throws SQLException{
return ds.getConnection();
}
public void executeUpdate() throws SQLException{
Connection con=null;
try {
con=this.getConenction();
pstmt=con.prepareStatement("insert into img_table(img_name,img_data) values (?,?)");
pstmt.setString(1, "连接池进行blob数据的新增");
File file=new File("F:\\androidworkspace\\LauncherActivity\\res\\drawable-nodpi\\wallpaper.jpg");
pstmt.setBinaryStream(2, new FileInputStream(file));
int rows=pstmt.executeUpdate();
System.out.println(rows);
} catch (Exception e) {
e.printStackTrace();
}finally{
if(pstmt!=null)
pstmt.close();
if(con!=null)
con.close();
}
}
public void execute()throws SQLException{
Connection con=null;
try {
con=this.getConenction();
pstmt=con.prepareStatement("select img_id,img_name,img_data from img_table"); // where img_id=?
//pstmt.setInt(1, 3);
rs =pstmt.executeQuery();
while(rs.next()){
File file=new File("C:\\Users\\Bin\\Desktop\\"+rs.getString(2)+".jpg");
Blob blob=rs.getBlob(3);
//InputStream in=blob.getBinaryStream();
OutputStream out=new FileOutputStream(file);
out.write(blob.getBytes(1l, (int) blob.length()));
/*byte[] b=new byte[1024];
while(in.read(b,0,b.length)!=-1){
out.write(b);
out.flush();
}*/
//in.close();
out.close();
}
} catch (Exception e) {
e.printStackTrace();
}finally{
if(pstmt!=null)
pstmt.close();
if(con!=null)
con.close();
}
}
public static void main(String[] args) throws SQLException {
// TODO Auto-generated method stub
DBCPConnectionPool dbcppool=new DBCPConnectionPool();
dbcppool.executeUpdate();
dbcppool.execute();
}
}
//-------------------------------
package com.enhance.jdbc;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSource;
//DBCP 下载地址:http://commons.apach.org/
//需要 commond-dbcp.jar commons-pool.jar
public class DBCPConnectionPool {
private static BasicDataSource ds=new BasicDataSource();
private PreparedStatement pstmt;
private ResultSet rs;
static{
Properties prop=new Properties();
try {
prop.load(new FileInputStream("src/mysql.ini"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ds.setDriverClassName(prop.getProperty("driver"));
ds.setUrl(prop.getProperty("url"));
ds.setUsername(prop.getProperty("user"));
ds.setPassword(prop.getProperty("pass"));
ds.setInitialSize(Integer.parseInt(prop.getProperty("initialSize")));
ds.setMaxActive(Integer.parseInt(prop.getProperty("maxActive")));
ds.setMinIdle(Integer.parseInt(prop.getProperty("minIdle")));
}
public Connection getConenction() throws SQLException{
return ds.getConnection();
}
//下面一样
}