pooledconnectionhandle.java

在机器上开一个端口,代理客户端的数据库请求,用在资源有限的终端访问大型数据库上使用,使用jdbc连接orcle, 客户端使用标准的socket即可

		} else if (li_len == 1) {
			return "000" + ret;
		} else if (li_len > 4) {
			ret = ret.substring(li_len - 4);
		}
		return ret;
	}

	/***************************************************************************
	 * 将rs的内容按curr, 和rowspage写到socket.outputstream中 pmCurPage index start 1,
	 * rowsPerPage need great than 0;
	 */
	private void ResultSetToString(OutputStream o, ResultSet rs, int pmCurPage,
			int rowsPerPage) throws Exception {
		if (rowsPerPage <= 0) {
			throw new behdException("getLocalResultSet:将结果集取本地错误！每页显示记录数量<=0");
		}
		if (pmCurPage <= 0) {
			throw new behdException("getLocalResultSet:将结果集取本地错误！当前显示页号<=0");
		}
		if (rs == null) {
			throw new behdException("getLocalResultSet:将结果集取本地错误！传入结果集为空");
		}
		int rowCount = 0;
		int curPage = pmCurPage - 1;
		int maxPage = 0;
		int getRowS = 0;
		int nRows = 0;
		int nCols = 0;

		try {
			rs.last();
			rowCount = rs.getRow();
			if (rowCount == 0) {
				return;
			}
			if (rowCount % rowsPerPage == 0) {
				maxPage = rowCount / rowsPerPage;
			} else {
				maxPage = rowCount / rowsPerPage + 1;
			}
			getRowS = curPage * rowsPerPage;
			if (rowCount < getRowS) {
				throw new behdException(
						"getLocalResultSet:将结果集取本地错误！页号已经超出记录集范围，总rows:"
								+ rowCount + " 要求取值开始:" + getRowS);
			}
			nRows = rowCount - getRowS;
			if (nRows > rowsPerPage) {
				nRows = rowsPerPage;
			}
			// 1.向outputstream 写入信息
			// 总记录数，总页数，本次取得的总记录数
			this.writeString(o, int28char(rowCount)); // 总行数
			this.writeString(o, int24char(rowsPerPage)); // 每页的行数
			this.writeString(o, int24char(curPage)); // 当前页
			this.writeString(o, int24char(maxPage)); // 总页数

			ResultSetMetaData md = rs.getMetaData();
			nCols = md.getColumnCount();
			char[] m_colType = new char[nCols];
			this.writeString(o, int24char(nCols)); // 总lie数
			// 各列的名称
			for (int j = 1; j <= nCols; j++) {
				this.writeString4ByteLen(o, md.getColumnName(j)); // 列名
				int li_coltype = md.getColumnType(j);
				if ((li_coltype == -5) || (li_coltype == 3)
						|| (li_coltype == 8) || (li_coltype == 6)
						|| (li_coltype == 4) || (li_coltype == 2)
						|| (li_coltype == 7) || (li_coltype == 5)) {// 数值
					m_colType[j - 1] = '0';
				} else if ((li_coltype == 1) || (li_coltype == 12)) {// 字符
					m_colType[j - 1] = '1';
				} else if (li_coltype == 91) {// 日期
					m_colType[j - 1] = '2';
				} else if (li_coltype == 92) {// 时间
					m_colType[j - 1] = '3';
				} else if (li_coltype == 93) {// 日期+时间
					m_colType[j - 1] = '4';
				} else if (li_coltype == 16) {// bool
					m_colType[j - 1] = '5';
				} else if ((li_coltype == 2004) || (li_coltype == 2005)) {// blob
					m_colType[j - 1] = '6';
				} else { // 字符
					m_colType[j - 1] = '1';
				}
			}
			// 具体的数据
			this.writeString(o, int24char(nRows));
			if (getRowS > 0) {
				rs.absolute(getRowS);
			} else {
				rs.beforeFirst();
			}
			rs.setFetchSize(nRows);
			for (int i = 0; i < nRows; i++) {
				if (!rs.next()) {
					behdLog.log("取数异常，准备取:" + nRows + "实际取:" + i);
					break;
				}
				for (int j = 1; j <= nCols; j++) {
					o.write(m_colType[j - 1]);
					Object obj = rs.getObject(j);
					if (obj == null) {
						this.writeString(o, "FFFF");
					} else {
						String tmpstr = null;
						if (m_colType[j - 1] == '0') { // 数值
							tmpstr = obj.toString();
						} else if (m_colType[j - 1] == '1') { // 字符
							tmpstr = (String) obj;
						} else if (m_colType[j - 1] == '2') { // 日期
							tmpstr = dFmt.format(obj);
						} else if (m_colType[j - 1] == '3') { // 时间
							tmpstr = obj.toString();
						} else if (m_colType[j - 1] == '4') { // 日期+时间
							tmpstr = dtFmt.format(obj);
						} else if (m_colType[j - 1] == '5') { // bool
							tmpstr = obj.toString();
						} else if (m_colType[j - 1] == '6') { // blob
							tmpstr = (String) obj;
							if (tmpstr == null) {
								tmpstr = "";
							} else {
								tmpstr = tmpstr.substring(0, 65535);
							}
						}
						this.writeString4ByteLen(o, tmpstr);
					}
				}
			}
		} catch (SQLException ex) {
			throw new behdException("getLocalResultSet:将结果集取本地错误！页号已经超出记录集范围"
					+ ex.getMessage());
		}
		return;
	}

	private Connection getConn(OutputStream o, char auto_commit)
			throws IOException {
		Connection conn = null;
		if (!ClientServiceID.equals("0000")) {
			conn = (Connection) Connlist.get(ClientServiceID);
			if (conn == null) {
				return conn;
			}
		} else {
			if (auto_commit == '0') {
				// ClientServiceID 如何确定服务号,分配新的服务号
				int i = 0;
				for (i = 1; i < 65534; i++) {
					String strSerid = int24char(i);
					if (Connlist.containsKey(strSerid)) {
						continue;
					}
					ClientServiceID = strSerid;
					break;
				}
				if (i == 65535) {
					return null;
				}
			}
		}
		// 检查mdb的合法性, 如,是否已经超时等等
		if (conn == null) {
			conn = behdConnect.getInstance().getConnection("ehdzb");
		}
		return conn;
	}

	/**
	 * 执行数据库修改操作
	 * 
	 * @param sql
	 * @param auto_commit
	 *            '0' 不提交, '1' 提交 0000 + serviceid + "执行结果"("0001" + 影响行数) --
	 *            成功处理的情况 0000 + serviceid + "执行结果"("0000" + "XXXX"文字) --
	 *            成功处理的情况, 没有修改记录 0000 + serviceid + "执行结果"("FFFF" + "XXXX"文字) --
	 *            处理时发生错误情况,
	 */
	public void execUpdate(String sql, char auto_commit) {
		OutputStream o = null;
		try {
			o = socket.getOutputStream();
			this.writeString(o, "0000"); // 执行结果返回
			if (auto_commit != '0' && auto_commit != '1' && auto_commit != '2') {
				// '0' - update 不提交, '1' - update 并提交, '2' - 直接回滚
				this.writeString(o, ClientServiceID); // 写服务号
				this.writeString(o, "FFFF"); // 执行失败,没有修改的行
				this
						.writeString4ByteLen(o,
								"auto_commit 标志 '0' - update 不提交, '1' - update 并提交, '2' - 直接回滚");
				return;
			} else {
				if ((sql == null) || (sql.equals(" "))) {
					this.writeString(o, ClientServiceID); // 写服务号
				} else {
					conn = getConn(o, auto_commit);
					this.writeString(o, ClientServiceID); // 写服务号
					if (conn == null) {
						this.writeString(o, "FFFF"); // 执行失败,没有修改的行
						this.writeString4ByteLen(o, "请求的服务号不存在! "
								+ ClientServiceID);
						return;
					}
					statement = conn.createStatement();
				}
			}

			if (auto_commit == '2') {
				conn.rollback();
				this.writeString(o, int24char(1)); // 执行成功
				this.writeString(o, "0000"); // 影响的行数
				if (!ClientServiceID.equals("0000")) {
					Connlist.remove(ClientServiceID);
				}
				this.writeString(o, "0000"); // serviceID
				System.out.println("执行回滚操作");
			} else {
				int rtn;
				if ((sql == null) || (sql.equals(" "))) {
					rtn = 0;
				} else {
					rtn = statement.executeUpdate(sql);
					System.out.println("执行sql:" + sql);
				}
				if (rtn >= 0) {
					System.out.println("修改了数据库");
					this.writeString(o, int24char(1)); // 执行成功
					this.writeString(o, int24char(rtn)); // 影响的行数
					if (auto_commit == '0') {
						Connlist.put(ClientServiceID, conn);
						this.writeString(o, ClientServiceID); // 重写serviceID
						System.out.println("不提交");
					} else {
						conn.commit();
						this.writeString(o, "0000"); // 清空serviceID
						if (!ClientServiceID.equals("0000")) {
							Connlist.remove(ClientServiceID);
						}
						System.out.println("提交");
					}
				} else {
					System.out.println("没修改数据库");
					this.writeString(o, int24char(0)); // 执行失败,没有修改的行
					if (auto_commit == '0') {
						Connlist.put(ClientServiceID, conn);
						this.writeString(o, ClientServiceID); // 重写serviceID
						System.out.println("不提交");
					} else {
						conn.commit();
						this.writeString(o, "0000"); // 清空serviceID
						if (!ClientServiceID.equals("0000")) {
							Connlist.remove(ClientServiceID);
						}
						System.out.println("提交");
					}
				}
			}
			if (auto_commit != '0' && conn != null) {
				behdConnect.getInstance().freeConnection("ehdzb", conn);
			}
			System.out.println("执行sql 成功！");
			// 正确执行返回执行结果
		} catch (Exception e) {
			try {
				String ls_mess = "执行Update/insert语句错误:" + e.getMessage();
				this.writeString(o, "FFFF"); // 执行失败,没有修改的行
				this.writeString4ByteLen(o, ls_mess);
				System.out.println(sql + ls_mess);
			} catch (Exception ioe) {
				ioe.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				o.flush();
				statement.close();
			} catch (Exception e) {
				System.out.println(new Date() + "socket.flush() err");
				e.printStackTrace();
			}
		}
	}

	/* 向客户端发送文件 */
	public void sendFile(String fileName) {
		try {
			// 获取要读取的文件名
			FileReader fileReader = new FileReader(new File(fileName));
			BufferedReader bufferedFileReader = new BufferedReader(fileReader);
			PrintWriter printWriter = new PrintWriter(socket.getOutputStream());
			String line = null;
			while ((line = bufferedFileReader.readLine()) != null) {
				printWriter.println(line);
			}
			fileReader.close();
			printWriter.close();
		} catch (Exception e) {
			System.out
					.println("Exception occured when handiling a client connection."
							+ e.getMessage());
		}
	}

}