Package sk.iway.iwcm

Class DB

java.lang.Object

sk.iway.iwcm.DB

Direct Known Subclasses:

DocDB, DocTreeDB, GroupsDB, HistoryDB, StatDB, TemplatesDB, UserGroupsDB

public class DB
extends Object

nadtrieda DB tried, ma zakladne uzitocne funkcie

Constructor Summary

Constructors

Constructor	Description
DB()

Method Summary

Modifier and Type	Method	Description
static String	dumpResultSet(ResultSet rs)	Produces a string representation of a complete result set
static void	execute(String sql, Object... arguments)
static String	filterEscaped(String escapedString)	Deprecated. - use filterHtml
static String	filterHtml(String data)	Nahradi nebezpecne HTML znaky za entity
static String	fixAiCiCol(String column)	Add lower/unaccent(column) for case insensitive search in Oracle/PostgreSQL
static String	fixAiCiValue(String value)	Fix value for case insensitive search in Oracle/PostgreSQL
static String	fixUntrustedColumnName(String columnName, String tableNameAlias, String... additionalTableNameAliases)	Resolves an untrusted column name against table qualifiers that are known to exist in the current query.
static Boolean	getBoolean(ResultSet rs, String colName)	Vrati Boolean hodnotu z databazy, pricom korektne vrati aj NULL
static String	getBooleanSql(boolean b)	Returns 1/0 or true/false dependind on DB type
static Date	getDate(ResultSet db_result, String fieldName)
static String	getDbDate(ResultSet db_result, String fieldName)	vrati naformatovany datum z databazy
static String	getDbDateTime(ResultSet db_result, String fieldName)	Gets the dbDateTime attribute of the DB class
static String	getDbDateTime(ResultSet db_result, String fieldName, String serverName)	Gets the dbDateTime attribute of the DB class
static String	getDbString(ResultSet db_result, String fieldName)	vrati prekodovany a trimnuty string z result setu (povodne sa pouzivalo kvoli Oracle)
static String	getDbString(ResultSet db_result, String fieldName, boolean filter)	vrati prekodovany a trimnuty string z result setu (povodne sa pouzivalo kvoli Oracle), ak je filter true vykona aj ResponseUtils.filter
static String	getDbTime(ResultSet db_result, String fieldName)	vrati naformatovany cas z databazy
static Timestamp	getDbTimestamp(long time)
static long	getDbTimestamp(ResultSet rs, String fieldName)	Vrati long hodnotu Timestampu (ak nie je null), inak vrati 0
static List<org.apache.commons.beanutils.DynaBean>	getDynaList(String sql)	Vrati List DynaBeanov pre zadane SQL
static List<org.apache.commons.beanutils.DynaBean>	getDynaList(String sql, List<?> params)
static List<org.apache.commons.beanutils.DynaBean>	getDynaList(String sql, List<?> params, String dbName)
static String	getFullName(ResultSet rs)
static String	getFullName(ResultSet rs, String titleName)	Gets the fullName attribute of the DB object
static Integer	getInteger(ResultSet rs, String colName)	Vrati Integer hodnotu z databazy, pricom korektne vrati aj NULL
static int	getIntValue(Object o, int defaultValue)
static long	getLongValue(Object o, long defaultValue)
static String	getOnlyNumbersIn(String ids)	Vrati a skontroluje IN zoznam pre SQL tak, aby obsahoval len cisla, cize z "1,xxx,2,3" spravi "1,2,3"
static String	getOnlyNumbersIn(String ids, boolean checkGroups)	Vrati a skontroluje IN zoznam pre SQL tak, aby obsahoval len cisla, cize z "1,xxx,2,3" spravi "1,2,3" ak sa jedna o id-cka GroupDetails tak v pripade ze je zapnuty cloud sa kontroluje aj ich dostupnost pre aktualnu domenu
static int	getSqlParamsDatatable(List<Object> params, PreparedStatement ps, int psCounter)	Fill PreparedStatement with parameters
static String	getSqlQueryDatatable(String fieldName, String searchText, boolean addAnd, List<Object> params)
static long	getTimestamp(String dateTime)
static long	getTimestamp(String date, String time)	return sql timestamp from given date and time
static long	getTimestamp(String date, String time, String serverName)
static long	getTimestampNotAfterYear(long timestamp, int year)	Upravi hodnotu timestampu tak, aby nebola za zadanym rokom (kedze DB to tak nevedia mat)
static long	getTimestampNotBeforeAfterYear(long timestamp, int yearFrom, int yearTo)	Upravi hodnotu timestampu tak, aby nebola pred a za zadanym rokom (kedze DB to tak nevedia mat)
static long	getTimestampNotBeforeYear(long timestamp, int year)	Upravi hodnotu timestampu tak, aby nebola pred zadanym rokom (kedze DB to tak nevedia mat)
static String	internationalToEnglish(String source)	prekoduje diakritiku a azbuku do cisteho ascii
static boolean	isHtmlAllowed(String fieldName)	Overi, ci je mozne v danom DB stlpci pouzit HTML kod, ak nie, tak sa pri citani rovno escapnu specialne znaky ako < alebo > aby nebolo mozne vykonat XSS utok https://thoughts-on-java.org/jpa-21-how-to-implement-type-converter/
static boolean	isValidColumnName(String value, boolean allowDot)	Checks whether the supplied value is an unquoted SQL identifier.
static String	prepareString(String s, int maxLen)	Oreze s tak, aby nebol dlhsi ako maxLen
static BigDecimal	queryForBigDecimal(String sql, Object... parameters)
static double	queryForDouble(String sql, Object... parameters)
static int	queryForInt(String sql, Object... parameters)
static List	queryForList(String sql, Object... parameters)
static long	queryForLong(String sql, Object... parameters)
static String	queryForString(String sql, Object... parameters)
static String	removeSlashes(String sqlParam)	odstrani apostrofy zo stringu, treba volat vzdy, ked sa nejaky parameter dava priamo do SQL prikazu
static String	replace(String src, String oldStr, String newStr)	Deprecated. replaced by Tools.replace(String, String, String)
static void	resetHtmlAllowedFields()
static void	setClob(PreparedStatement ps, int pos, String data)	nastavi pole text, alebo clob v preparedStatemente
static String	unfilterHtml(String data)	Replaces HTML entities with their corresponding characters, e.g.

