execute.java

基于数据库操作的封装

			do {
				Hashtable hashResult = new Hashtable();
				for (int s = 0; s < rs.getMetaData().getColumnCount(); s++) {
					hashResult.put(
						rs.getMetaData().getColumnName(s + 1),
						rs.getString(rs.getMetaData().getColumnName(s + 1)));
					int h = 0;
				}
				list.add(hashResult);

			} while (rs.next());
		}

		if (rs != null)
			rs.close();

		return list;
	}

/**
 * ��¼�б�
 * page     -- ��ʾҳ��ҳ��
 * pagesize -- ��ҳ��ʾ�ļ�¼��
 *
 * @see org.shaoye.magic.Operate#getAllItem(int, int)
 */
public Collection getAllItem(int page, int pagesize) throws SQLException {

	if (table == null || table.length() == 0)
		throw (new MagicException("Error 001 : table's name is null"));
	//001����
	ResultSet rs = null;
	List list = new ArrayList();

	sql = "";
	sql += "SELECT * FROM " + table;

	pstmt =
		conn.prepareStatement(
			sql,
			ResultSet.TYPE_SCROLL_INSENSITIVE,
			ResultSet.CONCUR_READ_ONLY);

	rs = pstmt.executeQuery();

	rs.absolute((page - 1) * pagesize + 1);
	do {
		Hashtable hashResult = new Hashtable();
		for (int s = 0; s < rs.getMetaData().getColumnCount(); s++) {
			hashResult.put(
				rs.getMetaData().getColumnName(s + 1),
				rs.getString(rs.getMetaData().getColumnName(s + 1)));
		}
		list.add(hashResult);

	} while (rs.next() && pagesize-- > 1);

	if (rs != null)
		rs.close();

	return list;
}

/**
 * @see org.shaoye.magic.Operate#destroy()
 */
public void destroy() {

	try {
                if (st != null)
			st.close();
		if (pstmt != null)
			pstmt.close();
		if (conn != null)
			conn.close();

	} catch (SQLException ex) {

		ex.printStackTrace();
	} catch ( Exception e )  {

		e.printStackTrace();
	}

}

/**
 * ��ʼ���������Ϣ
 * */
protected void initColumn() throws SQLException {
	sql += "select * from all_tab_columns ";
	sql += "where table_name=upper('" + table + "')";
	pstmt = conn.prepareStatement(sql);
	ResultSet rs = pstmt.executeQuery();
	while (rs.next()) {
		column = new Column();
		column.setColumnId(rs.getInt("COLUMN_ID"));
		column.setDefaultValue(rs.getString("DATA_DEFAULT"));
		column.setLength(rs.getInt("DATA_LENGTH"));
		column.setName(rs.getString("COLUMN_NAME"));
		String nullable = rs.getString("NULLABLE");
		column.setNullable(nullable.equalsIgnoreCase("Y") ? true : false);
		column.setType(rs.getString("DATA_TYPE"));
		if (column.getType().equalsIgnoreCase("number")) {
			column.setPrecision(rs.getInt("DATA_PRECISION"));
			column.setScale(rs.getInt("DATA_SCALE"));
		}
		columns.put(column.getName(), column);
	}
	rs.close();

}

/**
 * ����ݷŵ�PreparedStatement��
 * */
protected void setData(
	PreparedStatement ps,
	int columnIndex,
	String colname,
	Object value)
	throws SQLException {
	for (Enumeration en = columns.keys(); en.hasMoreElements();) {
		String _colname = (String)(en.nextElement());
		if (colname.equalsIgnoreCase(_colname)) {
			column = (Column)columns.get(_colname);
		}
	}
	/**
	 * zhangt�޸�
	 *
	 column = (Column) columns.get(colname.toUpperCase());
	 **/

	String type = column.getType();
	if (type.toUpperCase().indexOf("CHAR") >= 0) {
		ps.setString(columnIndex, (String) value);
		return;
	}
	if (type.equalsIgnoreCase("NUMBER") || type.equalsIgnoreCase("INT")) {
		if (column.getScale() == 0 && column.getPrecision() <= 12) {
			ps.setInt(columnIndex, ((Integer) value).intValue());
			return;
		}
		if (column.getScale() == 0 && column.getPrecision() > 12) {
			ps.setLong(columnIndex, ((Long) value).longValue());
			return;
		}
		if (column.getScale() > 0) {
			ps.setDouble(columnIndex, ((Double) value).doubleValue());
			return;
		}
	}
	if (type.equalsIgnoreCase("DATE")) {
		if (value instanceof java.sql.Date) {
			ps.setDate(columnIndex, (java.sql.Date) value);
			return;
		}
		if (value instanceof java.sql.Time) {
			ps.setTime(columnIndex, (java.sql.Time) value);
			return;
		}
		if (value instanceof java.util.Date) {
			ps.setTimestamp(
				columnIndex,
				(new java.sql.Timestamp(((java.util.Date) value).getTime())));
			return;
		}
		throw new MagicException(
			"Error 010 : Datetime object instance "
				+ value.getClass().getName()
				+ " no support");
		//010����
	}
}

