思想代码展示
思想在学习面对对象编程的时候，我们就接触过封装，简单理解就是将程序封装成一个角色，那么在这里对jdbc封装也是一样的
如果建立一个能与数据库进行交流的角色，它的特征应该包括
加载驱动，建立连接，连接，生成对话对象，对话对象，收尾，做与数据库写入数据的对话，做从数据库读出数据的对话，结果集，编译预处理级别的对话，预编译的对话对象，批处理，开启手动事务，提交事务，回滚事务
使用名词提炼法，动词提炼法完成对特征的定性，以此分辨属性和行为
名词类的特征：连接，普通对话对象，结果集，预编译对话对象
动词类的特征：加载，建立，生成，收尾，写对话，读对话，预编译对话，批处理对话，开启，提交，回滚
然后，本篇内容用到了连接池思想，连接池内容可使用公共连接池组件(dhcp或c3p0)代替，本人对于连接池的掌握只达到了会用的成都，理解还不够深入，后续理解深入会进行补充
代码展示连接池内容
package jdbc.pool;
import java.sql.*;
import java.util.linkedList;
public class ConnectionPool {
private static ConnectionPool pool=null;
private linkedList conns=new linkedList();
private ConnectionPool(){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.err.println("连接建立失败！！");
e.printStackTrace();
}
}
public static ConnectionPool getInstance(){
if (pool==null){
pool=new ConnectionPool();
}
return pool;
}
public synchronized Connection getConnection(){
Connection conn=null;
if (conns.size()>0){
conn=conns.removeLast();
System.out.println("从池中获取的连接。。。");
}else{
String url="jdbc:mysql://localhost:33060/mytest1? useUnicode=true&characterEncoding=UTF-8";
String userName="root";
String userPwd="Root";
try {
conn=DriverManager.getConnection(url,userName,userPwd);
System.out.println("新建了一个连接。。。");
} catch (SQLException e) {
System.err.println("连接建立失败！！");
e.printStackTrace();
}
}
return conn;
}
public void revokeConnection(ResultSet rs,Statement stmt,Connection conn){
try {
if (rs!=null){
rs.close();
}
if (stmt!=null){
stmt.close();
}
if (conn!=null){
if (conns.size()>=10){
conn.close();
}else {
synchronized (conns){
conns.addFirst(conn);
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void beginTx(Connection conn){
try {
conn.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void commitTx(Connection conn){
try {
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void rollbackTx(Connection conn){
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
jdbc封装
import java.sql.*;
import java.util.Map;
import java.util.*;
import jdbc.pool.*;
public class jdbcSqlFactory {
//属性-连接
private Connection conn;
//属性-普通对话对象
private Statement stmt;
//属性-预编译对话对象
private PreparedStatement pstmt;
//属性-结果集
private ResultSet rs;
public int executeUpdate(String sql){
int count=0;
conn= ConnectionPool.getInstance().getConnection();
try {
stmt=conn.createStatement();
ConnectionPool.getInstance().beginTx(conn);
count=stmt.executeUpdate(sql);
ConnectionPool.getInstance().commitTx(conn);
} catch (SQLException e) {
ConnectionPool.getInstance().rollbackTx(conn);
System.err.println("sql语句执行失败！！");
e.printStackTrace();
}finally {
ConnectionPool.getInstance().revokeConnection(rs,stmt,conn);
}
return count;
}
public List> executeQuery(String querySQL){
List> table = null;
conn=ConnectionPool.getInstance().getConnection();
try {
stmt=conn.createStatement();
rs=stmt.executeQuery(querySQL);
table =new ArrayList>();
while(rs.next()){
Map row =new HashMap<>();
ResultSetmetaData rsmd=rs.getmetaData();
for (int idx=1;idx<=rsmd.getColumnCount();idx++){
String columnName=rsmd.getColumnLabel(idx);
Object value=rs.getObject(idx);
row.put(columnName,value);
}
table.add(row);
}
} catch (SQLException e) {
System.err.println("sql语句执行失败！！");
e.printStackTrace();
}finally {
ConnectionPool.getInstance().revokeConnection(rs,stmt,conn);
}
return table;
}
public int prepareUpdate(String perpareSql,Object... values){
int count=0;
conn=ConnectionPool.getInstance().getConnection();
try {
pstmt=conn.prepareStatement(perpareSql);
for (int idx=0;idx> prepareQuery(String perpareQuerySql,Object... values){
List> table=null;
conn=ConnectionPool.getInstance().getConnection();
try {
pstmt=conn.prepareStatement(perpareQuerySql);
for (int idx=0;idx>();
while (rs.next()){
Map row =new HashMap<>();
ResultSetmetaData rsmd=rs.getmetaData();
for (int idx=1;idx<=rsmd.getColumnCount();idx++){
String columnName=rsmd.getColumnLabel(idx);
Object value=rs.getObject(idx);
row.put(columnName,value);
}
table.add(row);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
ConnectionPool.getInstance().revokeConnection(rs,pstmt,conn);
}
return table;
}
public void batchUpdate(String... sqls){
this.batchUpdate(Arrays.asList(sqls));
}
public int[] batchUpdate(List sqls){
int[] counts=null;
conn=ConnectionPool.getInstance().getConnection();
try {
stmt=conn.createStatement();
for (String sql:sqls){
stmt.addBatch(sql);
}
ConnectionPool.getInstance().beginTx(conn);
counts=stmt.executeBatch();
ConnectionPool.getInstance().commitTx(conn);
} catch (SQLException e) {
ConnectionPool.getInstance().rollbackTx(conn);
e.printStackTrace();
}finally {
ConnectionPool.getInstance().revokeConnection(rs,stmt,conn);
}
return counts;
}
public int[] prepareBatchUpdate(String prepareBatchSql,List params){
int[] counts=null;
conn=ConnectionPool.getInstance().getConnection();
try {
pstmt=conn.prepareStatement(prepareBatchSql);
for (Object[] values:params){
for (int idx=0;idx
本文代码还有改进空间，希望大家多提建议
