Rationale behind the choice of the DataSource interface instead of Driver?

[Guillaume Smet]

Hi,

We are considering the move to HikariCP (we are currently using the Tomcat pool) but there is something bothering us in the way we have to configure it.

Context: our core framework supports a lot of databases: PostgreSQL (which is our database of choice - I'm an ancient PostgreSQL contributor), MySQL, Oracle, SQL Server, H2... They are all configured in a generic way using a JDBC URL.

The Driver API, which is the API chosen by the connection pool we are using, allows to configure the Connection with a JDBC URL, which is comfortable as we can configure every database the same way.

HikariCP is based on the DataSource API which isn't really made to create a connection: it doesn't expose a stable interface for this and it's definitely a pain to have something generic for all the databases: the PostgreSQL DataSource implementation requires the database name, the host and the port, the SQL Server one wants a capitalized URL property whereas MySQL wants a lowercase url property...

Providing a generic way to configure the connection to a database with this API is clearly not as simple as with the Driver interface.

What is the rationale behind this choice?

Thanks for your feedback.

--
Guillaume

[Brett Wooldridge]

Hi Guillaume,

Thanks for considering HikariCP.

I'll do my best to defend the DataSource, but ultimately you may remain unconvinced.  First, I'll quote some from Oracle:

The JDBC 2.0 extension API introduced the concept of datasources, which are standard, general-use objects for specifying databases or other resources to use. Datasources can optionally be bound to Java Naming and Directory Interface (JNDI) entities so that you can access databases by logical names, for convenience and portability.

 

This functionality is a more standard and versatile alternative to the connection functionality described under "Opening a Connection to a Database". The datasource facility provides a complete replacement for the previous JDBC DriverManager facility.

 

You can use both facilities in the same application, but ultimately we encourage you to transition your application to datasources. Eventually, Sun Microsystems will probably deprecateDriverManager and related classes and functionality.

 

http://docs.oracle.com/javase/tutorial/jdbc/basics/sqldatasources.html

Because of its properties, a DataSource object is a better alternative than the DriverManager class for getting a connection. Programmers no longer have to hard code the driver name or JDBC URL in their applications, which makes them more portable. Also, DataSource properties make maintaining code much simpler. If there is a change, the system administrator can update data source properties and not be concerned about changing every application that makes a connection to the data source. For example, if the data source were moved to a different server, all the system administrator would have to do is set the serverName property to the new server name.

 

Aside from portability and ease of maintenance, using a DataSource object to get connections can offer other advantages. When the DataSource interface is implemented to work with a ConnectionPoolDataSource implementation, all of the connections produced by instances of that DataSource class will automatically be pooled connections.  [yada yada]

Which is more readable/maintainable?

jdbc:mysql://localhost:3306/mysql?user=foo&password=bar&socketTimeout=30&connectionLifecycleInterceptors=com.company.app.MySqlLifecycleInterceptor&useCompression=true&tcpRcvBuf=16384&tcpSndBuf=16384&autoReconnect=true&selfDestructOnPingMaxOperations=3&useSSL=true&clientCertificateKeyStoreType=PKCS12&trustCertificateKeyStorePassword=secret&metadataCacheSize=250&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048&cachePrepStmts=true&cacheResultSetMetadata=true&characterEncoding=UTF-8&continueBatchOnError=false

or

dataSource.user=foo

dataSource.password=bar

dataSource.socketTimeout=30

dataSource.connectionLifecycleInterceptors=com.company.app.MySqlLifecycleInterceptor

dataSource.useCompression=true

dataSource.tcpRcvBuf=16384

dataSource.tcpSndBuf=16384

dataSource.autoReconnect=true

dataSource.selfDestructOnPingMaxOperations=3

dataSource.useSSL=true

dataSource.clientCertificateKeyStoreType=PKCS12

dataSource.trustCertificateKeyStorePassword=secret

dataSource.metadataCacheSize=250

dataSource.prepStmtCacheSize=250

dataSource.prepStmtCacheSqlLimit=2048

dataSource.cachePrepStmts=true

dataSource.cacheResultSetMetadata=true

dataSource.characterEncoding=UTF-8

dataSource.continueBatchOnError=false

dataSourceClassName=com.mysql.jdbc.jdcb2.optional.MysqlDataSource

And that's not even setting everything I would set in a production environment.  MySQL has well over one hundred properties that can be set, of which 20-30 would probably be adjusted in a high-traffic environment.

Switching between DataSources can be as simple as:

HikariConfig config = new HikariConfig(System.getProperty("db.propfile"));

DataSource ds = new HikariDataSource(config);

or the Spring/IOC equivalent.  Then simply setting the db.propfile System property can be used to switch DataSources:

-Ddb.propfile=mysql.properties

-Ddb.propfile=pgsql.properties

There are many other ways to bake the configuration cake, that's just one cheap recipe.

Basically, the JDBC URL anti-pattern needs to die a horrible death.  Where else in the Java ecosystem would we put up with configuration by URL?

spring://context?annotation-config=true&component-scan-base-package=com.your.package&bean-id-MainDataSource-com.zaxxer.hikari.HikariDataSource&jpa-repositories=com.your.package.dao&transactionManager=org.springframework.orm.jpa.JpaTransactionManager...

log4j://root?log4j.rootLogger=INFO&log4j.appender.stdout=org.apache.log4j.ConsoleAppender&log4j.appender.stdout.layout=org.apache.log4j.PatternLayout...

You get the idea.  URLs are Uniform Resource Locators, not a configuration mechanism.  The Sun engineer who concocted the idea to abuse URLs in that way  should die in a fire.

Does that help explain the rationale?  

-Brett

ps. The company I work for has a product that supports 3 databases (MySQL, PostgreSQL, Derby) all configuration driven; without a URL in sight.

[Guillaume Smet]

Hi Brett,

(sorry for sending it twice but I wasn't aware the group doesn't support reply by email)

On Thu, Mar 13, 2014 at 3:17 PM, Brett Wooldridge wrote:
> You get the idea.  URLs are Uniform Resource Locators, not a configuration
> mechanism.  The Sun engineer who concocted the idea to abuse URLs in that
> way  should die in a fire.
>
> Does that help explain the rationale?
>

> ps. The company I work for has a product that supports 3 databases (MySQL,
> PostgreSQL, Derby) all configuration driven; without a URL in sight.

Thanks for taking the time to explain your reasons. Greatly appreciated.

While I now understand them (but you're right, I remain unconvinced
and I think supporting the JDBC URL option would be nice), this
approach is for now a showstopper for us.

Our JavaConfig based implementation is definitely not coded to support
specific configuration properties for each databases and changing this
implementation to test HikariCP and see how it performs would require
quite some time. I could see us investing time in it if I was sure of
the benefits but it's too bad we can't just switch pools and see how
it performs because of this issue (and eventually switch to DataSource
one day if I get convinced...).

My point is that, even if you think it's not a good idea, providing a
smooth migration path from <insert your pool here> to HikariCP could
probably help. I'm pretty sure we're not alone in this situation.

I'm not sure if it could be integrated without breaking your
clean/simple design so it's really just hand waving.

Anyway, thanks for providing new connection pooling solutions, it was
definitely a deserted area for far too long.

--
Guillaume

[Brett Wooldridge]

Hi Guilaume,

Well, as this is the first request for this feature, I'm not as convinced that

the demand is high.  You can probably tell that HikariCP is not just the

fastest, most stable pool, it is also the most opinionated.

We're sure to get requests in the future like "Why not just offer the option

to not test connections on checkout?  If the user wants to take the risk

that's their choice."  And certainly we could add that option.  Probably in

10 lines of code.

You request is obviously a little different, it does not affect stability or

performance.  So, we will take it under consideration.

Having said that, if you are really interested in testing HikariCP with a

DriverManager-based approach and JDBC URL, the code is in GitHub

and can be checked-out and compiled in 3 minutes.  Modifying the

code to do the Class.forName() on the driver etc. is about 15 minutes

of work.  If you're willing to do that, we'll merge it.

git clone https://github.com/brettwooldridge/HikariCP.git

cd HikariCP

git checkout dev

mvn install

You can then use 1.3.4-SNAPSHOT as a maven dependency, or use

the HikariCP-1.3.4-SNAPSHOT.jar from the target directory directly.

[Guillaume Smet]

Hi Brett,

On Friday, March 14, 2014 12:10:09 AM UTC+1, Brett Wooldridge wrote:

Well, as this is the first request for this feature, I'm not as convinced that

the demand is high.  You can probably tell that HikariCP is not just the

fastest, most stable pool, it is also the most opinionated.

Yeah, I saw that in what I read about the project. That's why I asked before coding something.

I agree this feature is not necessary per se. But having the capability to give a try to HikariCP without changing anything in your app would be nice.
 

We're sure to get requests in the future like "Why not just offer the option

to not test connections on checkout?  If the user wants to take the risk

that's their choice."  And certainly we could add that option.  Probably in

10 lines of code.

It's definitely an approach I like.
 

Having said that, if you are really interested in testing HikariCP with a

DriverManager-based approach and JDBC URL, the code is in GitHub

and can be checked-out and compiled in 3 minutes.  Modifying the

code to do the Class.forName() on the driver etc. is about 15 minutes

of work.  If you're willing to do that, we'll merge it.

Yes, my question was a subtle approach to know if you would merge such a PR.

I'll let you know when I come up with something.

Thanks.

--
Guillaume

[Brett Wooldridge]

We're sure to get requests in the future like "Why not just offer the option

to not test connections on checkout?  If the user wants to take the risk

that's their choice."  And certainly we could add that option.  Probably in

10 lines of code.

It's definitely an approach I like.

Maybe my example wasn't very clear.  That is the kind of request that would

not be accepted by the project.  HikariCP was designed to be completely

reliable, and not even offer configuration modes that would compromise that.

If a user wants a pool that trades reliability for performance, they surely has

lots of other choices and we would refer them to those projects.

But in the case of your request, even though we don't agree with the

approach of URL-based configuration introduced in JDBC 1.0, supporting it

does not compromise the reliability or performance of the pool, so we

are willing to accept it.

I'll let you know when I come up with something.

We look forward to it, if you have any questions feel free to ask.

 

[Robert DiFalco]

While I agree with your rational, the truth is that pretty much every database driver under the sun ends up having it's DataSource parse it's parameters into a JDBC Url consisting of a subprotocol, host, port, and database. I'm good with not overloading it for a bunch of setting but the most universal way to specify both a database AND a specific driver would be:

    hikaryConfig.setJdbcUrl( "jdbc:<driverType>//<host>:<port><path>" );

Then you don't have to mess with even the DataSource since you know what DataSource OR driver it is from the URL. But I agree that creating a DataSource instance is the BEST way. Any DB specific properties are set there, etc. Just pointing out that the above is pretty universal. As Guilaume points out the standard JDBC url (without settings) is the only standard generic way to switch between drivers. Each DataSource has a different protocol to be instantiate (setHost vs. setHostName vs. setPort vs setPortNumber, etc. etc. etc). And it's also funny to watch in the debugger how many DataSource implementations simply re-create a JDBC URL to send to the DriverManager in order to create a connection. :)

