Package ch.autumo.commons.utils.database

Class DBUtils

java.lang.Object

ch.autumo.commons.utils.database.DBUtils

public class DBUtils
extends Object

DB Utils.

Field Summary

Fields

Modifier and Type	Field	Description
protected static final org.slf4j.Logger	LOG	Logger.
static int	maxRefRecords	Maximum of referenced records to be loaded.
static final int	SORT_BY_ID	Sorting of foreign entities by ID.
static final int	SORT_BY_VALUE	Sorting of foreign entities by display value.

Method Summary

Modifier and Type	Method	Description
static int	countRows(Class<?> clz)	Count rows of type clz (entity class).
static int	countRows(String table)	Count rows of table.
static void	delete(Model model)	Delete a record.
static void	delete(Model model, Set<String> foreignDbKeys)	Delete a many-to-many-relation record.
static void	delete(Entity entity)	Delete a record.
static void	delete(Class<?> entityClass, int id)	Delete a record.
static void	delete(String entity, int id)	Delete a record.
static void	delete(Connection conn, Model model, Set<String> foreignDbKeys)	Delete a many-to-many-relation record.
static void	delete(Connection conn, Entity entity)	Delete a record.
static void	delete(Connection conn, String entity, int id)	Delete a record.
static String	escapeValuesForDb(String value)	Escape single value for DB.
static String	getBooleanDatabaseMappingValue(boolean value)	Get correct DB value for a boolean.
static String	getDisplayValue(Class<?> entityClass, int id)	Get display value: <ID:displayValue>.
static Map<Integer,String>	getDisplayValues(Class<?> entityClass)	Get display values: <ID:displayValue>.
static Map<Integer,String>	getDisplayValues(Class<?> entityClass, int amount)	Get display values: <ID:displayValue>.
static Map<Integer,String>	getDisplayValues(Class<?> entityClass, int amount, int sortType)	Get display values: <ID:displayValue>.
static String	getValue(ResultSet set, String dbColumnName)	Access result set value, no escape of value.
static Integer	insert(Model entity, String columns, String values)	Insert new entity.
static Integer	insert(Connection conn, Entity entity, String columns, String values)	Insert new entity.
static boolean	isSqlBinaryType(int sqlType)	Check if this SQL type is a html-input binary type.
static boolean	isSqlBooelanType(int sqlType)	Check if this SQL type is a html-input boolean type.
static boolean	isSqlDateTimeType(int sqlType)	Check if this SQL type is a html-input date type.
static boolean	isSqlNumberType(int sqlType)	Check if this SQL type is a html-input number type.
static boolean	isSqlTextType(int sqlType)	Check if this SQL type is a html-input text type.
static Connection	newGlobalConnection()	Get an new global DB connection.
static Timestamp	now()	Now time-stamp.
static List<Model>	query(Class<?> entityClass, String fullQuery, Object[] values)	Select a records of type entityClass (entity class).
static void	retireGlobalConnection(Connection conn)	Retire a global DB connection.
static Model	selectRecord(Class<?> entityClass, int id)	Select a record of type clz (entity class).
static List<Model>	selectRecords(Class<?> entityClass)	Select a records of type entityClass (entity class).
static List<Model>	selectRecords(Class<?> entityClass, int amount)	Select a records of type entityClass (entity class).
static List<Model>	selectRecords(Class<?> entityClass, int amount, int sortType)	Select a records of type entityClass (entity class).
static List<Model>	selectRecords(Class<?> entityClass, String condition, Object[] values)	Select a records of type entityClass (entity class).
static List<Model>	selectRecords(Class<?> entityClass, String condition, Object[] values, int amount)	Select a records of type entityClass (entity class).
static List<Model>	selectRecords(Class<?> entityClass, String condition, Object[] values, int amount, int sortType)	Select a records of type entityClass (entity class).
static Timestamp	timestamp(Date date)	Get time-stamp.
static void	update(Entity entity, String columns, String values)	Update entity.
static void	update(Connection conn, Entity entity, String columns, String values)	Update entity.
static void	updateModel(Entity entity, Map<String,Map<String,DBField>> model)	Update the given model with entity from database, if it hasn't been updated yet.
static void	updateSecretUserKey(int userId, String newSecretUserKey)	Update secret user key.

Methods inherited from class java.lang.Object

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

Field Details

LOG

protected static final org.slf4j.Logger LOG

Logger.

maxRefRecords

public static int maxRefRecords

Maximum of referenced records to be loaded.

SORT_BY_ID

public static final int SORT_BY_ID

Sorting of foreign entities by ID.

See Also:

• Constant Field Values

SORT_BY_VALUE

public static final int SORT_BY_VALUE

Sorting of foreign entities by display value.

See Also:

• Constant Field Values

Method Details

getDisplayValues

public static Map<Integer,String> getDisplayValues(Class<?> entityClass)
                                            throws Exception

Get display values: <ID:displayValue>. Max. 200 records to be returned.

Parameters:

entityClass - class

Returns:

entries

Throws:

Exception - exception

