jodd.db.orm.sqlgen
Class DbSqlBuilder

java.lang.Object
  jodd.db.orm.sqlgen.DbSqlBuilder

All Implemented Interfaces:

DbSqlGenerator

public class DbSqlBuilder
extends java.lang.Object
implements DbSqlGenerator

Nice SQL query generator that provides some automatic query generation.

Query is built by appending 'chunks' - parts of the query. These chunks may be very simple, as a simple string. However, the main reason of existence of this class are chunks that auto-generate part of the query based on provided domain object. They can be used to easily create some most common queries in an efficient way.

Although it is not necessary, use of DbIdentity annotation boost the functionality of this query builder.

Some chunks deals with relations between tables, so they must be aware of foreign key names. Here the naming convention is used, and even users might have their own foreign key naming convention.

Furthermore, if all queries are generated using just sql builder, it is possible to use dialects for various database types.

See Also:

DbSqlTemplate

Field Summary

protected ColumnAliasType	columnAliasType
protected int	columnCount Column counter for COLUMN_CODE column alias type.
protected java.util.Map<java.lang.String,ColumnData>	columnData Column or table aliases.
protected DbOrm	dbOrm
static ColumnAliasType	DEFAULT_COLUMN_ALIAS_TYPE Default column aliases type.
static FkNamingConvention	DEFAULT_FK_NAMING_CONVENTION Default foreign key naming convention.
protected SqlChunk	firstChunk
protected static java.lang.String[]	fkMacros
protected FkNamingConvention	fkNamingConvention
protected SqlChunk	lastChunk
protected int	paramCount
protected java.util.Map<java.lang.String,java.lang.Object>	parameters
protected java.util.Map<java.lang.String,java.lang.String>	tableRefs Table names used in this query.
protected java.util.Map<java.lang.String,DbEntityDescriptor>	tables Table references used in this query.

Constructor Summary

DbSqlBuilder()
Creates new SQL builder.

Method Summary

