This module offers some components for JDBC-based
database connectivity. The main component is the class
Sql
, an instance of which may be obtained for any
given DataSource
.
value sql = Sql(newConnectionFromDataSource(dataSource));
You can easily get a query result as a Sequence
where
each row is a Map
:
value rows = sql.Select("select * from mytable").execute(); for (row in rows) { assert (is String name = rows["name"]); assert (is Integer count = rows["count"]); ... }
You can define parameters to the query, using the ?
notation:
value rows = sql.Select("select * from mytable where col1=? and col2=?") .execute(arg1, arg2);
The Sql.Select.Results
class lets results be iterated
lazily:
try (results = sql.Select("select * from mytable where date>?") .Results(date)) { results.limit = 50; for (row in results) { ... } }
Alternatively, Sql.Select.forEachRow()
is a little less
verbose:
sql.Select("select * from mytable where date>?") .forEachRow(date)((row) { ... });
A Sql.Select
is reusable:
value query = sql.Select("select * from mytable where col=?"); value result1 = query.execute(value1); value result2 = query.execute(value2);
And of course you can execute update
and insert
statements, using Sql.Update
and Sql.Insert
:
sql.Update("update table SET col=? where key=?") .execute(newValue, key); sql.Insert("insert into table (key,col) values (?, ?)") .execute(key, initialValue);
If you need to perform several operations within a single
transaction, you can pass a function to the method
Sql.transaction()
. All statements of the function will
be executed within a transaction, using the same JDBC
connection, and finally the transaction is committed iff
the function returns true
:
sql.transaction { function do() { sql.Insert("insert ... ").execute(); sql.Update("update ... ").execute(); sql.Update("delete ... ").execute(); //return true to commit the transaction //return false or throw to roll it back return true; } };
To pass a null value as an argument, use a SqlNull
with the right SQL type (defined by the JDBC class
Types
):
sql.Update("update table set col=? where key=?") .execute(SqlNull(Types.integer));
If a column is null on a result row, it will be
represented as a SqlNull
instance under the column's
name.
Packages | |
ceylon.dbc | A module with components for JDBC-based database connectivity. |
Dependencies | ||
ceylon.collection | 1.3.3 | |
ceylon.decimal | 1.3.3 | |
ceylon.time | 1.3.3 | |
ceylon.transaction | 1.3.3 | |
ceylon.whole | 1.3.3 | |
java.base | 7 | |
java.jdbc | 7 |
A module with components for JDBC-based database connectivity.
Aliases | |
Row | Source Codeshared Row=> Map<String,Object> A row of results is represented as a |
Functions | |
newConnectionFromDataSource | Source Codeshared Connection newConnectionFromDataSource(DataSource dataSource)() Obtain a connection source, that is, an instance of
|
newConnectionFromDataSourceWithCredentials | Source Codeshared Connection newConnectionFromDataSourceWithCredentials(DataSource dataSource, String user, String pass)() Obtain a connection source, that is, an instance of
See also newConnectionFromDataSource() |
newConnectionFromXADataSource | Source Codeshared Connection newConnectionFromXADataSource(XADataSource dataSource)() Obtain a XA connection source, that is, an instance of
See also newConnectionFromXADataSource() |
newConnectionFromXADataSourceWithCredentials | Source Codeshared Connection newConnectionFromXADataSourceWithCredentials(XADataSource dataSource, String user, String pass)() Obtain a connection source, that is, an instance of
See also newConnectionFromDataSource() |
Classes | |
DbNull | Source Codeshared DbNull Deprecated: Renamed to |
Sql | Source Codeshared Sql An object that exposes operations for executing SQL DML or DDL queries against JDBC connections obtained by calling a given function. |
SqlNull | Source Codeshared SqlNull Represents a NULL value from the database, with its corresponding SQL type. |