All Downloads are FREE. Search and download functionalities are using the official Maven repository.
  • Log In
  • Register

    • com.tsc9526.monalisa.orm.Query Maven / Gradle / Ivy

    There is a newer version: 2.2.0
    Show newest version
    /*******************************************************************************************
 *	Copyright (c) 2016, zzg.zhou([email protected])
 * 
 *  Monalisa is free software: you can redistribute it and/or modify
 *	it under the terms of the GNU Lesser General Public License as published by
 *	the Free Software Foundation, either version 3 of the License, or
 *	(at your option) any later version.

 *	This program is distributed in the hope that it will be useful,
 *	but WITHOUT ANY WARRANTY; without even the implied warranty of
 *	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *	GNU Lesser General Public License for more details.

 *	You should have received a copy of the GNU Lesser General Public License
 *	along with this program.  If not, see .
 *******************************************************************************************/
package com.tsc9526.monalisa.orm;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;

import com.tsc9526.monalisa.orm.datasource.DBConfig;
import com.tsc9526.monalisa.orm.datasource.DataSourceManager;
import com.tsc9526.monalisa.orm.datasource.DbProp;
import com.tsc9526.monalisa.orm.dialect.Dialect;
import com.tsc9526.monalisa.orm.executor.BatchSqlExecutor;
import com.tsc9526.monalisa.orm.executor.BatchStatementExecutor;
import com.tsc9526.monalisa.orm.executor.CacheExecutor;
import com.tsc9526.monalisa.orm.executor.Execute;
import com.tsc9526.monalisa.orm.executor.ResultExecutor;
import com.tsc9526.monalisa.orm.executor.ResultHandler;
import com.tsc9526.monalisa.orm.executor.ResultLoadExecutor;
import com.tsc9526.monalisa.orm.executor.ResultSetExecutor;
import com.tsc9526.monalisa.orm.executor.ResultSetsExecutor;
import com.tsc9526.monalisa.orm.executor.UpdateExecutor;
import com.tsc9526.monalisa.orm.generator.DBExchange;
import com.tsc9526.monalisa.tools.agent.AgentClass;
import com.tsc9526.monalisa.tools.cache.Cache;
import com.tsc9526.monalisa.tools.cache.CacheKey;
import com.tsc9526.monalisa.tools.datatable.DataMap;
import com.tsc9526.monalisa.tools.datatable.DataTable;
import com.tsc9526.monalisa.tools.datatable.Page;
import com.tsc9526.monalisa.tools.io.MelpClose;
import com.tsc9526.monalisa.tools.logger.Logger;
import com.tsc9526.monalisa.tools.string.MelpSQL;
 

/**
 * Database query, usage: 

    
 * 
 * 
 * @DB(url="jdbc:mysql://127.0.0.1:3306/test", username="root", password="root")
    
 * public interface TestDB {
    
 * 	     public final static DBConfig DB=DBConfig.fromClass(TestDB.class)
    
 * }	
 *      
 *

     
 * 
 * Query q=new Query(TestDB.DB); //or: Query q=TestDB.DB.createQuery(); 
    
 * q.add("select * from xxx where id=?",1); 
    
 * List<Result> r=q.getList(Result.class);   
    
 * Page<Result> p=q.getPage(Result.class,10,0);
    
 * Result       x=q.getResult(Result.class);
    
 *     
 * 
 * @see com.tsc9526.monalisa.orm.annotation.DB
 * 
 * @author zzg.zhou([email protected])
 */

@SuppressWarnings({"unchecked"})
public class Query {	
	static Logger logger=Logger.getLogger(Query.class.getName());
	  
	/**
	 * Create a database query dynamically
	 * 
	 * @param theQueryClass Database query class which can be loaded dynamically.
	 * @param  the class type		
	 * @return create new instance.
	 */
	public static  T create(Class theQueryClass){
		 return AgentClass.createAgent(theQueryClass);
	}
 	
	/**
	 * Weather if show the running sql, default: false
	 */
	private Boolean debugSql=null;
	 
	protected DataSourceManager dsm=DataSourceManager.getInstance();
	
	protected StringBuffer  sql=new StringBuffer();
	protected List  parameters=new ArrayList();
 	 
	protected DBConfig      db;
 	
	//0: no cache, -1: no expired
	protected long ttlInSeconds=0;
	
	protected Boolean readonly;
 	
	protected List> batchParameters=new ArrayList>();
	