[Robert DiFalco]

Oh and Guillaume if you DO want to just try out Hikari for your environment (just to check out performance, etc) you can use this little DataSource shim. Use it like this (for example):

DriverDataSource dataSource = new DriverDataSource( "jdbc:postgresql://myserver:3100/mydatabase" );

dataSource.setUsername( "myUserName" );

dataSource.setPassword( "myPassword" );

HikariConfig config = new HikariConfig();

config.setDataSource( dataSource );

return new HikariDataSource( config );

You can easily modify this class if you use a Properties object for user and password. But it should get you up and running without having to make code changes:

class DriverDataSource implements DataSource {

    private String jdbcUrl;

    private String username;

    private String password;

    private PrintWriter logWriter;

    private int loginTimeout;

    DriverDataSource( String jdbcUrl ) {

        this.jdbcUrl = jdbcUrl;

    }

    public String getUsername() {

        return username;

    }

    public void setUsername( String username ) {

        this.username = username;

    }

    public String getPassword() {

        return password;

    }

    public void setPassword( String password ) {

        this.password = password;

    }

    @Override

    public Connection getConnection() throws SQLException {

        return DriverManager.getConnection( jdbcUrl );

    }

    @Override

    public Connection getConnection( String username, String password ) throws SQLException {

        return DriverManager.getConnection( jdbcUrl, this.username, password );

    }

