java对于数据库一系列操作汇总

 

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.List; /** *注：getConnection/commitConnection/closeConnection/rollbackConnection需要自己实现 * */ public class DataBaseUtil extends JDBCBaseBean { private String dDataSourceName = "datasourcename";//数据库名称 /** * 批量操作 * @return * @throws Exception */ public boolean batch() throws Exception { ArrayList batchSql = this.getBatchSql(); try { //conn = this.getConnection(); conn = this.getConnection(dDataSourceName); boolean autoCommit = conn.getAutoCommit(); conn.setAutoCommit(false); Statement stat = conn.createStatement(); for(int i = 0; i < batchSql.size(); i ++) { stat.addBatch(batchSql.get(i).toString()); } stat.executeBatch(); conn.commit(); conn.setAutoCommit(autoCommit); this.commitConnection(dDataSourceName); return true; } catch (Exception ex) { this.rollbackConnection(dDataSourceName); throw ex; } finally { this.closeConnection(dDataSourceName); } } /** * 批量插入操作 * @return * @throws Exception */ public boolean insertBatch() throws Exception { ArrayList batchSql = this.getBatchSql(); try { conn = this.getConnection(dDataSourceName); boolean autoCommit = conn.getAutoCommit(); conn.setAutoCommit(false); Statement stat = conn.createStatement(); for(int i = 0; i < batchSql.size(); i ++) { stat.addBatch(batchSql.get(i).toString()); } stat.executeBatch(); conn.commit(); conn.setAutoCommit(autoCommit); this.commitConnection(dDataSourceName); return true; } catch (Exception ex) { this.rollbackConnection(dDataSourceName); throw ex; } finally { this.closeConnection(dDataSourceName); } } /** * 向DataBase增加数据 * @param sqlString * @return * @throws Exception */ public boolean insert(String sqlString) throws Exception { try { this.getConnection(dDataSourceName).createStatement().execute(sqlString); this.commitConnection(dDataSourceName); return true; } catch (Exception ex) { this.rollbackConnection(dDataSourceName); throw ex; } finally { this.closeConnection(dDataSourceName); } } /** * 删除多条数据 * @param id[] * @param sql * @return * @throws Exception */ public boolean delete(String id[], String sql) throws Exception { try { conn = this.getConnection(dDataSourceName); } catch (Exception e) { this.closeConnection(dDataSourceName); System.out.println(e.toString()); throw e; } java.sql.Statement StReader = null; try { for (int i = 0; i < id.length; i++) { StReader = conn.createStatement(); String SQLStatement = sql + Long.parseLong(id[i].trim()); StReader.execute(SQLStatement); } } catch (Exception ex) { StReader.close(); this.closeConnection(dDataSourceName); throw ex; } finally { StReader.close(); this.commitConnection(dDataSourceName); this.closeConnection(dDataSourceName); } return true; } /** * 删除单条数据 * @param id * @param sql * @return * @throws Exception */ public boolean delete(String id, String sql) throws Exception { try { conn = this.getConnection(dDataSourceName); } catch (Exception e) { this.closeConnection(dDataSourceName); System.out.println(e.toString()); throw e; } java.sql.Statement StReader = null; try { StReader = conn.createStatement(); String SQLStatement = sql + Long.parseLong(id); StReader.execute(SQLStatement); } catch (Exception ex) { StReader.close(); this.closeConnection(dDataSourceName); throw ex; } finally { StReader.close(); this.commitConnection(dDataSourceName); this.closeConnection(dDataSourceName); } return true; } /** * 删除全部数据 * @param id * @param sql * @return * @throws Exception */ public boolean delete(String sql) throws Exception { try { conn = this.getConnection(dDataSourceName); } catch (Exception e) { this.closeConnection(dDataSourceName); // System.out.println(e.toString()); throw e; } java.sql.Statement StReader = null; try { StReader = conn.createStatement(); StReader.execute(sql); } catch (Exception ex) { throw ex; } finally { StReader.close(); this.commitConnection(dDataSourceName); this.closeConnection(dDataSourceName); } return true; } /** * 根据查询条件获得结果集 * @param sqlString * @return List * @throws Exception */ public List getAllElement(String sqlString) throws Exception { try { conn = this.getConnection(dDataSourceName); } catch (Exception e) { this.closeConnection(dDataSourceName); System.out.println(e.toString()); throw e; } ArrayList result = new ArrayList(); java.sql.Statement stmt = null; java.sql.ResultSet rs = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery(sqlString); //设置列数，在调用的类中用get方法获得列数 this.setColNum(rs.getMetaData().getColumnCount()); while (rs.next()) { for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) { result.add((String) rs.getString(i)); } } return result; } catch (Exception ex) { rs.close(); stmt.close(); this.closeConnection(dDataSourceName); throw ex; } finally { rs.close(); stmt.close(); this.closeConnection(dDataSourceName); } } /** * 根据查询条件获得结果集 * @param sqlString * @return List * @throws Exception */ public ResultSet getResultSet(String sqlString) throws Exception { ArrayList result = new ArrayList(); java.sql.Statement stmt = null; java.sql.ResultSet rs = null; try { conn = this.getConnection(dDataSourceName); } catch (Exception e) { this.closeConnection(dDataSourceName); System.out.println(e.toString()); throw e; } try { stmt = conn.createStatement(); rs = stmt.executeQuery(sqlString); return rs; } catch (Exception ex) { rs.close(); stmt.close(); this.closeConnection(dDataSourceName); throw ex; } finally { rs.close(); stmt.close(); this.closeConnection(dDataSourceName); } } /** * 根据查询条件获得结果集和列名称 * @param sqlString * @return List * @throws Exception */ public List getAllElementAndColName(String sqlString) throws Exception { try { conn = this.getConnection(dDataSourceName); } catch (Exception e) { this.closeConnection(dDataSourceName); // System.out.println(e.toString()); throw e; } ArrayList result = new ArrayList(); ArrayList colName = new ArrayList(); java.sql.Statement stmt = null; java.sql.ResultSet rs = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery(sqlString); // 设置列数，在调用的类中用get方法获得列数 int ColCount = rs.getMetaData().getColumnCount(); //向调用的方法返回列数 this.setColNum(ColCount); //获得列名，并存入ArrayList for(int i = 1; i <= ColCount; i ++) { colName.add(rs.getMetaData().getColumnName(i)); } //向调用的方法返回列名 this.setColName(colName); while (rs.next()) { for (int i = 1; i <= ColCount; i++) { result.add((String) rs.getString(i)); } } return result; } catch (Exception ex) { rs.close(); stmt.close(); this.closeConnection(dDataSourceName); throw ex; } finally { rs.close(); stmt.close(); this.closeConnection(dDataSourceName); //connmgr.dropDB2Connection(conn); } } /** * 根据查询条件获得分页结果集 * @param pageNum 页数 * @param pageRowNum 每页显示的行数 * @param sqlString 例：select * from tableName * @param otherWhereclause 如果没有则此字段置为null或者为空。例：name = 'examplename' AND id = 0 * @param orderbyName 排序字段名 * @param ascORdesc asc or desc * @return list * @throws Exception */ public List getAllElement(int pageNum, int pageRowNum, String sqlString, String otherWhereclause, String orderbyName, String ascORdesc) throws Exception { String sql = null; // 构建查询语句 if (otherWhereclause.equals(null) || otherWhereclause.equals("")) { sql = "select * from (" + sqlString + " WHERE ROWNUM < " + (pageNum * pageRowNum + 1) + " minus " + sqlString + " WHERE ROWNUM < " + ((pageNum - 1) * pageRowNum + 1) + ") order by " + orderbyName + " " + ascORdesc; } else { sql = "select * from (" + sqlString + " WHERE " + otherWhereclause + " AND ROWNUM < " + (pageNum * pageRowNum + 1) + " minus " + sqlString + " WHERE " + otherWhereclause + " AND ROWNUM < " + ((pageNum - 1) * pageRowNum + 1) + ") order by " + orderbyName + " " + ascORdesc; } // System.out.println(sql); try { conn = this.getConnection(dDataSourceName); } catch (Exception e) { //connmgr.dropDB2Connection(conn); System.out.println(e.toString()); throw e; } ArrayList result = new ArrayList(); java.sql.Statement stmt = null; java.sql.ResultSet rs = null; try { //System.out.println(sql); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); // 设置列数，在调用的类中用get方法获得列数 this.setColNum(rs.getMetaData().getColumnCount()); while (rs.next()) { for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) { result.add((String) rs.getString(i)); } } return result; } catch (Exception ex) { rs.close(); stmt.close(); this.closeConnection(dDataSourceName); throw ex; } finally { rs.close(); stmt.close(); this.closeConnection(dDataSourceName); } } /** * 分页获得结果集的方法 * @param pageNum * @param pageRowNum * @param sqlString * @return list * @throws Exception */ public List getAllElement(int pageNum, int pageRowNum, String sqlString) throws Exception { //String sql = null; // 构建查询语句 try { conn = this.getConnection(dDataSourceName); } catch (Exception e) { this.closeConnection(dDataSourceName); // System.out.println(e.toString()); throw e; } ArrayList result = new ArrayList(); java.sql.PreparedStatement ps = null; java.sql.ResultSet rs = null; try { ps = conn.prepareStatement(sqlString, ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); rs = ps.executeQuery(); rs.absolute( -1); //重置游标的位置 int rowCount = rs.getRow(); int columnCount = rs.getMetaData().getColumnCount(); // 设置列数，在调用的类中用get方法获得列数 this.setColNum(columnCount); // 设置行数，在调用的类中用get方法获得行数 this.setRowNum(rowCount); //add by wuhongbo at 20070703 int offset = 1; offset = (pageNum - 1) * pageRowNum + 1; rs.absolute(offset); for (int i = 0; i< pageRowNum && offset < rowCount + 1; i ++, offset ++) { for(int j = 1; j <= columnCount; j ++) { result.add((String) rs.getString(j)); } rs.next(); } return result; } catch (Exception ex) { ex.printStackTrace(); rs.close(); ps.close(); this.closeConnection(dDataSourceName); throw ex; } finally { rs.close(); ps.close(); this.closeConnection(dDataSourceName); } } /** * 通过sql，返回单一的字符串 * @param sqlString * @return * @throws Exception */ public String getSingleElement(String sqlString) throws Exception { String element = ""; try { conn = this.getConnection(dDataSourceName); } catch (Exception e) { this.closeConnection(dDataSourceName); // System.out.println(e.toString()); throw e; } java.sql.Statement stmt = null; java.sql.ResultSet rs = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery(sqlString); while (rs.next()) { element = rs.getString(1); } return element; } catch (Exception ex) { throw ex; } finally { rs.close(); stmt.close(); this.closeConnection(dDataSourceName); } } /** * 根据条件获得结果集的总数，查询的sql由用户传入 * @param sqlString * @return int * @throws Exception */ public int getElementTotal(String sqlString) throws Exception { try { conn = this.getConnection(dDataSourceName); } catch (Exception e) { this.closeConnection(dDataSourceName); System.out.println(e.toString()); throw e; } int total = 0; java.sql.Statement pstmt = null; java.sql.ResultSet rs = null; try { pstmt = conn.createStatement(); // 查询 rs = pstmt.executeQuery(sqlString); while (rs.next()) { //获得结果集总数 total = rs.getInt(1); } return total; } catch (Exception ex) { rs.close(); pstmt.close(); this.closeConnection(dDataSourceName); throw ex; } finally { rs.close(); pstmt.close(); this.closeConnection(dDataSourceName); } } /** * @return the colName */ public ArrayList getColName() { return ColName; } /** * @param colName * the colName to set */ public void setColName(ArrayList colName) { ColName = colName; } /** * @return the colNum */ public int getColNum() { return ColNum; } /** * @param colNum * the colNum to set */ public void setColNum(int colNum) { ColNum = colNum; } /** * * @return */ public int getRowNum() //add by wuhongbo at 20070703 { return RowNum; } /** * * @param rowNum */ public void setRowNum(int rowNum) //add by wuhongbo at 20070703 { RowNum = rowNum; } private int ColNum = 0; // 列数 private int RowNum = 0; // 行数 add by wuhongbo at 20070703 private ArrayList ColRmarks = null; private ArrayList ColSize = null; private ArrayList ColNull = null; private ArrayList ColName = null; private ArrayList BatchSql = null; private Connection conn = null; /** * @return the batchSql */ public ArrayList getBatchSql() { return BatchSql; } /** * @param batchSql the batchSql to set */ public void setBatchSql(ArrayList batchSql) { BatchSql = batchSql; } public boolean execSql(String sqlString) throws Exception { try { conn=this.getConnection(dDataSourceName); conn.createStatement().execute(sqlString); this.commitConnection(dDataSourceName); return true; } catch (Exception ex) { this.rollbackConnection(dDataSourceName); throw ex; } finally { this.closeConnection(dDataSourceName); } } public boolean getTableColAttr(String tableName)throws Exception { try { conn = this.getConnection(dDataSourceName); } catch (Exception e) { this.closeConnection(dDataSourceName); System.out.println(e.toString()); throw e; } try { DatabaseMetaData metadata = conn.getMetaData(); ResultSet colRet =metadata.getColumns(null, "%", tableName, "%"); ArrayList colRmarks = new ArrayList(); ArrayList colSize = new ArrayList(); ArrayList colNull = new ArrayList(); while (colRet.next()) { String remarks = colRet.getString("REMARKS"); colRmarks.add(remarks); int datasize = colRet.getInt("COLUMN_SIZE"); colSize.add(datasize + ""); int nullable = colRet.getInt("NULLABLE"); colNull.add(nullable + ""); } this.setColRmarks(colRmarks); this.setColSize(colSize); this.setColNull(colNull); return true; } catch(Exception ex) { ex.printStackTrace(); throw ex; } finally { this.closeConnection(dDataSourceName); } } public ArrayList getColNull() { return ColNull; } public void setColNull(ArrayList colNull) { ColNull = colNull; } public ArrayList getColRmarks() { return ColRmarks; } public void setColRmarks(ArrayList colRmarks) { ColRmarks = colRmarks; } public ArrayList getColSize() { return ColSize; } public void setColSize(ArrayList colSize) { ColSize = colSize; } /** * 该SQL语句是否能查询出数据。 * @param sqlString * @return * @throws Exception */ public boolean isExistData(String sqlString) throws Exception { boolean flag = false; try { conn = this.getConnection(dDataSourceName); } catch (Exception e) { this.closeConnection(dDataSourceName); System.out.println(e.toString()); throw e; } java.sql.Statement stmt = null; java.sql.ResultSet rs = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery(sqlString); if (rs.next()) { flag = true; } return flag; } catch (Exception ex) { throw ex; } finally { rs.close(); stmt.close(); this.closeConnection(dDataSourceName); } } }