Methods inherited from class java.lang.Object

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

Constructor Details

DB

public DB()

Method Details

isValidColumnName

public static boolean isValidColumnName(String value,
 boolean allowDot)

Checks whether the supplied value is an unquoted SQL identifier. One or more dot-separated qualifiers or property-path segments can be enabled explicitly. This method validates only identifier syntax; it does not verify that the path, table, or column exists. A qualified value must not be appended directly to an SQL expression because the same syntax can also represent a package function. Use fixUntrustedColumnName(String, String, String...) when the value is used as a column reference in an SQL expression.

Parameters:

value - identifier to validate (column name or a qualified/property path)

allowDot - true to allow a path such as customer.address.city

Returns:

true for field_a and, when enabled, qualified paths

fixUntrustedColumnName

public static String fixUntrustedColumnName(String columnName,
 String tableNameAlias,
 String... additionalTableNameAliases)

Resolves an untrusted column name against table qualifiers that are known to exist in the current query. A qualifier can be a table name or its SQL alias. An unqualified value is bound to defaultQualifier; a qualified value is accepted only when its qualifier matches one of the trusted qualifiers. The returned reference is always reconstructed from the trusted qualifier and the validated column token. Multi-segment paths are intentionally rejected because this resolver is meant for SQL column references, not JPQL property paths.

This method validates syntax and query context, but it does not verify that the column exists in the database.

 column = DB.fixUntrustedColumnName(column, "documents");
 if (column == null) return Collections.emptyList();
 

Parameters:

columnName - unqualified or qualified column name

tableNameAlias - trusted table name or alias used for unqualified values

additionalTableNameAliases - other trusted qualifiers present in the query

Returns:

canonical qualifier.column reference, or null when invalid

isHtmlAllowed

public static boolean isHtmlAllowed(String fieldName)

Overi, ci je mozne v danom DB stlpci pouzit HTML kod, ak nie, tak sa pri citani rovno escapnu specialne znaky ako < alebo > aby nebolo mozne vykonat XSS utok https://thoughts-on-java.org/jpa-21-how-to-implement-type-converter/

Parameters:

fieldName -

Returns:

resetHtmlAllowedFields

public static void resetHtmlAllowedFields()

getDbString

public static String getDbString(ResultSet db_result,
 String fieldName,
 boolean filter)
                          throws SQLException

vrati prekodovany a trimnuty string z result setu (povodne sa pouzivalo kvoli Oracle), ak je filter true vykona aj ResponseUtils.filter

Parameters:

db_result -

fieldName -

filter -

Returns:

Throws:

SQLException

getDbString

public static String getDbString(ResultSet db_result,
 String fieldName)
                          throws SQLException

vrati prekodovany a trimnuty string z result setu (povodne sa pouzivalo kvoli Oracle)

