Revision 58
| org.gvsig.oracle/trunk/org.gvsig.oracle/org.gvsig.oracle.provider/src/main/java/org/gvsig/oracle/dal/OracleSQLBuilder.java | ||
|---|---|---|
| 27 | 27 |
import java.util.ArrayList; |
| 28 | 28 |
import java.util.List; |
| 29 | 29 |
import org.apache.commons.lang3.StringUtils; |
| 30 |
import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
|
| 30 | 31 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
| 31 | 32 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
| 33 |
import org.gvsig.fmap.dal.store.jdbc2.spi.operations.ListTablesOperation; |
|
| 32 | 34 |
|
| 33 | 35 |
public class OracleSQLBuilder extends JDBCSQLBuilderBase {
|
| 34 | 36 |
|
| ... | ... | |
| 258 | 260 |
|
| 259 | 261 |
public class OracleSelectBuilderBase extends SelectBuilderBase {
|
| 260 | 262 |
|
| 261 |
@Override |
|
| 262 |
protected boolean isValid(StringBuilder message) {
|
|
| 263 |
if( message == null ) {
|
|
| 264 |
message = new StringBuilder(); |
|
| 265 |
} |
|
| 266 |
if( this.has_offset() && !this.has_order_by() ) {
|
|
| 267 |
// Algunos gestores de BBDD requieren que se especifique un |
|
| 268 |
// orden para poder usar OFFSET. Como eso parece buena idea para |
|
| 269 |
// asegurar que siempre tengamos los mismo resultados, lo exijimos |
|
| 270 |
// siempre. |
|
| 271 |
message.append("Can't use OFFSET without an ORDER BY.");
|
|
| 272 |
return false; |
|
| 273 |
} |
|
| 274 |
return true; |
|
| 275 |
} |
|
| 276 |
|
|
| 277 |
@Override |
|
| 278 |
public String toString() {
|
|
| 279 |
StringBuilder builder = new StringBuilder(); |
|
| 280 |
if( !isValid(builder) ) {
|
|
| 281 |
throw new IllegalStateException(builder.toString()); |
|
| 282 |
} |
|
| 263 |
protected StringBuilder appendMainClause(StringBuilder builder) {
|
|
| 283 | 264 |
builder.append("SELECT ");
|
| 284 | 265 |
if( this.distinct ) {
|
| 285 | 266 |
builder.append("DISTINCT ");
|
| ... | ... | |
| 315 | 296 |
builder.append(item.toString()); |
| 316 | 297 |
} |
| 317 | 298 |
} |
| 299 |
return builder; |
|
| 300 |
} |
|
| 318 | 301 |
|
| 319 |
if( this.has_limit() && this.has_offset() ) {
|
|
| 320 |
builder.append(" OFFSET ");
|
|
| 321 |
builder.append(this.offset); |
|
| 322 |
builder.append(" FETCH NEXT ");
|
|
| 323 |
builder.append(this.limit); |
|
| 324 |
builder.append(" ROWS ONLY");
|
|
| 325 |
|
|
| 326 |
} else if( this.has_limit() ) {
|
|
| 327 |
builder.append(" LIMIT ");
|
|
| 328 |
builder.append(this.limit); |
|
| 329 |
|
|
| 330 |
} else if( this.has_offset() ) {
|
|
| 331 |
builder.append(" LIMIT ALL OFFSET ");
|
|
| 332 |
builder.append(this.offset); |
|
| 302 |
@Override |
|
| 303 |
public String toString() {
|
|
| 304 |
StringBuilder builder = new StringBuilder(); |
|
| 305 |
if( !isValid(builder) ) {
|
|
| 306 |
throw new IllegalStateException(builder.toString()); |
|
| 333 | 307 |
} |
| 308 |
if (this.has_offset() || this.has_limit()) {
|
|
| 309 |
/** |
|
| 310 |
* Using ROWNUM for limit & offset, compatible with any Oracle version |
|
| 311 |
* This requires wrapping the original query as shown in the example: |
|
| 312 |
* select * from ( |
|
| 313 |
* select a.*, ROWNUM rnum from ( |
|
| 314 |
* <select statement with order by clause> |
|
| 315 |
* ) a where rownum <= MAX_ROW |
|
| 316 |
* ) where rnum >= MIN_ROW |
|
| 317 |
* because ROWNUM is considered before applying order by and group by |
|
| 318 |
*/ |
|
| 319 |
// |
|
| 320 |
builder.append("SELECT ");
|
|
| 321 |
boolean first = true; |
|
| 322 |
for( SelectColumnBuilder column : columns ) {
|
|
| 323 |
if( first ) {
|
|
| 324 |
first = false; |
|
| 325 |
} else {
|
|
| 326 |
builder.append(", ");
|
|
| 327 |
} |
|
| 328 |
builder.append(column.toString()); |
|
| 329 |
} |
|
| 330 |
builder.append(" FROM ( SELECT a.*, ROWNUM rnum FROM (");
|
|
| 331 |
this.appendMainClause(builder); |
|
| 332 |
builder.append(") a");
|
|
| 333 |
if (this.has_limit()) {
|
|
| 334 |
builder.append(" WHERE ROWNUM <= ");
|
|
| 335 |
builder.append(this.limit); |
|
| 336 |
} |
|
| 337 |
builder.append(" )");
|
|
| 338 |
if (this.has_offset()) {
|
|
| 339 |
|
|
| 340 |
builder.append(" WHERE rnum >= ");
|
|
| 341 |
builder.append(this.offset); |
|
| 342 |
} |
|
| 343 |
} |
|
| 344 |
else {
|
|
| 345 |
this.appendMainClause(builder); |
|
| 346 |
} |
|
| 334 | 347 |
return builder.toString(); |
| 335 |
|
|
| 336 | 348 |
} |
| 337 | 349 |
} |
| 338 | 350 |
|
| 351 |
protected TableNameBuilder createTableNameBuilder() {
|
|
| 352 |
return new OracleTableNameBuilder(); |
|
| 353 |
} |
|
| 354 |
|
|
| 339 | 355 |
@Override |
| 340 | 356 |
public String bytearray(byte[] data) {
|
| 341 | 357 |
return bytearray_x(data); |
| ... | ... | |
| 355 | 371 |
protected UpdateTableStatisticsBuilder createUpdateTableStatisticsBuilder() {
|
| 356 | 372 |
return new OracleUpdateTableStatisticsBuilderBase(); |
| 357 | 373 |
} |
| 374 |
|
|
| 375 |
protected class OracleTableNameBuilder extends TableNameBuilderBase {
|
|
| 376 |
@Override |
|
| 377 |
public String toString() {
|
|
| 378 |
if( this.has_schema()) {
|
|
| 379 |
return identifier(this.schemaName) + "." + |
|
| 380 |
identifier(this.tableName); |
|
| 381 |
} |
|
| 382 |
return identifier(this.tableName); |
|
| 383 |
} |
|
| 384 |
} |
|
| 385 |
|
|
| 358 | 386 |
} |
Also available in: Unified diff