execute.java

基于数据库操作的封装

/*
 * Created on 2003-6-6
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
package org.shaoye.magic.oracle;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Enumeration;
import java.util.Vector;
import java.util.Map;
import java.util.Hashtable;
import java.util.ArrayList;
import java.util.List;
import java.util.Iterator;

import org.shaoye.magic.Item;
import org.shaoye.magic.Operate;
import org.shaoye.magic.Column;
import org.shaoye.magic.MagicException;
import org.shaoye.magic.PrimaryKeyObj;

/**
 * @author ����ү
 *
 * To change this generated comment edit the template variable "typecomment":
 * Window>Preferences>Java>Templates.
 * To enable and disable the creation of type comments go to
 * Window>Preferences>Java>Code Generation.
 */
public class Execute implements Operate {

	protected Connection conn = null;
	protected Statement st = null;
	protected PreparedStatement pstmt = null;
	protected String sql = "";
	protected Hashtable columns = new Hashtable();
	protected Column column = new Column();
	protected String table = "";
	protected String key = "";

	/**
	 * ���췽����������ʵ��ֻ��ִ��SQL���
	 * */
	public Execute(Connection connection) {
		this.conn = connection;
	}

	/**
	 * ���췽����������ʵ���ܹ����һ�����
	 * */
	public Execute(Connection connection, String table) throws SQLException {
		if (table == null || table.length() == 0)
			throw (new MagicException("Error 001 : table's name is null"));
		//001����
		this.conn = connection;
		this.table = table;
		this.initColumn();
	}

	/**
	 * ���췽����������ʵ�������һ��IJ���
	 * �������һЩ���IJ���
	 * */
	public Execute(Connection connection, String table, String key_field)
		throws SQLException {
		if (table == null || table.length() == 0)
			throw (new MagicException("Error 001 : table's name is null"));
		//001����
		if (table == null || table.length() == 0)
			throw (new MagicException("Error 002 : key_field is null"));
		//002����
		this.conn = connection;
		this.table = table;
		this.key = key_field;
		this.initColumn();
	}

	/**
	 * @see org.shaoye.magic.Operate#add(Map)
	 */
	public void add(Map item) throws SQLException {
		if (table == null || table.length() == 0)
			throw (new MagicException("Error 001 : table's name is null"));
		//001����
		sql = "";
		sql += "insert into " + table + " (";
		int size = item.size();
		String[] arrfield = new String[size];
		Object[] arrvalue = new Object[size];
		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);
			sql += fieldName;
			param += "?";
			if (i < size - 1) {
				sql += ",";
				param += ",";
			}
			i++;
		}
		sql += ") values(" + param + ")";
		pstmt = conn.prepareStatement(sql);
		for (int j = 0; j < arrfield.length; j++) {
			this.setData(pstmt, j + 1, arrfield[j], arrvalue[j]);
		}
		pstmt.executeUpdate();

	}

	/**
	 * ��ݸ��£�
	 *    key  -- PrimaryKeyObj
	 * 	  item -- Hashtable	��Ҫ���µ����
	 *
	 * @see org.shaoye.magic.Operate#set(Object, Map)
	 *
	 */
	public void set(Object key, 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];

		PrimaryKeyObj _pk = new PrimaryKeyObj();
		_pk = (PrimaryKeyObj) key;

		String condition_param = "";
		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);
			sql = sql + fieldName + "=?";
			if (i < size - 1) {
				sql += ",";
				param += ",";
			}
			i++;
		}
		sql += " WHERE ";

		for (int j = 0; j < _pk.getPrimaryKeySize(); j++) {
			Column col = new Column();
			sql = sql + ((Column) _pk.getPrimaryKey(j)).getName() + "=?";
			if (j < _pk.getPrimaryKeySize() - 1)
				sql += " and ";
		}

		pstmt = conn.prepareStatement(sql);

		for (int k = 0; k < arrfield.length; k++) {
			this.setData(pstmt, k + 1, arrfield[k], arrvalue[k]);
		}

		for (int l = 0; l < _pk.getPrimaryKeySize(); l++) {
			this.setData(
				pstmt,
				l + 1 + arrfield.length,
				((Column) _pk.getPrimaryKey(l)).getName(),
				((Column) _pk.getPrimaryKey(l)).getDefaultValue());
		}

		pstmt.executeUpdate();

	}

	/**
	 * @see org.shaoye.magic.Operate#set(Object, String, Object)
	 */
	public void set(Object key, String field, Object value)
		throws SQLException {
	}

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

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

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

		for (int j = 0; j < _pk.getPrimaryKeySize(); j++) {
			Column col = new Column();
			sql = sql + ((Column) _pk.getPrimaryKey(j)).getName() + "=?";
			if (j < _pk.getPrimaryKeySize() - 1)
				sql += " and ";
		}
		pstmt =
			conn.prepareStatement(
				sql);

		for (int l = 0; l < _pk.getPrimaryKeySize(); l++) {
			this.setData(
				pstmt,
				l + 1,
				((Column) _pk.getPrimaryKey(l)).getName(),
				((Column) _pk.getPrimaryKey(l)).getDefaultValue());
		}
		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;
	}

	/**
	 * ɾ��һ���¼
	 * key -- ArrayList ��¼��������б�
	 *
	 * @see org.shaoye.magic.Operate#remove(Object)
	 */
	public void remove(Object key) 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) key;
		Iterator iterator = list.iterator();
		PrimaryKeyObj _pk = new PrimaryKeyObj();

		sql = "";
		sql += "DELETE FROM " + table + " WHERE ";
		_pk = (PrimaryKeyObj) list.get(0);

		for (int j = 0; j < _pk.getPrimaryKeySize(); j++) {
			Column col = new Column();
			sql = sql + ((Column) _pk.getPrimaryKey(j)).getName() + "=?";
			if (j < _pk.getPrimaryKeySize() - 1)
				sql += " and ";
		}

		pstmt = conn.prepareStatement(sql);
		while (iterator.hasNext()) {
			_pk = new PrimaryKeyObj();
			_pk = ( PrimaryKeyObj )iterator.next();
			for (int l = 0; l < _pk.getPrimaryKeySize(); l++) {
				this.setData(
					pstmt,
					l + 1,
					((Column) _pk.getPrimaryKey(l)).getName(),
					((Column) _pk.getPrimaryKey(l)).getDefaultValue());
			}
			pstmt.addBatch();
		}

		pstmt.executeBatch();
	}

	/**
	 * MYSQL ʹ�õķ���
	 *
	 * @see org.shaoye.magic.Operate#getAllItem()
	 */
	public Collection getAllItem() 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); //Mysqlʹ��

		rs = pstmt.executeQuery();

		if (rs.next()) {