dbutils.java

DWR 3级联动代码 基于DWR 的整合 是一个不错的例子

				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){ // 整数返回Integer
				m.put(columnName, rs.getInt(columnName));
			} else { // 小数返回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("执行查询时失败",e);
		} finally {
			closeRs(rs);
			closeStmt(pstmt);
		}
	}
	
	/**
	 * 该方法只能用于oracle 10g 数据库
	 * @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 数据库
	 * @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 数据库
	 * @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 数据库
	 * @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);
		}
	}
	
	/**
	 * 该方法用于执行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("执行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("执行存储过程时失败",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的参数,用法：
	 * ParameterCollection ps = new ParameterCollection();
	 * ps.append(对象参数1).append(对象参数2);
	 * Object params = ps.getParameterArray(); // 返回参数数组
	 * @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();
	}
	
	/**
	 * 执行查询某SQL语句返回结果的总记录数
	 * @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,例如 执行：
	 * insertTop4SQLServer("select * from A where b = ?", 10)
	 * 结果返回：
	 * 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();
	}

}