Automatically close JDBC connections - possible?

[timowest]

Hi Timo,

How do you think if it makes sense to close JDBC connections automatically once the data is retrieved and the connection should be closed/returned to the pool?

Currently I have Wrapper classes created for all SQLQueryImpl, SQLUpdateClause, SQLInsertClause, SQLDeleteClause. So I simply override the required methods closing the connection in the finally block in the wrappers rather than closing it every time in my actual database call.

I can provide the source code. Please send me an email if sources are necessary.

Thanks,
Alexander

alexkut

This behaviour might be too uncommon to be supported by Querydsl directly.

Usually the scope of a connection is longer than that of a single query or statement.

Have you considered using AOP based connection allocation and closing?

timowest

This behaviour might be too uncommon to be supported by Querydsl directly.
Usually the scope of a connection is longer than that of a single query or statement.

Agree. But most often connection pool is used. So every JDBC call is using a pooled connection rather than creating a new connection all the time. Reusing of the same connection is better than get/return from the pool. In the same time separation of the logic from connection/transaction management makes code simpler and stay focused on the logic.
I think you are right that QueryDSL should provide the common API like it's right now. And everyone who wants to close/open connections automatically will write the Wrapper or something similar.

Have you considered using AOP based connection allocation and closing

I thought about it but did not find a solution. I considered something like this initially.

@DatabaseCall
public List<User> findAll() {
    SQLQuery q = new SQLQueryImpl(getConnection(), dialect);
    //...  
}


where @DatabaseCall extends @Around advice and provides a Connection somehow.

But this implementation requires to bind the JDBC connection to the method. It is also not clear to me how the same connection can be reused with other methods. So I simply wrapped the core SQLQueryImpl/SQLUpdate/Insert/DeleteClause's. Wrapping of DML clauses was trivial since execute and executeWithKey(s) are the only methods I should care. :).

Thanks a lot for the feedback.
Alexander

[Marlon Patrick]

Hi Timo,

I believe that this option is not that unusual, principalmenet where SQLQuery. In my application often need to do a query and immediately close the connection. As I do not want to handle the Connection directly I overwrite the SQLQuery class to have this option. Here is an excerpt of the code:

public SQLQuery enableCloseConnectionOnList() {

this.closeConnectionOnList = Boolean.TRUE;

return this;

}

public SQLQuery disableCloseConnectionOnList() {

this.closeConnectionOnList = Boolean.FALSE;

return this;

}

@Override

public <RT> SearchResults<RT> listResults(Expression<RT> expr) {

SearchResults<RT> result = super.listResults(expr);

if(this.closeConnectionOnList){

closeConnection();

}

return result;

}

In addition, extended DML classes (SQLUpdateClause, SQLInsertClause) to add a EXECUTE method that gives option to close the connection immediately after running the script:

@Override

public long execute() {

return execute(Boolean.FALSE);

}

public long execute(Boolean closeConnection) {

Long result = super.execute();

if (closeConnection) {

closeConnection();

}

return result;

}

[Marlon Patrick]

Hi Timo,

Have you considered implementing this?

I'm asking because this is for me a impeditive for update querydsl version, then, would be very important (for me).

Em segunda-feira, 26 de setembro de 2011 13h50min30s UTC-3, timowest escreveu:

[timowest]

Hi.

Yes, I have considered it, but I am unsure how to do it.

Maybe this could be provided via a service accessed from the Configuration:

interface JDBCOperations {

    PreparedStatement prepare(Connection conn, String sql);

    void close(ResultSet rs);

    void close(Statement stmt);

    // ...

}

Timo

[Marlon Patrick]

Hi Timo,

You need implement this in a way that possibilities the same Configuration be possible to close the connection or no. For example:

void insertSomething(Something obj){

  obj.setId(nextval(qObj, connection)); //At this point the connection is kept open because it will be used in next operation

  sqlDelete.where(qObj.id.eq(obj.getId())).execute(); //At this point the connection is kept open because it will be used in next operation

  sqlInsert.populate(obj).executeAndClose(); //At this point the connection is closed because it's no longer necessary

}

