package dark.db.record;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * <p>Title:            數據庫查詢操作封裝類</p>
 * <p>Description:      定義了各種數據庫查詢方法</p>
 * <p>Copyright:        Copyright (c) 2003</p>
 * <p>Company:          cniti</p>
 * <p>Create Time:      2004-3-21 14:00:32</p>
 * @author             <a href="mailto:dark_he@hotmail.com">darkhe</a>
 * @version            1.0
 */
public class Query
{			
	private Connection conn;	

	private Statement stmt;
    
	// 表名
	private String table_name;
	   
	// 目標列名
	private String field;
  	  
	// 條件表達式
	private String condition;
  
	// 分組列名
	private String groupBy;
  
	// 分組的附加條件
	private String having;
  
	// 排序列名
	private String orderBy;
    
	// 升序排序
	public static final String ASC = "ASC";
  
	// 降序排序
	public static final String DESC = "DESC";
  
	// 是否去除重復記錄,default不去除
	private boolean isDistinct = false;	
	
	
	public Query(String table_name, Connection conn)
	{		
		this.table_name = table_name;
		this.conn = conn;
	}
	
	
	public Query()
	{
	}	
  

   /** 
	*  根據指定參數生成查詢結果集  
	*  type參數值:<br>
	*  1. TYPE_FORWARD_ONLY 結果集不可滾動<br>
	*  2. TYPE_SCROLL_INSENSITIVE 結果集可以滾動，但不反映數據庫的變化<br>
	*  3. TYPE_SCROLL_SENSITIVE 結果集可以滾動，并反映數據庫的變化<br>
	*  concurrency參數值:<br>
	*  1. CONCUR_READ_ONLY 不能用結果集更新數據庫<br>
	*  2. CONCUR_UPDATEABLE 可以用結果集更新數據庫<br>	
	*  @param type ResultSet類型值
	*  @param concurrency ResultSet同步值  
	*  @exception SQLException
	*/   
	public ResultSet executeQuery( int type,
								 	int concurrency )
	throws SQLException
	{	
		String sql = getSelectSQL();
		stmt = conn.createStatement( type , concurrency );
		
		//System.out.println("darkhe>>>" + this + ">>>SQL>>>" + sql );
		
		return stmt.executeQuery( sql );
	}
	  
	  
	/**
	 * 根據Default設置生成查詢結果集
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery()
	throws SQLException
	{
		String sql = getSelectSQL();		
		stmt = conn.createStatement();
		
		System.out.println(this + ">>>SQL>>>" + sql );
				
		return stmt.executeQuery( sql );
	}
	
	
	
	
	/**
	 * 根據Default設置生成查詢結果集
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String sql)
	throws SQLException
	{		
		stmt = conn.createStatement();
		
		System.out.println(this + ">>>SQL>>>" + sql );
				
		return stmt.executeQuery( sql );
	}	 
  
  
    
//    /**
//     * 將記錄集形式的查詢結果轉換為Record格式
//     * @param rs
//     * @return
//     * @throws SQLException
//     */
//    public static Record getRecordByResultSet(ResultSet rs)
//    throws SQLException
//    {
//    	Record r = new Record();
//    	ResultSetMetaData rsmd = rs.getMetaData();
//    	int columnCount = rsmd.getColumnCount();
//    	 
//    	for(int i=1; i<columnCount && rs.next(); i++)
//    	{
//    		String columnName = rsmd.getColumnName(i);
//    		int columnType = rsmd.getColumnType(i);
//    		
//    		if( columnType == Types.BIGINT )
//    		{
//    			r.put(columnName, rs.getBigDecimal(columnName).toBigInteger() );
//    		}
//    	}
//    	
//    	
//    	/*while( rs.next() )
//    	{
//    	}*/
//    	
//    	return r;
//    }
    
        


