以下版本的sqlHelper可以支持普通的DDL，DML和查询语句，对于连接池，事务等的支持还有待改进
1)将数据库连接相关信息存储为属性文件，如database.properties,建立DataBase相关的辅助类进行读取
package com.bobo.db; import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties; public class DataBase {
private static String url;
private static String driver;
private static String username;
private static String password;
// 数据源名称
private static String dataSource;
/*
* 数据库访问类型，是连接池还是普通链接
*/
private static String type; private static String fileName = "database.properties";
private static ThreadLocal<Connection> connection = new ThreadLocal<Connection>();
// 下面这种语法在java中叫做初始化块，初始化块无法接受参数，通常用于对类中field字段的统一初始化操作
// 类初始化块在类初始化的时候调用
// 对象初始化块在对象初始化时，先于构造函数调用
static {
config();
} public static void main(String[] args) {
Connection con = getConnection();
releaseConnection(con);
} private static void config() {
// 位于src目录下的文件，需要使用类加载器来读取
String path = DataBase.class.getClassLoader().getResource(fileName)
.getPath();
// 从配置文件中读取数据库相关参数
Properties pro = new Properties();
try {
FileInputStream fis = new FileInputStream(path);
pro.load(fis); if (pro.containsKey("driver")) {
driver = pro.getProperty("driver");
}
if (pro.containsKey("url")) {
url = pro.getProperty("url");
}
if (pro.containsKey("username")) {
username = pro.getProperty("username");
}
if (pro.containsKey("password")) {
password = pro.getProperty("password");
}
if (pro.containsKey("type")) {
type = pro.getProperty(type);
}
System.out.println("DataBase:" + "driver:" + driver + "," + "url:"
+ url + "," + "username:" + username + "," + "password:"
+ password);
fis.close(); } catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} } public static Connection getConnection() {
Connection con = connection.get();
try {
if (con != null && !con.isClosed()) {
return con;
}
if ("pool".equalsIgnoreCase("type")) {
// 数据库链接池中获得连接，这里暂且不管
} else {
// 直接使用JDBC驱动连接
Class providerClass = Class.forName(driver);
con = DriverManager.getConnection(url, username, password);
con.setAutoCommit(false);
connection.set(con);
return con; }
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
} public static void releaseConnection(Connection con) { try {
if (con != null && !con.isClosed()) {
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
con = null;
}
} public static void commit() {
Connection con = (Connection) connection.get();
try {
con.commit();
} catch (SQLException e) {
e.printStackTrace();
}
} public static void rollback() {
Connection con = (Connection) connection.get();
try {
con.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
} }
2）SqlHelper类
注意：
a：这里有些地方使用的是Object进行封装，考虑到从数据库中读出的都可以是字符串，因此对于记录中的每一项，根据需求也可以使用String封装
b：因为将查询结果封装为了Object或者String，在SqlHelper类中其实也可以关闭Connection,不过这样的一个弊端是，每一次单独的查询都需要打开和关闭connection，对于一次查询需要借助多个表的时候，这加大了资源消耗，因此本类实现中没有这么做，而是在调用sqlHelper的时候，再进行连接的打开和关闭
package com.bobo.util; import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.SortedMap;
import java.sql.*; import com.bobo.db.DataBase; public class SqlHelper {
private Connection con; public SqlHelper() { } public void setConnection(Connection con) {
this.con = con; } private void prepareCommand(PreparedStatement pstmt, String[] parms) {
try {
if (parms != null) {
for (int i = ; i < parms.length; i++) {
try {
pstmt.setDate(i + , java.sql.Date.valueOf(parms[i]));
} catch (Exception e) {
try {
pstmt.setDouble(i + , Double.parseDouble(parms[i]));
} catch (Exception e1) {
try {
pstmt.setInt(i + , Integer.parseInt(parms[i]));
} catch (Exception e2) {
try {
pstmt.setString(i + , parms[i]);
} catch (Exception e3) {
System.out
.print("SQLHelper-PrepareCommand Err1:"
+ e3);
}
}
}
}
}
}
} catch (Exception e1) {
System.out.print("SQLHelper-PrepareCommand Err2:" + e1);
}
} /**
* 执行插入语句，返回对应行的自增key值
*
* @param sqlText
* @param params
* @return
* @throws Exception
*/
public int ExecuteInsertReturnKey(String sqlText, String[] params)
throws Exception {
PreparedStatement ps = null;
java.sql.Connection con = null;
int key = -;
ResultSet rs = null;
try { ps = con.prepareStatement(sqlText, Statement.RETURN_GENERATED_KEYS);
prepareCommand(ps, params);
ps.executeUpdate();
rs = ps.getGeneratedKeys();
if (rs.next()) {
key = rs.getInt(); }
} catch (Exception e) {
throw new Exception("ExecuteInsertReturnKey出错:" + e.getMessage());
} finally {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
} }
return key;
} /**
* 执行非查询sql语句(insert,update,delete)
*
* @param sqlText
* sql命令
* @param params
* 参数值
* @return int 返回操作影响的记录条数
* @throws Exception
*/
public int ExecuteNonQuery(String sqlText, String[] params)
throws Exception {
PreparedStatement ps = null;
java.sql.Connection con = null;
try { ps = con.prepareStatement(sqlText);
prepareCommand(ps, params);
return ps.executeUpdate();
} catch (Exception e) {
throw new Exception("executeNonQuery出错:" + e.getMessage());
} finally { if (ps != null) {
ps.close();
} }
} /**
*
* @param cmdtext
* 查询语句
* @param parms查询参数
* @return String[] 返回查询结果对应的列信息
*/
public String[] executeColumnInfo(String cmdtext, String[] parms) {
PreparedStatement pstmt = null;
String[] result = null;
try { pstmt = con.prepareStatement(cmdtext); prepareCommand(pstmt, parms);
ResultSet rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int column = rsmd.getColumnCount();
result = new String[column];
for (int i = ; i <= column; i++) {
result[i - ] = rsmd.getColumnName(i);
} } catch (Exception e) { } finally { if (pstmt != null)
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} }
return result;
} /**
* 执行查询语句,返回记录内容
*
* @param cmdtext
* sql指令
* @param parms
* 参数
* @return ArrayList 返回一个list，里面是String[列数]对象
* @throws Exception
*/
public ArrayList<String[]> ExecuteReader(String cmdtext, String[] parms)
throws Exception {
PreparedStatement pstmt = null; try { pstmt = con.prepareStatement(cmdtext); prepareCommand(pstmt, parms);
ResultSet rs = pstmt.executeQuery(); ArrayList<String[]> al = new ArrayList<String[]>();
ResultSetMetaData rsmd = rs.getMetaData();
int column = rsmd.getColumnCount();
while (rs.next()) {
String[] ob = new String[column];
for (int i = ; i <= column; i++) {
ob[i - ] = rs.getString(i);
}
al.add(ob);
} rs.close();
return al; } catch (Exception e) {
throw new Exception("executeSqlResultSet出错:" + e.getMessage());
} finally {
try {
if (pstmt != null)
pstmt.close(); } catch (Exception e) {
throw new Exception("executeSqlResultSet出错:" + e.getMessage());
}
}
} /**
*
* @param cmdtext
* 查询的sql语句
* @param parms
* 查询参数
* @return 仅仅返回符合条件的第一条记录
* @throws Exception
*/
public String[] ExecuteFirstRecorder(String cmdtext, String[] parms)
throws Exception {
PreparedStatement pstmt = null; try { pstmt = con.prepareStatement(cmdtext); prepareCommand(pstmt, parms);
ResultSet rs = pstmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData();
int column = rsmd.getColumnCount();
String[] ob = null;
if (rs.next()) {
ob = new String[column];
for (int i = ; i <= column; i++) {
ob[i - ] = rs.getString(i);
} } rs.close();
return ob; } catch (Exception e) {
throw new Exception("executeSqlResultSet出错:" + e.getMessage());
} finally {
try {
if (pstmt != null)
pstmt.close(); } catch (Exception e) {
throw new Exception("executeSqlResultSet出错:" + e.getMessage());
}
}
} /***
*
* @param cmdtext
* 查询的sql语句
* @param parms
* 查询参数
* @return 返回ArrayList<HashMap<String, String>>，map的结构是列名：列值
* @throws Exception
*/
public ArrayList<HashMap<String, String>> ExecuteMapReader(String cmdtext,
String[] parms) throws Exception {
PreparedStatement pstmt = null; try { pstmt = con.prepareStatement(cmdtext); prepareCommand(pstmt, parms);
ResultSet rs = pstmt.executeQuery();
ArrayList<HashMap<String, String>> al = new ArrayList<HashMap<String, String>>();
ResultSetMetaData rsmd = rs.getMetaData();
int column = rsmd.getColumnCount();
System.out.println("SqlHelper:" + rsmd.getColumnName(column));
while (rs.next()) {
HashMap<String, String> map = new HashMap<String, String>();
for (int k = ; k <= column; k++) {
map.put(rsmd.getColumnName(k), rs.getString(k));
}
al.add(map);
}
rs.close();
return al; } catch (Exception e) {
throw new Exception("executeSqlResultSet出错:" + e.getMessage());
} finally {
try {
if (pstmt != null)
pstmt.close(); } catch (Exception e) {
throw new Exception("executeSqlResultSet出错:" + e.getMessage());
}
}
} /**
* 执行查询语句，返回符合条件的记录数目
*
* @param cmdtext
* sql指令
* @param parms
* 参数
* @return int 返回符合条件的记录数目，如果没有返回-1
* @throws Exception
*/
public int ExecuteRowCountQuery(String cmdtext, String[] parms)
throws Exception {
PreparedStatement pstmt = null; int result = -;
try { pstmt = con.prepareStatement(cmdtext); prepareCommand(pstmt, parms);
ResultSet rs = pstmt.executeQuery(); rs.next();
result = rs.getInt();
rs.close(); } catch (Exception e) {
throw new Exception("executeSqlResultSet出错:" + e.getMessage());
} finally {
try {
if (pstmt != null)
pstmt.close(); } catch (Exception e) {
throw new Exception("executeSqlResultSet出错:" + e.getMessage());
}
}
return result;
} /**
* 执行单结果单列查询语句，如果记录存在，返回首条记录的对应列，否则返回空（按照列名查询）
*
* @param cmdtext
* SQL命令
* @param name
* 列名称
* @param parms
* OracleParameter[]
* @return Object 返回列对象
* @throws Exception
*/
public Object ExecuteScalar(String cmdtext, String name, String[] parms)
throws Exception {
PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = con.prepareStatement(cmdtext);
prepareCommand(pstmt, parms); rs = pstmt.executeQuery();
if (rs.next()) {
return rs.getObject(name);
} else {
return null;
}
} catch (Exception e) {
throw new Exception("executeSqlObject出错:" + e.getMessage());
} finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close(); } catch (Exception e) {
throw new Exception("executeSqlObject出错:" + e.getMessage());
}
}
} /**
* 执行单结果单列查询语句，如果记录存在，返回首条记录的对应列，否则返回空（按照列索引查询）
*
* @param cmdtext
* SQL命令
* @param index
* 第几列
* @param parms
* OracleParameter[]
* @return Object
* @throws Exception
*/
public Object ExecuteScalar(String cmdtext, int index, String[] parms)
throws Exception {
PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = con.prepareStatement(cmdtext);
prepareCommand(pstmt, parms); rs = pstmt.executeQuery();
if (rs.next()) {
return rs.getObject(index);
} else {
return null;
}
} catch (Exception e) {
throw new Exception("executeSqlObject出错:" + e.getMessage());
} finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close(); } catch (Exception e) {
throw new Exception("executeS qlObject出错:" + e.getMessage());
}
}
} }
3，sqlHelper调用
public ArrayList<User> getAllUser() {
Connection conn = DataBase.getConnection();
ArrayList<User> result = new ArrayList<User>();
sqlHelper.setConnection(conn);
String sql = "SELECT * FROM NP_USER ";
try {
ArrayList<String[]> rs = sqlHelper.ExecuteReader(sql, null);
for (int i = ; i < rs.size(); i++) {
User user = new User();
String[] temp = rs.get(i);
user.setId(temp[]);
user.setUsername(temp[]);
user.setPassword(temp[]);
user.setName(temp[]);
user.setApartment(temp[]);
user.setTitle(temp[]);
user.setPhonenumber(temp[]);
user.setType(Integer.parseInt(temp[]));
user.setAuthority(Integer.parseInt(temp[]));
// 根据城市码查询城市
int ProviceCode = Integer.parseInt(temp[]);
Province pro = new Province();
pro.setProvinceCode(temp[]);
String provinceSql = "select PROVINCE_NAME from np_province where PROVINCE_CODE=?";
String[] provinceParams = { temp[] };
Object proObject = sqlHelper.ExecuteScalar(provinceSql,
"PROVINCE_NAME", provinceParams);
pro.setProvinceName(proObject + "");
// System.out.println("UserServie:" + pro);
user.setPro(pro);
user.setProvince(Integer.parseInt(temp[]));
user.setCity(Integer.parseInt(temp[]));
// 同上，通过城市码查找城市
String citySql = "select * from np_city where PROVINCE_CODE=? and city_code=?";
String[] cityParams = { temp[], temp[] };
String[] cityRs = sqlHelper.ExecuteFirstRecorder(citySql,
cityParams);
City ci = new City();
ci.setCityCode(temp[]);
ci.setProvinceCode(temp[]);
ci.setProvinceName(cityRs[]);
ci.setCityName(cityRs[]);
ci.setProvince(pro);
// System.out.println("UserService:"+ci);
user.setCit(ci);
// user.setCreator(Integer.parseInt(temp[11]));
// todo:还需要得到角色和类型这两张表
result.add(user);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DataBase.releaseConnection(conn);
}
return result;
}