How to use JDBC in JavaEE?

Asked 19/3, 2012 at 9:28 Answered 19/3, 2012 at 9:53

I'm developing in a JavaEE environment (weblogic 12), and part of my code uses JDBC; Therefore, I need to aqcuire a JDBC connection from the application server.
I know it's a really bad practice to use JDBC in JavaEE, but that's a code I cannot change (legacy).

I've found a way to do it, but I'm not sure it's the right way:

@Resource(mappedName="mydsjndipath")
private DataSource ds;

public void foo() {
    Connection conn = ds.getConnection();
}

The question is what do I do with the connection at the end?
I can't really commit/rollback it, because I use a distributed transaction. But should I at least close it?
And will the JTA transaction will always effect the connection (on commit/rollback)?

Or maybe there's another better way to use JDBC in JavaEE? (no, the EntityManager's native queries won't do)

Jamisonjammal answered 19/3, 2012 at 9:28 Comment(10)

Why would it be bad practice to use JDBC in JEE? – Blader 19/3, 2012 at 9:53

For two reasons: 1. It is probably better to use ORM (JPA). 2. I'm not sure about this, but when you use this interface, aren't you opening a new connection which is not managed by the application server? – Jamisonjammal 19/3, 2012 at 11:4

Re 1. Why is it better to use ORM? If I need to do a bulk update or a specific query or don't need an Object model, why would I not just use JDBC. – Blader 19/3, 2012 at 11:27

Re 2. Getting a connection from the data source means that you are asking the container/application server for a new connection, so you'll get the benefits of container managed connections. Also, it does not necessarily open a new connection, it may use the connection that is associated with the current transaction. – Blader 19/3, 2012 at 11:29

At the end of the day, under the hood the ORM will pretty much do the same thing, it'll ask a data source for a connection when it needs to and when it is finished, it'll close the connection (which will actually hand the connection back to the pool managed by the application server) – Blader 19/3, 2012 at 11:31

1. I find only three reasons for using JDBC in JEE; Performance; not having time to learn ORM; and having a legacy code which uses JDBC. If you have a table that you don't want to map with ORM but only to fetch something specific from it, you can use the EntityManager's native queries. If it's the only query in your application then I would doubt your need for JEE. 2. I think it creates a new connection; I've invoked 'getConnection' twice and it gave me two different connections. – Jamisonjammal 19/3, 2012 at 14:56

I think a statement like "database access in JEE should always be done using ORM" is generalising and asking for trouble. Of course, there are plenty of reasons to use ORM for specific problems. For other specific problems using ORM is overkill and is going to result in reduced maintainability and reduced performance and solution that is worse than using straight JDBC. After all, just because you've got a screwdriver, you wouldn't use it with nails... – Blader 19/3, 2012 at 15:53

I never said that DB access should always be done using ORM. If using ORM in your application is an overkill, then there's a good chance using JEE in your application is an overkill as well. – Jamisonjammal 19/3, 2012 at 16:56

I think we'll just agree to disagree. In my view the assertion that "JDBC in JEE is bad practice" implies that there is no place for it in JEE and in my view there very much is and letting an application server manage the connections used with JDBC makes sense and is something that happens in more than legacy code. – Blader 19/3, 2012 at 20:37

Possible duplicate of Closing JDBC Connections in Pool – Nominate 20/3, 2018 at 19:53

Why should using JDBC be bad practice?

If your application server supports JDBC and you let him connect to a DB via JDBC I, for myself, see no reason why you shouldn't use it in your application, too!?

Another approach would be to load the driver manually in your application and get a connection from it. But this would be like reinventing the wheel!

Also you dismiss the advantage of

the server side management of your JDBC connection-pool
the reusability of this connection

At the end you should always close your Connection/Statement/ResultSet like:

try {
  // your stuff here
}
finally {
  if(connection != null) {
    connection.close();
  }
  // same for statement/ResultSet ift not used anymore
}

Seeress answered 19/3, 2012 at 9:53 Comment(6)

Why it is a bad practice is another question for another discussion. It is not what I'm asking. And I'm aware of how JDBC resources should be closed, it is also not the question. – Jamisonjammal 19/3, 2012 at 11:10

I don't get it. Your question is what to do with the connection at the end. The answers is: close it. On the basis of your code your server already uses JDBC and will manage your commit/rollback functionality. Therefore you made the statement JDBC is bad practice I (and some other users) throw in my two cents. No offense! Cheers! – Seeress 19/3, 2012 at 11:35

1. My question doesn't really have something to do with my opinion that JDBC is usually a bad practice in JEE (maybe I shouldn't have mentioned it). 2. Using regular JDBC, you can't commit or roll-back a connection after you close it. This is why it's weird to me that combined with JTA, the connection can be commited or rolled-back after invoking it with 'close'. – Jamisonjammal 19/3, 2012 at 15:6

OK, the JDBC-issue aside. To close a connection in your JTA setup just means to release this ressource to the server where the server decides what hapens next (GC or commit/rollback). If you can invoke an .commit() on an already closed connection it could be that the connection wasn't closed/returned by the transaction manager because it is still holding some references to an open transaction. Usually there is an autoCommit value set to your connection which commits after every statement. If you disabled this you'll have to take care of this by yourself. Cheers – Seeress 19/3, 2012 at 15:49

I didn't mean that I can commit or roll-back the connection after it's closed (I can't do it even when it's open), but that the application server can do it. I guess they simply overridden the 'close' method in their implementation of Connection, so it doesn't really closes the connection. – Jamisonjammal 19/3, 2012 at 17:19

I stick with my statement that you delegated the transaction management to the server and therfore it holds references to objects. Because of this mangement the server is able to delay a commit which should normaly already be performed. You said you use distributed transactions: Therefore you should close YOUR connection to make sure that the server can commit/rollback the transaction after all transactions have finished. Cheers! – Seeress 19/3, 2012 at 19:1

close the connection- conn.close();

Rockingham answered 19/3, 2012 at 9:39 Comment(2)

What about commiting or rolling back the JTA transaction? Would it still effect the connection even if it's closed? – Jamisonjammal 19/3, 2012 at 11:7

Nope transaction is either commited or rollback depending on driver implementation during close.. – Rockingham 21/3, 2012 at 14:46

Recommended topics

Hot tags