    @Override

    public PrintWriter getLogWriter() throws SQLException {

        return logWriter;

    }

    @Override

    public void setLogWriter( PrintWriter logWriter ) throws SQLException {

        this.logWriter = logWriter;

    }

    @Override

    public void setLoginTimeout( int seconds ) throws SQLException {

        loginTimeout = seconds;

    }

    @Override

    public int getLoginTimeout() throws SQLException {

        return loginTimeout;

    }

    @Override

    public java.util.logging.Logger getParentLogger() throws SQLFeatureNotSupportedException {

        throw new SQLFeatureNotSupportedException();

    }

    @Override

    public <T> T unwrap( Class<T> iface ) throws SQLException {

        throw new SQLFeatureNotSupportedException();

    }

    @Override

    public boolean isWrapperFor( Class<?> iface ) throws SQLException {

        return false;

    }

}

[Robert DiFalco]

This one should work a little better for you and also perform better:

class DriverDataSource implements DataSource {

    private String jdbcUrl;

    private PrintWriter logWriter;

    private int loginTimeout;

    private Driver driver;

    private Properties properties = new Properties();

    DriverDataSource( String jdbcUrl ) throws SQLException {

        this.jdbcUrl = jdbcUrl;

        driver = DriverManager.getDriver( jdbcUrl );

    }