	protected Object tag;
	
	protected PrintWriter writer=null;
	
	
	protected Cache cache;
	
	public Query(){		 
	}
	
	public Query(DBConfig db){
		 this.db=db;
	}	 
	  
	public Query setDebugSql(boolean debugSql){
		this.debugSql=debugSql;
		return this;
	}
	
	public boolean isDebugSql(){
		return debugSql==null?false:debugSql;
	}
	
	public  T getTag(){
		return (T)tag;
	}
	
	public void setTag(Object tag){
		this.tag=tag;
	}
	 
	public Query notin(Object value,Object... otherValues){
		Dialect dialect=db==null?Dialect.SQLDialect:getDialect();
		
		return dialect.notin(this, new Object[]{value,otherValues});
	}
	 
	public Query in(Object value,Object... otherValues){
		Dialect dialect=db==null?Dialect.SQLDialect:getDialect();
		 
		return dialect.in(this, new Object[]{value,otherValues});
	}
	  
	public Query addQuery(Query q){	
		return add(q.getSql(),q.getParameters());
	}
	
	public Query add(String segment,Object ... args){		
		if(segment!=null && segment.length()>0){			 
			sql.append(segment);
		}
		
		if(args!=null){
			for(Object arg:args){
				if(arg instanceof Collection){
					for(Object x:((Collection)arg)){
						parameters.add(x);
					}
				}else{
					parameters.add(arg);
				}
			}
		}
		return this;
	}	
  
	/**
	 * Add segment to SQL only if the args is not empty
	 * 
	 * @param segment the SQL segment
	 * @param args  the SQL parameters
	 * @return  this Query
	 */
	public Query addIfNotEmpty(String segment,Object ... args){
		if(args!=null && args.length==1){
			if(args[0]==null){
				return this;
			}else if(args[0] instanceof String){
				String s=(String)args[0];
				if(s.trim().length()<1){
					return this;
				}
			}
		}
		
		return add(segment, args);
	}
	
	/**
	 * Add segment to SQL only if 'condition' is true
	 * 
	 * @param condition true: add the segment and args to SQL otherwise ignore.
	 * @param segment the SQL segment
	 * @param args the SQL parameters
	 * @return this Query
	 */
	public Query addIf(boolean condition,String segment,Object ... args){
		if(condition){
			add(segment, args);
		}
		return this;
	}
	
	public boolean isEmpty(){
		return sql.length()==0;
	}
	
	/**
	 * 
	 * @return Original SQL, maybe "?" in it.
	 */
	public String getSql() {
		String r=sql.toString();
		return r;
	}
	
	/**
	 * 
	 * get the SQL which replace ? to the real value
	 * 
	 * @return the executable SQL
	 */
	public String getExecutableSQL() {
		 return MelpSQL.getExecutableSQL(getSql(), parameters);
	}

	/**
	 * 
	 * Clear the SQL statement and parameters.
	 * 
	 * @return this Query
	 */
	public Query clear(){		 
		if(this.sql.length()>0){
			this.sql.delete(0,this.sql.length());
		}		 
		this.parameters.clear();
		
		return this;
	}

	public int parameterCount(){
		return parameters.size();
	}
 
	public List getParameters() {
		return parameters;
	}
	 
	public Query clearParameters(){
		this.parameters.clear();
		return this;
	}
	
	public Query setParameters(List parameters) {
		this.parameters = parameters;
		
		return this;
	}
	
	public Query addBatch(Object... parameters) {
		if(this.parameters!=null && this.parameters.size()>0){
			this.batchParameters.add(this.parameters);
		}
		
		this.batchParameters.add(Arrays.asList(parameters));
				
		return this;
	}
	
	protected Connection getConnectionFromTx(Tx tx) throws SQLException{
		return tx.getConnection(db);		 
	}
	
	protected Connection getConnectionFromDB(boolean autoCommit) throws SQLException{
		Connection conn=db.getDataSource().getConnection();
		conn.setAutoCommit(autoCommit);
		return conn;
	}
	

	/**
	 * Execute the SQL
	 * 
	 * @return the effected number of rows
	 */
	public int execute(){
		return doExecute(new UpdateExecutor());
	}
	  
	/**
	 * Execute sql statements in transaction
	 * @param sqls sql statements
	 * @return each result of sql statements
	 */
	public int[] executeBatch(final String[] sqls){
		return Tx.execute(new Tx.Atom() {
			public int[] execute() throws Throwable {
				return doExecute(new BatchSqlExecutor(sqls));
			}
		});
	}
	
