tcupreparedstatement.java

为公司做的质量考核接口源码,用spring,hibernate,XML实现,对XML接口编程很有帮助

/**
 * 
 */
package com.jr81.source.sql;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.GregorianCalendar;



import com.jr81.source.common.TcuDataType;
import com.jr81.source.dataset.TcuDataRecord;
import com.jr81.source.dataset.TcuFieldValue;
import com.jr81.source.stream.TcuBaseItemSTR;
import com.jr81.source.stream.TcuBaseItemsSTR;


/**
 * @author Yanslat
 *
 */
public class TcuPreparedStatement extends TcuBaseItemsSTR{
	//private PreparedStatement pstmt = null;
	private Connection con = null;
	private String SQL = "";
	private String TableName = "";
	/**
	 * @param tablename
	 * @param con
	 */
	public TcuPreparedStatement(String tablename,Connection con) {
		super();
		// TODO Auto-generated constructor stub
		this.con = con;
		this.TableName = tablename;
	}
	
	/**
	 * @param con
	 */
	public TcuPreparedStatement(Connection con) {
		super();
		// TODO Auto-generated constructor stub
		this.con = con;
	}

	public TcuPreparedStatement() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	private String GetInsertSQL(String table_name) {
		if (this.GetCount() > 0) {
			String SQLValue = "";
			SQL = "Insert " + table_name + " (";
			SQLValue = " Values(";
			String FieldName1 = new String(((TcuBaseItemSTR)this.Item(0)).getName());
			SQL += FieldName1;
			SQLValue += "?";
			for (int i = 1; i < this.GetCount(); i++) {
				String FieldName = new String(((TcuBaseItemSTR)this.Item(i)).getName());
				SQL += "," + FieldName;	
				SQLValue += ",?";
			}
			SQLValue += ")";
			SQL += ") "  + SQLValue;
						
			return SQL;
		} else {
			return "";
		}
		
	}
	
	private String GetUpdateSQL(String table_name,String update_condition) {
		if (this.GetCount() > 0) {
			
			SQL = "Update " + table_name + " SET ";
			SQL += this.GetItem(0) + "=?"; 
			for (int i = 1; i < this.GetCount(); i++) {
				String FieldName = new String(((TcuBaseItemSTR)this.Item(i)).getName());
				SQL += "," + FieldName + "=?"; 
			}
			
			if (update_condition.length() > 0) {
				SQL += " where " + update_condition;
			}
			return SQL;
		} else {
			return "";
		}
	}
	
	private String GetDeleteSQL(String table_name) {
		if (this.GetCount() > 0) {
			
			SQL = "Delete " + table_name + " WHERE ";
			SQL += this.GetItem(0) + "=?"; 
			for (int i = 1; i < this.GetCount(); i++) {
				String FieldName = new String(((TcuBaseItemSTR)this.Item(i)).getName());
				SQL += " and " + FieldName + "=?"; 
			}
			
			return SQL;
		} else {
			return "";
		}
	}
	
	public void AddDataRecord(TcuDataRecord DataRecord) {
		
		int iCount = DataRecord.GetCount();
		for (int i = 0; i < iCount; i++) {
			String FieldName = ((TcuFieldValue)DataRecord.GetItem(i)).getFieldName();
			this.AddItem(FieldName,((TcuFieldValue)DataRecord.GetItem(i)).getFieldValue(), TcuDataType.AsString);
		}
	}
		

	
	private void AddPstmtValue(PreparedStatement pstmt,int index, TcuBaseItemSTR BaseValue) throws SQLException {
		switch (BaseValue.getItemTag()) {
		case TcuDataType.AsString:
			try {
					String value = new String(BaseValue.getValue());
					pstmt.setString(index, value);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					pstmt.setString(index, "");
				}
		break;
		case TcuDataType.AsBoolean:
			try {
				String value = new String(BaseValue.getValue());
				if (value.equals("1")) {
					pstmt.setBoolean(index, true);
				} else {
					pstmt.setBoolean(index, false);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				pstmt.setBoolean(index, true);
			}
		break;
		case TcuDataType.AsInteger:
			try {
				String value = new String(BaseValue.getValue());
				if (value.equals("")) {
					value = "0";
				}
				pstmt.setInt(index, Integer.valueOf(value).intValue());	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				pstmt.setInt(index, 0);	
			}
		break;
		case TcuDataType.AsFloat:
			try {
				String value = new String(BaseValue.getValue());
				pstmt.setFloat(index, Float.parseFloat(value));	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				pstmt.setFloat(index, 0);	
			}
		break;
		case TcuDataType.AsByte:
			pstmt.setBytes(index, BaseValue.getValue());
		break;
		case TcuDataType.AsDateTime:
			String value = new String(BaseValue.getValue());
			SimpleDateFormat dateFormatter =new SimpleDateFormat("yyyyMMddHHmmssSSS");
			
			try {
				Date DateValue = (Date) dateFormatter.parse(value);
				pstmt.setDate(index, DateValue);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				GregorianCalendar gc=new GregorianCalendar();
				pstmt.setDate(index, (Date) gc.getTime());
				e.printStackTrace();
			}
		break;
		}
	}
	
	public boolean ExecInsert() {
		boolean bResult = false;
		
		try {
			PreparedStatement pstmt = con.prepareStatement(GetInsertSQL(TableName));
			
			for (int i = 0; i < this.GetCount(); i++) {
				TcuBaseItemSTR BaseItem = Item(i);
				AddPstmtValue(pstmt, i+1, BaseItem);
			}
			
			pstmt.executeUpdate();
			bResult = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
			bResult = false;
		}
		
		return bResult;
	}
	
	public boolean ExecUpdate(String update_condition) {
		boolean bResult = false;
		try {
			PreparedStatement pstmt = con.prepareStatement(GetUpdateSQL(TableName, update_condition));
			
			for (int i = 0; i < this.GetCount(); i++) {
				TcuBaseItemSTR BaseItem = Item(i);
				AddPstmtValue(pstmt, i+1, BaseItem);
			}
			
			pstmt.executeUpdate();
			bResult = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
			bResult = false;
		}
		return bResult;
	}
	
	public boolean ExecDelete() {
		boolean bResult = false;
		
		try {
			PreparedStatement pstmt = con.prepareStatement(GetDeleteSQL(TableName));
			
			for (int i = 0; i < this.GetCount(); i++) {
				TcuBaseItemSTR BaseItem = Item(i);
				AddPstmtValue(pstmt, i+1, BaseItem);
			}
			
			pstmt.executeUpdate();
			bResult = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
			bResult = false;
		}
		
		return bResult;
	}
	/**
	 * @return Returns the tableName.
	 */
	public String getTableName() {
		return TableName;
	}

	/**
	 * @param tableName The tableName to set.
	 */
	public void setTableName(String tableName) {
		TableName = tableName;
	}
	

}