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

/**
	 * @Title: operate
	 * @Description: 增、删、改的公共操作方法
	 * @param sql
	 *            : 操作语句
	 * @param params
	 *            : 操作条件
	 * @return
	 * @author
	 * @date 2011-12-29
	 */
	public int operate(String sql, String[] params) {

		// 进行数据库操作时,受影响的行数
		int affectedRows = -1;

		if (null == sql || "".equals(sql.trim())) {
			LOG.error("The sql is null, return.");
			return affectedRows;
		}

		// 获取数据库连接
		Connection connection = JDBCUtil.getInstance().getConnection();

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

		PreparedStatement ps = null;

		try {
			// 不允许自动提交事务
			connection.setAutoCommit(false);

			/*
			 * 设置事务隔离级别. 指示不可以发生脏读和不可重复读的常量；虚读可以发生
			 */
			connection
					.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
		} catch (SQLException e) {
			LOG.error("set connectin properties faile!", e);
			return affectedRows;
		}

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

			for (int i = 0; i < len; i++) {
				if (null != params[i] && 0 != params[i].trim().length()) {
					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(...) */

			// 执行sql语句,并返回受影响的行数
			affectedRows = ps.executeUpdate();

			// 提交事务
			connection.commit();

		} catch (SQLException e) {
			LOG.error("JDBCCore:=>operate:SQLException!", e);
			JDBCUtil.rollback(connection);
		} catch (Exception e) {
			LOG.error("JDBCCore:=>operate:Exception!", e);
			JDBCUtil.rollback(connection);
		} finally {
			JDBCUtil.close(connection, null, ps);
		}
		return affectedRows;
	}