001 package railo.runtime.tag; 002 003 import java.util.ArrayList; 004 import java.util.TimeZone; 005 006 import railo.commons.date.TimeZoneUtil; 007 import railo.commons.lang.StringUtil; 008 import railo.runtime.PageContext; 009 import railo.runtime.db.DataSource; 010 import railo.runtime.db.DataSourceImpl; 011 import railo.runtime.db.DatasourceConnection; 012 import railo.runtime.db.DatasourceManagerImpl; 013 import railo.runtime.db.HSQLDBHandler; 014 import railo.runtime.db.SQL; 015 import railo.runtime.db.SQLImpl; 016 import railo.runtime.db.SQLItem; 017 import railo.runtime.debug.DebuggerImpl; 018 import railo.runtime.exp.ApplicationException; 019 import railo.runtime.exp.DatabaseException; 020 import railo.runtime.exp.ExpressionException; 021 import railo.runtime.exp.PageException; 022 import railo.runtime.ext.tag.BodyTagTryCatchFinallyImpl; 023 import railo.runtime.listener.ApplicationContextPro; 024 import railo.runtime.op.Caster; 025 import railo.runtime.op.Decision; 026 import railo.runtime.orm.ORMSession; 027 import railo.runtime.orm.ORMUtil; 028 import railo.runtime.tag.util.DeprecatedUtil; 029 import railo.runtime.type.Array; 030 import railo.runtime.type.ArrayImpl; 031 import railo.runtime.type.Collection; 032 import railo.runtime.type.KeyImpl; 033 import railo.runtime.type.List; 034 import railo.runtime.type.QueryColumn; 035 import railo.runtime.type.QueryImpl; 036 import railo.runtime.type.QueryPro; 037 import railo.runtime.type.Struct; 038 import railo.runtime.type.StructImpl; 039 import railo.runtime.type.dt.DateTime; 040 import railo.runtime.type.dt.DateTimeImpl; 041 import railo.runtime.type.dt.TimeSpan; 042 import railo.runtime.type.query.SimpleQuery; 043 044 045 046 /** 047 * Passes SQL statements to a data source. Not limited to queries. 048 **/ 049 public final class Query extends BodyTagTryCatchFinallyImpl { 050 051 private static final Collection.Key SQL_PARAMETERS = KeyImpl.intern("sqlparameters"); 052 private static final Collection.Key EXECUTION_TIME = KeyImpl.intern("executiontime"); 053 private static final Collection.Key CFQUERY = KeyImpl.intern("cfquery"); 054 private static final Collection.Key GENERATEDKEY = KeyImpl.intern("generatedKey"); 055 private static final Collection.Key MAX_RESULTS = KeyImpl.intern("maxResults"); 056 private static final Collection.Key TIMEOUT = KeyImpl.intern("timeout"); 057 058 private static final int RETURN_TYPE_QUERY = 1; 059 private static final int RETURN_TYPE_ARRAY_OF_ENTITY = 2; 060 061 062 /** If specified, password overrides the password value specified in the data source setup. */ 063 private String password; 064 065 /** The name of the data source from which this query should retrieve data. */ 066 private DataSource datasource=null; 067 068 /** The maximum number of milliseconds for the query to execute before returning an error 069 ** indicating that the query has timed-out. This attribute is not supported by most ODBC drivers. 070 ** timeout is supported by the SQL Server 6.x or above driver. The minimum and maximum allowable values 071 ** vary, depending on the driver. */ 072 private int timeout=-1; 073 074 /** This is the age of which the query data can be */ 075 private TimeSpan cachedWithin; 076 077 /** Specifies the maximum number of rows to fetch at a time from the server. The range is 1, 078 ** default to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. 079 ** Certain ODBC drivers may dynamically reduce the block factor at runtime. */ 080 private int blockfactor=-1; 081 082 /** The database driver type. */ 083 private String dbtype; 084 085 /** Used for debugging queries. Specifying this attribute causes the SQL statement submitted to the 086 ** data source and the number of records returned from the query to be returned. */ 087 private boolean debug=true; 088 089 /* This is specific to JTags, and allows you to give the cache a specific name */ 090 //private String cachename; 091 092 /** Specifies the maximum number of rows to return in the record set. */ 093 private int maxrows=-1; 094 095 /** If specified, username overrides the username value specified in the data source setup. */ 096 private String username; 097 098 /** */ 099 private DateTime cachedafter; 100 101 /** The name query. Must begin with a letter and may consist of letters, numbers, and the underscore 102 ** character, spaces are not allowed. The query name is used later in the page to reference the query's 103 ** record set. */ 104 private String name; 105 106 private String result=null; 107 108 //private static HSQLDBHandler hsql=new HSQLDBHandler(); 109 110 private boolean orgPSQ; 111 private boolean hasChangedPSQ; 112 113 ArrayList<SQLItem> items=new ArrayList<SQLItem>(); 114 115 private boolean clearCache; 116 private boolean unique; 117 private Struct ormoptions; 118 private int returntype=RETURN_TYPE_ARRAY_OF_ENTITY; 119 private TimeZone timezone; 120 private TimeZone tmpTZ; 121 private boolean lazy; 122 123 124 125 /** 126 * @see javax.servlet.jsp.tagext.Tag#release() 127 */ 128 public void release() { 129 super.release(); 130 items.clear(); 131 password=null; 132 datasource=null; 133 timeout=-1; 134 clearCache=false; 135 cachedWithin=null; 136 cachedafter=null; 137 //cachename=""; 138 blockfactor=-1; 139 dbtype=null; 140 debug=true; 141 maxrows=-1; 142 username=null; 143 name=""; 144 result=null; 145 146 orgPSQ=false; 147 hasChangedPSQ=false; 148 unique=false; 149 150 ormoptions=null; 151 returntype=RETURN_TYPE_ARRAY_OF_ENTITY; 152 timezone=null; 153 tmpTZ=null; 154 lazy=false; 155 } 156 157 158 public void setOrmoptions(Struct ormoptions) { 159 this.ormoptions = ormoptions; 160 } 161 162 163 public void setReturntype(String strReturntype) throws ApplicationException { 164 if(StringUtil.isEmpty(strReturntype)) return; 165 strReturntype=strReturntype.toLowerCase().trim(); 166 167 if(strReturntype.equals("query")) 168 returntype=RETURN_TYPE_QUERY; 169 //mail.setType(railo.runtime.mail.Mail.TYPE_TEXT); 170 else if(strReturntype.equals("array_of_entity") || strReturntype.equals("array-of-entity") || 171 strReturntype.equals("array_of_entities") || strReturntype.equals("array-of-entities") || 172 strReturntype.equals("arrayofentities") || strReturntype.equals("arrayofentities")) 173 returntype=RETURN_TYPE_ARRAY_OF_ENTITY; 174 //mail.setType(railo.runtime.mail.Mail.TYPE_TEXT); 175 else 176 throw new ApplicationException("attribute returntype of tag query has a invalid value","valid values are [query,array-of-entity] but value is now ["+strReturntype+"]"); 177 } 178 179 180 public void setUnique(boolean unique) { 181 this.unique = unique; 182 } 183 /** 184 * @param result the result to set 185 */ 186 public void setResult(String result) { 187 this.result = result; 188 } 189 190 /** 191 * @param psq set preserver single quote 192 */ 193 public void setPsq(boolean psq) { 194 orgPSQ=pageContext.getPsq(); 195 if(orgPSQ!=psq){ 196 pageContext.setPsq(psq); 197 hasChangedPSQ=true; 198 } 199 } 200 201 /** set the value password 202 * If specified, password overrides the password value specified in the data source setup. 203 * @param password value to set 204 **/ 205 public void setPassword(String password) { 206 this.password=password; 207 } 208 209 /** set the value datasource 210 * The name of the data source from which this query should retrieve data. 211 * @param datasource value to set 212 **/ 213 public void setDatasource(String datasource) throws PageException { 214 this.datasource=pageContext.getConfig().getDataSource(datasource); 215 } 216 217 /** set the value timeout 218 * The maximum number of milliseconds for the query to execute before returning an error 219 * indicating that the query has timed-out. This attribute is not supported by most ODBC drivers. 220 * timeout is supported by the SQL Server 6.x or above driver. The minimum and maximum allowable values 221 * vary, depending on the driver. 222 * @param timeout value to set 223 **/ 224 public void setTimeout(double timeout) { 225 this.timeout=(int)timeout; 226 } 227 228 /** set the value cachedafter 229 * This is the age of which the query data can be 230 * @param cachedafter value to set 231 **/ 232 public void setCachedafter(DateTime cachedafter) { 233 //railo.print.ln("cachedafter:"+cachedafter); 234 this.cachedafter=cachedafter; 235 } 236 237 /** set the value cachename 238 * This is specific to JTags, and allows you to give the cache a specific name 239 * @param cachename value to set 240 **/ 241 public void setCachename(String cachename) { 242 DeprecatedUtil.tagAttribute(pageContext,"query", "cachename"); 243 //this.cachename=cachename; 244 } 245 246 /** set the value cachedwithin 247 * 248 * @param cachedwithin value to set 249 **/ 250 public void setCachedwithin(TimeSpan cachedwithin) { 251 if(cachedwithin.getMillis()>0) 252 this.cachedWithin=cachedwithin; 253 else clearCache=true; 254 } 255 256 public void setLazy(boolean lazy) { 257 this.lazy=lazy; 258 } 259 260 /** set the value providerdsn 261 * Data source name for the COM provider, OLE-DB only. 262 * @param providerdsn value to set 263 * @throws ApplicationException 264 **/ 265 public void setProviderdsn(String providerdsn) throws ApplicationException { 266 DeprecatedUtil.tagAttribute(pageContext,"Query", "providerdsn"); 267 } 268 269 /** set the value connectstring 270 * @param connectstring value to set 271 * @throws ApplicationException 272 **/ 273 public void setConnectstring(String connectstring) throws ApplicationException { 274 DeprecatedUtil.tagAttribute(pageContext,"Query", "connectstring"); 275 } 276 277 278 public void setTimezone(String timezone) throws ExpressionException { 279 this.timezone=TimeZoneUtil.toTimeZone(timezone); 280 } 281 282 /** set the value blockfactor 283 * Specifies the maximum number of rows to fetch at a time from the server. The range is 1, 284 * default to 100. This parameter applies to ORACLE native database drivers and to ODBC drivers. 285 * Certain ODBC drivers may dynamically reduce the block factor at runtime. 286 * @param blockfactor value to set 287 **/ 288 public void setBlockfactor(double blockfactor) { 289 this.blockfactor=(int) blockfactor; 290 } 291 292 /** set the value dbtype 293 * The database driver type. 294 * @param dbtype value to set 295 **/ 296 public void setDbtype(String dbtype) { 297 this.dbtype=dbtype.toLowerCase(); 298 } 299 300 /** set the value debug 301 * Used for debugging queries. Specifying this attribute causes the SQL statement submitted to the 302 * data source and the number of records returned from the query to be returned. 303 * @param debug value to set 304 **/ 305 public void setDebug(boolean debug) { 306 this.debug=debug; 307 } 308 309 /** set the value dbname 310 * The database name, Sybase System 11 driver and SQLOLEDB provider only. If specified, dbName 311 * overrides the default database specified in the data source. 312 * @param dbname value to set 313 * @throws ApplicationException 314 **/ 315 public void setDbname(String dbname) { 316 DeprecatedUtil.tagAttribute(pageContext,"Query", "dbname"); 317 } 318 319 /** set the value maxrows 320 * Specifies the maximum number of rows to return in the record set. 321 * @param maxrows value to set 322 **/ 323 public void setMaxrows(double maxrows) { 324 this.maxrows=(int) maxrows; 325 } 326 327 /** set the value username 328 * If specified, username overrides the username value specified in the data source setup. 329 * @param username value to set 330 **/ 331 public void setUsername(String username) { 332 if(!StringUtil.isEmpty(username)) 333 this.username=username; 334 } 335 336 /** set the value provider 337 * COM provider, OLE-DB only. 338 * @param provider value to set 339 * @throws ApplicationException 340 **/ 341 public void setProvider(String provider) { 342 DeprecatedUtil.tagAttribute(pageContext,"Query", "provider"); 343 } 344 345 /** set the value dbserver 346 * For native database drivers and the SQLOLEDB provider, specifies the name of the database server 347 * computer. If specified, dbServer overrides the server specified in the data source. 348 * @param dbserver value to set 349 * @throws ApplicationException 350 **/ 351 public void setDbserver(String dbserver) { 352 DeprecatedUtil.tagAttribute(pageContext,"Query", "dbserver"); 353 } 354 355 /** set the value name 356 * The name query. Must begin with a letter and may consist of letters, numbers, and the underscore 357 * character, spaces are not allowed. The query name is used later in the page to reference the query's 358 * record set. 359 * @param name value to set 360 **/ 361 public void setName(String name) { 362 this.name=name; 363 } 364 365 public String getName() { 366 return name==null? "query":name; 367 } 368 369 370 371 372 373 /** 374 * @param item 375 */ 376 public void setParam(SQLItem item) { 377 items.add(item); 378 } 379 380 381 /** 382 * @throws PageException 383 * @see javax.servlet.jsp.tagext.Tag#doStartTag() 384 */ 385 public int doStartTag() throws PageException { 386 // default datasource 387 if(datasource==null && (dbtype==null || !dbtype.equals("query"))){ 388 String str = ((ApplicationContextPro)pageContext.getApplicationContext()).getDefaultDataSource(); 389 if(StringUtil.isEmpty(str)) 390 throw new ApplicationException( 391 "attribute [datasource] is required, when attribute [dbtype] has not value [query] and no default datasource is defined", 392 "you can define a default datasource as attribute [defaultdatasource] of the tag cfapplication or as data member of the application.cfc (this.defaultdatasource=\"mydatasource\";)"); 393 394 datasource=pageContext.getConfig().getDataSource(str); 395 } 396 397 398 // timezone 399 if(timezone!=null || (datasource!=null && (timezone=((DataSourceImpl)datasource).getTimeZone())!=null)) { 400 tmpTZ=pageContext.getTimeZone(); 401 pageContext.setTimeZone(timezone); 402 } 403 404 405 return EVAL_BODY_BUFFERED; 406 } 407 408 /** 409 * @see railo.runtime.ext.tag.BodyTagTryCatchFinallyImpl#doFinally() 410 */ 411 public void doFinally() { 412 if(tmpTZ!=null) { 413 pageContext.setTimeZone(tmpTZ); 414 } 415 super.doFinally(); 416 } 417 418 /** 419 * @throws PageException 420 * @see javax.servlet.jsp.tagext.Tag#doEndTag() 421 */ 422 public int doEndTag() throws PageException { 423 424 425 if(hasChangedPSQ)pageContext.setPsq(orgPSQ); 426 String strSQL=bodyContent.getString(); 427 if(strSQL.length()==0) throw new DatabaseException("no sql string defined, inside query tag",null,null,null); 428 SQL sql=items.size()>0?new SQLImpl(strSQL,(SQLItem[])items.toArray(new SQLItem[items.size()])):new SQLImpl(strSQL); 429 430 railo.runtime.type.Query query=null; 431 int exe=0; 432 boolean hasCached=cachedWithin!=null || cachedafter!=null; 433 434 435 if(clearCache) { 436 hasCached=false; 437 pageContext.getQueryCache().remove(sql,datasource!=null?datasource.getName():null,username,password); 438 } 439 else if(hasCached) { 440 query=pageContext.getQueryCache().getQuery(sql,datasource!=null?datasource.getName():null,username,password,cachedafter); 441 } 442 443 444 if(query==null) { 445 if("query".equals(dbtype)) query=executeQoQ(sql); 446 else if("orm".equals(dbtype) || "hql".equals(dbtype)) { 447 long start=System.currentTimeMillis(); 448 Object obj = executeORM(sql,returntype,ormoptions); 449 450 if(obj instanceof railo.runtime.type.Query){ 451 query=(railo.runtime.type.Query) obj; 452 } 453 else { 454 if(!StringUtil.isEmpty(name)) { 455 pageContext.setVariable(name,obj); 456 } 457 if(result!=null){ 458 Struct sct=new StructImpl(); 459 sct.setEL(QueryImpl.CACHED, Boolean.FALSE); 460 sct.setEL(QueryImpl.EXECUTION_TIME, Caster.toDouble(System.currentTimeMillis()-start)); 461 sct.setEL(QueryImpl.SQL, sql.getSQLString()); 462 if(Decision.isArray(obj)){ 463 464 } 465 else sct.setEL(QueryImpl.RECORDCOUNT, Caster.toDouble(1)); 466 467 pageContext.setVariable(result, sct); 468 } 469 else 470 setExecutionTime(System.currentTimeMillis()-start); 471 return EVAL_PAGE; 472 } 473 } 474 else query=executeDatasoure(sql,result!=null); 475 //query=(dbtype!=null && dbtype.equals("query"))?executeQoQ(sql):executeDatasoure(sql,result!=null); 476 477 if(cachedWithin!=null) { 478 DateTimeImpl cachedBefore = null; 479 //if(cachedWithin!=null) 480 cachedBefore=new DateTimeImpl(pageContext,System.currentTimeMillis()+cachedWithin.getMillis(),false); 481 pageContext.getQueryCache().set(sql,datasource!=null?datasource.getName():null,username,password,query,cachedBefore); 482 483 484 } 485 exe=query.executionTime(); 486 } 487 else query.setCached(hasCached); 488 489 if(pageContext.getConfig().debug() && debug) { 490 boolean debugUsage=DebuggerImpl.debugQueryUsage(pageContext,query); 491 ((DebuggerImpl)pageContext.getDebugger()).addQuery(debugUsage?query:null,datasource!=null?datasource.getName():null,name,sql,query.getRecordcount(),pageContext.getCurrentPageSource(),exe); 492 } 493 494 if(!query.isEmpty() && !StringUtil.isEmpty(name)) { 495 pageContext.setVariable(name,query); 496 } 497 498 // Result 499 if(result!=null) { 500 501 Struct sct=new StructImpl(); 502 sct.setEL(QueryImpl.CACHED, Caster.toBoolean(query.isCached())); 503 if(!query.isEmpty())sct.setEL(QueryImpl.COLUMNLIST, List.arrayToList(query.getColumns(),",")); 504 int rc=query.getRecordcount(); 505 if(rc==0)rc=query.getUpdateCount(); 506 sct.setEL(QueryImpl.RECORDCOUNT, Caster.toDouble(rc)); 507 sct.setEL(QueryImpl.EXECUTION_TIME, Caster.toDouble(query.executionTime())); 508 sct.setEL(QueryImpl.SQL, sql.getSQLString()); 509 510 // GENERATED KEYS 511 // FUTURE when getGeneratedKeys() exist in interface the toQueryImpl can be removed 512 QueryPro qi = Caster.toQueryPro(query,null); 513 if(qi !=null){ 514 QueryPro qryKeys = Caster.toQueryPro(qi.getGeneratedKeys(),null); 515 if(qryKeys!=null){ 516 StringBuffer generatedKey=new StringBuffer(),sb; 517 Collection.Key[] columnNames = qryKeys.getColumnNames(); 518 QueryColumn column; 519 for(int c=0;c<columnNames.length;c++){ 520 column = qryKeys.getColumn(columnNames[c]); 521 sb=new StringBuffer(); 522 int size=column.size(); 523 for(int r=1;r<=size;r++) { 524 if(r>1)sb.append(','); 525 sb.append(Caster.toString(column.get(r))); 526 } 527 if(sb.length()>0){ 528 sct.setEL(columnNames[c], sb.toString()); 529 if(generatedKey.length()>0)generatedKey.append(','); 530 generatedKey.append(sb); 531 } 532 } 533 if(generatedKey.length()>0) 534 sct.setEL(GENERATEDKEY, generatedKey.toString()); 535 } 536 } 537 538 // sqlparameters 539 SQLItem[] params = sql.getItems(); 540 if(params!=null && params.length>0) { 541 Array arr=new ArrayImpl(); 542 sct.setEL(SQL_PARAMETERS, arr); 543 for(int i=0;i<params.length;i++) { 544 arr.append(params[i].getValue()); 545 546 } 547 } 548 pageContext.setVariable(result, sct); 549 } 550 // cfquery.executiontime 551 else { 552 setExecutionTime(exe); 553 554 } 555 556 557 558 559 return EVAL_PAGE; 560 } 561 562 private void setExecutionTime(long exe) { 563 Struct sct=new StructImpl(); 564 sct.setEL(EXECUTION_TIME,new Double(exe)); 565 pageContext.undefinedScope().setEL(CFQUERY,sct); 566 } 567 568 569 private Object executeORM(SQL sql, int returnType, Struct ormoptions) throws PageException { 570 ORMSession session=ORMUtil.getSession(pageContext); 571 572 // params 573 SQLItem[] _items = sql.getItems(); 574 Array params=new ArrayImpl(); 575 for(int i=0;i<_items.length;i++){ 576 params.appendEL(_items[i]); 577 } 578 579 // query options 580 if(maxrows!=-1 && !ormoptions.containsKey(MAX_RESULTS)) ormoptions.setEL(MAX_RESULTS, new Double(maxrows)); 581 if(timeout!=-1 && !ormoptions.containsKey(TIMEOUT)) ormoptions.setEL(TIMEOUT, new Double(timeout)); 582 /* MUST 583 offset: Specifies the start index of the resultset from where it has to start the retrieval. 584 cacheable: Whether the result of this query is to be cached in the secondary cache. Default is false. 585 cachename: Name of the cache in secondary cache. 586 */ 587 Object res = session.executeQuery(pageContext,sql.getSQLString(),params,unique,ormoptions); 588 if(returnType==RETURN_TYPE_ARRAY_OF_ENTITY) return res; 589 return session.toQuery(pageContext, res, null); 590 591 } 592 593 public static Object _call(PageContext pc,String hql, Object params, boolean unique, Struct queryOptions) throws PageException { 594 ORMSession session=ORMUtil.getSession(pc); 595 //ORMEngine engine= ORMUtil.getEngine(pc); 596 if(Decision.isCastableToArray(params)) 597 return session.executeQuery(pc,hql,Caster.toArray(params),unique,queryOptions); 598 else if(Decision.isCastableToStruct(params)) 599 return session.executeQuery(pc,hql,Caster.toStruct(params),unique,queryOptions); 600 else 601 return session.executeQuery(pc,hql,(Array)params,unique,queryOptions); 602 } 603 604 605 private railo.runtime.type.Query executeQoQ(SQL sql) throws PageException { 606 try { 607 return new HSQLDBHandler().execute(pageContext,sql,maxrows,blockfactor,timeout); 608 } 609 catch (Exception e) { 610 throw Caster.toPageException(e); 611 } 612 } 613 614 private railo.runtime.type.Query executeDatasoure(SQL sql,boolean createUpdateData) throws PageException { 615 DatasourceManagerImpl manager = (DatasourceManagerImpl) pageContext.getDataSourceManager(); 616 DatasourceConnection dc=manager.getConnection(pageContext,datasource, username, password); 617 try { 618 if(lazy && !createUpdateData && cachedWithin==null && cachedafter==null && result==null) 619 return new SimpleQuery(dc,sql,maxrows,blockfactor,timeout,getName(),pageContext.getCurrentPageSource().getDisplayPath()); 620 621 622 return new QueryImpl(dc,sql,maxrows,blockfactor,timeout,getName(),pageContext.getCurrentPageSource().getDisplayPath(),createUpdateData,true); 623 } 624 finally { 625 manager.releaseConnection(pageContext,dc); 626 } 627 } 628 629 630 /** 631 * @see javax.servlet.jsp.tagext.BodyTag#doInitBody() 632 */ 633 public void doInitBody() { 634 635 } 636 637 /** 638 * @see javax.servlet.jsp.tagext.BodyTag#doAfterBody() 639 */ 640 public int doAfterBody() { 641 return SKIP_BODY; 642 } 643 }