DbSqlBuilder	_(java.lang.String sql) Appends raw SQL to the query.
DbSqlBuilder	_and()
DbSqlBuilder	_between()
DbSqlBuilder	_deleteFrom()
DbSqlBuilder	_eq()
DbSqlBuilder	_from()
DbSqlBuilder	_ge()
DbSqlBuilder	_gt()
DbSqlBuilder	_in()
DbSqlBuilder	_innerJoin()
DbSqlBuilder	_join()
DbSqlBuilder	_le()
DbSqlBuilder	_leftJoin()
DbSqlBuilder	_lt()
DbSqlBuilder	_or()
DbSqlBuilder	_orderBy()
DbSqlBuilder	_rightJoin()
DbSqlBuilder	_select()
DbSqlBuilder	_selectCount()
DbSqlBuilder	_space()
DbSqlBuilder	_update()
DbSqlBuilder	_where()
void	addParameter(java.lang.String name, java.lang.Object value) Adds query parameter.
DbSqlBuilder	chunk(SqlChunk chunk) Appends chunk to the list.
DbSqlBuilder	column(java.lang.String reference) Resolves column.
DbSqlBuilder	column(java.lang.String tableRef, java.lang.String columnRef) Resolves column.
DbSqlBuilder	columnsAll(java.lang.String tableRef) Resolves all columns.
DbSqlBuilder	columnsIds(java.lang.String tableRef) Resolves all identity columns.
DbSqlBuilder	condition(java.lang.String tableRef, java.lang.Object value) Creates condition part of the query only for existing columns.
DbSqlBuilder	conditionAll(java.lang.String tableRef, java.lang.Object value) Creates condition part of the query for all columns, including the null values.
DbSqlBuilder	conditionIds(java.lang.String tableRef, java.lang.Object value) Creates condition part of the query for id columns
DbSqlBuilder	conditionSafe(java.lang.String tableRef, java.lang.Object value) Creates condition part of the query for all columns, including the null values.
static DbSqlBuilder	create() Alternative factory.
java.lang.String	generateQuery() Generates SQL query.
ColumnAliasType	getColumnAliasType() Returns column alias type.
java.util.Map<java.lang.String,ColumnData>	getColumnData() Returns an optional map of table and column names, used by ResultSetMapper.
DbOrm	getDbOrm() Returns associated DbOrm.
SqlChunk	getFirstChunk() Returns previous chunk.
FkNamingConvention	getFkNamingConvention()
SqlChunk	getLastChunk() Returns last chunk.
java.lang.String	getNextParameterName() Returns the next auto-generated parameter name.
java.util.Map<java.lang.String,java.lang.Object>	getQueryParameters() Returns a map of parameters used by generated query.
DbEntityDescriptor	getTableDescriptor(java.lang.String tableRef) Returns entity descriptor for provided table reference.
java.lang.String	getTableName(java.lang.String tableRef) Returns table name for provided table reference.
DbSqlBuilder	insert(java.lang.Class entity, java.lang.Object values)
DbSqlBuilder	insert(java.lang.Object values)
DbSqlBuilder	insert(java.lang.String entityName, java.lang.Object values)
DbSqlBuilder	on(java.lang.String source, java.lang.String target)
DbSqlBuilder	onIds()
DbSqlBuilder	ref(java.lang.String columnRef) Resolves single reference.
DbSqlBuilder	reffk(java.lang.String source, java.lang.Object destination) Resolves reference foreign key joins.
DbSqlBuilder	refId(java.lang.String columnRef) Resolves single identity reference.
DbSqlBuilder	refjoin(java.lang.String source, java.lang.String target) Resolves reference join.
java.lang.String	registerColumnDataForColumnCode(java.lang.String tableName, java.lang.String column)
void	registerColumnDataForTableRef(java.lang.String tableRef, java.lang.String tableName)
java.lang.String	registerTableReference(DbEntityDescriptor ded, java.lang.String tableAlias) Registers table reference for provided entity and returns used table reference.
java.lang.String	resolveFkName(DbEntityDescriptor sourceDed, DbEntityDescriptor destDed) Resolves foreign key name.
DbSqlBuilder	setColumnAliasType(ColumnAliasType aliasesType) Specifies column alias type.
DbSqlBuilder	setFkNamingConvention(FkNamingConvention fkNamingConvention)
static DbSqlBuilder	sql() Alternative factory for building query.
DbSqlBuilder	table(java.lang.Class entity) Defines table name and alias.
DbSqlBuilder	table(java.lang.Class entity, java.lang.String alias) Defines table name.
DbSqlBuilder	table(java.lang.Object entity)
DbSqlBuilder	table(java.lang.Object entity, java.lang.String alias)
DbSqlBuilder	table(java.lang.String entityName) Defines table and alias for entity.
DbSqlBuilder	table(java.lang.String entityName, java.lang.String alias) Defines table and alias.
DbSqlBuilder	updateSet(java.lang.String tableRef, java.lang.Object values) Resolves update set.
DbSqlBuilder	updateSetAll(java.lang.String tableRef, java.lang.Object values) Resolves update set.
DbSqlBuilder	value(java.lang.Object value)
DbSqlBuilder	value(java.lang.String name, java.lang.Object value) Resolves values.
DbSqlBuilder	whereCriteria(DbSqlBuilder criteria)

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

dbOrm

protected final DbOrm dbOrm

tables

protected final java.util.Map<java.lang.String,DbEntityDescriptor> tables

Table references used in this query.

tableRefs

protected final java.util.Map<java.lang.String,java.lang.String> tableRefs

Table names used in this query.

columnData

protected final java.util.Map<java.lang.String,ColumnData> columnData

Column or table aliases.

columnCount

protected int columnCount

Column counter for COLUMN_CODE column alias type.

