tcusqlbuilder.java

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

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

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import com.jr81.source.common.TcuDataType;

/**
 * @author Sanlen
 *
 */
public class TcuSQLBuilder {
	private Connection con = null;
	private String SQL = "";
	private String TableName = "";
	private String Fields = " * ";
	private String CusSQL = "";
	private TcuSQLParts WhereItems=new TcuSQLParts();
	private TcuSQLParts InsertItems=new TcuSQLParts();
	private TcuSQLParts UpdateItems=new TcuSQLParts();
	private TcuSQLParts ProcItems=new TcuSQLParts();
	private boolean Debug=false;
	/**
	 * 
	 */
	/*public TcuSQLBuilder() {
		super();
		// TODO 自动生成构造函数存根
	}*/
	
	public TcuSQLBuilder(String tablename,Connection con) {
		super();
		// TODO Auto-generated constructor stub
		this.con = con;
		this.TableName = tablename;
	}
	
	/**
	 * @param con
	 */
	public TcuSQLBuilder(Connection con) {
		super();
		// TODO Auto-generated constructor stub
		this.con = con;
	}

	private void PrintDebugMsg(String msg){
		if (Debug){
			System.out.println(msg);
		}
	}
	
	public void Clear(){
		SQL="";
		TableName="";
		Fields=" * ";
		CusSQL = "";
		WhereItems.Clear();
		InsertItems.Clear();
		UpdateItems.Clear();
		ProcItems.Clear();
	}	
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO 自动生成方法存根
		SimpleDateFormat dateFormatter =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		//SimpleDateFormat dateFormatter =new SimpleDateFormat("yyyymmddhhmmsszzz");
		