	/**
	 * Execute in transaction
	 * 
	 * @return each result of sql statements
	 */
	public int[] executeBatch(){
		return Tx.execute(new Tx.Atom() {
			public int[] execute() throws Throwable {
				return doExecute(new BatchStatementExecutor(batchParameters));
			}
		});
	}
	 

	public  X execute(Execute execute){
		return doExecute(execute);
	}	
	
	protected  X doCacheExecute(Execute x){
		CacheExecutor ce=new CacheExecutor(this, x);
		return doExecute(ce);
	}
	
	protected  X doExecute(Execute x){
		Tx tx=Tx.getTx();
		
		Connection conn=null;
		PreparedStatement pst=null;
		try{
			conn= tx==null?getConnectionFromDB(true):getConnectionFromTx(tx);
			 
			pst=x.preparedStatement(conn,getSql());
			if(pst!=null){ 
				MelpSQL.setPreparedParameters(pst, parameters);
				logSql(getExecutableSQL());
			}
			return x.execute(conn,pst);
		}catch(SQLException e){
			String executeSQL=sql.toString();
			try{
				executeSQL=getExecutableSQL();
			}catch(Exception ex){}
			
			throw new RuntimeException("SQL Exception: "+e.getMessage()+"\r\n========================================================================\r\n"
					                  +executeSQL+"\r\n========================================================================",e);
		}finally{
			MelpClose.close(pst);
			
			if(tx==null){
				MelpClose.close(conn);
			}
		}
	}
	 
 
	/**
	 * Get single result
	 * 
	 * @return DataMap
	 */
	public DataMap getResult(){
		return getResult(DataMap.class);				
	}
	 

	/**
	 * Get multi ResultSets from the SQL query
	 * 
	 * @return multi ResultSets
	 */
	public List> getAllResults(){
		queryCheck();
		 
		int deepth = DbProp.PROP_DB_MULTI_RESULTSET_DEEPTH.getIntValue(db,100);
		ResultHandler resultHandler=new ResultHandler(this,DataMap.class);
		
		return doCacheExecute(new ResultSetsExecutor(resultHandler,deepth));  
	}
	 
	/**
	 * Translate data to the resultClass
	 *  
	 * @param resultClass translate DataMap to the result class
	 * @param  result type
	 * @return the result object
	 */
	public  T getResult(final Class resultClass){
		return getResult(new ResultHandler(this,resultClass));
	}
	
	/**
	 * Translate data to the resultClass
	 *  
	 * @param resultHandler handle result set
	 * @param  result type
	 * @return the result object
	 */
	public  T getResult(final ResultHandler resultHandler){
		if(!doExchange()){			
			queryCheck();
			
			return doCacheExecute(new ResultExecutor(resultHandler));	 
		}else{
			return null;
		}
	}
	
	/**
	 * @return List DataMap
	 */
	public DataTable getList() {
		return getList(DataMap.class);
	}
	
	/**
	 * @param resultClass the result class
	 * @param  result type
	 * @return List Data
	 */
	public  DataTable getList(final Class resultClass) {
		return getList(new ResultHandler(this, resultClass));
	}
	
	
	/**
	 * @param limit The max number of records for this query
	 * @param offset Base 0, the first record is 0
	 * @return List DataMap
	 */
	public DataTable getList(int limit,int offset) {		 
		return getList(DataMap.class,limit,offset);		 
	}
	
	/**
	 * @param resultClass the result class
	 * @param limit The max number of records for this query
	 * @param offset Base 0, the first record is 0
	 * @param  result type
	 * @return List Data
	 */
	public  DataTable getList(Class resultClass,int limit,int offset) {		 
		return getList(new ResultHandler(this,resultClass),limit,offset);
	}	
	
	/**
	 * @param resultHandler handle result set
	 * @param limit The max number of records for this query
	 * @param offset Base 0, the first record is 0
	 * @param  result type
	 * @return List Data
	 */
	public  DataTable getList(ResultHandler resultHandler,int limit,int offset) {
		Query listQuery=getDialect().getLimitQuery(this, limit, offset);
		DataTable  list=listQuery.getList(resultHandler);
			
		return list;
	}	 