DEFAULT_COLUMN_ALIAS_TYPE

public static ColumnAliasType DEFAULT_COLUMN_ALIAS_TYPE

Default column aliases type.

columnAliasType

protected ColumnAliasType columnAliasType

parameters

protected final java.util.Map<java.lang.String,java.lang.Object> parameters

paramCount

protected int paramCount

DEFAULT_FK_NAMING_CONVENTION

public static FkNamingConvention DEFAULT_FK_NAMING_CONVENTION

Default foreign key naming convention.

fkNamingConvention

protected FkNamingConvention fkNamingConvention

fkMacros

protected static java.lang.String[] fkMacros

firstChunk

protected SqlChunk firstChunk

lastChunk

protected SqlChunk lastChunk

Constructor Detail

DbSqlBuilder

public DbSqlBuilder()

Creates new SQL builder.

Method Detail

create

public static DbSqlBuilder create()

Alternative factory.

sql

public static DbSqlBuilder sql()

Alternative factory for building query.

getDbOrm

public DbOrm getDbOrm()

Returns associated DbOrm.

getTableDescriptor

public DbEntityDescriptor getTableDescriptor(java.lang.String tableRef)

Returns entity descriptor for provided table reference.

getTableName

public java.lang.String getTableName(java.lang.String tableRef)

Returns table name for provided table reference.

registerTableReference

public java.lang.String registerTableReference(DbEntityDescriptor ded,
                                               java.lang.String tableAlias)

Registers table reference for provided entity and returns used table reference.

getColumnAliasType

public ColumnAliasType getColumnAliasType()

Returns column alias type.

setColumnAliasType

public DbSqlBuilder setColumnAliasType(ColumnAliasType aliasesType)

Specifies column alias type. May be null when column aliases are not used.

registerColumnDataForTableRef

public void registerColumnDataForTableRef(java.lang.String tableRef,
                                          java.lang.String tableName)

registerColumnDataForColumnCode

public java.lang.String registerColumnDataForColumnCode(java.lang.String tableName,
                                                        java.lang.String column)

getNextParameterName

public java.lang.String getNextParameterName()

Returns the next auto-generated parameter name.

addParameter

public void addParameter(java.lang.String name,
                         java.lang.Object value)

Adds query parameter.

getFkNamingConvention

public FkNamingConvention getFkNamingConvention()

setFkNamingConvention

public DbSqlBuilder setFkNamingConvention(FkNamingConvention fkNamingConvention)

resolveFkName

public java.lang.String resolveFkName(DbEntityDescriptor sourceDed,
                                      DbEntityDescriptor destDed)

Resolves foreign key name.

chunk

public DbSqlBuilder chunk(SqlChunk chunk)

Appends chunk to the list. Chunks must be added using this method.

getFirstChunk

public SqlChunk getFirstChunk()

Returns previous chunk.

getLastChunk

public SqlChunk getLastChunk()

Returns last chunk.

generateQuery

public java.lang.String generateQuery()

Generates SQL query.

Specified by:

generateQuery in interface DbSqlGenerator

getColumnData

public java.util.Map<java.lang.String,ColumnData> getColumnData()

Returns an optional map of table and column names, used by ResultSetMapper.

Specified by:

getColumnData in interface DbSqlGenerator

getQueryParameters

public java.util.Map<java.lang.String,java.lang.Object> getQueryParameters()

Returns a map of parameters used by generated query. May be invoked only after the DbSqlGenerator.generateQuery().

Specified by:

getQueryParameters in interface DbSqlGenerator

_

public DbSqlBuilder _(java.lang.String sql)

Appends raw SQL to the query.

table

public DbSqlBuilder table(java.lang.String entityName)

Defines table and alias for entity. If entity name contains space it is assumed that both table ref and alias is in one string.

table

public DbSqlBuilder table(java.lang.String entityName,
                          java.lang.String alias)

Defines table and alias.