Something findSomethingById(Long id){

return query(qOj,conn).where(qObj.id.eq(id)).listAndClose(qObj); //At this point the connection is closed because it's no longer necessary

}

Methods in SQLQuery and DML classes can be:

SQLInsertClause.execute(Boolean isToCloseConnection)

SQLQuery.list(Boolean isToCloseConnection)

SQLQuery.closeConnectionAfterList();

SQLQuery.setCloseConnectionAfterList(Boolean isToCloseConnection);

2014-02-05 timowest via Querydsl <querydsl+noreply-APn2wQefkk7kI02...@googlegroups.com>:

--
You received this message because you are subscribed to a topic in the Google Groups "Querydsl" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/querydsl/Et16lquE4qM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Atenciosamente,

Marlon Patrick

[timowest]

Hi.

On Thursday, February 6, 2014 4:20:13 AM UTC+2, Marlon Patrick wrote:

Hi Timo,

You need implement this in a way that possibilities the same Configuration be possible to close the connection or no. For example:

void insertSomething(Something obj){

  obj.setId(nextval(qObj, connection)); //At this point the connection is kept open because it will be used in next operation

  sqlDelete.where(qObj.id.eq(obj.getId())).execute(); //At this point the connection is kept open because it will be used in next operation

  sqlInsert.populate(obj).executeAndClose(); //At this point the connection is closed because it's no longer necessary

}

Something findSomethingById(Long id){

return query(qOj,conn).where(qObj.id.eq(id)).listAndClose(qObj); //At this point the connection is closed because it's no longer necessary

}

Methods in SQLQuery and DML classes can be:

SQLInsertClause.execute(Boolean isToCloseConnection)

SQLQuery.list(Boolean isToCloseConnection)

SQLQuery.closeConnectionAfterList();

SQLQuery.setCloseConnectionAfterList(Boolean isToCloseConnection);

That's not really an option, at least not for a minor release.

Also you describe a use case where you use multiple statements inside the scope of a single connection.

The usage of connections via Connection handling callbacks or AOP is too common for doing such drastical changes.

Do you use Querydsl SQL without any container technology such as Spring or Guice?

Br,
Timo
 

2014-02-05 timowest via Querydsl <querydsl+noreply-APn2wQefkk7kI02e0xPnNp6yrNOiXUEZ44_gIyQiFakJHtI@googlegroups.com>:

--
Atenciosamente,

Marlon Patrick

[Marlon Patrick]

In truth I use QueryDSL in application that runs in JBOSS server and it offer a way of close connections automatically after exit of ejb context, but, generates a error message case detect that a programmer forgot closes connection.

The EJB itself has interceptors and I could use them to make this "automatic", but closure would be a very complex control. Add Spring to my project just for that, does not sound cool to me. Finally, note that suggestion and when possible put in QueryDSL especially considering applications that run outside a container as you yourself said.

[Marlon Patrick]

Hi Timo,

Has a ticket for this?

--
Atenciosamente,

Marlon Patrick

[timowest]

Hi.

I couldn't find a ticket.

Timo

On Monday, July 14, 2014 7:37:25 AM UTC+3, Marlon Patrick wrote:

Hi Timo,

Has a ticket for this?

To unsubscribe from this group and all its topics, send an email to querydsl+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
Atenciosamente,

Marlon Patrick

[Marlon Patrick]

Can I open ticket for this?

For more options, visit https://groups.google.com/d/optout.

--
Atenciosamente,

Marlon Patrick

[Timo Westkämper]

Hi.

As stated before I'd rather not add any JDBC resource closing functionality to the signature of the query and dml classes.

There is also a problem with the method based approach and exceptions.

Have you considered using the try with resources pattern with JDBC connections? Here are some examples http://stackoverflow.com/questions/8066501/how-should-i-use-try-with-resources-with-jdbc

Br,

Timo

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.

[Marlon Patrick]

No problem Timo. Thank you.