1

I'm coding a Java server application that has to communicate with a MySQL database a lot. I'm using JDBC to connect to the database.

Now should I create one Connection and use it during the whole runtime?

Or should I create a new Connection always when I need to communicate with the database?

Which solution is the better one?

MinecraftShamrock
  • 3,504
  • 2
  • 25
  • 44

3 Answers3

4

You'll probably want to use a jdbc connection pool. Leaving connections open too long can cause odd timeout bugs and such.

StormeHawke
  • 5,987
  • 5
  • 45
  • 73
  • 4
    @MinecraftShamrock: Did you research "jdbc connection pool" before asking? – Jon Skeet Aug 16 '13 at 16:42
  • @MinecraftShamrock a good point is you can hover the mouse over tags in questions and a popup with an [info (e.g. JDBC)](http://stackoverflow.com/tags/jdbc/info) link will appear that will navigate to a wiki. At least the Java and related technologies have a decent wiki to learn the basics and/or links that points to good resources for learning purposes. E.g. [Connection pooling options with JDBC: DBCP vs C3P0](http://stackoverflow.com/q/520585/1065197) – Luiggi Mendoza Aug 16 '13 at 16:47
  • Would it be a good possibility to keep a connection for about 20 seconds and the create a new one and keep this for 20 seconds? – MinecraftShamrock Aug 16 '13 at 16:48
  • @MinecraftShamrock - No. All you're doing with that is creating needless complexity. Use a JDBC connection pool. There are plenty of them available, you don't even need to implement one. Then, using the pool, open your connection, do your work, and "close" the connection. – StormeHawke Aug 16 '13 at 16:53
2

Let's see, well let's say that you have an application, and you open you have a connection open the entire time that you are in the app (until you close it) that will take a lot of your resources, so you decide to use it inside methods, like this

   public static void(){
       //open connection
      //doing stuff
       connection.Close();
}

so every time that you use the method you will open and then close the connection, which means that you are avoiding the first problem. However opening and closing connections could lead into more resources usage.

So the solution is Using connection pooling this will allow you to create a pool of Connections Objects and every time that you don't need it instead of disposing the connection you return this connection to the pool, waiting for you to reuse it.

Abstract
  • 664
  • 1
  • 5
  • 15
  • And what does the pool with the connection so it is not a waste of resources? – MinecraftShamrock Aug 16 '13 at 17:30
  • It allows you to have many connections open, so when a thread needs one it can "reserve" the connection for use, when this thread finish he return the connection so it can be used in other threads (without having to open and close the connection every time). In this way if you have a connection available a thread does not has to wait until another finish. As you can see is up to your app needs, I would advise you to analyze what you need first and then you can choose if you will use a connection pool or not. – Abstract Aug 16 '13 at 17:39
  • Also it depends if you are going to do two or more queries into the database at the same time, in this case you should use the connection pool. Although the application only makes one query at the same time and does not require to be used for long periods of time, you should not worry about it, and just keep the connection open until you close the app. – Abstract Aug 16 '13 at 17:47
  • So it only holds one connection that is given to many threads? – MinecraftShamrock Aug 16 '13 at 17:50
  • Using a pool connection you will have multiples connection that you can assign to a thread, if a thread is using one of the connections this connection can't be used in another thread, however since you have multiple connections you can assign another one to this thread, whenever you are done whit the connection you just "close it" (which it does not really close it, the connection it's just returned to the pool, so you can assign it to another thread). – Abstract Aug 16 '13 at 18:05
  • If you decide to use just one connection, you won't be able to use this connection in more than one thread. – Abstract Aug 16 '13 at 18:06
  • And a connection pool would manage all my connections for all threads? – MinecraftShamrock Aug 16 '13 at 18:10
  • yes indeed, that's right! try to search a little bit about it, you won't regret it! – Abstract Aug 16 '13 at 18:13
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/35618/discussion-between-nivde92-and-minecraftshamrock) – Abstract Aug 16 '13 at 18:54
0

Solution is
use a jdbc connection pool.
Use JDBC Type 3 drivers.

Sushil Deshmukh
  • 114
  • 7
  • COuld you explain why you say to use Type 3 drivers? They are not very common, and I don't see what benefit they would provide here – Mark Rotteveel Aug 17 '13 at 06:52