?? dbutils.java
字號:
rsmd.getColumnType(index) == Types.BIGINT) {
m.put(columnName, rs.getInt(columnName));
} else if (rsmd.getColumnType(index) == Types.NUMERIC ) {
// System.out.println(rsmd.getPrecision(i+1)+" "+ rsmd.getScale(i+1));
if(rsmd.getScale(index) == 0){ // 整數(shù)返回Integer
m.put(columnName, rs.getInt(columnName));
} else { // 小數(shù)返回BigDecimal
m.put(columnName, rs.getBigDecimal(columnName));
}
} else {
// System.out.println("rs.getObject(columnName)" + rs.getObject(columnName).getClass() + " " + rsmd.getColumnType(index));
m.put(columnName, rs.getObject(columnName));
}
}
/**
* 查詢唯一記錄
* @param sql
* @param params
* @return 找到返回一個Map對象,否則返回null
*/
public Map uniqueResult (String sql, Object... params) {
Connection conn = getCurrentConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = conn.prepareStatement(sql);
parseParameters(pstmt, params);
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
Map m = new IgnoreCaseHashMap();
boolean found = false;
if(rs.next()) {
for(int i = 0; i < columnCount; i++) {
parseResultSet(rs, rsmd, m, i+1);
}
found = true;
}
if(rs.next()){
throw new RuntimeException("返回記錄不唯一");
}
return (found)?m:null;
} catch (SQLException e) {
throw new RuntimeException("執(zhí)行查詢時失敗",e);
} finally {
closeRs(rs);
closeStmt(pstmt);
}
}
/**
* 該方法只能用于oracle 10g 數(shù)據(jù)庫
* @param blobIs
* @param blob
*/
public void write2Blob(InputStream blobIs, Blob blob) {
OutputStream blobOs = null;
try {
blobOs = blob.setBinaryStream(0L);
is2Os(blobIs, blobOs, 4096);
} catch (Exception e) {
throw new RuntimeException("Blob 寫入失敗",e);
} finally {
try { if( blobOs != null) blobOs.close(); } catch (IOException e) { }
try { if( blobIs != null) blobIs.close(); } catch (IOException e) { }
}
}
/**
* 該方法只能用于oracle 10g 數(shù)據(jù)庫
* @param clobIs
* @param clob
*/
public void write2Clob(InputStream clobIs, Clob clob) {
Writer clobWriter = null;
Reader clobReader = null;
try {
clobWriter = clob.setCharacterStream(0L);
clobReader = new InputStreamReader(clobIs, "UTF-8");
reader2Writer(clobReader, clobWriter, 4096);
} catch (Exception e) {
throw new RuntimeException("Clob 寫入失敗",e);
} finally {
try { if( clobReader != null) clobReader.close(); } catch (IOException e) { }
try { if( clobWriter != null) clobWriter.close(); } catch (IOException e) { }
try { if( clobIs != null) clobIs.close(); } catch (IOException e) { }
}
}
/**
* 該方法能用于oracle 9i和10g XE 數(shù)據(jù)庫
* @param clobIs
* @param rs
* @param columnName
*/
public void write2CLOB(InputStream clobIs, ResultSet rs, String columnName) {
Writer clobWriter = null;
Reader clobReader = null;
try {
CLOB c = ((OracleResultSet) rs).getCLOB(columnName);
clobWriter = c.setCharacterStream(0L);
clobReader = new InputStreamReader(clobIs, "UTF-8");
reader2Writer(clobReader, clobWriter, c.getBufferSize());
} catch (Exception e) {
throw new RuntimeException("CLOB 寫入失敗",e);
} finally {
try { if( clobReader != null) clobReader.close(); } catch (IOException e) { }
try { if( clobWriter != null) clobWriter.close(); } catch (IOException e) { }
try { if( clobIs != null) clobIs.close(); } catch (IOException e) { }
}
}
private void reader2Writer(Reader clobReader, Writer clobWriter, int size) throws Exception {
try {
char[] cbuffer = new char[size];
int length = -1;
while ((length = clobReader.read(cbuffer)) != -1) {
clobWriter.write(cbuffer, 0, length);
}
} catch (Exception e) {
throw e;
} finally {
try { if( clobReader != null) clobReader.close(); } catch (IOException e) { }
}
}
/**
* 該方法能用于oracle 9i和10g XE 數(shù)據(jù)庫
* @param blobIs
* @param rs
* @param columnName
*/
public void write2BLOB(InputStream blobIs, ResultSet rs, String columnName) {
OutputStream blobOs = null;
try {
BLOB b = ( (OracleResultSet) rs).getBLOB(columnName);
blobOs = b.setBinaryStream(0L);
is2Os(blobIs, blobOs, b.getBufferSize());
} catch (Exception e) {
throw new RuntimeException("BLOB 寫入失敗",e);
} finally {
try { if( blobOs != null) blobOs.close(); } catch (IOException e) { }
try { if( blobIs != null) blobIs.close(); } catch (IOException e) { }
}
}
public void readBLOB(ResultSet rs, String columnName, OutputStream os) {
InputStream blobIs = null;
try {
BLOB blob = ((OracleResultSet) rs).getBLOB(columnName);
blobIs = blob.getBinaryStream();
is2Os(blobIs, os, blob.getBufferSize());
} catch (Exception e) {
throw new RuntimeException("BLOB 讀取失敗",e);
} finally {
try { if( os != null) os.close(); } catch (IOException e) { }
try { if( blobIs != null) blobIs.close(); } catch (IOException e) { }
}
}
public void readCLOB(ResultSet rs, String columnName, Writer writer) {
Reader clobReader = null;
try {
CLOB c = ( (OracleResultSet) rs ).getCLOB(columnName);
clobReader = c.characterStreamValue();
reader2Writer(clobReader, writer, c.getBufferSize());
} catch (Exception e) {
throw new RuntimeException("CLOB 讀取失敗",e);
} finally {
try { if( writer != null) writer.close(); } catch (IOException e) { }
try { if( clobReader != null) clobReader.close(); } catch (IOException e) { }
}
}
public void is2Os(InputStream is, OutputStream os, int size) throws IOException {
byte[] buffer = new byte[size];
int length = -1;
while ((length = is.read(buffer)) != -1) {
os.write(buffer, 0, length);
}
}
/**
* 該方法用于執(zhí)行DDL等SQL語句
* @param sql
*/
public void execute(String sql){
execute(sql, false);
}
public void execute(String sql, boolean ignoreError) {
Connection conn = getCurrentConnection();
Statement stmt = null;
try {
stmt = conn.createStatement();
stmt.execute(sql);
} catch (SQLException e) {
if(! ignoreError) {
throw new RuntimeException("執(zhí)行SQL時失敗",e);
}
} finally {
closeStmt(stmt);
}
}
public CallableStatement getCallableStatement(String sql) {
Connection conn = getCurrentConnection();
CallableStatement cstmt = null;
try {
cstmt = conn.prepareCall(sql);
return cstmt;
} catch (SQLException e) {
throw new RuntimeException("執(zhí)行存儲過程時失敗",e);
}
}
public java.sql.Date getSQLNow() {
return new java.sql.Date(new java.util.Date().getTime());
}
public java.sql.Date getSQLDate(java.util.Date time) {
return (time == null) ? null : new java.sql.Date(time.getTime());
}
public java.util.Date getUtilDate(java.sql.Timestamp time) {
return (time == null) ? null : new java.util.Date(time.getTime());
}
public java.util.Date getUtilDate(java.sql.Date time) {
return (time == null) ? null : new java.util.Date(time.getTime());
}
/**
* 用于收集PreparedStatement的參數(shù),用法：
* ParameterCollection ps = new ParameterCollection();
* ps.append(對象參數(shù)1).append(對象參數(shù)2);
* Object params = ps.getParameterArray(); // 返回參數(shù)數(shù)組
* @author yihang
*
*/
public static class ParameterCollection {
private List params = new ArrayList();
public ParameterCollection append(Object param){
this.params.add(param);
return this;
}
public Object[] getParameterArray(){
return params.toArray(new Object[]{});
}
public void clear() {
this.params.clear();
}
}
private String getCountSQL(String originalSQL){
int index = originalSQL.toLowerCase().indexOf(" from");
return new StringBuilder()
.append("SELECT COUNT(*) as _count")
.append(originalSQL.substring(index)).toString();
}
/**
* 執(zhí)行查詢某SQL語句返回結(jié)果的總記錄數(shù)
* @param sql
* @param params
* @return
*/
public int countResult (String sql, Object... params) {
Map map = uniqueResult(getCountSQL(sql), params);
return (map==null)?0:(Integer)map.get("_count");
}
/**
* 為原有SQL語句加入top,例如 執(zhí)行：
* insertTop4SQLServer("select * from A where b = ?", 10)
* 結(jié)果返回：
* select top 10 from A where b = ?
* @param originalSQL
* @return
*/
public String insertTop4SQLServer(String originalSQL, int top) {
String after = originalSQL.substring("select".length());
return new StringBuilder().append("SELECT top ").append(top).append(after).toString();
}
public static void main(String[] args) throws SQLException {
ResultSet rs = null;
// 增
// DBUtils.getInstance().executeUpdate(
// "insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) " +
// "values (?,?,?,?,?,?,?,?)",
// 8889,"TOM","SALESMAN",7369,
// new java.sql.Date(new Date().getTime()),
// new BigDecimal("3000.00"),null,20);
// 刪
// DBUtils.getInstance().executeUpdate("delete from emp where empno=?",
// 7499);
// 改
// DBUtils.getInstance().executeUpdate(
// "update emp set sal=?,job=? where empno=?",
// new BigDecimal("10000.00"),"MANAGER",8888);
// 查
// rs = DBUtils.getInstance().executeQuery("select empno,ename,sal from emp where sal > ?",
// new BigDecimal("2000"));
// while(rs.next()){
// System.out.print(rs.getInt(1));
// System.out.print(" ");
// System.out.print(rs.getString(2));
// System.out.print(" ");
// System.out.println(rs.getString(3));
// }
// DBUtils.getInstance().closeRs(rs);
// rs = DBUtils.getInstance().executeQuery("select empno,ename,comm from emp where comm is ?",
// new Object[]{null});
// while(rs.next()){
// System.out.print(rs.getInt(1));
// System.out.print(" ");
// System.out.print(rs.getString(2));
// System.out.print(" ");
// System.out.println(rs.getString(3));
// }
// DBUtils.getInstance().closeRs(rs);
// List list = DBUtils.getInstance().list(
// "select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like ?", "S%");
// for (Iterator i = list.iterator(); i.hasNext();) {
// Map m = (Map) i.next();
// Set columnNames = m.keySet();
// for (Iterator j = columnNames.iterator(); j.hasNext();) {
// String key = (String) j.next();
// Object value = m.get(key);
// System.out.print(key + ":" + value + "," + ((value==null)?"":value.getClass()));
// }
// System.out.println("");
// }
Calendar c = Calendar.getInstance();
c.set(Calendar.YEAR, 1982);
c.set(Calendar.MONTH, 0);
List list = DBUtils.getInstance().list(
"select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where hiredate > ?", new java.sql.Date(c.getTime().getTime()));
for (Iterator i = list.iterator(); i.hasNext();) {
Map m = (Map) i.next();
Set columnNames = m.keySet();
for (Iterator j = columnNames.iterator(); j.hasNext();) {
String key = (String) j.next();
Object value = m.get(key);
System.out.print(key + ":" + value + " " + ((value==null)?"":value.getClass()));
}
System.out.println("");
}
// Map m = DBUtils.getInstance().uniqueResult(
// "select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename = ?", "SMITH");
// Set columnNames = m.keySet();
// for (Iterator j = columnNames.iterator(); j.hasNext();) {
// String key = (String) j.next();
// Object value = m.get(key);
// System.out.print(key + ":" + value + " " );//+ ((value==null)?"":value.getClass()));
// Map m = DBUtils.getInstance().uniqueResult(
// "select count(*) total from emp", null);
// Set columnNames = m.keySet();
// for (Iterator j = columnNames.iterator(); j.hasNext();) {
// String key = (String) j.next();
// Object value = m.get(key);
// System.out.print(key + ":" + value + " " );//+ ((value==null)?"":value.getClass()));
// }
DBUtils.getInstance().commit();
DBUtils.getInstance().closeAll();
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -