de.schwarzrot.data.access.support
Interface DbUtil

All Known Implementing Classes:

AbstractDbUtils, MyUtils, PgUtils

public interface DbUtil

use database specific code in a generic way. AbstractDbUtils is a base class for all database implementations and handles the common stuff. This way we decide at runtime, which type of database should be used.

Author:

Reinhard Mantey

Field Summary

static String	EMPTY_STRING
static String	ONE_SPACE
static String	PK_NAME

Method Summary

Collection<ColumnMetaData>	columnDefinitions(Connection conn, TableDefinition td)
String	genAccessRightsFor(TableDefinition tbInfo, String defaultUser) generates a SQL-string to grant access rights to tables
String	genAccessRightsForPK(TableDefinition tbInfo, String defaultUser) generates a SQL-string to grant access to an external sequence
String	genColumnCreation(ColumnDefinition colDef) generates a SQL string to add a column to an existing table
String	genColumnModification(ColumnDefinition colDef) generates a SQL string to modify a column of an existing table
String	genCondition(EntityDescriptor ed, Collection<ConditionElement> args) generates a condition string for SQL statements
<E extends Entity> String	genCondition(EntityDescriptor ed, E instance, Collection<String> fields) generates a condition string for SQL statements
String	genCondition(EntityDescriptor ed, Map<String,Object> args) generates a condition string for SQL statements
String	genDBAccess(DatabaseModel dbInfo, String user) generates a SQL-string to grant access rights to the entire database
String	genDBCreation(DatabaseModel dbInfo) generates a SQL string to create a database
String	genIndexCreation(IndexDefinition iInfo) generates a SQL string to create an index
String	genIndexRemoval(IndexDefinition iInfo) generates a SQL string to remove an index
String	genInsert(EntityDescriptor ed, Entity instance) generates a SQL-insert statement for given instance
String	genInsertIdQuery(EntityDescriptor ed, Date dtCreated, String userName) generates a query for the generated autoincrement key.
String	genOrder(EntityDescriptor ed, Collection<String> properties) generates the sorting part of a SQL query
String	genOrder(EntityDescriptor ed, Collection<String> properties, Collection<SortInfo> orderSequence) generates the sorting part of an SQL-query.
String	genRemove(EntityDescriptor ed, Collection<ConditionElement> args) generates a SQL delete statement
String	genSchemaAccess(SchemaDefinition schemInfo, String defaultUser) generates a SQL-string to grant access rights to schema
String	genSchemaCreation(SchemaDefinition schemInfo) generates a SQL string to create a schema
String	genSequenceReset(TableDefinition tbInfo) generates a SQL string to reset an external sequence
String	genTableCreation(TableDefinition tbInfo) generates a SQL string to create a database table
String	genTableMetaAccess(TableDefinition tbInfo) generates a SQL query that returns no data to access tables meta data information
String	genTablePKCreation(TableDefinition tbInfo) generates a statement to create an external sequence for autoincrement-column
String	genTablePKMod(TableDefinition tbInfo) generates a statement to modify the external sequence after table creation.
String	genTableRemoval(TableDefinition tbInfo) generates a SQL string that removes a table from database
String	genTableRemoval(TableDefinition tbInfo, boolean withExist) generates a SQL string that removes a table from database.
String	genTruncate(EntityDescriptor ed) generates a SQL statement that truncates a database table, which means, that all entries will be removed.
String	genUpdate(EntityDescriptor ed, Entity instance) generates a SQL statement to change the persistent view of an entity
String	genUpdate(EntityDescriptor ed, Entity instance, Collection<String> fields, boolean withCondition) generates a SQL statement to partially change the persistent view of an entity.
String	genUserCreation(String username, String password) generates a SQL statement to create a user/role/principal
String	genUserCreation(String username, String password, String host) generates a SQL statement to create a user/role/principal.
String	getFieldSeparator() returns the separator used in field lists
String	getMapped(EntityDescriptor ed, String attributeName) get the physical name for the given property, already quoted with the right syntax for the involved database
String	getMapped(String dbObjName) returns the quoted name of database identifiers (table, column, etc), cause we always want do handle case sensitive identifiers
String	getSchemaSeparator() returns the schema separator for the actual storage.
String	getTableNameFor(EntityDescriptor ed) returns the tablename for given entity
String	getTableNameFor(TableDefinition ti) returns the tablename of given table model information
String	getTerminator() returns the terminator of SQL statements
ColumnType	getTypeFrom(int sqlType) maps SQL types to logical ColumnTypes used by application
Collection<IndexMetaData>	indexDefinitions(Connection conn, TableDefinition td)
boolean	needDropIndexAfterDropTable() signals whether an index must be dropped separately after removal of a table
boolean	needExternalSequenceForSerial() signals whether the database needs an external sequence to support autoincrement columns
boolean	needSequenceResetAfterTruncate() signals whether the database needs to set a new value to the sequence on removing all records of a table
boolean	needsUpdate(ColumnDefinition targetColDef, ColumnDefinition other)
boolean	needsUpdate(IndexDefinition targetIndexDef, IndexDefinition other)
String	quoteAndCutValue(Object value, int size) some databases pad strings to an unsupported size, so this call ensures, that the quoted string does not exceed the defined column size.
Collection<SchemaDefinition>	schemaDefinitions(Connection conn)
boolean	supportsGeneratedKeyQuery() signals whether the JDBC implementation supports the call for generated (autoincrement) keys.
Collection<TableDefinition>	tableDefinitions(Connection conn, SchemaDefinition sd)

