DB (JavaLite - Java, light as a feather 2.5-j8-SNAPSHOT API)

java.lang.Object
- org.javalite.activejdbc.DB

All Implemented Interfaces:

Closeable, AutoCloseable
```
public class DB
extends Object
implements Closeable
```
This class provides a number of convenience methods for opening/closing database connections, running various types of queries, and executing SQL statements. This class differs from Base such that in this class you can provide a logical name for a current connection. Use this class when you have more than one database in the system.

Author:

Igor Polevoy, Eric Nielsen

Field Summary

Fields
Modifier and Type Field and Description

static String DEFAULT_NAME

Fields
Modifier and Type	Field and Description
`static String`	`DEFAULT_NAME`

Constructor Summary

Constructors
Constructor and Description
`DB()` Creates a new DB object representing a connection to a DB with default name.
`DB(String name)` Creates a new DB object representing a connection to a DB.

Method Summary

All Methods Static Methods Instance Methods Concrete Methods
Modifier and Type	Method and Description
`void`	`addBatch(PreparedStatement ps, Object... params)` Adds a batch statement using given `java.sql.PreparedStatement` and parameters.
`List<Map>`	`all(String query)` Alias to `findAll(String)`
`List<Map>`	`all(String query, Object... params)` Alias to `findAll(String, Object...)`
`void`	`attach(Connection connection)` Attaches a database connection to current thread under a name provided to constructor.
`void`	`close()` Closes connection and detaches it from current thread.
`void`	`close(boolean suppressWarning)` Closes connection and detaches it from current thread.
`static void`	`closeAllConnections()` Closes all current connections.
`void`	`closePreparedStatement(PreparedStatement ps)` Quietly closes the `java.sql.PreparedStatement` used in a batch execution.
`void`	`commitTransaction()` Commits local transaction.
`Connection`	`connection()` Provides connection from current thread.
`static Map<String,Connection>`	`connections()` Provides connections available on current thread.
`Long`	`count(String table)` Returns count of rows in table.
`Long`	`count(String table, String query, Object... params)` Runs a count query, returns a number of matching records.
`Connection`	`detach()` Detaches a connection from current thread and returns an instance of it.
`int`	`exec(String query)` Executes DML.
`int`	`exec(String query, Object... params)` Executes parametrized DML - will contain question marks as placeholders.
`int[]`	`executeBatch(PreparedStatement ps)` Executes a batch on `java.sql.PreparedStatement`.
`RowProcessor`	`find(RowProcessor.ResultSetType type, RowProcessor.ResultSetConcur concur, int fetchSize, String query, Object... params)` Executes a raw query and returns an instance of `RowProcessor`.
`RowProcessor`	`find(String query, Object... params)` Convenience method, same as `find(RowProcessor.ResultSetType, RowProcessor.ResultSetConcur, int, String, Object...)`, but passes in default values:
`void`	`find(String sql, RowListener listener)` Executes a raw query and calls instance of `RowListener` with every row found.
`List<Map>`	`findAll(String query)` This method returns entire resultset as one list.
`List<Map>`	`findAll(String query, Object... params)` This method returns entire resultset as one list.
`Object`	`firstCell(String query, Object... params)` This method returns the value of the first column of the first row.
`List`	`firstColumn(String query, Object... params)` This method returns entire resultset with one column as a list.
`Connection`	`getConnection()` Synonym of `connection()` for people who like getters.
`static List<String>`	`getCurrrentConnectionNames()` Provides a names' list of current connections.
`boolean`	`hasConnection()` Use to check if there is a connection present on current thread.
`String`	`name()` Return logical name for a database.
`DB`	`open()` This method will open a connection defined in the file 'database.properties' set by an initial previous call to `DBConfiguration.loadConfiguration(String)`.
`DB`	`open(ConnectionConfig config)` This method is used internally by the framework.
`DB`	`open(DataSource datasource)` Opens a connection from a datasource.
`DB`	`open(String jndiName)` Opens a connection from JNDI based on a registered name.
`DB`	`open(String jndiName, Properties jndiProperties)` Opens a new connection from JNDI data source by name using explicit JNDI properties.
`DB`	`open(String driver, String url, Properties props)` Opens a new connection in case additional driver-specific parameters need to be passed in.
`DB`	`open(String driver, String url, String user, String password)` Opens a new connection based on JDBC properties and attaches it to a current thread.
`void`	`openTransaction()` Opens local transaction.
`void`	`rollbackTransaction()` Rolls back local transaction.
`PreparedStatement`	`startBatch(String parametrizedStatement)` Creates a `java.sql.PreparedStatement` to be used in batch executions later.
`<T> T`	`withDb(DataSource dataSource, Supplier<T> supplier)` Convenience method to be used outside ActiveWeb.
`<T> T`	`withDb(String jndiName, Properties jndiProperties, Supplier<T> supplier)` Convenience method to be used outside ActiveWeb.
`<T> T`	`withDb(String driver, String url, Properties properties, Supplier<T> supplier)` Convenience method to be used outside ActiveWeb.
`<T> T`	`withDb(String driver, String url, String user, String password, Supplier<T> supplier)` Convenience method to be used outside ActiveWeb.
`<T> T`	`withDb(String jndiName, Supplier<T> supplier)` Convenience method to be used outside ActiveWeb.
`<T> T`	`withDb(Supplier<T> supplier)` Convenience method to be used outside ActiveWeb.

Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

- Field Detail
  - DEFAULT_NAME
```
public static final String DEFAULT_NAME
```
    See Also:
    
    Constant Field Values
- Constructor Detail
  - DB
```
public DB(String name)
```
    Creates a new DB object representing a connection to a DB.
    
    Parameters:
    
    name - logical name for a database.
  - DB
```
public DB()
```
    Creates a new DB object representing a connection to a DB with default name. Calling this constructor is equivalent to new DB(DB.DEFAULT_NAME).
- Method Detail
  - name
```
public String name()
```
    Return logical name for a database.
    
    Returns:
    
    logical name for a database.
  - open
```
public DB open(String driver,
               String url,
               String user,
               String password)
```
    Opens a new connection based on JDBC properties and attaches it to a current thread.
    
    Parameters:
    
    driver - class name of driver
    
    url - URL connection to DB
    
    user - user name.
    
    password - password.
  - open
```
public DB open(String driver,
               String url,
               Properties props)
```
    Opens a new connection in case additional driver-specific parameters need to be passed in.
    
    Parameters:
    
    driver - driver class name
    
    url - JDBC URL
    
    props - connection properties
  - open
```
public DB open(String jndiName)
```
    Opens a connection from JNDI based on a registered name. This assumes that there is a jndi.properties file with proper JNDI configuration in it.
    
    Parameters:
    
    jndiName - name of a configured data source.
  - open
```
public DB open()
```
    This method will open a connection defined in the file 'database.properties' set by an initial previous call to DBConfiguration.loadConfiguration(String). The connection picked up from the file is defined by ACTIVE_ENV environment variable or active_env system property. If this variable is not defined, it defaults to 'development' environment.
    It is expected to find a single connection configuration in a current environment.
    
    See Also:
    
    AppConfig.activeEnv()
  - attach
```
public void attach(Connection connection)
```
    Attaches a database connection to current thread under a name provided to constructor.
    
    Parameters:
    
    connection - instance of connection to attach to current thread.
  - detach
```
public Connection detach()
```
    Detaches a connection from current thread and returns an instance of it. This method does not close a connection. Use it for cases of advanced connection management, such as integration with Spring Framework.
    
    Returns:
    
    instance of a connection detached from current thread by name passed to constructor.
  - open
```
public DB open(DataSource datasource)
```
    Opens a connection from a datasource. This methods gives a high level control while sourcing a DB connection.
    
    Parameters:
    
    datasource - datasource will be used to acquire a connection.
  - open
```
public DB open(String jndiName,
               Properties jndiProperties)
```
    Opens a new connection from JNDI data source by name using explicit JNDI properties. This method can be used in cases when file jndi.properties cannot be easily updated.
    
    Parameters:
    
    jndiName - name of JNDI data source.
    
    jndiProperties - JNDI properties
  - open
