Proposed new API design principles

[dimzon]

I want to propose to re-design new (2.0) release version architecture|api using following principles

1. Separation of Concerns - each component must solve 1 concrete task, every component can be replaced by custom implementation
2. Using well-known GoF patterns instead self-made solutions (for example PojoMetadata is awful, it must be separated by PojoMetadataProvider && PojoMetadata)
3. Must not prevent to write plain-old JDBC-code (must be able to get Connection from sql2o.Connection, PreparedStatement from Query, ResultSet from IterableResultSet (since we can use third-party library wich consumes JDBC objects, as example - converting ResultSet to CSV)
4. Must be able to consume JDBC objects at any step (must be create sql2o.Connection from Connection, Query from PreparedStatement, must be able to transform ResultSet to all possible forms (List|Table|LazyTable|IterableResultSet (since sometimes we write not project-from-scratch but code wich obtains JDBC objects outside)

[dimzon]

So let's start...

sql2o.Conf class.

Aggregates all sql2o settings at one place. Implements sql2o.ConfSource.

sql2o.ConfSource interface.

contains ``sql2o.Conf getConf()` method

sql2o.Sql2o class.

my vision: it's factory class.
responsibility: single entry-point, configure once use multiple. Takes javax.sql.DataSource in constructor. Produce sql2o.Conn. Also can create sql2o.Conn from java.sql.Connection, implements sql2o.ConfSource.

typical usage: one instance per javax.sql.DataSource. Developer must create new instance and perform application-wide tweaks (Quirks|Strategies etc). In case of application servers developer can|must somehow implement singleton behavior (lifetime management is outside sql2o.Sql2o responsibility) using any preferred way (plain old static fields|IoC containers|whatever)

[dimzon]

sql2o.Conn class

my vision: it's adapter class.

responsibility: database connection. takes sql2o.ConfSource and java.sql.Connection in constructor. Can create sql2o.Query. Implements sql2o.ConfSource

typical usage: mostly like plain old java.sql.Connection. Provides some syntax sugar. Works like a decorator around sql2o.ConfSource. Allows connection-wide tweaks (locally overriding tweaks from sql2o.ConfSource)

JDBC consumer - can be created from plain JDBC Connection
JDBC provider - can return ready-to use JDBC Connection

[dimzon]

sql2o.Query class

my vision: it's PreparedStatement on steroids. takes sql2o.ConfSource, java.sql.Connection and sqlQuery in constructor
can produce sql2o.QueryResult.

typical usage: mostly like plain old java.sql.PreparedStatement.

JDBC consumer - can be created from plain JDBC Connection
JDBC provider - can return ready-to use PreparedStatement/CallableStatement

[dimzon]

sql2o.QueryResult class

my vision: adapter around java.sql.ResultSet. takes java.sql.ResultSet and sql2o.ConfSource in constructor. Contains all neat List<Bean> listOf(Class<Bean> ofBeanClass) etc methods.
as addition must provide generic getString, getInteger, getDate, getUUID etc methods which behavior is customizable via Quirks (some projects|databases use getNString another getString etc)

JDBC consumer - can be created from plain JDBC ResultSet

[aaberg]

Separation of Concerns - each component must solve 1 concrete task, every component can be replaced by custom implementation

I agree

Using well-known GoF patterns instead self-made solutions (for example PojoMetadata is awful, it must be separated by PojoMetadataProvider && PojoMetadata)

I'm not sure I agree that it is awful! :). But I agree that it's better to use patterns under the GoF umbrella.

Must not prevent to write plain-old JDBC-code (must be able to get Connection from sql2o.Connection, PreparedStatement from Query, ResultSet from IterableResultSet (since we can use third-party library wich consumes JDBC objects, as example - converting ResultSet to CSV)

I think this is a really good idea.

Must be able to consume JDBC objects at any step (must be create sql2o.Connection from Connection, Query from PreparedStatement, must be able to transform ResultSet to all possible forms (List|Table|LazyTable|IterableResultSet (since sometimes we write not project-from-scratch but code wich obtains JDBC objects outside)

As above. It think this is a really good idea :)

[aaberg]

I think your vision for sql2o v2 is very promising. I have read though your comments, and I think it looks very promising!

Some things I think is important:

• API must be clean and simple.
• No annotations in core lib.
• No manipulation of sql in core lib.
• No need to catch any exceptions. All exceptions should be runtime exceptions.
• I think the api today (as seen from the user) is good, some changes will happen, but it shouldn't change too much

I think all of this fits nicely into your vision!

Something I would like to discuss:

• Will sql2o v2 have converters? How will they fit in?
• How will transactions fit in? I think a transaction should be attached to a connection object and not extend it. I think it should be possible to begin a transaction both from a sql2o instance directly and from a connection instance.
• Today you can execute a query directly on a sql2o instance (will automatically open a connection, execute the query, and close the connection). Should we keep this behavior?
• v2 Will probably not compatible with v1. Should we create a new package (namespace) for sql2o v2, like they did with org.apache.commons.lang3. So an application can use both sql2o v1 and v2 at the same time?

[dimzon]

No need to catch any exceptions. All exceptions should be runtime exceptions.

Yes, checked exception is wrost Java feature even. But re-throwing all of them as RuntimeException is evil too since you can't catch on concrete exception and you must search for proper CallStack every time...
I just prefer to write throws Exception in all my business-code. This is really better solution - keep original exception clean without RuntimeException noise and Java doesn't harass you to catch anything.

[aldenquimby]

I think the current design for sql2o exceptions is good and shouldn't
change. Catch everything and rethrow as a Sql2oException, which extends
RuntimeException. A lot of people don't add "throws Exception" everywhere,
so it could be annoying to users to have to try catch every sql2o call

On Thursday, April 10, 2014, Dmitry notifications@github.com wrote:

No need to catch any exceptions. All exceptions should be runtime
exceptions.
Yes, checked exception is wrost Java feature even. But re-throwing all of
them as RuntimeException is evil too since you can't catch on concrete
exception and you must search for proper CallStack every time...
I just prefer to write throws Exception in all my business-code. This is
really better solution - keep original exception clean without
RuntimeException noise and Java doesn't harass you to catch anything.

Reply to this email directly or view it on GitHubhttps://github.com//issues/83#issuecomment-40145809
.

[dimzon]

Will sql2o v2 have converters? How will they fit in?

I think better way is

interface ResultSetExtractor{
    Object extractValue(QueryResult resultSet, int columnNumber) throws SQLException;
}

back direction is ParamSetter you already used

current converters become Extractor|Setter factories

[dimzon]

Today you can execute a query directly on a sql2o instance (will automatically open a connection, execute the query, and close the connection). Should we keep this behavior?

WARNING! IT'S IMHO
API is something designed by professionals for non-professionals (read is some FrmeworkDesignGuide book). We must force consumers to use proper programming style. To learn to use try-with-resource. To not open new connection for every query.
take PHP as example - it allows bad coding...

[dimzon]

v2 Will probably not compatible with v1. Should we create a new package (namespace) for sql2o v2, like they did with org.apache.commons.lang3. So an application can use both sql2o v1 and v2 at the same time?

great idea

[dimzon]

How will transactions fit in? I think a transaction should be attached to a connection object and not extend it. I think it should be possible to begin a transaction both from a sql2o instance directly and from a connection instance.

this is a subject to think about. this is not so easy, think about distributed transactions...
I really like how this woks in .NET, let me show this:

// using is 100% same as try-with-resource
using(Transaction tr = new Transaction()){
     // any code here automatically enlisted in transaction
     // this is done via ThreadLocal<Stack<Transaction>>
     // every constructor invocation puts new transaction onto stack top  
     // every dispose() (dispose == java AutoClosable.close() )
     // removes transaction from stack
     // every database statement constructor checks top of this stack
     // if it's not empty it will enlist in transaction
     this.doSomething(); 
     tr.commit();
}

[dimzon]

A lot of people don't add "throws Exception" everywhere,
so it could be annoying to users to have to try catch every sql2o call

this is subject to talk about. I think (we can discuss and also look for best practics) wrapping everything in runtime exceptioon is evil (look my explanation in previous post). It's better to teach 'users' better and clean programming style (wich will help in other cases to, not just sql2o)