/**
 * ��ݸ���(����һ���ֶ�)��
 * 	  item -- Hashtable	��Ҫ���µ����
 *
 * @see org.shaoye.magic.Operate#set(Object, Map)
 *
 */
public void set(Map item) throws SQLException {

	if (table == null || table.length() == 0)
		throw (new MagicException("Error 001 : table's name is null"));
	//001����

	sql = "";
	sql += "UPDATE " + table + " SET ";
	int size = item.size();
	String[] arrfield = new String[size];
	Object[] arrvalue = new Object[size];

	String condition_param = "";
	String key_value = "";
	String param = "";
	Hashtable _item = new Hashtable(item);
	int i = 0;

	for (java.util.Enumeration en = _item.keys(); en.hasMoreElements();) {
		String fieldName = (String) en.nextElement();
		arrfield[i] = fieldName;
		arrvalue[i] = _item.get(fieldName);

		if (fieldName.equals(this.key)) {
			key_value = (String) arrvalue[i];
			i++;
			continue;
		}

		sql = sql + fieldName + "=?";
		if (i < size - 1) {
			sql += ",";
			param += ",";
		}
		i++;
	}
	sql += " WHERE ";
	sql = sql + this.key + "=?";

	pstmt = conn.prepareStatement(sql);

	int flag = -1;
	for (int k = 0; k < arrfield.length; k++) {
		if (arrfield[k].equals(this.key)) {
			flag = k;
			continue;
		}
		if (flag >= 0)
			this.setData(pstmt, flag + 1, arrfield[k], arrvalue[k]);
		else
			this.setData(pstmt, k + 1, arrfield[k], arrvalue[k]);
	}
	this.setData(pstmt, arrfield.length, this.key, key_value);

	pstmt.executeUpdate();

}

/**
 * ɾ��һ���¼(����һ���ֶ�)
 * key -- ArrayList ��¼��������б�
 *
 * @see org.shaoye.magic.Operate#remove(Object)
 */
public void del(Collection collection) throws SQLException {

	if (table == null || table.length() == 0)
		throw (new MagicException("Error 001 : table's name is null"));
	//001����
	List list = new ArrayList();
	list = ( ArrayList )collection;

	sql = "";
	sql += "DELETE FROM " + table + " WHERE " + this.key + "=?";

	pstmt = conn.prepareStatement(sql);

	for( int i=0;i<list.size();i++ )
	{
		Column column = new Column();
		column = ( Column )list.get(i);
		this.setData(pstmt, 1, column.getName(), column.getDefaultValue());
		pstmt.addBatch();

	}

	pstmt.executeBatch();
}

/**
 * ȡ��һ���¼
 * key -- ��¼�������
 *
 * @see org.shaoye.magic.Operate#get(Object)
 */
public Map get(String key_value) throws SQLException {

	if (table == null || table.length() == 0)
		throw (new MagicException("Error 001 : table's name is null"));
	//001����
	Hashtable hashResult = new Hashtable();
	ResultSet rs = null;

	sql = "";
	sql += "SELECT * FROM " + table + " WHERE " + this.key + "=?";

	//pstmt =conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
	pstmt = conn.prepareStatement(sql);
	this.setData(pstmt, 1, this.key, key_value);
	rs = pstmt.executeQuery();

	if (rs.next()) {
		for (int s = 0; s < rs.getMetaData().getColumnCount(); s++) {
			hashResult.put(
				rs.getMetaData().getColumnName(s + 1),
				rs.getString(rs.getMetaData().getColumnName(s + 1)));
		}
	}
	if (rs != null)
		rs.close();

	return hashResult;
}

}