```
public DB open(ConnectionConfig config)
```
    This method is used internally by the framework.
    
    Parameters:
    
    config - specification for a JDBC connection.
  - close
```
public void close()
```
    Closes connection and detaches it from current thread.
    
    Specified by:
    
    close in interface Closeable
    
    Specified by:
    
    close in interface AutoCloseable
  - close
```
public void close(boolean suppressWarning)
```
    Closes connection and detaches it from current thread.
    
    Parameters:
    
    suppressWarning - true to not display a warning in case of a problem (connection not there)
  - count
```
public Long count(String table)
```
    Returns count of rows in table.
    
    Parameters:
    
    table - name of table.
    
    Returns:
    
    count of rows in table.
  - count
```
public Long count(String table,
                  String query,
                  Object... params)
```
    Runs a count query, returns a number of matching records.
    
    Parameters:
    
    table - table in which to count rows.
    
    query - this is a filtering query for the count. If '*' provided, all records will be counted. Example: "age > 65 AND department = 'accounting'"
    
    params - parameters for placeholder substitution.
    
    Returns:
    
    count number of records found in a table.
  - firstCell
```
public Object firstCell(String query,
                        Object... params)
```
    This method returns the value of the first column of the first row. If query results have more than one column or more than one row, they will be ignored.
    
    Parameters:
    
    query - query
    
    params - parameters
    
    Returns:
    
    fetched value, or null if query did not fetch anything.
  - all
```
public List<Map> all(String query,
                     Object... params)
```
    Alias to findAll(String, Object...)
  - findAll
```
public List<Map> findAll(String query,
                         Object... params)
```
    This method returns entire resultset as one list. Do not use it for large result sets. Example:
```
 List<Map<String, Object>> people = Base.findAll("select * from people where first_name = ?", "John");
  for(Map person: people)
      System.out.println(person.get("first_name"));
 
 
```
    Parameters:
    
    query - raw SQL query. This query is parametrized.
    
    params - list of parameters for a parametrized query.
    
    Returns:
    
    entire result set corresponding to the query.
  - firstColumn
```
public List firstColumn(String query,
                        Object... params)
```
    This method returns entire resultset with one column as a list. Do not use it for large result sets. Example:
```
  List ssns = new DB("default").firstColumn("select ssn from people where first_name = ?", "John");
  for(Object ssn: ssns)
      System.out.println(ssn);
 
```
    This method collects the value of the first column of each row. If query results have more than one column, the remainder will be ignored.
    Parameters:
    
    query - raw SQL query. This query can be parametrized.
    
    params - list of parameters for a parametrized query.
    
    Returns:
    
    entire result set corresponding to the query.
  - all
```
public List<Map> all(String query)
```
    Alias to findAll(String)
  - findAll
```
public List<Map> findAll(String query)
```
    This method returns entire resultset as one list. Do not use it for large result sets.
    
    Parameters:
    
    query - raw SQL query. This query is not parametrized.
    
    Returns:
    
    entire result set corresponding to the query.
  - find
```
public RowProcessor find(String query,
                         Object... params)
```
    Convenience method, same as find(RowProcessor.ResultSetType, RowProcessor.ResultSetConcur, int, String, Object...), but passes in default values:
```
     RowProcessor.ResultSetType.FORWARD_ONLY, RowProcessor.ResultSetConcur.READ_ONLY, 0
 
```
    Executes a raw query and returns an instance of RowProcessor. Use it in the following pattern:
```
 new DB("default").find("select first_name, last_name from really_large_table").with(new RowListenerAdapter() {
            public void onNext(Map row) {
                ///write your code here
                Object o1 = row.get("first_name");
                Object o2 = row.get("last_name");
            }
        });
     
```
    Parameters:
    
    query - raw SQL, parametrized if needed
    
    params - list of parameters if query is parametrized.
    
    Returns:
    
    instance of RowProcessor which has with() method for convenience.
  - find