Parameters:

db_result - result set

fieldName - meno fieldu v databaze

Returns:

prekodovany a trimnuty string

Throws:

Exception - Description of the Exception

SQLException

filterHtml

public static String filterHtml(String data)

Nahradi nebezpecne HTML znaky za entity

Parameters:

data -

Returns:

unfilterHtml

public static String unfilterHtml(String data)

Replaces HTML entities with their corresponding characters, e.g. &lt; is replaced with <

Parameters:

data -

Returns:

getDbTimestamp

public static long getDbTimestamp(ResultSet rs,
 String fieldName)

Vrati long hodnotu Timestampu (ak nie je null), inak vrati 0

Parameters:

rs -

fieldName -

Returns:

Throws:

Exception

getDbTimestamp

public static Timestamp getDbTimestamp(long time)

getDbDate

public static String getDbDate(ResultSet db_result,
 String fieldName)
                        throws SQLException

vrati naformatovany datum z databazy

Parameters:

db_result - result set

fieldName - meno fieldu v databaze

Returns:

naformatovany datum z databazy

Throws:

Exception - Description of the Exception

SQLException

getDate

public static Date getDate(ResultSet db_result,
 String fieldName)
                    throws SQLException

Throws:

SQLException

getDbTime

public static String getDbTime(ResultSet db_result,
 String fieldName)
                        throws Exception

vrati naformatovany cas z databazy

Parameters:

db_result - result set

fieldName - meno fieldu v databaze

Returns:

naformatovany cas z databazy

Throws:

Exception - Description of the Exception

getDbDateTime

public static String getDbDateTime(ResultSet db_result,
 String fieldName)
                            throws SQLException

Gets the dbDateTime attribute of the DB class

Parameters:

db_result - Description of the Parameter

fieldName - Description of the Parameter

Returns:

The dbDateTime value

Throws:

Exception - Description of the Exception

SQLException

getDbDateTime

public static String getDbDateTime(ResultSet db_result,
 String fieldName,
 String serverName)
                            throws SQLException

Gets the dbDateTime attribute of the DB class

Parameters:

db_result - Description of the Parameter

fieldName - Description of the Parameter

serverName - Description of the Parameter

Returns:

The dbDateTime value

Throws:

Exception - Description of the Exception

SQLException

getTimestamp

public static long getTimestamp(String date,
 String time,
 String serverName)

getTimestamp

public static long getTimestamp(String dateTime)

getTimestamp

public static long getTimestamp(String date,
 String time)

return sql timestamp from given date and time

Parameters:

date - datum

time - cas

Returns:

The timestamp value

internationalToEnglish

public static String internationalToEnglish(String source)

prekoduje diakritiku a azbuku do cisteho ascii

Parameters:

source - Description of the Parameter

Returns:

Description of the Return Value

replace

@Deprecated
public static String replace(String src,
 String oldStr,
 String newStr)

Deprecated.

replaced by Tools.replace(String, String, String)

Replace stringu na string

Parameters:

src - zdrojovy string

oldStr - co sa ma nahradit

newStr - za co sa ma nahradit

Returns:

string src v ktorom je nahradene oldStr za newStr

setClob

public static void setClob(PreparedStatement ps,
 int pos,
 String data)
                    throws Exception

nastavi pole text, alebo clob v preparedStatemente

Parameters:

ps - The new clob value

pos - The new clob value

data - The new clob value

Throws:

Exception - Description of the Exception

removeSlashes

public static String removeSlashes(String sqlParam)

odstrani apostrofy zo stringu, treba volat vzdy, ked sa nejaky parameter dava priamo do SQL prikazu

Parameters:

sqlParam -

Returns:

dumpResultSet

public static String dumpResultSet(ResultSet rs)
                            throws SQLException

Produces a string representation of a complete result set

Parameters:

rs - The ResultSet to be displayed

Returns:

The string representation

Throws:

SQLException - to indicate a problem with the ResultSet

getFullName

public static String getFullName(ResultSet rs)
                          throws Exception

Throws:

Exception

getFullName

public static String getFullName(ResultSet rs,
 String titleName)
                          throws Exception

Gets the fullName attribute of the DB object

Parameters:

rs - Description of the Parameter

Returns:

The fullName value

Throws:

Exception

prepareString

public static String prepareString(String s,
 int maxLen)

Oreze s tak, aby nebol dlhsi ako maxLen

Parameters:

s -

maxLen -

Returns:

getDynaList

public static List<org.apache.commons.beanutils.DynaBean> getDynaList(String sql)