	/**
	 *  設置目標列名。
	 *  example: id, name, sex
	 *  如果沒有明確調用當前方法明確設置目標列名，
	 *  則默認為*(所有字段)
	 *  @param field 目標列名  
	 */
	public void setField(String field)
	{    
	  this.field = field;
	}
  
  
  
	/**
	*  設置分組列名
	*  @param groupBy 分組列名
	*/
	public void setGroupBy(String groupBy)
	{
	  this.groupBy = groupBy;
	}
 
  
  
	/**
	*  設置分組的附加條件  
	*  @param having 分組的附加條件        
	*/
	public void setHaving(String having)  
	{        
		this.having = having;
	}
  
  
	/**
	 * 指定查詢按ASC方式排序
	 * @param orderBy
	 */
	public void setOrderByASC(String orderBy)							                      
	{	  
	     
		this.orderBy = orderBy + " ASC";     	  
	}
	
	
	/**
	 * 指定查詢按DESC方式排序
	 * @param orderBy
	 */
	public void setOrderByDESC(String orderBy)						    	                       
	{	  	     
		this.orderBy = orderBy + " DESC";     	 
	}
  
  
  
	/**
	*  設置是否去除重復記錄
	*  @param isDistinct true為去除，false為不去除
	**/
	public void isDistinct(boolean isDistinct)
	{
	  this.isDistinct = isDistinct;
	}



	/**  
	*  @return 構成的Select SQL串值
	*  @throws SQLException  
	*/
	public String getSelectSQL() throws SQLException
	{
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT ");

		if (isDistinct)
		{
			sql.append("DISTINCT ");
		}

		if (field == null)
		{
			sql.append("*");
		}
		else
		{
			sql.append(field);
		}

		sql.append(" FROM ");

		if (sql == null)
		{
			throw new SQLException("未設置表名");
		}
		else
		{
			sql.append(table_name);
		}

		if (condition != null)
		{
			sql.append(" WHERE (");
			sql.append(condition);
			sql.append(") ");
		}

		if (orderBy != null)
		{
			sql.append(" ORDER BY ");
			sql.append(orderBy);			
		}
		
		if (groupBy != null)
		{
			sql.append(" GROUP BY ");
			sql.append(groupBy);
			if (having != null)
			{
				sql.append(" HAVING ");
				sql.append(having);
			}
			sql.append(" ");
		}

		return sql.toString();
	}
              
		
	/**
	 * @return
	 */
	public String getCondition()
	{
		return condition;
	}

	/**
	 * @return
	 */
	public String getTablename()
	{
		return table_name;
	}

	/**
	 * @param string
	 */
	public void setCondition(String string)
	{
		condition = string;
	}

	/**
	 * @param string
	 */
	public void setTablename(String string)
	{
		table_name = string;
	}


	public static void main(String[] args) throws SQLException
	{
		Connection conn = null;
		Query q = new Query( "Tablename",conn );		
		//q.setField("id,name");
		q.setCondition("id=100");
		q.setOrderByDESC("name");
		System.out.println("sql=" + q.getSelectSQL() );
	}
	
	
	/**
	 * @return
	 */
	public boolean isDistinct()
	{
		return isDistinct;
	}


	/**
	 * @param b
	 */
	public void setDistinct(boolean b)
	{
		isDistinct = b;
	}


	/**
	 * @return
	 */
	public String getField()
	{
		return field;
	}


	/**
	 * @return
	 */
	public String getGroupBy()
	{
		return groupBy;
	}


	/**
	 * @return
	 */
	public String getHaving()
	{
		return having;
	}


	/**
	 * @return
	 */
	public Connection getConnection()
	{
		return conn;
	}


	/**
	 * @param connection
	 */
	public void setConnection(Connection connection)
	{
		conn = connection;
	}
	
	
//	protected void finalize() throws Throwable
//	{		
//		super.finalize();
//		stmt.close();   // 
//		stmt = null;	// ??? 是應該手工關閉呢，還是不呢，得做個實驗。
//	}

}