getDisplayValues

public static Map<Integer,String> getDisplayValues(Class<?> entityClass,
 int amount)
                                            throws Exception

Get display values: <ID:displayValue>. Max. 200 records to be returned.

Parameters:

entityClass - class

amount - max. amount of records to be loaded

Returns:

entries

Throws:

SQLException - SQL exception

Exception

getDisplayValues

public static Map<Integer,String> getDisplayValues(Class<?> entityClass,
 int amount,
 int sortType)
                                            throws Exception

Get display values: <ID:displayValue>.

Parameters:

entityClass - class

amount - max. amount of records to be loaded

sortType - sort entries by ID or by values, see 0 and 1

Returns:

entries

Throws:

Exception - exception

getDisplayValue

public static String getDisplayValue(Class<?> entityClass,
 int id)
                              throws Exception

Get display value: <ID:displayValue>.

Parameters:

entityClass - class

id - id

Returns:

entry

Throws:

Exception - exception

escapeValuesForDb

public static String escapeValuesForDb(String value)

Escape single value for DB.

Parameters:

value - value

Returns:

escaped value

updateSecretUserKey

public static void updateSecretUserKey(int userId,
 String newSecretUserKey)
                                throws SQLException

Update secret user key.

Parameters:

userId - DB user id

newSecretUserKey - new secret user key

Throws:

SQLException - SQL exception

countRows

public static int countRows(Class<?> clz)
                     throws SQLException

Count rows of type clz (entity class).

Parameters:

clz - entity class

Returns:

amount of rows or -1 if something bad happens

Throws:

SQLException - SQL exception

countRows

public static int countRows(String table)
                     throws SQLException

Count rows of table.

Parameters:

table - table DB name

Returns:

amount of rows or -1 if something bad happens

Throws:

SQLException - SQL exception

selectRecord

public static Model selectRecord(Class<?> entityClass,
 int id)
                          throws Exception

Select a record of type clz (entity class).

Parameters:

entityClass - entity class

id - DB record id

Returns:

entity bean

Throws:

Exception - if selection fails

selectRecords

public static List<Model> selectRecords(Class<?> entityClass)
                                 throws Exception

Select a records of type entityClass (entity class). Max. 200 records to be returned.

Parameters:

entityClass - class

Returns:

entity beans

Throws:

Exception - exception

selectRecords

public static List<Model> selectRecords(Class<?> entityClass,
 int amount)
                                 throws Exception

Select a records of type entityClass (entity class). Max. 200 records to be returned.

Parameters:

entityClass - class

amount - max. amount of records to be loaded

Returns:

entity beans

Throws:

Exception - exception

selectRecords

public static List<Model> selectRecords(Class<?> entityClass,
 int amount,
 int sortType)
                                 throws Exception

Select a records of type entityClass (entity class).

Parameters:

entityClass - entity class

amount - max. amount of records to be loaded

sortType - sort entries by ID or by values, see 0 and 1

Returns:

entity beans

Throws:

Exception - exception

selectRecords

public static List<Model> selectRecords(Class<?> entityClass,
 String condition,
 Object[] values)
                                 throws Exception

Select a records of type entityClass (entity class).

Parameters:

entityClass - class

condition - condition for where clause, e.g. 'age >= ? AND gender = ?'

values - values for the condition

Returns:

entity beans

Throws:

Exception - exception

selectRecords

public static List<Model> selectRecords(Class<?> entityClass,
 String condition,
 Object[] values,
 int amount)
                                 throws Exception

Select a records of type entityClass (entity class).

Parameters:

entityClass - class

condition - condition for where clause, e.g. 'age >= ? AND gender = ?'

values - values for the condition

amount - max. amount of records to be loaded

Returns:

entity beans

Throws:

Exception - exception

selectRecords

public static List<Model> selectRecords(Class<?> entityClass,
 String condition,
 Object[] values,
 int amount,
 int sortType)
                                 throws Exception

Select a records of type entityClass (entity class).

Parameters:

entityClass - entity class

condition - condition for where clause, e.g. 'age >= ? AND gender = ?'

values - values for the condition

amount - max. amount of records to be loaded

sortType - sort entries by ID or by values, see 0 and 1

Returns:

entity beans

Throws:

Exception - exception

query

public static List<Model> query(Class<?> entityClass,
 String fullQuery,
 Object[] values)
                         throws SQLException

Select a records of type entityClass (entity class). Respect the database dialect used for the underlying database.

Parameters:

entityClass - entity class; must match the table in the query

fullQuery - the full SQL query with query place-holders

values - values for the query

Returns:

entity beans

Throws:

SQLException - SQL exception

delete

public static void delete(Model model,
 Set<String> foreignDbKeys)
                   throws SQLException

Delete a many-to-many-relation record.

Parameters:

model - model

foreignDbKeys - DB foreign keys as given by the keys within the return value of the model method Model.getForeignReferences()

Throws:

SQLException - SQL exception

delete

public static void delete(Model model)
                   throws SQLException

Delete a record.

Parameters:

model - model

