java版本的sqlHelper
以下版本的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 = 0; i < parms.length; i++) { try { pstmt.setDate(i + 1, java.sql.Date.valueOf(parms[i])); } catch (Exception e) { try { pstmt.setDouble(i + 1, Double.parseDouble(parms[i])); } catch (Exception e1) { try { pstmt.setInt(i + 1, Integer.parseInt(parms[i])); } catch (Exception e2) { try { pstmt.setString(i + 1, 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 = -1; 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(1); } } 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 = 1; i <= column; i++) { result[i - 1] = 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 = 1; i <= column; i++) { ob[i - 1] = 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 = 1; i <= column; i++) { ob[i - 1] = 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 = 1; 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 = -1; try { pstmt = con.prepareStatement(cmdtext); prepareCommand(pstmt, parms); ResultSet rs = pstmt.executeQuery(); rs.next(); result = rs.getInt(1); 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 = 0; i < rs.size(); i++) { User user = new User(); String[] temp = rs.get(i); user.setId(temp[0]); user.setUsername(temp[1]); user.setPassword(temp[2]); user.setName(temp[3]); user.setApartment(temp[4]); user.setTitle(temp[5]); user.setPhonenumber(temp[6]); user.setType(Integer.parseInt(temp[7])); user.setAuthority(Integer.parseInt(temp[8])); // 根据城市码查询城市 int ProviceCode = Integer.parseInt(temp[9]); Province pro = new Province(); pro.setProvinceCode(temp[9]); String provinceSql = "select PROVINCE_NAME from np_province where PROVINCE_CODE=?"; String[] provinceParams = { temp[9] }; Object proObject = sqlHelper.ExecuteScalar(provinceSql, "PROVINCE_NAME", provinceParams); pro.setProvinceName(proObject + ""); // System.out.println("UserServie:" + pro); user.setPro(pro); user.setProvince(Integer.parseInt(temp[9])); user.setCity(Integer.parseInt(temp[10])); // 同上，通过城市码查找城市 String citySql = "select * from np_city where PROVINCE_CODE=? and city_code=?"; String[] cityParams = { temp[9], temp[10] }; String[] cityRs = sqlHelper.ExecuteFirstRecorder(citySql, cityParams); City ci = new City(); ci.setCityCode(temp[10]); ci.setProvinceCode(temp[9]); ci.setProvinceName(cityRs[0]); ci.setCityName(cityRs[2]); 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; }
