Start line:  
End line:  

Snippet Preview

Snippet HTML Code

Stack Overflow Questions
   /*
    *  Copyright 2009-2014 Pavel Ponec
    *
    *  Licensed under the Apache License, Version 2.0 (the "License");
    *  you may not use this file except in compliance with the License.
    *  You may obtain a copy of the License at
    *
    *      http://www.apache.org/licenses/LICENSE-2.0
    *
   *  Unless required by applicable law or agreed to in writing, software
   *  distributed under the License is distributed on an "AS IS" BASIS,
   *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   *  See the License for the specific language governing permissions and
   *  limitations under the License.
   */
  package org.ujorm.orm;
  
  import java.io.Reader;
  import java.util.HashSet;
  import java.util.List;
  import java.util.Set;
  import org.ujorm.Key;
  import org.ujorm.Ujo;
SQL dialect abstract class. Methods of this class print a SQL statement(s) along a metamodel usually. You may create a subclass of any implementation to create another SQL statement, however just I can't exclude some small changes of this API in the next release.

Author(s):
Pavel Ponec
Composed:
- - 1 SeqTableModel
  
  @SuppressWarnings("unchecked")
  abstract public class SqlDialect {

    
Logger
  
      private static final UjoLogger LOGGER = UjoLoggerFactory.getLogger(SqlDialect.class);

    
The table model for an internal sequence tabable support
  
      final private SeqTableModel pkTableModel = new SeqTableModel();

    
The table key for a common sequence emulator.
  
      public static final String COMMON_SEQ_TABLE_KEY = "<ALL>";
    
The default schema symbol
  
      public static final String DEFAULT_SCHEMA_SYMBOL = "~";

    
The ORM handler
  
      protected OrmHandler ormHandler;
    
The name provider
  
      private SqlNameProvider nameProvider;
    
Extended dialect
  
      private SqlDialectEx extentedDialect;

    
Prints quoted name (identifier) to SQL
  
      private Boolean quoteRequest;

    
Set the OrmHandler - the method is for internal call only.
  
      public void setHandler(OrmHandler ormHandler) {
          if (this.!=null) {
              throw new IllegalStateException("The OrmHandler is assigned yet.");
          }
          this. = ormHandler;
      }

    
Returns a default JDBC URL
  
      abstract public String getJdbcUrl();

    
Returns a JDBC driver class name.
  
      abstract public String getJdbcDriver();

    
Create a new database connection
  
      public Connection createConnection(final MetaDatabase dbthrows Exception {
          return db.createInternalConnection();
      }

    
Get or create an Initial Context for the JNDI lookup.
  
     public InitialContext createJndiInitialContext(final MetaDatabase dbthrows NamingException {
           return new InitialContext();
     }

    
Does the database support a catalog? The feature supports: MySqlDialect and MSSqlDialect.

Returns:
The default value is false.
 
     public boolean isCatalog() {
         return false;
     }

    
Print SQL 'CREATE SCHEMA'
 
     public Appendable printCreateSchema(String schemaAppendable outthrows IOException {
         out.append("CREATE SCHEMA IF NOT EXISTS ");
         printQuotedName(schemaout);
         return out;
     }

    
Print SQL 'SET SCHEMA'. The method is not used yet.
 
     @Deprecated
     public Appendable printDefaultSchema(String schemaAppendable outthrows IOException {
         out.append("SET SCHEMA ");
         printQuotedName(schemaout);
         return out;
     }

    
Print a full SQL table name by sample: SCHEMA.TABLE
 
     final public Appendable printFullTableName(final MetaTable tablefinal Appendable outthrows IOException {
         return printFullTableName(tablefalseout);
     }

    
Print a extended SQL table name by sample: SCHEMA.TABLE

Parameters:
printSymbolSchema True parameter replaces a <string>default schema</string> name for the symbol "~" by the example: ~.TABLE
Throws:
java.io.IOException
 
     public Appendable printFullTableName(final MetaTable tablefinal boolean printSymbolSchemafinal Appendable outthrows IOException {
         final String tableSchema = ..of(table);
         final String tableName = ..of(table);
 
         if (isFilled(tableSchema)) {
             if (printSymbolSchema && table.isDefaultSchema()) {
                 out.append();
             } else {
                 printQuotedName(tableSchemaout);
             }
             out.append('.');
         }
         printQuotedName(tableNameout);
         return out;
     }

    
Print a SQL database and table name and an alias definition - by sample: SCHEMA.TABLE ALIAS
 
     public void printTableAliasDefinition(final TableWrapper tablefinal Appendable outthrows IOException {
         printFullTableName(table.getModel(), out);
         final String alias = table.getAlias();
         if (isFilled(alias)) {
             out.append(' ');
             printQuotedName(aliasout);
         }
     }

    
Print a full SQL column alias name by sample: "TABLE_ALIAS"."ORIG_COLUMN"
 
     public Appendable printColumnAlias(final ColumnWrapper columnfinal Appendable outthrows IOException {
         printQuotedName(column.getTableAlias(), out);
         out.append('.');
         printQuotedName(column.getName(), out);
         return out;
     }

    
Print a SQL script to create table
 
     public Appendable printTable(MetaTable tableAppendable outthrows IOException {
         out.append("CREATE TABLE ");
         printFullTableName(tableout);
         String separator = "\n\t( ";
         for (MetaColumn column : ..getList(table)) {
             out.append(separator);
             separator = "\n\t, ";
 
             if (column.isForeignKey()) {
                 printFKColumnsDeclaration(columnout);
             } else {
                 printColumnDeclaration(columnnullout);
             }
         }
         out.append("\n\t)");
         return out;
     }

    
Print a SQL script to add a new column to the table
 
     public Appendable printAlterTableAddColumn(MetaColumn columnAppendable outthrows IOException {
         return printAlterTableColumn(columntrueout);
     }

    
Print a SQL script to add a new column to the table
 
     public Appendable printAlterTableColumn(MetaColumn columnboolean addAppendable outthrows IOException {
         out.append("ALTER TABLE ");
         printFullTableName(column.getTable(), out);
         out.append(add
             ? " ADD COLUMN "
             : " ALTER COLUMN ");
         if (column.isForeignKey()) {
             printFKColumnsDeclaration(columnout);
         } else {
             printColumnDeclaration(columnnullout);
         }
         if (column.hasDefaultValue()) {
             printDefaultValue(columnout);
         }
         return out;
     }

    
Print a SQL phrase for the DEFAULT VALUE, for example: DEFAULT 777
 
     public Appendable printDefaultValue(final MetaColumn columnfinal Appendable outthrows IOException {
         Object value = column.getJdbcFriendlyDefaultValue();
         boolean isDefault = value != null;
         String quotMark = "";
         if (value instanceof String) {
             isDefault = ((Stringvalue).length() > 0;
             quotMark = "'";
         } else if (value instanceof java.sql.Date) {
             isDefault = true;
             quotMark = "'";
         }
         if (isDefault) {
             out.append(" DEFAULT ");
             out.append(quotMark);
             out.append(value.toString());
             out.append(quotMark);
         }
         return out;
     }

    
Print Default Constraint
 
     public Appendable printDefaultConstraint(MetaColumn columnStringBuilder outthrows IOException {
         if (column.hasDefaultValue()) {
             MetaTable table = column.getTable();
             out.append("ALTER TABLE ");
             printFullTableName(tableout);
             out.append(" ALTER COLUMN ");
             printQuotedName(column.getName(), out);
             out.append(" SET ");
             printDefaultValue(columnout);
         }
         return out;
     }

    
Print foreign key for the parameter column

Returns:
More statements separated by the ';' characters are enabled
 
     public Appendable printForeignKey(MetaColumn columnAppendable outthrows IOException {
 
         MetaTable table = column.getTable();
         List<MetaColumnfColumns = column.getForeignColumns();
         MetaTable foreignTable = fColumns.get(0).getTable();
         int columnsSize = fColumns.size();
 
         out.append("ALTER TABLE ");
         printFullTableName(tableout);
         out.append("\n\tADD CONSTRAINT ");
         getNameProvider().printConstraintName(tablecolumnout);
         out.append(" FOREIGN KEY ");
 
         for (int i=0; i<columnsSize; ++i) {
             out.append(i==0 ? "(" : ", ");
             final String name = column.getForeignColumnName(i);
             printQuotedName(nameout);
         }
 
         out.append(")\n\tREFERENCES ");
         printFullTableName(foreignTableout);
         String separator = "(";
 
         for (MetaColumn fColumn : fColumns) {
             out.append(separator);
             separator = ", ";
             printQuotedName(fColumn.getName(), out);
         }
 
         out.append(")");
         //out.append("\tON DELETE CASCADE");
         return out;
     }

    
Print an INDEX for the parameter column.

Returns:
More statements separated by the ';' characters are enabled
 
     public Appendable printIndex(final MetaIndex indexfinal Appendable outthrows IOException {
 
         out.append("CREATE ");
         if (..of(index)) {
             out.append("UNIQUE ");
         }
         out.append("INDEX ");
         out.append(..of(index));
         out.append(" ON ");
         printFullTableName(..of(index), out);
         String separator = " (";
 
         for (MetaColumn column : ..of(index)) {
             out.append(separator);
             printQuotedName(column.getName(), out);
             separator = ", ";
         }
         out.append(')');
         return out;
     }

    
Is allowed a column length in a SQL phrase for creating column? The length example can be: NAME CHAR(10)
 
     protected boolean isColumnLengthAllowed(final MetaColumn column) {
         switch (..of(column)) {
             case :
             case :
             case :
             case :
             case :
             case :
                  return false;
             case :
             case :
             case :
           //case NUMERIC:
             default:
                 return !..isDefault(column);
         }
     }

    
Print a SQL to create column

Parameters:
column Database Column
aName The name parameter is not mandatory, the not null value means a foreign key.
Throws:
java.io.IOException
 
     public Appendable printColumnDeclaration(MetaColumn columnString aNameAppendable outthrows IOException {
 
         String name = aName!=null ? aName : column.getName();
         printQuotedName(nameout);
         out.append(' ');
         out.append(getColumnType(column));
 
         if (isColumnLengthAllowed(column)) {
             out.append("(" + ..of(column));
             if (!..isDefault(column)) {
                 out.append("," + ..of(column));
             }
             out.append(")");
         }
         if (..of(column) && aName == null) {
             out.append(" NOT NULL");
         }
         if (..of(column) && aName == null) {
             out.append(" PRIMARY KEY");
         }
         return out;
     }

    
Returns a database column type
 
     protected String getColumnType(final MetaColumn column) {
         return ..of(column).name();
     }

    
Print a SQL to create foreign keys.
 
     public Appendable printFKColumnsDeclaration(MetaColumn columnAppendable outthrows IOException {
 
         final List<MetaColumncolumns = column.getForeignColumns();
 
         for (int i=0; i<columns.size(); ++i) {
             MetaColumn col = columns.get(i);
             out.append(i==0 ? "" : "\n\t, ");
             String name = column.getForeignColumnName(i);
             printColumnDeclaration(colnameout);
             if (..of(column)) {
                 out.append(" NOT NULL");
             }
             if (..of(column)) {
                 out.append(" PRIMARY KEY");
             }
         }
         return out;
     }

    
Print an SQL INSERT statement.
 
     public Appendable printInsert(final OrmUjo bofinal Appendable outthrows IOException {
 
         MetaTable table = .findTableModel((Classbo.getClass());
         StringBuilder values = new StringBuilder();
 
         out.append("INSERT INTO ");
         printFullTableName(tableout);
         out.append(" (");
 
         printTableColumns(table.getColumns(), valuesout);
 
         out.append(") VALUES (")
            .append(values)
            .append(")")
            ;
 
         return out;
     }

    
Print an SQL INSERT statement.

Parameters:
bos Business object list
idxFrom Start index from list
idxTo Finished index from list (excluded)
See also:
isMultiRowInsertSupported()
 
     public Appendable printInsert(final List<? extends OrmUjobosfinal int idxFromfinal int idxTofinal Appendable outthrows IOException {
 
         MetaTable table = .findTableModel(bos.get(idxFrom).getClass());
         StringBuilder values = new StringBuilder(32);
 
         out.append("INSERT INTO ");
         printFullTableName(tableout);
         out.append(" (");
 
         printTableColumns(table.getColumns(), valuesout);
 
         for (int i=idxFromi<idxTo; ++i) {
             out.append(i==idxFrom ? ") VALUES \n(" : "),\n(")
                .append(values);
         }
         out.append(")");
         return out;
     }

    
Print an batch SQL INSERT statement using SELECT UNION statement.

Parameters:
bos Business object list
idxFrom Start index from list
idxTo Finished index from list (excluded)
fromPhrase For example the Oracle syntax: SELECT 1,2,3 FROM DUAL;
See also:
isMultiRowInsertSupported()
 
     public Appendable printInsertBySelect(final List<? extends OrmUjobosfinal int idxFromfinal int idxTofinal String fromPhrasefinal Appendable outthrows IOException {
 
         MetaTable table = .findTableModel(bos.get(idxFrom).getClass());
         StringBuilder values = new StringBuilder(32);
 
         out.append("INSERT INTO ");
         printFullTableName(tableout);
         out.append(" (");
 
         printTableColumns(table.getColumns(), valuesout);
 
         for (int i=idxFromi<idxTo; ++i) {
             out.append(i==idxFrom ? ")\nSELECT " : " UNION ALL\nSELECT ")
                .append(values);
             if (isFilled(fromPhrase)) {
                 out.append(" ").append(fromPhrase);
             }
         }
         return out;
     }


    
Is supported the Multirow inserts ? Default value is true

 
     public boolean isMultiRowInsertSupported() {
         return true;
     }

    
Print an SQL UPDATE statement.
 
     public Appendable printUpdate
         ( List<MetaColumnchangedColumns
         , CriterionDecoder decoder
         , Appendable out
         ) throws IOException
     {
         final MetaTable table = decoder.getBaseTable();
         out.append("UPDATE ");
         printTableAliasDefinition(tableout);
         out.append("\n\tSET ");
 
         for (int i=0; i<changedColumns.size(); i++) {
             MetaColumn ormColumn = changedColumns.get(i);
             if (ormColumn.isPrimaryKey()) {
                 throw new IllegalStateException("Primary key can not be changed: " + ormColumn);
             }
             out.append(i==0 ? "" :  ", ");
             printQuotedName(ormColumn.getName(), out);
             out.append("=?");
         }
         out.append("\n\tWHERE ");
 
         if (decoder.getTableCount() > 1) {
             printQuotedName(table.getFirstPK().getName(), out);
             out.append(" IN (");
             printSelectTableBase(createSubQuery(decoder), falseout);
             out.append(")");
         } else {
             out.append(decoder.getWhere());
         }
         return out;
     }

    
Print an SQL DELETE statement.
 
     public Appendable printDelete
         ( CriterionDecoder decoder
         , Appendable out
         ) throws IOException
     {
         final MetaTable table = decoder.getBaseTable();
         out.append("DELETE FROM ");
         printTableAliasDefinition(tableout);
         out.append(" WHERE ");
 
         if (decoder.getTableCount() > 1) {
             printQuotedName(table.getFirstPK().getName(), out);
             out.append(" IN (");
             printSelectTableBase(createSubQuery(decoder), falseout);
             out.append(")");
         } else {
             //String where = decoder.getWhere().replace(tableAlias + '.', fullTableName + '.');
             out.append(decoder.getWhere());
         }
         return out;
     }

    
Create a subquery for the DELETE/UPDATE statement
 
     protected Query createSubQuery(CriterionDecoder decoder) {
         final MetaTable baseTable = decoder.getBaseTable();
         final Query result = new Query(baseTabledecoder.getCriterion());
         result.setDecoder(decoder);
         result.setColumns(true);
         return result;
     }

    
Returns an SQL criterion template. The result is a tempate by the next sample: "{0}={1}" .
See an example of the implementation:
 switch (crit.getOperator()) {
     case EQ:
         return "{0}={1}";
     case NOT_EQ:
         return "{0}<>{1}";
     case GT:
         return "{0}>{1}";
     ...
 
 
     @SuppressWarnings("unchecked")
     public String getCriterionTemplate(ValueCriterion crit) {
 
         switch (crit.getOperator()) {
             case :
                 return "{0}={1}";
             case :
                 return "{0}<>{1}";
             case :
                 return "{0}>{1}";
             case :
                 return "{0}>={1}";
             case :
                 return "{0}<{1}";
             case :
                 return "{0}<={1}";
             case :
                 return "UPPER({0})={1}";
             case :
             case :
             case :
                 return "UPPER({0}) LIKE {1}";
             case :
             case :
             case :
                 return "{0} LIKE {1}";
             case :
                 return "{0} IN ({1})";
             case :
                 return "NOT {0} IN ({1})";
             case :
                 return crit.evaluate((Ujo)null)
                     ? "1=1" // "true"
                     : "1=0" // "false"
                     ;
             case :
                 Object tmp = crit.getRightNode() instanceof TemplateValue
                         ? ((TemplateValuecrit.getRightNode()).getTemplate()
                         : crit.getRightNode();
                 return "(" + tmp + ')' ;
             case :
             case :
             default:
                 throw new UnsupportedOperationException("Unsupported: " + crit.getOperator());
         }
     }

    
Print table columns

Parameters:
columns List of tablel columns
values Print columns including its aliases.
out Table columns output.
Throws:
java.io.IOException
 
     public void printTableColumns(Collection<? extends ColumnWrappercolumnsAppendable valuesAppendable outthrows IOException {
         String separator = "";
         boolean select = values==null// SELECT
         for (ColumnWrapper wColumn : columns) {
             final MetaColumn column = wColumn.getModel();
             if (column.isForeignKey()) {
                 for (int i = 0; i < column.getForeignColumns().size(); ++i) {
                     out.append(separator);
                     if (select) {
                         printQuotedName(wColumn.getTableAlias(), out);
                         out.append('.');
                     }
                     printQuotedName(column.getForeignColumnName(i), out);
                     if (values != null) {
                         values.append(separator);
                         values.append("?");
                     }
                     separator = ", ";
                 }
             } else if (column.isColumn()) {
                 out.append(separator);
                 if (select) {
                     printColumnAlias(columnout);
                 } else {
                     printQuotedName(column.getName(), out);
                 }
                 if (values != null) {
                     values.append(separator);
                     values.append("?");
                 }
                 separator = ", ";
             }
         }
     }


    
Print a condition phrase from the criterion.

Returns:
A value criterion to assign into the SQL query.
 
     public ValueCriterion printCriterion(ValueCriterion critAppendable outthrows IOException {
         final Operator operator = crit.getOperator();
         final Key key = crit.getLeftNode();
         final ColumnWrapper column = key != null
                 ? AliasKey.getLastKey(key).getColumn() : null;
         Object right = crit.getRightNode();
 
         if (right==null ) {
             switch (operator) {
                 case :
                 case :
                     printColumnAlias(columnout);
                     out.append(" IS NULL");
                     return null;
                 case :
                     printColumnAlias(columnout);
                     out.append(" IS NOT NULL");
                     return null;
                 default:
                     throw new UnsupportedOperationException("Comparation the NULL value is forbiden by a operator: " + operator);
             }
         }
 
         String template = getCriterionTemplate(crit);
         if (template == null) {
             throw new UnsupportedOperationException("Unsupported SQL operator: " + operator);
         }
 
         switch (crit.getOperator()) {
             case :
                 out.appendtemplate );
                 break;
             case :
                 if (right instanceof TemplateValue) {
                     right = ((TemplateValueright).getRightVale();
                     ValueCriterion crit2 = (ValueCriterion) Criterion.where
                             ( crit.getLeftNode()
                             , . // The hack
                             , right);
                     return printCriterionValue(templatecolumncrit2out);
                 }
                 if (template.contains("{0}")) {
                     out.append(MessageFormat.format(templategetAliasColumnName(column)));
                 } else {
                     out.append(template);
                 }
                 break;
             default:
                 return printCriterionValue(templatecolumncritout);
         }
         return null;
     }

    
Write a right value form criterion

Returns:
A value criterion to assign into the SQL query.
 
     protected ValueCriterion printCriterionValue(String templateColumnWrapper columnValueCriterion critAppendable outthrows IOException {
         final Object right = crit.getRightNode();
         if (right instanceof Key) {
             final Key rightProperty = (Keyright;
             final ColumnWrapper col2 = AliasKey.getLastKey(rightProperty).getColumn();
 
             if (col2.getModel().isForeignKey()) {
                 throw new UnsupportedOperationException("Foreign key is not supported yet");
             }
             if (true) {
                 // Better performance:
                 String f = MessageFormat.format(templategetAliasColumnName(column), getAliasColumnName(col2));
                 //String f=String.format(template, column.getColumnAlias(), col2.getColumnAlias());
                 out.append(f);
             }
         } else if (right instanceof Object[]) {
             final Object[] os = (Object[]) right;
             final StringBuilder sb = new StringBuilder(2*os.length);
             for (Object o : os) {
                 sb.append(sb.length()>0 ? ",?" : "?");
             }
             String f = MessageFormat.format(templategetAliasColumnName(column), sb.toString());
             out.append(f);
             return crit;
         } else if (column.getModel().isForeignKey()) {
             printForeignKey(critcolumntemplateout);
             return crit;
         } else {
             String f = MessageFormat.format(templategetAliasColumnName(column), "?");
             out.append(f);
             return crit;
         }
         return null;
     }

    
Returns quoted column name including the alias table
 
     protected String getAliasColumnName(ColumnWrapper columnthrows IOException {
         final Appendable out = new StringBuilder(32);
         printQuotedName(column.getTableAlias(), out);
         out.append('.');
         printQuotedName(column.getName(), out);
         return out.toString();
     }

    
Print all items of the foreign key
 
     public void printForeignKey
         ( final ValueCriterion crit
         , final ColumnWrapper column
         , final String template
         , final Appendable out
         ) throws IOException
     {
         int size = column.getModel().getForeignColumns().size();
         for (int i=0; i<sizei++) {
             if (i>0) {
                 out.append(' ');
                 out.append(crit.getOperator().name());
                 out.append(' ');
             }
 
             StringBuilder columnName = new StringBuilder(256);
             String alias = column.getTableAlias();
             if (isFilled(alias)) {
                 printQuotedName(aliascolumnName);
                 columnName.append('.');
             }
             printQuotedName(column.getModel().getForeignColumnName(i), columnName);
             String f = MessageFormat.format(templatecolumnName"?");
             out.append(f);
         }
     }

    
Print a SQL SELECT by table model and query

Parameters:
query The UJO query
count only count of items is required;
 
     final public Appendable printSelect
         ( final TableWrapper table
         , final Query query
         , final boolean count
         , final Appendable out
         ) throws IOException {
 
         return table.isView()
             ? printSelectView(tablequerycountout)
             : printSelectTable(querycountout)
             ;
     }

    
Print SQL view SELECT

Parameters:
query The UJO query
count only count of items is required;
 
     protected Appendable printSelectView(TableWrapper tableQuery queryboolean countAppendable outthrows IOException {
         final String userSql = query.getSqlParameters()!=null
                 ? query.getSqlParameters().getSqlStatement()
                 : null
                 ;
         final MetaSelect select = userSql!=null
                 ? new MetaSelect(userSql..of(table.getModel()))
                 : ..of(table.getModel())
                 ;
         final String where = query.getDecoder().getWhere();
         final List<KeyorderByList = query.getOrderBy();
 
         for (Key p : select.readKeys()) {
             String value = (Stringp.of(select);
 
             if (p==. && count) {
                 out.append(p.toString());
                 out.append"COUNT(*)" );
             } else if (p==. && value.length()+where.length()>0) {
                 out.append(p.toString());
                 out.appendvalue );
                 out.appendvalue.length()==0 || where.length()==0 ? "" : " AND " );
                 out.appendwhere );
             } else if (p==. && !count && !orderByList.isEmpty()){
                 printSelectOrder(queryout);
             } else if (p==. && !count && query.getLimit()>0){
                 out.append(p.toString());
                 out.append(String.valueOf(query.getLimit()));
             } else if (p==. && !count && query.getOffset()>0){
                 out.append(p.toString());
                 out.append(String.valueOf(query.getOffset()));
             } else if (isFilled(value)) {
                 out.append(p.toString());
                 out.appendvalue );
             }
         }
         return out;
     }

    
Print SQL database SELECT

Parameters:
query The UJO query
count only count of items is required;
 
     protected Appendable printSelectTable(final Query queryfinal boolean countfinal Appendable outthrows IOException {
         if (count && query.isDistinct()) {
             out.append("SELECT COUNT(*) FROM (");
             printSelectTableBase(querycountout);
             out.append("\n\tGROUP BY ");
             printTableColumns(query.getColumns(), nullout);
             out.append(") ujorm_count_");
         } else {
             printSelectTableBase(querycountout);
         }
         return out;
     }

    
Print SQL database SELECT

Parameters:
query The UJO query
count only count of items is required;
 
     protected void printSelectTableBase(final Query queryfinal boolean countfinal Appendable outthrows IOException {
         out.append("SELECT ");
 
         if (count!=query.isDistinct()) {
             out.append(count
                 ? "COUNT(*)"
                 : "DISTINCT "
                 );
         }
         if (!count || query.isDistinct()) {
             printTableColumns(query.getColumns(), nullout);
         }
         out.append("\n\tFROM ");
 
         if (query.getCriterion() != null) {
             final CriterionDecoder ed = query.getDecoder();
             final TableWrapper[] tables = ed.getTables();
 
             for (int i=0; i<tables.length; ++i) {
                 if (i>0) {
                     out.append(", ");
                 }
                 printTableAliasDefinition(tables[i], out);
             }
 
             final String where = ed.getWhere();
             if (where.length()>0) {
                 out.append(" WHERE ");
                 out.append(where);
             }
         } else {
             printTableAliasDefinition(query.getTableModel(), out);
         }
 
         if (!count) {
             if (!query.getOrderBy().isEmpty()) {
                printSelectOrder(queryout);
             }
             if (query.isOffset()) {
                 printOffset(queryout);
             }
             if (query.isLockRequest()) {
                out.append(' ');
                printLockForSelect(queryout);
             }
         }
     }

    
Print a 'lock clausule' to the end of SQL SELECT statement to use a pessimistic lock. The current database does not support the feature, throw an exception UnsupportedOperationException.
The method prints a text "FOR UPDATE".

Parameters:
query The UJO query
 
     protected Appendable printLockForSelect(final Query queryfinal Appendable outthrows IOExceptionUnsupportedOperationException {
         out.append("FOR UPDATE");
         return out;
     }

    
Print SQL ORDER BY
 
     protected void printSelectOrder(Query queryAppendable outthrows IOException {
 
         out.append(" ORDER BY ");
         final List<Keyprops = query.getOrderBy();
         for (int i=0; i<props.size(); i++) {
             MetaColumn column = query.readOrderColumn(i);
             boolean ascending = props.get(i).isAscending();
             if (i>0) {
                 out.append(", ");
             }
             printColumnAlias(columnout);
             if (!ascending) {
                 out.append(" DESC");
             }
         }
     }

    
Print the call of a stored procedure by template:
{? = call procedure_when(?,?)}
 
     public Appendable printCall(MetaProcedure procedureAppendable outthrows IOException {
 
         List<MetaColumnpropList = ..of(procedure);
 
         out.append('{').append(' ');
         if (!propList.get(0).isVoid()) {
            out.append("? =");
         }
         out.append(" call ");
         out.append(procedure.getProcedureName());
 
         // Print all parameters:
         if (propList.size()>1) {
             for (int i=1; i<propList.size(); i++) {
                 out.append(i==1 ? "(?" : ",?");
             }
             out.append(')');
         }
         out.append(' ').append('}');
         return out;
     }

    
Print an OFFSET of the statement SELECT.
 
     public void printOffset(Query queryAppendable outthrows IOException {
         int limit = query.isLimit()
             ? query.getLimit()
             : .
             ;
         out.append(" LIMIT " + limit);
         out.append(" OFFSET " + query.getOffset());
     }

    
Prinnt the full sequence table
 
     protected Appendable printSequenceTableName(final UjoSequencer sequencefinal Appendable outthrows IOException {
         String schema = sequence.getDatabaseSchema();
         if (isFilled(schema)) {
             printQuotedName(schemaout);
             out.append('.');
         }
         printQuotedName(getSeqTableModel().getTableName(), out);
         return out;
     }

    
Print SQL CREATE SEQUENCE. No JDBC parameters.
 
     public Appendable printSequenceTable(final MetaDatabase dbfinal Appendable outthrows IOException {
         String schema = ..of(db);
         Integer cache = ..of(db.getParams());
 
         out.append("CREATE TABLE ");
         if (isFilled(schema)) {
             printQuotedName(schemaout);
             out.append('.');
         }
 
         final MetaColumn pkType = new MetaColumn();
         ..setValue(pkType.);
 
         printQuotedName(getSeqTableModel().getTableName(), out);
         out.append ( ""
         + "\n\t( " + getQuotedName(getSeqTableModel().getId()) + " VARCHAR(96) NOT NULL PRIMARY KEY"
         + "\n\t, " + getQuotedName(getSeqTableModel().getSequence()) + " " + getColumnType(pkType) + " DEFAULT " + cache + " NOT NULL"
         + "\n\t, " + getQuotedName(getSeqTableModel().getCache()) + " INT DEFAULT " + cache + " NOT NULL"
         + "\n\t, " + getQuotedName(getSeqTableModel().getMaxValue()) + " " + getColumnType(pkType) + " DEFAULT 0 NOT NULL"
         + "\n\t)");
         return out;
     }

    
Print SQL CREATE SEQUENCE (insert sequence row). No JDBC parameters.
 
     public Appendable printSequenceInit(final UjoSequencer sequencefinal Appendable outthrows IOException {
         Integer cache = ..of(sequence.getDatabase().getParams());
         return printSequenceInitWithValues(sequencecachecacheout);
    }
    public Appendable printSequenceInitWithValues(final UjoSequencer sequencelong seqint cachefinal Appendable outthrows IOException {
        out.append("INSERT INTO ");
        printSequenceTableName(sequenceout);
        out.append(" (");
        out.append(",");
        out.append(",");
        out.append(",");
        out.append(") VALUES (?," + seq).append("," + cache).append(",0)");
        return out;
    }

    
Print SQL UPDATE NEXT SEQUENCE value.
    public Appendable printSequenceNextValue(final UjoSequencer sequencefinal Appendable outthrows IOException {
        out.append("UPDATE ");
        printSequenceTableName(sequenceout);
        out.append(" SET ");
        out.append("=");
        out.append("+");
        out.append(" WHERE ");
        out.append("=?");
        return out;
    }

    
Set sequence to the max value.
    public Appendable printSetMaxSequence(final UjoSequencer sequencefinal Appendable outthrows IOException {
        out.append("UPDATE ");
        printSequenceTableName(sequenceout);
        out.append(" SET ");
        out.append("=");
        out.append(" WHERE ");
        out.append("=?");
        return out;
    }

    
Print SQL CURRENT SEQUENCE VALUE. Returns a new sequence limit and the. The SQL columns are always selected in the order: sequence, cache, maxValue. current cache.
    public Appendable printSequenceCurrentValue(final UjoSequencer sequencefinal Appendable outthrows IOException {
        final SeqTableModel tm = getSeqTableModel();
        out.append("SELECT ");
        printQuotedNameAlways(tm.getSequence(), out);
        out.append(", ");
        printQuotedNameAlways(tm.getCache(), out);
        out.append(", ");
        printQuotedNameAlways(tm.getMaxValue(), out);
        out.append(" FROM ");
        printSequenceTableName(sequenceout);
        out.append(" WHERE ");
        printQuotedNameAlways(tm.getId(), out);
        out.append("=?");
        return out;
    }

    
Print SQL DELETE SEQUENCE BY ID.
    public Appendable printSequenceDeleteById(final UjoSequencer sequenceString idfinal Appendable outthrows IOException {
        final SeqTableModel tm = getSeqTableModel();
        out.append("DELETE FROM ");
        printSequenceTableName(sequenceout);
        out.append(" WHERE ");
        printQuotedNameAlways(tm.getId(), out);
        out.append("=?");
        return out;
    }

    
Returns true, if the argument text is not null and not empty.
    final protected boolean isFilled(final CharSequence text) {
        return text!=null && text.length()>0;
    }

    
Print the new line.
    final public void println(final Appendable outthrows IOException {
        out.append('\n');
    }

    
Print SQL 'COMMIT'
    public Appendable printCommit(Appendable outthrows IOException {
        out.append("COMMIT");
        return out;
    }

    
Print a Comment to a database Table
    public Appendable printComment(MetaTable tableAppendable outthrows IOException {
        out.append("COMMENT ON TABLE ");
        printFullTableName(tableout);
        out.append(" IS '");
        escape(..of(table), out);
        out.append("'");
        return out;
    }

    
Print a Comment to a database Column
    public Appendable printComment(MetaColumn columnAppendable outthrows IOException {
        out.append("COMMENT ON COLUMN ");
        printFullTableName(..of(column), out);
        out.append('.');
        out.append(column.