table

public DbSqlBuilder table(java.lang.Class entity)

Defines table name and alias.

table

public DbSqlBuilder table(java.lang.Class entity,
                          java.lang.String alias)

Defines table name.

table

public DbSqlBuilder table(java.lang.Object entity)

table

public DbSqlBuilder table(java.lang.Object entity,
                          java.lang.String alias)

column

public DbSqlBuilder column(java.lang.String reference)

Resolves column.

column

public DbSqlBuilder column(java.lang.String tableRef,
                           java.lang.String columnRef)

Resolves column.

columnsAll

public DbSqlBuilder columnsAll(java.lang.String tableRef)

Resolves all columns.

columnsIds

public DbSqlBuilder columnsIds(java.lang.String tableRef)

Resolves all identity columns.

insert

public DbSqlBuilder insert(java.lang.String entityName,
                           java.lang.Object values)

insert

public DbSqlBuilder insert(java.lang.Class entity,
                           java.lang.Object values)

insert

public DbSqlBuilder insert(java.lang.Object values)

ref

public DbSqlBuilder ref(java.lang.String columnRef)

Resolves single reference.

refId

public DbSqlBuilder refId(java.lang.String columnRef)

Resolves single identity reference.

refjoin

public DbSqlBuilder refjoin(java.lang.String source,
                            java.lang.String target)

Resolves reference join.

reffk

public DbSqlBuilder reffk(java.lang.String source,
                          java.lang.Object destination)

Resolves reference foreign key joins.

on

public DbSqlBuilder on(java.lang.String source,
                       java.lang.String target)

onIds

public DbSqlBuilder onIds()

value

public DbSqlBuilder value(java.lang.String name,
                          java.lang.Object value)

Resolves values.

value

public DbSqlBuilder value(java.lang.Object value)

updateSet

public DbSqlBuilder updateSet(java.lang.String tableRef,
                              java.lang.Object values)

Resolves update set.

updateSetAll

public DbSqlBuilder updateSetAll(java.lang.String tableRef,
                                 java.lang.Object values)

Resolves update set.

condition

public DbSqlBuilder condition(java.lang.String tableRef,
                              java.lang.Object value)

Creates condition part of the query only for existing columns.

conditionIds

public DbSqlBuilder conditionIds(java.lang.String tableRef,
                                 java.lang.Object value)

Creates condition part of the query for id columns

conditionAll

public DbSqlBuilder conditionAll(java.lang.String tableRef,
                                 java.lang.Object value)

Creates condition part of the query for all columns, including the null values.

conditionSafe

public DbSqlBuilder conditionSafe(java.lang.String tableRef,
                                  java.lang.Object value)

Creates condition part of the query for all columns, including the null values. If all fields are null, returns '1==1'.

_deleteFrom

public DbSqlBuilder _deleteFrom()

_orderBy

public DbSqlBuilder _orderBy()

_and

public DbSqlBuilder _and()

_or

public DbSqlBuilder _or()

_in

public DbSqlBuilder _in()

_between

public DbSqlBuilder _between()

_select

public DbSqlBuilder _select()

_selectCount

public DbSqlBuilder _selectCount()

_from

public DbSqlBuilder _from()

_space

public DbSqlBuilder _space()

_where

public DbSqlBuilder _where()

_update

public DbSqlBuilder _update()

_eq

public DbSqlBuilder _eq()

_lt

public DbSqlBuilder _lt()

_le

public DbSqlBuilder _le()

_gt

public DbSqlBuilder _gt()

_ge

public DbSqlBuilder _ge()

_join

public DbSqlBuilder _join()

_innerJoin

public DbSqlBuilder _innerJoin()

_leftJoin

public DbSqlBuilder _leftJoin()

_rightJoin

public DbSqlBuilder _rightJoin()

whereCriteria

public DbSqlBuilder whereCriteria(DbSqlBuilder criteria)