	/**
	 * @param resultHandler handle result set
	 * @param  result type
	 * @return List Data
	 */
	public  DataTable getList(final ResultHandler resultHandler) {
		if(!doExchange()){		 
			queryCheck();
			
			return doCacheExecute(new ResultSetExecutor(resultHandler));
		}else{
			return new DataTable();
		}
	}
	
	/**
	 * @param limit The max number of records for this query
	 * @param offset   Base 0, the first record is 0
	 * @return Page Data
	 */
	public Page getPage(int limit,int offset) {
		return getPage(DataMap.class,limit, offset);
	}
	
	/**
	 * @param resultClass the result class
	 * @param limit The max number of records for this query
	 * @param offset   Base 0, the first record is 0
	 * @param  result type
	 * @return Page Data
	 */
	public  Page getPage(Class resultClass,int limit,int offset) {
		return getPage(new ResultHandler(this,resultClass), limit, offset);
	}
	
	/**
	 * @param resultHandler handle result set
	 * @param limit The max number of records for this query
	 * @param offset   Base 0, the first record is 0
	 * @param  result type
	 * @return Page Data
	 */
	public  Page getPage(ResultHandler resultHandler,int limit,int offset) {
		if(!doExchange()){			
			queryCheck();
			
			Query countQuery=getDialect().getCountQuery(this);			
			long total=countQuery.getResult(Long.class);			
			 
			Query listQuery=getDialect().getLimitQuery(this, limit, offset);
			DataTable  list=listQuery.getList(resultHandler);
			 
			Page page=new Page(list,total,limit,offset);
		
			return page;
		}else{
			return new Page();		 
		}
	}
	
	public  T load(final T result){
		if(!doExchange()){			 
			queryCheck();
			
			ResultHandler resultHandler=new ResultHandler(this,(Class)result.getClass());
			
			return doCacheExecute(new ResultLoadExecutor(resultHandler, result)); 
		}else{
			return result;
		}
	}
	 
	protected boolean doExchange(){
		DBExchange exchange=DBExchange.getExchange(false);
		if(exchange!=null){
			ResultHandler.processExchange(this,exchange);
			return true;
		}else{
			return false;
		}
	}
 	
	protected void queryCheck(){
		if(db==null){
			throw new RuntimeException("Query must use db!");
		}
	}
	
	public PrintWriter getPrintWriter(){
		if(writer==null){
			writer= new PrintWriter(new Writer(){
				public void write(char[] cbuf, int off, int len) throws IOException {
					 add(new String(cbuf,off,len));
				}

				public void flush() throws IOException {}
	
				public void close() throws IOException {}
			});
		}
		return writer;
	}
	
	protected void logSql(String sql){
		boolean debug=false;
		if(debugSql==null){
			debug=  "true".equalsIgnoreCase( DbProp.PROP_DB_SQL_DEBUG.getValue(db));
		}else{
			debug=debugSql.booleanValue();
		}
		
		if(debug){
			logger.info(sql);
		}
	}
	  
	public CacheKey getCacheKey(){
		 CacheKey cacheKey = new CacheKey();
		    
		 cacheKey.update(db.getKey());
		 cacheKey.update(""+tag);
		 cacheKey.update(getSql());
		 
		 for(Object p:this.getParameters()){
			 cacheKey.update(p);
		 } 
		    
		return cacheKey;    
	}
	
	public Cache getCache(){
		if(this.cache!=null){
			return this.cache;
		}else if(ttlInSeconds!=0 || DbProp.CFG_CACHE_GLOABLE_ENABLE){
			if(db==null){
				throw new RuntimeException("Query must use db!");
			}
			
			return getDb().getCfg().getCache();
		}else{
			return null;
		}
	}
	
	public void setCache(Cache cache){
		this.cache=cache;
	}
	 
	public DBConfig getDb() {
		return db;
	}

	public Query use(DBConfig db) {
		this.db = db;
		return this;
	}
	
	public Dialect getDialect(){
		if(db==null){
			throw new RuntimeException("Query must use db!");
		}
		
		return dsm.getDialect(db);
	}
      
	public void setReadonly(Boolean readonly) {
		this.readonly = readonly;
	}

	public long getCacheTime() {
		return ttlInSeconds;
	}

	public Query setCacheTime(long ttlInSeconds) {
		this.ttlInSeconds = ttlInSeconds;
		return this;
	}
	
	public String toString(){
		return "SQL: "+getSql();
	}
}    
    
    
    

    




    
    
    © 2015 - 2025 Weber Informatics LLC | Privacy Policy