Field Detail

EMPTY_STRING

static final String EMPTY_STRING

See Also:

Constant Field Values

ONE_SPACE

static final String ONE_SPACE

See Also:

Constant Field Values

PK_NAME

static final String PK_NAME

See Also:

Constant Field Values

Method Detail

columnDefinitions

Collection<ColumnMetaData> columnDefinitions(Connection conn,
                                             TableDefinition td)

genAccessRightsFor

String genAccessRightsFor(TableDefinition tbInfo,
                          String defaultUser)

generates a SQL-string to grant access rights to tables

Parameters:

tbInfo - - table model information

defaultUser - - the user that gets the rights granted

Returns:

- the ready to use SQL statement

genAccessRightsForPK

String genAccessRightsForPK(TableDefinition tbInfo,
                            String defaultUser)

generates a SQL-string to grant access to an external sequence

Parameters:

tbInfo - - table model information

defaultUser - - the user that gets the rights granted

Returns:

- the ready to use SQL statement

genColumnCreation

String genColumnCreation(ColumnDefinition colDef)

generates a SQL string to add a column to an existing table

Parameters:

colDef - - column model information

Returns:

- the ready to use SQL statement

genColumnModification

String genColumnModification(ColumnDefinition colDef)

generates a SQL string to modify a column of an existing table

Parameters:

colDef - - column model information

Returns:

-the ready to use SQL statement

genCondition

String genCondition(EntityDescriptor ed,
                    Collection<ConditionElement> args)

generates a condition string for SQL statements

Parameters:

ed - - descriptor of the entity

args - - list of elements that form the condition

Returns:

- the generated condition string

genCondition

<E extends Entity> String genCondition(EntityDescriptor ed,
                                       E instance,
                                       Collection<String> fields)

generates a condition string for SQL statements

Type Parameters:

E - - the entity to use

Parameters:

ed - - descriptor of the entity

instance - - instance of that entity, containing the values for the condition

fields - - list of attributes used in the condition

Returns:

- the generated condition string

genCondition

String genCondition(EntityDescriptor ed,
                    Map<String,Object> args)

generates a condition string for SQL statements

Parameters:

ed - - descriptor of the entity

args - - map of attribute/value pairs to build the condition from

Returns:

- the generated condition string

genDBAccess

String genDBAccess(DatabaseModel dbInfo,
                   String user)

generates a SQL-string to grant access rights to the entire database

Parameters:

dbInfo - - database model information

user - - the user that gets the rights granted

Returns:

- the ready to use SQL statement

genDBCreation

String genDBCreation(DatabaseModel dbInfo)

generates a SQL string to create a database

Parameters:

dbInfo - - database model information

Returns:

- the ready to use SQL statement

genIndexCreation

String genIndexCreation(IndexDefinition iInfo)

generates a SQL string to create an index

Parameters:

iInfo - - index model information

Returns:

- the ready to use SQL statement

genIndexRemoval

String genIndexRemoval(IndexDefinition iInfo)

generates a SQL string to remove an index

Parameters:

iInfo - - index model information