		try {
			java.util.Date DateValue = dateFormatter.parse(new String("2007-09-09 10:10:10000"));
			System.out.println(DateValue);	
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	/**
	 * @return 返回 insertItems。
	 */
	public TcuSQLParts getInsertItems() {
		return InsertItems;
	}

	/**
	 * @param insertItems 要设置的 insertItems。
	 */
	public void setInsertItems(TcuSQLParts insertItems) {
		InsertItems = insertItems;
	}

	/**
	 * @return 返回 tableName。
	 */
	public String getTableName() {
		return TableName;
	}

	/**
	 * @param tableName 要设置的 tableName。
	 */
	public void setTableName(String tableName) {
		TableName = tableName;
	}

	/**
	 * @return 返回 updateItems。
	 */
	public TcuSQLParts getUpdateItems() {
		return UpdateItems;
	}

	/**
	 * @param updateItems 要设置的 updateItems。
	 */
	public void setUpdateItems(TcuSQLParts updateItems) {
		UpdateItems = updateItems;
	}
	
	private String GetInsertSQL() {
		if (InsertItems.GetCount() > 0) {
			String SQLValue = "";
			SQL = "Insert " + TableName + " (";
			SQLValue = " Values(";
			
			TcuSQLPart sql_item = InsertItems.ItemByID(0);
			//String FieldName1 = new String((InsertItems.(0)).getName());
			SQL += sql_item.getFieldName();
			SQLValue += "?";
			for (int i = 1; i < InsertItems.GetCount(); i++) {
				sql_item = InsertItems.ItemByID(i);
				//String FieldName = new String(((TcuBaseItemSTR)this.Item(i)).getName());
				SQL += "," +sql_item.getFieldName();
				SQLValue += ",?";
			}
			SQLValue += ")";
			SQL += ") "  + SQLValue;
			PrintDebugMsg(SQL);			
			return SQL;
		} else {
			return "";
		}		
	}
	
	private String GetUpdateSQL() {
		if (UpdateItems.GetCount() > 0) {
			
			SQL = "Update " + TableName + " SET ";
			TcuSQLPart sql_item = UpdateItems.ItemByID(0);
			SQL += sql_item.getFieldName() + "=?"; 
			for (int i = 1; i < UpdateItems.GetCount(); i++) {
				//String FieldName = new String(((TcuBaseItemSTR)this.Item(i)).getName());
				sql_item = UpdateItems.ItemByID(i);
				SQL += "," + sql_item.getFieldName() + "=?"; 
			}
			
			SQL+=GetWhereSQL();
			PrintDebugMsg(SQL);
			return SQL;
		} else {
			return "";
		}
	}
	
	private String GetDeleteSQL() {
		SQL="delete from "+TableName+GetWhereSQL();
		PrintDebugMsg(SQL);
		return SQL;
	}
	
	private String GetSelectSQL() {
		SQL="Select "+Fields+" from "+TableName+GetWhereSQL();
		PrintDebugMsg(SQL);
		return SQL;
	}
	
	private String GetProcSQL() {
		SQL="{call "+TableName+"(";
		if (ProcItems.GetCount() > 0) {
			SQL += "?"; 
			for (int i = 1; i < ProcItems.GetCount(); i++) {
				SQL += "," + "?"; 
			}
		}
		SQL+=")}";
		PrintDebugMsg(SQL);
		return SQL;
	}
	
	private String GetWhereSQL() {
		if (WhereItems.GetCount() > 0) {
			TcuSQLPart sql_item = WhereItems.ItemByID(0);
			//SQL = "Delete " + table_name + " WHERE ";
			SQL =" where " +sql_item.getFieldName() +GetOperator(sql_item.getOperator()); 
			for (int i = 1; i < WhereItems.GetCount(); i++) {
				//String FieldName = new String(((TcuBaseItemSTR)this.Item(i)).getName());
				sql_item = WhereItems.ItemByID(i);
				SQL +=GetJoinType(sql_item.getJoinType()) + sql_item.getFieldName() +GetOperator(sql_item.getOperator()); 
			}
			SQL+=CusSQL;
			return SQL;
		} else {
			if (CusSQL.equals("")){
				return "";
			}else{
				SQL=" where "+CusSQL;
				return SQL;
			}
		}
	}

	private String GetOperator(Integer operator){
		switch (operator.intValue()) {
		case TcuOperator.Equals:{ return "=?";}
		case TcuOperator.Like:{ return " like '%'?'%'";}
		case TcuOperator.In:{ return " in (?)";}
		case TcuOperator.Between:{ return "between ? and ?";}
		case TcuOperator.Less:{ return "<?";}
		case TcuOperator.More:{ return ">?";}
		default: {return "=";}
		}
	}
	
	private String GetJoinType(Integer JoinType){
		if (JoinType.intValue()==TcuJoinType.Or){
			return " or ";
		}else{
			return " and ";
		}
	}
	
	private void SetPstmtValue(PreparedStatement pstmt,int index, TcuSQLPart BaseValue) throws SQLException {
		switch (BaseValue.getDataType().intValue()) {		
		case TcuDataType.String:
			try {
					if (BaseValue.getFieldValue() == null) { 
						pstmt.setString(index, "");						
					} else {
						String value = new String(BaseValue.getFieldValue());
						pstmt.setString(index, value);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					pstmt.setString(index, "");
				}
		break;
		case TcuDataType.Text:
			try {
					if (BaseValue.getFieldValue() == null) { 
						pstmt.setString(index, "");						
					} else {
						String value = new String(BaseValue.getFieldValue());
						pstmt.setString(index, value);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					pstmt.setString(index, "");
				}
		break;
		case TcuDataType.Boolean:
			try {
				if (BaseValue.getFieldValue() == null) { 
					pstmt.setBoolean(index, false);						
				} else {
					String value = new String(BaseValue.getFieldValue());
					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.Integer:
			try {
				if (BaseValue.getFieldValue() == null) { 
					pstmt.setString(index, null);						
				} else {
					String value = new String(BaseValue.getFieldValue());
					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.Float:
			try {
				if (BaseValue.getFieldValue() == null) { 
					pstmt.setString(index, null);						
				} else {
					String value = new String(BaseValue.getFieldValue());
					pstmt.setFloat(index, Float.parseFloat(value));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				pstmt.setFloat(index, 0);	
			}
		break;
		case TcuDataType.ByteArray:
			if (BaseValue.getFieldValue() == null) { 
				//pstmt.setString(index, null);	
				pstmt.setNull(index,java.sql.Types.BINARY);
			} else {
				pstmt.setBytes(index, BaseValue.getFieldValue());
			}
		break;
		case TcuDataType.DateTime:
			if (BaseValue.getFieldValue() == null) { 
				pstmt.setDate(index, null);					
			} else {
				String value = new String(BaseValue.getFieldValue());
			
				if (value.equals("")){
					pstmt.setDate(index, null);
				}
				else{
					pstmt.setString(index,value);
					//SimpleDateFormat dateFormatter =new SimpleDateFormat("yyyyMMddHHmmssSSS");
					/*SimpleDateFormat dateFormatter =new SimpleDateFormat("yyyy-mm-dd hh:mm:ss");
					
					try {
						Date DateValue = (Date) dateFormatter.parse(value);
						pstmt.setDate(index, (java.sql.Date) DateValue);
					} catch (ParseException e) {
						// TODO Auto-generated catch block
						GregorianCalendar gc=new GregorianCalendar();
						pstmt.setDate(index, (java.sql.Date) gc.getTime());
						e.printStackTrace();
					}*/
				}
			}
		break;
		}
	}
	
	public boolean ExecInsert() {
		boolean bResult = false;
		
		try {
			PreparedStatement pstmt = con.prepareStatement(GetInsertSQL());
			
			for (int i = 0; i < InsertItems.GetCount(); i++) {
				TcuSQLPart sql_item = InsertItems.ItemByID(i);
				SetPstmtValue(pstmt, i+1, sql_item);
			}
			
			pstmt.executeUpdate();
			bResult = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			bResult = false;
		}
		
		return bResult;
	}
	
	public boolean ExecUpdate() {
		boolean bResult = false;
		try {
			PreparedStatement pstmt = con.prepareStatement(GetUpdateSQL());
			int id=0;
			
			for (int i = 0; i < UpdateItems.GetCount(); i++) {
				TcuSQLPart sql_item = UpdateItems.ItemByID(i);
				id+=1;
				SetPstmtValue(pstmt, id, sql_item);
			}
			
			for (int i = 0; i < WhereItems.GetCount(); i++) {
				TcuSQLPart sql_item = WhereItems.ItemByID(i);
				id+=1;
				SetPstmtValue(pstmt, id, sql_item);
			}
			
			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());
			
			for (int i = 0; i < WhereItems.GetCount(); i++) {
				TcuSQLPart sql_item = WhereItems.ItemByID(i);
				SetPstmtValue(pstmt, i+1, sql_item);
			}
			
			pstmt.executeUpdate();
			bResult = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
			bResult = false;
		}
		
		return bResult;
	}
	
	public ResultSet ExecProc(){
		try {
			CallableStatement cStmt = con.prepareCall(GetProcSQL());
			for (int i = 0; i < ProcItems.GetCount(); i++) {
				TcuSQLPart sql_item = ProcItems.ItemByID(i);
				SetPstmtValue(cStmt, i+1, sql_item);
			}
			return  cStmt.executeQuery();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return null;
	}
	
	public ResultSet ExecSelect() {
		ResultSet bResult = null;		
		try {
			PreparedStatement pstmt = con.prepareStatement(GetSelectSQL());
			
			for (int i = 0; i < WhereItems.GetCount(); i++) {
				TcuSQLPart sql_item = WhereItems.ItemByID(i);
				SetPstmtValue(pstmt, i+1, sql_item);
			}
			
			bResult=pstmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
			bResult = null;
		}		
		return bResult;
	}
	
	/**
	 * @return 返回 whereItems。
	 */
	public TcuSQLParts getWhereItems() {
		return WhereItems;
	}

	/**
	 * @param whereItems 要设置的 whereItems。
	 */
	public void setWhereItems(TcuSQLParts whereItems) {
		WhereItems = whereItems;
	}

	/**
	 * @return 返回 procItems。
	 */
	public TcuSQLParts getProcItems() {
		return ProcItems;
	}

	/**
	 * @param procItems 要设置的 procItems。
	 */
	public void setProcItems(TcuSQLParts procItems) {
		ProcItems = procItems;
	}

	/**
	 * @param fields 要设置的 fields。
	 */
	public void setFields(String fields) {
		Fields = fields;
	}

	/**
	 * @param cusSQL 要设置的 cusSQL。
	 */
	public void setCusSQL(String cusSQL) {
		CusSQL = cusSQL;
	}

	/**
	 * @param debug the debug to set
	 */
	public void setDebug(boolean debug) {
		Debug = debug;
	}

}