```
public RowProcessor find(RowProcessor.ResultSetType type,
                         RowProcessor.ResultSetConcur concur,
                         int fetchSize,
                         String query,
                         Object... params)
```
    Executes a raw query and returns an instance of RowProcessor. Use it in the following pattern:
```
   new DB("default").find("select first_name, last_name from really_large_table", ....).with(new RowListenerAdapter() {
         public void onNext(Map row) {
                 ///write your code here
                 Object o1 = row.get("first_name");
                 Object o2 = row.get("last_name");
             }
         });
     
```
    See ResultSet Docs
    Parameters:
    
    query - raw SQL.
    
    type - type of result set
    
    concur - concurrent mode of result set
    
    fetchSize - size of result set
    
    params - list of parameters if query is parametrized.
    
    Returns:
    
    instance of RowProcessor which has with() method for convenience.
  - find
```
public void find(String sql,
                 RowListener listener)
```
    Executes a raw query and calls instance of RowListener with every row found. Use this method for very large result sets.
    
    Parameters:
    
    sql - raw SQL query.
    
    listener - client listener implementation for processing individual rows.
  - exec
```
public int exec(String query)
```
    Executes DML. Use it for inserts and updates.
    
    Parameters:
    
    query - raw DML.
    
    Returns:
    
    number of rows afected by query.
  - exec
```
public int exec(String query,
                Object... params)
```
    Executes parametrized DML - will contain question marks as placeholders.
    
    Parameters:
    
    query - query to execute - will contain question marks as placeholders.
    
    params - query parameters.
    
    Returns:
    
    number of records affected.
  - openTransaction
```
public void openTransaction()
```
    Opens local transaction.
  - commitTransaction
```
public void commitTransaction()
```
    Commits local transaction.
  - rollbackTransaction
```
public void rollbackTransaction()
```
    Rolls back local transaction.
  - connection
```
public Connection connection()
```
    Provides connection from current thread.
    
    Returns:
    
    connection from current thread.
  - hasConnection
```
public boolean hasConnection()
```
    Use to check if there is a connection present on current thread.
    
    Returns:
    
    true if finds connection on current thread, false if not.
  - getConnection
```
public Connection getConnection()
```
    Synonym of connection() for people who like getters.
    
    Returns:
    
    connection from current thread.
  - getCurrrentConnectionNames
```
public static List<String> getCurrrentConnectionNames()
```
    Provides a names' list of current connections.
    
    Returns:
    
    a names' list of current connections.
  - closeAllConnections
```
public static void closeAllConnections()
```
    Closes all current connections.
  - connections
```
public static Map<String,Connection> connections()
```
    Provides connections available on current thread.
    
    Returns:
    
    connections available on current thread.
  - startBatch
```
public PreparedStatement startBatch(String parametrizedStatement)
```
    Creates a java.sql.PreparedStatement to be used in batch executions later.
    
    Parameters:
    
    parametrizedStatement - Example of a statement: INSERT INTO employees VALUES (?, ?).
    
    Returns:
    
    instance of java.sql.PreparedStatement with compiled query.
  - addBatch
```
public void addBatch(PreparedStatement ps,
                     Object... params)
```
    Adds a batch statement using given java.sql.PreparedStatement and parameters.
    
    Parameters:
    
    ps - java.sql.PreparedStatement to add batch to.
    
    params - parameters for the query in java.sql.PreparedStatement. Parameters will be set on the statement in the same order as provided here.
  - executeBatch
```
public int[] executeBatch(PreparedStatement ps)
```
    Executes a batch on java.sql.PreparedStatement.
    
    Parameters:
    
    ps - java.sql.PreparedStatement to execute batch on.
    
    Returns:
    
    an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.
    
    See Also:
    
    Statement#executeBatch()
  - closePreparedStatement