Returns:

- the ready to use SQL statement

genInsert

String genInsert(EntityDescriptor ed,
                 Entity instance)

generates a SQL-insert statement for given instance

Parameters:

ed - - descriptor of the entity

instance - - the instance that should be copied to persistence

Returns:

- the ready to use SQL statement

genInsertIdQuery

String genInsertIdQuery(EntityDescriptor ed,
                        Date dtCreated,
                        String userName)

generates a query for the generated autoincrement key. This method is a fallback for JDBC implementations, that don't support the Statement.getGeneratedKeys(). So they have to support at least dirty reads.

Parameters:

ed - - descriptor of the entity

dtCreated - - timestamp of the insert

userName - - name of the user that triggered the insert

Returns:

- the ready to use SQL statement

genOrder

String genOrder(EntityDescriptor ed,
                Collection<String> properties)

generates the sorting part of a SQL query

Parameters:

ed - - descriptor of the entity

properties - - list of attributes to sort by

Returns:

- the generated sort part of the SQL query

genOrder

String genOrder(EntityDescriptor ed,
                Collection<String> properties,
                Collection<SortInfo> orderSequence)

generates the sorting part of an SQL-query. This method validates that only attributes that get read form part of the sort order

Parameters:

ed - - descriptor of the entity

properties - - list of attributes to read

orderSequence - - list of sorting segment info

Returns:

- the sorting part of an SQL query

genRemove

String genRemove(EntityDescriptor ed,
                 Collection<ConditionElement> args)

generates a SQL delete statement

Parameters:

ed - - descriptor of the entity to remove

args - - list of elements that form the condition

Returns:

- the ready to use SQL statement

genSchemaAccess

String genSchemaAccess(SchemaDefinition schemInfo,
                       String defaultUser)

generates a SQL-string to grant access rights to schema

Parameters:

schemInfo - - schema model information

defaultUser - - the user that gets the rights granted

Returns:

- the ready to use SQL statement

genSchemaCreation

String genSchemaCreation(SchemaDefinition schemInfo)

generates a SQL string to create a schema

Parameters:

schemInfo - - schema model information

Returns:

- the ready to use SQL statement

genSequenceReset

String genSequenceReset(TableDefinition tbInfo)

generates a SQL string to reset an external sequence

Parameters:

tbInfo - - table model information

Returns:

- the ready to use SQL statement

genTableCreation

String genTableCreation(TableDefinition tbInfo)

generates a SQL string to create a database table

Parameters:

tbInfo - - table model information

Returns:

- the ready to use SQL statement

genTableMetaAccess

String genTableMetaAccess(TableDefinition tbInfo)

generates a SQL query that returns no data to access tables meta data information

Parameters:

tbInfo - - table model information

Returns:

- the ready to use SQL statement

genTablePKCreation

String genTablePKCreation(TableDefinition tbInfo)

generates a statement to create an external sequence for autoincrement-column

Parameters:

tbInfo - - table model information

Returns:

- the ready to use SQL statement

genTablePKMod

String genTablePKMod(TableDefinition tbInfo)

generates a statement to modify the external sequence after table creation.

Parameters:

tbInfo - - table model information

Returns:

- the ready to use SQL statement

genTableRemoval

String genTableRemoval(TableDefinition tbInfo)

generates a SQL string that removes a table from database

Parameters:

tbInfo - - table model information

Returns:

- the ready to use SQL statement

genTableRemoval

String genTableRemoval(TableDefinition tbInfo,
                       boolean withExist)

generates a SQL string that removes a table from database. SQL statement is extended with table existence check

Parameters:

tbInfo - - table model information

withExist - - flag to indicate, whether existence check should be added

Returns:

- the ready to use SQL statement

genTruncate

String genTruncate(EntityDescriptor ed)

generates a SQL statement that truncates a database table, which means, that all entries will be removed. The truncation of a db table is vendor specific, so for database systems, that support truncation, that statement should be created. For database systems, that don't support truncation, a delete without where clause will be used.

Truncation of a table may require additional permissions beside the permission to delete records.

Parameters:

ed - - descriptor of the Entity

Returns:

- the ready to use truncate expression

genUpdate

String genUpdate(EntityDescriptor ed,
                 Entity instance)

generates a SQL statement to change the persistent view of an entity

Parameters:

ed - - descriptor of the involved entity

instance - - the instance to save

Returns:

- the field modification part of the SQL - needs to get extended by a condition

genUpdate

String genUpdate(EntityDescriptor ed,
                 Entity instance,
                 Collection<String> fields,
                 boolean withCondition)

generates a SQL statement to partially change the persistent view of an entity. Only those attributes contained in the list of fields will be modified.

Parameters:

ed - - descriptor of the involved entity

instance - - instance containing the modified values to save

fields - the attribute names to modify

withCondition -

• true - the condition to update the given instance is included
• false - no condition is included (usable to update more than one instance)

Returns:

- the generated SQL statement

genUserCreation

String genUserCreation(String username,
                       String password)

generates a SQL statement to create a user/role/principal

Parameters:

username - - name of the user

password - - password for that user

Returns:

- the generated SQL statement

genUserCreation

String genUserCreation(String username,
                       String password,
                       String host)

generates a SQL statement to create a user/role/principal. Use this call, if the users permissions should be bound to given host

Parameters:

username - - name of the user

password - - password for that user

host - - name of the host

Returns:

- the generated SQL statement

getFieldSeparator

String getFieldSeparator()

returns the separator used in field lists

Returns:

a separator string

getMapped

String getMapped(EntityDescriptor ed,
                 String attributeName)

get the physical name for the given property, already quoted with the right syntax for the involved database

Parameters:

ed - - descriptor of the involved entity

attributeName - - name of attribute to map

Returns:

- the physical name of the attribute with id-quotes

getMapped

String getMapped(String dbObjName)

returns the quoted name of database identifiers (table, column, etc), cause we always want do handle case sensitive identifiers

Parameters:

dbObjName - - the identifier to quote

Returns:

- the quoted identifier

getSchemaSeparator

String getSchemaSeparator()

returns the schema separator for the actual storage. Storage systems, that don't support schemata should return an '_' (underscore).

Returns:

- separator used for schema handling

getTableNameFor

String getTableNameFor(EntityDescriptor ed)

returns the tablename for given entity

Parameters:

ed - - descriptor of an entity

Returns:

- a tablename

getTableNameFor

String getTableNameFor(TableDefinition ti)

returns the tablename of given table model information

Parameters:

ti - - table model information

Returns:

- the table name

getTerminator

String getTerminator()

returns the terminator of SQL statements

Returns:

- a SQL statement terminator

getTypeFrom

ColumnType getTypeFrom(int sqlType)

maps SQL types to logical ColumnTypes used by application

Parameters:

sqlType - - a SQL type to map

Returns:

- the mapped ColumnType

indexDefinitions

Collection<IndexMetaData> indexDefinitions(Connection conn,
                                           TableDefinition td)

needDropIndexAfterDropTable

boolean needDropIndexAfterDropTable()

signals whether an index must be dropped separately after removal of a table

Returns:

true if index must be dropped separately, otherwise false

needExternalSequenceForSerial

boolean needExternalSequenceForSerial()

signals whether the database needs an external sequence to support autoincrement columns

Returns:

- true if the database needs an external sequence, otherwise false

needSequenceResetAfterTruncate

boolean needSequenceResetAfterTruncate()

signals whether the database needs to set a new value to the sequence on removing all records of a table

Returns:

- true if the database needs to set a value to the sequence, otherwise false

needsUpdate

boolean needsUpdate(ColumnDefinition targetColDef,
                    ColumnDefinition other)

needsUpdate

boolean needsUpdate(IndexDefinition targetIndexDef,
                    IndexDefinition other)

quoteAndCutValue

String quoteAndCutValue(Object value,
                        int size)

some databases pad strings to an unsupported size, so this call ensures, that the quoted string does not exceed the defined column size.

Parameters:

value - - the value to store

size - - declared field size

Returns:

- the quoted string that fits the field

schemaDefinitions

Collection<SchemaDefinition> schemaDefinitions(Connection conn)

supportsGeneratedKeyQuery

boolean supportsGeneratedKeyQuery()

signals whether the JDBC implementation supports the call for generated (autoincrement) keys.

Returns:

- true if the JDBC implementation supports the call of generatedKeys, otherwise false

tableDefinitions

Collection<TableDefinition> tableDefinitions(Connection conn,
                                             SchemaDefinition sd)