JDBC DAO_JAVA_编程开发_程序员俱乐部

class="java" name="code">public Connection getConnection() throws SQLException {
		try {
			Class.forName(ConstantUtil.DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		String url = ConstantUtil.URL;
		String user = ConstantUtil.USER;
		String password = ConstantUtil.PASSWORD;
		Connection conn = (Connection) DriverManager.getConnection(url,user,password);
		return conn;
//		return getPoolConnection();
	}
	/**
	 * 注：Oracle键必须是大写的
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> query(String sql, Object[] params) throws SQLException {
		Connection connection = getConnection();
		PreparedStatement ps = connection.prepareStatement(sql);
		for (int i = 0; i < params.length; i++) {
			ps.setObject((i + 1), params[i]);
		}
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData rsmd = rs.getMetaData();
		int column_count = rsmd.getColumnCount();
		List<String> fields = new ArrayList<String>();
		for (int i = 1; i <= column_count; i++) {
			String field = rsmd.getColumnName(i);
			fields.add(field);
		}
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		while (rs.next()) {
			Map<String, Object> record = new HashMap<String, Object>();
			for (String field : fields) {
				Object value = rs.getObject(field);
				record.put(field, value);
			}
			result.add(record);
		}
		if(rs!=null){
			rs.close();
		}
		if(ps!=null){
			ps.close();
		}
		if(connection!=null){
			connection.close();
		}

		return result;
	}
	/**
	 * 
	 * @param sql
	 * @param params
	 * @param alias 总数别名
	 * @return
	 * @throws Exception
	 */
	public int queryCount(String sql, Object[] params,String alias) throws Exception {
		Connection connection = getConnection();
		PreparedStatement ps = connection.prepareStatement(sql);
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				ps.setObject((i + 1), params[i]);
			}
		}
		ResultSet rs = ps.executeQuery();
		int count = 0;
		if (rs.next()) {
			count = rs.getInt(alias);
		}
		if(rs!=null){
			rs.close();
		}
		if(ps!=null){
			ps.close();
		}
		if(connection!=null){
			connection.close();
		}
		return count;
	}

	public int update(String sql, Object[] params) throws SQLException {
		Connection connection = getConnection();
		PreparedStatement ps = connection.prepareStatement(sql);
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				ps.setObject((i + 1), params[i]);
			}
		}
		int count = ps.executeUpdate();
		if(ps!=null){
			ps.close();
		}
		if(connection!=null){
			connection.close();
		}
		return count;
	}

	public void batchUpdate(String sql, List<Object[]> list) throws SQLException {
		Connection connection = getConnection();
		connection.setAutoCommit(false);
		PreparedStatement ps = connection.prepareStatement(sql);

		for (Object[] param : list) {
			for (int i = 0; i < param.length; i++) {
				ps.setObject((i + 1), param[i]);
			}
			ps.addBatch();
		}
		int[] counts = ps.executeBatch();
		connection.setAutoCommit(true);
		if(ps!=null){
			ps.close();
		}
		if(connection!=null){
			connection.close();
		}
	}

?