Throws:

SQLException - SQL exception

delete

public static void delete(Entity entity)
                   throws SQLException

Delete a record.

Parameters:

entity - entity

Throws:

SQLException - SQL exception

delete

public static void delete(Class<?> entityClass,
 int id)
                   throws SQLException

Delete a record.

Parameters:

entityClass - entity class

id - if

Throws:

SQLException - SQL exception

delete

public static void delete(String entity,
 int id)
                   throws SQLException

Delete a record.

Parameters:

entity - entity table name

id - if

Throws:

SQLException - SQL exception

update

public static void update(Entity entity,
 String columns,
 String values)
                   throws SQLException

Update entity.

Parameters:

entity - entity

columns - columns; "a,b,c".

values - values; "'1','2','3'".

Throws:

SQLException - SQL exception

insert

public static Integer insert(Model entity,
 String columns,
 String values)
                      throws SQLException

Insert new entity.

Parameters:

entity - entity

columns - columns; "a,b,c".

values - values; "'1','2','3'".

Returns:

generated id id of newly inserted entity

Throws:

SQLException - SQL exception

timestamp

public static Timestamp timestamp(Date date)

Get time-stamp.

Parameters:

date - date

Returns:

time-stamp.

now

public static Timestamp now()

Now time-stamp.

Returns:

time-stamp.

getValue

public static String getValue(ResultSet set,
 String dbColumnName)
                       throws SQLException

Access result set value, no escape of value.

Parameters:

set - result set

dbColumnName - db column name

Returns:

escaped db value

Throws:

SQLException - SQL exception

isSqlTextType

public static boolean isSqlTextType(int sqlType)

Check if this SQL type is a html-input text type.

Parameters:

sqlType - SQL type

Returns:

true if it is a html-input text type.

isSqlNumberType

public static boolean isSqlNumberType(int sqlType)

Check if this SQL type is a html-input number type.

Parameters:

sqlType - SQL type

Returns:

true if it is a html-input number type.

isSqlDateTimeType

public static boolean isSqlDateTimeType(int sqlType)

Check if this SQL type is a html-input date type.

Parameters:

sqlType - SQL type

Returns:

true if it is a html-input date type.

isSqlBinaryType

public static boolean isSqlBinaryType(int sqlType)

Check if this SQL type is a html-input binary type.

Parameters:

sqlType - SQL type

Returns:

true if it is a html-input binary type.

isSqlBooelanType

public static boolean isSqlBooelanType(int sqlType)

Check if this SQL type is a html-input boolean type.

Parameters:

sqlType - SQL type

Returns:

true if it is a html-input boolean type.

getBooleanDatabaseMappingValue

public static String getBooleanDatabaseMappingValue(boolean value)

Get correct DB value for a boolean.

Parameters:

value - boolean value

Returns:

DB boolean value as string

updateModel

public static void updateModel(Entity entity,
 Map<String,Map<String,DBField>> model)
                        throws SQLException

Update the given model with entity from database, if it hasn't been updated yet.

Parameters:

entity - entity

model - model

Throws:

SQLException - SQL exception

newGlobalConnection

public static Connection newGlobalConnection()
                                      throws SQLException

Get an new global DB connection. You have to roll back or commit the transaction, before you retire it with retireGlobalConnection(Connection). If you use DBUtils roll-backs are done automatically and you'll receive an SQLException. Don't close it by yourself!

Returns:

global DB connection

Throws:

SQLException - SQL exception

retireGlobalConnection

public static void retireGlobalConnection(Connection conn)
                                   throws SQLException

Retire a global DB connection.

Parameters:

conn - connection

Throws:

SQLException - SQL exception

See Also:

• newGlobalConnection()

insert

public static Integer insert(Connection conn,
 Entity entity,
 String columns,
 String values)
                      throws SQLException

Insert new entity.

Parameters:

conn - global connection

entity - entity

columns - columns; "a,b,c".

values - values; "'1','2','3'".

Returns:

generated id

Throws:

SQLException - SQL Exception

update

public static void update(Connection conn,
 Entity entity,
 String columns,
 String values)
                   throws SQLException

Update entity.

Parameters:

conn - global connection

entity - entity

columns - columns; "a,b,c".

values - values; "'1','2','3'".

Throws:

SQLException - SQL Exception

delete

public static void delete(Connection conn,
 String entity,
 int id)
                   throws SQLException

Delete a record.

Parameters:

conn - global connection

entity - entity table name

id - if

Throws:

SQLException - SQL Exception

delete

public static void delete(Connection conn,
 Model model,
 Set<String> foreignDbKeys)
                   throws SQLException

Delete a many-to-many-relation record.

Parameters:

conn - global connection

model - model

foreignDbKeys - DB foreign keys as given by the keys within the return value of the model method Model.getForeignReferences()

Throws:

SQLException - SQL Exception

delete

public static void delete(Connection conn,
 Entity entity)
                   throws SQLException

Delete a record.

Parameters:

conn - global connection

entity - entity

Throws:

SQLException - SQL Exception