JDBC封装

自己封装了下JDBC，代码如下：

1、通过properties文件读取连接信息：

package com.hoteam.kaide.db.jdbc;


import java.sql.*;


import com.hoteam.kaide.tool.properties.OperateProperties;


public class JdbcConnection {


/**
* @see 创建数据库连接
* @return connection
* @throws ClassNotFoundException
* @author ywp
*/
public Connection getConnection() throws ClassNotFoundException{

//从配置文件中获取jdbc连接参数
OperateProperties opProperties = new OperateProperties();

String connectName = opProperties.redValueByKey("connectName");
String connectPassword = opProperties.redValueByKey("connectPassword");
String jdbcDriver = opProperties.redValueByKey("jdbcDriver");
String connectURL = opProperties.redValueByKey("connectURL");


Connection connection = null;


try {
Class.forName(jdbcDriver);
connection = DriverManager.getConnection(connectURL,connectName,connectPassword);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println(e.getMessage());
}


return connection;
}


}

2、 插入、更新、删除执行：

public class SqlExecute {

JdbcConnection connection = new JdbcConnection();
Connection con = null;

/**
* @see execute SQL:insert、update、delete
* @param insertSql
* @author ywp
*/
public void executeSql(String insertSql){

try {
con = connection.getConnection();
} catch (ClassNotFoundException e2) {
// TODO Auto-generated catch block
e2.printStackTrace();
}
Statement statement = null;

try {
//开启事物
con.setAutoCommit(false);
statement = con.createStatement();
statement.executeUpdate(insertSql);
//提交事务
con.commit();

} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("jdbc事物提交失败,事物将进行自动回滚,异常信息："+e.getMessage());

//提交失败,事物回滚
try {
con.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
System.out.println("jdbc事物再一次提交失败,事物将进行自动回滚,异常信息："+e1.getMessage());
}
}finally{
//关闭statement
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//关闭connection
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

3、批处理

/**
* @see SQL的批处理
* @throws SQLException
* @author ywp
*/
public void runExecuteSql(List<String> listSql) throws SQLException{

Statement state = null;
try {
con = connection.getConnection();

con.setAutoCommit(false);
state = con.createStatement();
for(String strSql:listSql){
state.addBatch(strSql);
}
state.executeBatch();
//提交事务
con.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
//事物回滚
try{
con.rollback();
}catch(Exception e2){

}
}finally{
//关闭statement
try{
state.close();
}catch(Exception e3){}
//关闭Connection
try{
con.close();
}catch(Exception e3){

}
}

}

4、查询

/**
* @see execute SQL:select
* @param selectSql
* @author ywp
* @return
*/
public void executeSelect(String selectSql){

Statement statement = null;
ResultSet rs = null;
try {
con = connection.getConnection();
} catch (ClassNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
statement = con.createStatement();
//执行SQL
rs = statement.executeQuery(selectSql);
//获取ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
//结果集
Object value = new Object();

int countColumn = rsmd.getColumnCount();
// System.out.println("countColumn:"+countColumn);

while(true){
//移动到下一条数据
boolean b = rs.next();
//检查下一条数据是否存在
if(false==b){
//如果下一条数据不存在,就不在遍历;
break;
}
for(int i=1;i<=countColumn;i++){

value = rs.getObject(i);

System.out.println(value);

// / System.out.println(rs.getString(i));

}
}


} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}