JDBC公共操作方法(四):【查询】、【增、删、改】和【调用存储过程】的公共方法 01

JDBCCore

 

public final class JDBCCore {

	private static final Logger LOG = Logger.getLogger(JDBCCore.class);

	private static JDBCCore instance = new JDBCCore();

	private JDBCCore() {
	}

	/**
	 * @Title: getInstance
	 * @Description: 获取JDBCCore的实例,单例
	 * @return JDBCCore
	 * @author wenjianhai
	 * @date 2011-12-29
	 */
	public static JDBCCore getInstance() {
		return instance;
	}

	/**
	 * @Title: query
	 * @Description: 公共查询操作
	 * @param sql
	 *            : 查询语句
	 * @param params
	 *            : 查询条件
	 * @return
	 * @author wenjianhai
	 * @date 2011-12-29
	 */
	public String[][] query(String sql, String[] params) {

		if (null == sql || "".equals(sql.trim())) {
			LOG.error("The sql is null, return.");
			return null;
		}
		// 获取数据库连接
		Connection connection = JDBCUtil.getInstance().getConnection();

		if (null == connection) {
			LOG.error("The database connction is null, return.");
			return null;
		}

		PreparedStatement ps = null;
		ResultSet rs = null;
		String[][] result = null;

		try {
			ps = connection.prepareStatement(sql);
			int len = params == null ? 0 : params.length;
			int position = 0;

			for (int i = 0; i < len; i++) {
				position = i + 1;
				try {
					ps.setString(position, params[i]);
				} catch (Exception e) {
					LOG.error("set String property faile!", e);
					Reader reader = new BufferedReader(new StringReader(
							params[i]));
					try {
						ps.setCharacterStream(position, reader, params.length);
					} catch (Exception ex) {
						LOG.error("set characterStream faile!", ex);
					} finally {
						// 关闭流
						IOUtil.closeReader(reader);
					}
				}
			} /* end of for(...) */
			// 获取查询结果集
			rs = ps.executeQuery();
			// 返回此 ResultSet 对象中的列数
			int cols = rs.getMetaData().getColumnCount();

			List<String[]> rows = new ArrayList<String[]>(10);
			String[] rowValue = null;
			int i = 0;

			while (rs.next()) {

				rowValue = new String[cols];

				for (i = 0; i < cols; i++) {
					rowValue[i] = rs.getString(i + 1);
				}
				rows.add(rowValue);
			}

			result = new String[rows.size()][cols];

			rows.toArray(result);

			if (!rows.isEmpty()) {
				rows.clear();
			}

		} catch (SQLException e) {
			LOG.error("JDBCCore:=>query:SQLException!", e);
		} catch (Exception e) {
			LOG.error("JDBCCore:=>query:Exception!", e);
		} finally {
			JDBCUtil.close(connection, rs, ps);
		}
		return result;
	}