    public Properties getProperties() {

        return properties;

    }

    public void setProperties( Properties properties ) {

        this.properties = properties;

    }

    public String getUsername() {

        return properties.getProperty( "user" );

    }

    public void setUsername( String username ) {

        properties.setProperty( "user", username );

    }

    public String getPassword() {

        return properties.getProperty( "password" );

    }

    public void setPassword( String password ) {

        properties.setProperty( "password", password );

    }

    @Override

    public Connection getConnection() throws SQLException {

        return driver.connect( jdbcUrl, properties );

    }

    @Override

    public Connection getConnection( String username, String password ) throws SQLException {

        properties.setProperty( "user", username );

        properties.setProperty( "password", password  );

        return driver.connect( jdbcUrl, properties );

[Brett Wooldridge]

Ok, DriverManager/JDBC URL-based support has been committed to the 'dev' branch.  HikariConfig now has two additional setters, setJdbcUrl() and setDriverClassName().  When they are used, setDataSourceClassName() cannot be used.  However, instead of configuring all driver parameters though the URL, you can still use the addDataSourceProperty() setter, or construct the HikariConfig with a set of properties.  Driver property names should still be prefixed with "dataSource." when using property file-based configuration.

You can checkout the 'dev' branch and build using 'mvn install', and use 1.3.4-SNAPSHOT as a maven dependency or use the HikariCP-1.3.4-SNAPSHOT.jar from the target directory.

[Guillaume Smet]

Hi Brett,

On Sunday, March 16, 2014 9:49:33 AM UTC+1, Brett Wooldridge wrote:

Ok, DriverManager/JDBC URL-based support has been committed to the 'dev' branch.

Thanks. Removed it from my TODO list for this week.

I tested it on our sample application and it's working great.

Next step is to give another shot to pgjdbc-ng on our apps.

--
Guillaume, calling again from 1999

[Brett Wooldridge]

-- 

Guillaume, calling again from 1999

:-)