```
public void closePreparedStatement(PreparedStatement ps)
```
    Quietly closes the java.sql.PreparedStatement used in a batch execution. The advantage over calling java.sql.PreparedStatement.close() directly is not having to explicitly handle a checked exception (java.sql.SQLException). This method should typically be called in a finally block. So as not to displace any exception (e.g. from a failed batch execution) that might already be in flight, this method swallows any exception that might arise from closing the statement. This is generally seen as a worthwhile trade-off, as it much less likely for a close to fail without a prior failure.
    
    Parameters:
    
    ps - java.sql.PreparedStatement with which a batch has been executed. If null, this is a no-op.
  - withDb
```
public <T> T withDb(String jndiName,
                    Properties jndiProperties,
                    Supplier<T> supplier)
```
    Convenience method to be used outside ActiveWeb. This method will open a connection, run the Runnable and then will close the connection. The connection to open is the same as in open(String, Properties) method.
    Example of usage:
```
     Object result = withDb("jndiName1", props, () -> {
        //place code here
        return res;
     });
 
```
    Parameters:
    
    jndiName - name of a configured data source.
    
    jndiProperties - JNDI properties.
    
    supplier - instance of Supplier to execute.
  - withDb
```
public <T> T withDb(DataSource dataSource,
                    Supplier<T> supplier)
```
    Convenience method to be used outside ActiveWeb. This method will open a connection, run the Runnable and then will close the connection. The connection to open is the same as in open(DataSource) method.
    Example of usage:
```
     Object result = withDb(datasource, () -> {
        //place code here
        return res;
     });
 
```
    Parameters:
    
    dataSource - instance of DataSource to get a connection from.
    
    supplier - instance of Supplier to execute.
  - withDb
```
public <T> T withDb(String jndiName,
                    Supplier<T> supplier)
```
    Convenience method to be used outside ActiveWeb. This method will open a connection, run the Supplier and then will close the connection. The connection to open is the same as in open(String) method.
    Example of usage:
```
     Object result = withDb(jndiName, () -> {
         //place code here
         return res;
     });
 
```
    Parameters:
    
    jndiName - name of a JNDI connection from container
    
    supplier - instance of Supplier to execute.
  - withDb
```
public <T> T withDb(String driver,
                    String url,
                    Properties properties,
                    Supplier<T> supplier)
```
    Convenience method to be used outside ActiveWeb. This method will open a connection, run the Supplier and then will close the connection. The connection to open is the same as in open(String, String, Properties) method.
    Example of usage:
```
     Object results = withDb(driver, url, properties, () -> {
        //place code here
        return res;
     });
 
```
    The arguments to this method are the same as to open(String, String, Properties) method.
    Parameters:
    
    supplier - instance of Supplier to execute.
  - withDb
```
public <T> T withDb(String driver,
                    String url,
                    String user,
                    String password,
                    Supplier<T> supplier)
```
    Convenience method to be used outside ActiveWeb. This method will open a connection, run the Supplier.get() and then will close the connection. The connection to open is the same as in open(String, String, String, String) method.
    Example of usage:
```
     Object result = withDb(driver, url, user, password, () -> {
        //place code here
        return val;
     });
 
```
    The arguments to this method are the same as to open(String, String, String, String) method.
    Parameters:
    
    supplier - instance of Supplier to execute.
  - withDb
```
public <T> T withDb(Supplier<T> supplier)
```
    Convenience method to be used outside ActiveWeb. This method will open a connection, run the Supplier.get() and then will close the connection. The connection to open is the same as in open() method.
    Example of usage:
```
     Object result = withDb(() -> {
        //place code here
        return res; // whatever it is
     });
 
```
    Parameters:
    
    supplier - instance of Supplier to execute.

Class DB

Field Summary

Constructor Summary

Method Summary

Methods inherited from class java.lang.Object

Field Detail

DEFAULT_NAME

Constructor Detail

DB

DB

Method Detail

name

open

open

open

open

attach

detach

open

open

open

close

close

count

count

firstCell

all

findAll

firstColumn

all

findAll

find

find

find

exec

exec

openTransaction

commitTransaction

rollbackTransaction

connection

hasConnection

getConnection

getCurrrentConnectionNames

closeAllConnections

connections

startBatch

addBatch

executeBatch

closePreparedStatement

withDb

withDb

withDb

withDb

withDb

withDb