documentation examples changes overview quick start installation command-line configuration admin amber clustering caching database deployment ejb 3.0 embedding filters hessian hmtp ioc jsp logging messaging performance quercus/php remoting scheduled tasks security server push servlets third-party troubleshooting virtual hosting watchdog webapp xml and xslt | database configuration
Resin provides a robust and tested connection pool that is used to obtain connections to databases.
A basic <database> configuration specifies the following:
<database jndi-name='jdbc/test_mysql'> <driver type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource"> <url>jdbc:mysql://localhost:3306/test</url> <user></user> <password></password> </driver> </database> This <database> will configure a javax.sql.DataSource and store it in JNDI at java:comp/env/jdbc/test_mysql. To use the data source, follow the database use pattern in the DataSource tutorial. Sample <database> configurations are available in the thirdparty driver page. Although some deployments will specify driver and connection pool parameters, the default values will be fine for most applications. Connection
A database connection is used to allow the Java program, running in a JVM, to communicate with a database server. Connection Pool
Connection pools are used to reduce the overhead of using a database. Establishing a connection to the database is a costly operation. A connection pool keeps a pool of open connections, each connection can be used for a time as needed, and then released back to the pool. A connection that has been released back to the pool can then be reused. Connection pooling is especially important in server applications. The overhead of opening a new connection for each new client request is too costly. Instead, the database pool allows for a connection to be opened once and then reused for many requests. DataSource
Resin provides an implementation of Driver
A Driver provides an interface and is responsible for the communication with the database. Every different database (i.e Oracle, MySQL) has their own means of enabling communication from the client (in this case Resin and you applications) and the database. The Driver provides a common interface that hides the details of that communication. Transaction
Transactions are especially important in server applications where many threads of processing may be interacting with the database at the same time. For a simple example, imagine a set of operations that reads a value, calculates a new value, and then updates the database. read value A=1 calculate A=A+1 update A=2 read value A=2 calculate A=A+1 update A=3 Imagine if one thread is performing this operation, and in the middle of this read/calculate/update, another thread performs an update. The data that the first thread obtained from the read and is using for the calculation and update is no longer valid. Thread 1 Thread 2 -------- -------- read value A=1 read value A=1 calculate A=A+1 calculate A=A+1 update A=2 update A=2 Placing the read/calculate/update operations in a transactions guarantees that only one thread can perform those operations at a time, if a second thread comes along and tries to perform the operation, it will have to wait for the first thread to finish before it can begin. Thread1 Thread 2 ------- -------- read value A=1 calculate A=A+1 (tries to read A, but has to wait for thread 1) update A=2 read value A=2 calculate A=A+1 update A=3 Distributed Transaction
If the guarantees that transactions apply need to apply to operations that occur on two databases within the same transaction, distributed transactions are needed. If read value db1.A=1 read value db2.B=99 calculate A=A+1 calculate B=B-A update db1.A=2 update db2.B=97 Distributed transactions are rarely needed, and few databases really support them. databasechild of server, host-default, host, web-app-default, web-appConfigure a resource, which is a database pool that manages and provides connections to a database.
All times default to seconds, but can use longer time periods:
The class that corresponds to <database> is com.caucho.sql.DBPool driverchild of databaseConfigure a database . The driver is a class provided by the database vendor, it is responsible for the communication with the database.
The jar file with the driver in it can be placed in Examples of common driver configurations are in Third-party Database Configuration. The class that corresponds to <driver> is com.caucho.sql.DriverConfig
Choosing a driver class for <type>Database vendors usually provide many different classes that are potential candidates for . The JDBC api has developed over time, and is now being replaced by the more general JCA architecture. The driver you choose depends on the options the vendor offers, and whether or not you need distributed transactions.JCA driversJCA is replacing JDBC as the API for database drivers. JCA is a much more flexible approach that defines an API that can be used for any kind of connection, not just a connection to a database. If a database vendor provides a JCA interface, it is the best one to use. A JCA driver implements The same JCA driver is used for both non-distributed and distributed transactions JDBC 2.0 - ConnectionPoolDataSourceJDBC 2.0 defined the interface A driver that implements is better than a JDBC 1.0 driver that implements .JDBC 2.0 - XADataSourceJDBC 2.0 defined the interface for connections that can participate in . A distributed transaction is needed when transactions involve multiple connections. For example, with two different database backends, if the guarantees that transactions apply need to apply to operations that occur on both databases within the same transaction, distributed transactions are needed.Distributed transactions are rarely needed, and few databases really support
them. Some vendors will provide
Set driver properties with init-paramis used to set properties of the database driver that are specific to the driver and are not generic enough for resin to provide a named configuration tag. For example, MySQL drivers accept the <database> <jndi-name>jdbc/mysql</jndi-name> <driver> <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type> <url>jdbc:mysql://localhost:3306/dbname</url> <user>username</user> <password>password</password> <init-param useUnicode="true"/> </driver> ... </database> Pooling configuration controls the behaviour of Resin's pooling of database connections. For most applications and databases the only needed change is to increase the max-connections value to meet high demand. Other pooling parameters have defaults that are based on our years of experience with many different databases in many different applications. Changes from the defaults should only be done in response to specific problems, and with a good understanding of how pooling works. pingResin's database pool can test if the pooled database connection is still alive by configuring a pooling parameters are changed from their default values. query. This is typically only necessary if theIf the pool is configured with a long max-idle-time the database connection may become stale if the database is restarted, or if the database is configured with a shorter connection timeout value than the configuration of the Resin pool. Normally when a database connection is returned to the pool it will wait there until the next request or the idle-time expires. If the database goes down in the meantime or closes the connection, the connection will become stale. The configuration can test the database connection.When pinging, Resin's DBPool will test a table specified with the
<database jndi-name="..."> <driver type="..."> ... </driver> <ping>true</ping> <ping-table>BROOMS</ping-table> </database> You can test the ping using the following steps:
<driver> listIf there is a pool of database servers available that can be used for database operations, Resin can be configured with a list of <driver> tags. Resin uses a round robin algorithm to cycle through the list of drivers when obtaining connections. If a particular <driver> fails to provide a connection, Resin continues the attempt to obtain a connection. If all of the configured drivers fail to provide a connection the exception is propogated to the caller. <database jndi-name="jdbc/hogwarts"> <driver> <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type> <url>jdbc:mysql://192.168.0.110:3306/hogwarts</url> ... </driver> <driver> <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type> <url>jdbc:mysql://192.168.0.111:3306/hogwarts</url> ... </driver> ... </database> <backup-driver> listDrivers in a driver list can be marked as backups. The drivers configured with <backup-driver> are used only if all of the drivers configured with <driver> have failed. Each time a new connection is needed Resin goes through the process of first attempting to use one of the <driver> configured drivers to get a connection, and if that fails then the <backup-driver> are used. A new connection is needed from the driver if the pool of connections that is maintained by Resin does not contain an idle connection. The Pooling configuration and the usage pattern of the application determine how often a connection is obtained from a driver. The pooling configuration typically allows a single real connection to be reused by the application many times. The lifetime of a connection obtained from a <backup-driver> is determined by the Pooling configuration, thus even if the main <driver> becomes available again a connection previously obtained from a <backup-driver> will continue to be used until it expires from the pool. <database jndi-name="jdbc/hogwarts"> <driver> <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type> <url>jdbc:mysql://192.168.0.110:3306/hogwarts</url> ... </driver> <driver> <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type> <url>jdbc:mysql://192.168.0.111:3306/hogwarts</url> ... </driver> <backup-driver> <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type> <url>jdbc:mysql://192.168.0.112:3306/hogwarts</url> ... </backup-driver> <backup-driver> <type>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</type> <url>jdbc:mysql://192.168.0.113:3306/hogwarts</url> ... </backup-driver> ... </database> Getting the DataSourceThe Ideally, the JNDI lookup of import javax.sql.*; import javax.webbeans.*; public class .... { @Named("jdbc/test") DataSource _pool; ... } Getting a ConnectionA connection is obtained from the It is very important that the close() in a finally block, to guarantee that it is called.The following example shows the use of a package javax.webbeans.*; package javax.sql.*; public class MyBean() { @In DataSource _pool; public void doStuff() { Connection conn = null; try { conn = _pool.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(" ... "); ... rs.close(); stmt.close(); } catch (SQLException e) { throw new ServletException(e); } finally { try { if (conn != null) conn.close(); } catch (SQLException e) { } } } } Getting the underlying driver connection
The connection obtained by
In rare circumstances it is necessary to obtain the real connection returned by the driver. Typically this is a requirement for situations where the driver provides a specialized API that is not available with the standard JDBC API. Connection driverConn = ((com.caucho.sql.UserConnection) connection).getConnection(); // never do this: driverConn.close() Resin provides facilities that allow you to plugin your own custom code that returns a password to Resin. However any solution is vulnerable, unless you require a person to type in a password every time Resin starts (or restarts). Typically the security of the machine hosting Resin, and proper permissions on the readability of the resin.conf file, are sufficient to protect your database password. The solution shown below is not really secure because you can disassemble the Password code to get the decryption key, but it may be marginally better than plaintext. <driver type="..."> <password resin:type="com.hogwarts.Password">mX9aN9M==</password> ... You will need to provide com.hogwarts.Password: package com.hogwarts; public class Password { private String _value; public void addText(String value) { _value = value; } public Object replaceObject() { return decrypt(_value); } private String decrypt(String encrypted) { ... custom code ... } } This solution is completely general, you can use resin:type anywhere in the configuration files where a string value is allowed. Resin does not provide the equivalent of com.hogwarts.Password because it's not really secure. Providing that kind of solution would lead some to believe it was a secure solution.
|