Vrati List DynaBeanov pre zadane SQL

Parameters:

sql - - SQL prikaz

Returns:

getDynaList

public static List<org.apache.commons.beanutils.DynaBean> getDynaList(String sql,
 List<?> params)

getDynaList

public static List<org.apache.commons.beanutils.DynaBean> getDynaList(String sql,
 List<?> params,
 String dbName)

getIntValue

public static int getIntValue(Object o,
 int defaultValue)

getLongValue

public static long getLongValue(Object o,
 long defaultValue)

execute

public static void execute(String sql,
 Object... arguments)

queryForInt

public static int queryForInt(String sql,
 Object... parameters)

queryForLong

public static long queryForLong(String sql,
 Object... parameters)

queryForString

public static String queryForString(String sql,
 Object... parameters)

queryForDouble

public static double queryForDouble(String sql,
 Object... parameters)

queryForBigDecimal

public static BigDecimal queryForBigDecimal(String sql,
 Object... parameters)

queryForList

public static List queryForList(String sql,
 Object... parameters)

getOnlyNumbersIn

public static String getOnlyNumbersIn(String ids)

Vrati a skontroluje IN zoznam pre SQL tak, aby obsahoval len cisla, cize z "1,xxx,2,3" spravi "1,2,3"

Parameters:

ids -

Returns:

getOnlyNumbersIn

public static String getOnlyNumbersIn(String ids,
 boolean checkGroups)

Vrati a skontroluje IN zoznam pre SQL tak, aby obsahoval len cisla, cize z "1,xxx,2,3" spravi "1,2,3" ak sa jedna o id-cka GroupDetails tak v pripade ze je zapnuty cloud sa kontroluje aj ich dostupnost pre aktualnu domenu

Parameters:

ids -

checkGroups - indikuje ci sa jedna o id-cka GroupDetails

Returns:

getTimestampNotBeforeYear

public static long getTimestampNotBeforeYear(long timestamp,
 int year)

Upravi hodnotu timestampu tak, aby nebola pred zadanym rokom (kedze DB to tak nevedia mat)

Parameters:

timestamp -

year -

Returns:

getTimestampNotAfterYear

public static long getTimestampNotAfterYear(long timestamp,
 int year)

Upravi hodnotu timestampu tak, aby nebola za zadanym rokom (kedze DB to tak nevedia mat)

Parameters:

timestamp -

year -

Returns:

getTimestampNotBeforeAfterYear

public static long getTimestampNotBeforeAfterYear(long timestamp,
 int yearFrom,
 int yearTo)

Upravi hodnotu timestampu tak, aby nebola pred a za zadanym rokom (kedze DB to tak nevedia mat)

Parameters:

timestamp -

yearFrom -

yearTo -

Returns:

filterEscaped

@Deprecated
public static String filterEscaped(String escapedString)

Deprecated.

- use filterHtml

osetri HTML znacky v retazci - DEPRECATED, pouzi filterHtml

Parameters:

escapedString -

Returns:

*

getInteger

public static Integer getInteger(ResultSet rs,
 String colName)
                          throws SQLException

Vrati Integer hodnotu z databazy, pricom korektne vrati aj NULL

Parameters:

rs -

colName -

Returns:

Throws:

SQLException

getBoolean

public static Boolean getBoolean(ResultSet rs,
 String colName)
                          throws SQLException

Vrati Boolean hodnotu z databazy, pricom korektne vrati aj NULL

Parameters:

rs -

colName -

Returns:

Throws:

SQLException

getBooleanSql

public static String getBooleanSql(boolean b)

Returns 1/0 or true/false dependind on DB type

Parameters:

b -

Returns:

fixAiCiCol

public static String fixAiCiCol(String column)

Add lower/unaccent(column) for case insensitive search in Oracle/PostgreSQL

Parameters:

column -

Returns:

fixAiCiValue

public static String fixAiCiValue(String value)

Fix value for case insensitive search in Oracle/PostgreSQL

Parameters:

value -

Returns:

getSqlQueryDatatable

public static String getSqlQueryDatatable(String fieldName,
 String searchText,
 boolean addAnd,
 List<Object> params)

getSqlParamsDatatable

public static int getSqlParamsDatatable(List<Object> params,
 PreparedStatement ps,
 int psCounter)
                                 throws SQLException

Fill PreparedStatement with parameters

Parameters:

params - - list of Parameters

ps - - prepared statement

psCounter - - counter of parameters, starts with 1 for first parameter

Returns:

- new counter of parameters

Throws:

SQLException