public interface IDatabaseAccess
There are several pre-defined select-calls for quickly getting single values (
selectSingle
), optionally ordered (
selectSingleOrdered
), for getting the result of a
single column as a list ( selectList
), and of course for full selects
that return a list of rows ( select
), optionally ordered (
selectOrdered
).
update
, delete
, and
insert
are available for manipulating existing data in case the database is not
opened readonly.Modifier and Type | Interface and Description |
---|---|
static class |
IDatabaseAccess.Where
May be used to provide a where condition with a large number of where conditions.
|
Modifier and Type | Method and Description |
---|---|
void |
delete(java.lang.String table,
IDatabaseAccess.Where where)
The same as
delete(String, String) but with possibly very large where condition. |
void |
delete(java.lang.String table,
java.lang.String where)
Delete all rows with the given condition.
|
java.lang.String |
escapeColumn(java.lang.String column) |
java.util.Map<java.lang.String,java.lang.Object> |
executeSql(java.lang.String[] sql,
java.lang.String... columns)
CAUTION!!!
|
java.lang.String |
formatDate(java.util.Date date)
Format a date object according to the locale of the current database connection.
|
java.lang.String |
getCurrentDriver() |
int[] |
insert(java.lang.String table,
java.lang.String idColumnName,
java.util.List<java.lang.String> columns,
java.util.List<java.util.List<java.lang.Object>> values)
Batch-insertion of new rows to the given table.
|
int |
insert(java.lang.String table,
java.lang.String idColumnName,
java.util.Map<java.lang.String,java.lang.Object> insertion)
Insert a new row into the given table.
|
boolean |
isReadOnly() |
boolean |
isReplication() |
boolean |
isSqlExceptionTooComplexQuery(java.sql.SQLException e)
Whether or not the given exception is a database-specific exception that the query was too complex.
|
java.util.List<java.util.Map<java.lang.String,java.lang.Object>> |
select(java.lang.String table,
IDatabaseAccess.Where where,
java.lang.String... columns)
The same as
select(String, String, String...) , but with flexible where condition that may have a large
number of expressions. |
java.util.List<java.util.Map<java.lang.String,java.lang.Object>> |
select(java.lang.String table,
java.lang.String where,
java.lang.String... columns)
Complex select statement that retrieves a list of rows.
|
java.util.List<java.util.Map<java.lang.String,java.lang.Object>> |
selectDistinct(java.lang.String table,
java.lang.String where,
java.lang.String... columns)
The same as
select(String, String, String...) , but the resulting rows are distinct. |
java.util.List<java.util.Map<java.lang.String,java.lang.Object>> |
selectDistinctOrdered(java.lang.String table,
java.lang.String where,
java.lang.String[] orderColumns,
java.lang.String... columns)
The same as
#selectOrdered(String, String, String, String...) , but the resulting rows are distinct. |
<T> java.util.List<T> |
selectList(java.lang.String table,
IDatabaseAccess.Where where,
java.lang.String column)
Returns a list of values for the given column for all rows that match the where condition.
|
<T> java.util.List<T> |
selectList(java.lang.String table,
java.lang.String where,
java.lang.String column)
Returns a list of values for the given column for all rows that match the where condition.
|
<T> java.util.List<T> |
selectListOrdered(java.lang.String table,
java.lang.String where,
java.lang.String[] orderColumns,
java.lang.String column)
Returns a list of values for the given column for all rows that match the where condition ordered by the given
order columns.
|
java.util.List<java.util.Map<java.lang.String,java.lang.Object>> |
selectOrdered(java.lang.String table,
java.lang.String where,
java.lang.String[] orderColumns,
java.lang.String... columns)
The same as
select(String, String, String...) , but the resulting rows are ordered by the columns
orderColumns . |
<T> T |
selectSingle(java.lang.String table,
java.lang.String where,
java.lang.String column)
Returns the single value of the first row that matches the where condition.
|
<T> T |
selectSingleOrdered(java.lang.String table,
java.lang.String where,
java.lang.String[] orderColumns,
java.lang.String column)
Returns the single value of the first row that matches the where condition, ordered by the specified column.
|
java.util.Map<java.lang.String,java.lang.Object> |
selectSingleRow(java.lang.String table,
java.lang.String where,
java.lang.String... columns)
The same as
select(String, String, String...) , but only the first row is returned. |
java.lang.String |
sqlColumnAsString(java.lang.String column)
Deprecated.
Use
sqlColumnAsString(String, int) instead. |
java.lang.String |
sqlColumnAsString(java.lang.String column,
int strSize)
Several SQL drivers implement String comparison differently for varchar, nvarchar, and ntext column types.
|
java.lang.String |
sqlIndexOf(java.lang.String text,
java.lang.String needle)
SQL dialects have different implementations of finding a substring in another string.
|
int |
update(java.lang.String table,
java.lang.String where,
java.util.Map<java.lang.String,java.lang.Object> updates)
Performs an update operation on the given table.
|
java.lang.String getCurrentDriver()
java.util.List<java.util.Map<java.lang.String,java.lang.Object>> select(java.lang.String table, java.lang.String where, java.lang.String... columns) throws java.sql.SQLException
column
and whose values are the actual objects in the table cells.
The objects may be of different types, depending on the respective table schema.
The where
parameter defines the condition that must hold for the rows that are returned.
Please note: Strings must be escaped!
"t_object o, t_connector c"
, where =
"o.Object_ID = c.Start_Object_ID"
, and columns =
["o.Name as Name", "c.Name as ConnectorName"]
.table
- The table, e.g. "t_object"
. Must not be null
.where
- The where condition, e.g. "Package_ID = 42 AND Object_Type = 'Class'"
. May be
null
.columns
- An array of requested columns, e.g. ["Object_ID as ID", "Name", "Abstract"]
. Must not be
null
.["ID" -> 23, "Name" -> "MyClass", "Abstract" -> 1]
.java.sql.SQLException
- In case there is a syntax error in the parameters or the connection to the database fails.java.util.List<java.util.Map<java.lang.String,java.lang.Object>> select(java.lang.String table, IDatabaseAccess.Where where, java.lang.String... columns) throws java.sql.SQLException
select(String, String, String...)
, but with flexible where condition that may have a large
number of expressions. The reason are database limits like:
Please note: this does only work reliably if whereSegments
yield exclusive results!
The reason is simple: if multiple queries are accumulated, each with a subset of whereSegments, then there might
be duplicate result entries if the whereSegments are *not* exclusive.
Example:
table = "t_object"
whereWithPlaceholder = "Object_Type='Class' AND Object_ID IN (%s)"
whereSegments = idList.stream().map(id -> id.toString()).collect(Collectors.toList())
delimiter = ","
columns = "Name", "Object_ID", "ea_guid"
table
- The table.where
- The where condition which may have the above explained properties.columns
- The columns for the query.java.sql.SQLException
java.util.List<java.util.Map<java.lang.String,java.lang.Object>> selectDistinct(java.lang.String table, java.lang.String where, java.lang.String... columns) throws java.sql.SQLException
select(String, String, String...)
, but the resulting rows are distinct.java.sql.SQLException
java.util.List<java.util.Map<java.lang.String,java.lang.Object>> selectOrdered(java.lang.String table, java.lang.String where, java.lang.String[] orderColumns, java.lang.String... columns) throws java.sql.SQLException
select(String, String, String...)
, but the resulting rows are ordered by the columns
orderColumns
. The parameter orderColumns
does not need to be included in the parameter
columns
.java.sql.SQLException
java.util.List<java.util.Map<java.lang.String,java.lang.Object>> selectDistinctOrdered(java.lang.String table, java.lang.String where, java.lang.String[] orderColumns, java.lang.String... columns) throws java.sql.SQLException
#selectOrdered(String, String, String, String...)
, but the resulting rows are distinct.java.sql.SQLException
java.util.Map<java.lang.String,java.lang.Object> executeSql(java.lang.String[] sql, java.lang.String... columns) throws java.sql.SQLException
select*, insert,
and update
methods!
All sql statements are executed and if the last is a select, its first row is returned. Otherwise, an empty map is returned.
java.sql.SQLException
<T> T selectSingleOrdered(java.lang.String table, java.lang.String where, java.lang.String[] orderColumns, java.lang.String column) throws java.sql.SQLException
null
is returned.
Please see select(String, String, String...)
for more details about parameter formats.
java.sql.SQLException
<T> T selectSingle(java.lang.String table, java.lang.String where, java.lang.String column) throws java.sql.SQLException
null
is returned.
Please see select(String, String, String...)
for more details about parameter formats.
java.sql.SQLException
java.util.Map<java.lang.String,java.lang.Object> selectSingleRow(java.lang.String table, java.lang.String where, java.lang.String... columns) throws java.sql.SQLException
select(String, String, String...)
, but only the first row is returned. If the result set is
empty, an empty map is returned.java.sql.SQLException
<T> java.util.List<T> selectList(java.lang.String table, java.lang.String where, java.lang.String column) throws java.sql.SQLException
Please see select(String, String, String...)
for more details about parameter formats.
java.sql.SQLException
<T> java.util.List<T> selectList(java.lang.String table, IDatabaseAccess.Where where, java.lang.String column) throws java.sql.SQLException
Please see select(String, Where, String...)
for more details about parameter formats.
java.sql.SQLException
<T> java.util.List<T> selectListOrdered(java.lang.String table, java.lang.String where, java.lang.String[] orderColumns, java.lang.String column) throws java.sql.SQLException
Please see selectOrdered(String, String, String[], String...)
for more details about parameter formats.
java.sql.SQLException
int update(java.lang.String table, java.lang.String where, java.util.Map<java.lang.String,java.lang.Object> updates) throws java.sql.SQLException
The format of the actual updates is a list of assignments.
Please note: Strings must be escaped!
table
- The table to update, e.g. "t_object"
.where
- The condition for the row/rows that shall be updated, e.g.
"Abstract = 0 AND Package_ID = 42"
;updates
- The actual updates, e.g.
["Note -> 'This is an abstract class'", "Scope -> 'Public'"]
.java.sql.SQLException
- In case there is a syntax error in the parameters or the connection to the database fails.java.lang.UnsupportedOperationException
- If the database was opened with readonly access.void delete(java.lang.String table, java.lang.String where) throws java.sql.SQLException
table
- The table in which a row/rows shall be deleted, e.g. "t_object"
.where
- The condition for the row/rows that shall be deleted, e.g. "Object_ID = 42"
; ignored if
null
(then the entire contents of the table will be deleted!).java.sql.SQLException
- In case there is a syntax error in the parameters or the connection to the database fails.java.lang.UnsupportedOperationException
- If the database was opened with readonly access.void delete(java.lang.String table, IDatabaseAccess.Where where) throws java.sql.SQLException
delete(String, String)
but with possibly very large where condition.table
- The table in which a row/rows shall be deleted, e.g. "t_object"
.where
- The condition for the row/rows that shall be deleted, see select(String, Where, String...)
for an example.java.sql.SQLException
- In case there is a syntax error in the parameters or the connection to the database fails.int insert(java.lang.String table, java.lang.String idColumnName, java.util.Map<java.lang.String,java.lang.Object> insertion) throws java.sql.SQLException
table
- The table name, e.g. "t_object"
. Must not be null
.idColumnName
- The name of a column whose value shall be returned; actually only useful for auto-incrementing ID
columns, e.g. "Object_ID"
. May be null
, then -1
is returned.insertion
- The actual values that shall be added to the table, e.g.
["Name" -> "'Test'", "Object_Type" -> "'Class'", "Abstract" -> "1", "author" -> null]
. Parameter must not be null
.idColumnName
or -1
, if no column was specified.java.sql.SQLException
- In case there is a syntax error in the parameters or the connection to the database fails.java.lang.UnsupportedOperationException
- If the database was opened with readonly access.int[] insert(java.lang.String table, java.lang.String idColumnName, java.util.List<java.lang.String> columns, java.util.List<java.util.List<java.lang.Object>> values) throws java.sql.SQLException
table
- The table name, e.g. "t_object"
. Must not be null
.idColumnName
- The name of a column whose value shall be returned; actually only useful for auto-incrementing ID
columns, e.g. "Object_ID"
. May be null
, then -1
is returned.columns
- The columns to be inserted.values
- A list of values to be batch-inserted; the size of the value lists must be equals to the number of
columns!idColumnName
(in the same order as the values are given) or
null
, if no column was specified.java.sql.SQLException
- In case there is a syntax error in the parameters or the connection to the database fails.java.lang.UnsupportedOperationException
- If the database was opened with readonly access.java.lang.String sqlIndexOf(java.lang.String text, java.lang.String needle)
This call simply delegates to IDBDriver
.
text
- The text or column which may contain the needle.needle
- The string or column which may be contained in the given text.IDBDriver.sqlIndexOf(String, String)
boolean isSqlExceptionTooComplexQuery(java.sql.SQLException e)
java.lang.String sqlColumnAsString(java.lang.String column, int strSize)
This call simply delegates to IDBDriver
.
column
- The column which should be casted, if necessary.strSize
- The max. string size for conversion, e.g. the varchar size.IDBDriver#sqlColumnAsString(String)
@Deprecated java.lang.String sqlColumnAsString(java.lang.String column)
sqlColumnAsString(String, int)
instead.java.lang.String escapeColumn(java.lang.String column)
boolean isReadOnly()
boolean isReplication()
true
if this database is a replication; false
otherwise.java.lang.String formatDate(java.util.Date date)
date
- The date to be formatted.Copyright (c) 2015 itemis AG, and others. All rights reserved.