This week's book giveaway is in the Artificial Intelligence and Machine Learning forum.
We're giving away four copies of Transfer Learning for Natural Language Processing (MEAP) and have Paul Azunre on-line!
See this thread for details.
Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

I read that you should make a new JDBC connection and close it ASAP when inserting data?

 
Greenhorn
Posts: 12
1
IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I'm grabbing data from an API and I get very fast like 100 items.

I want to insert these into my database but I have no idea when the last item will be or come in.

That means that I can't make a List and then at the end insert all the data at once.

So every time I get this data (sometimes +/-100 items very fast) I would need to call a class that inserts this data immediately.

This is all fine but my question is, this call to that method in that class makes a JDBC connection, inserts data and closes the connection. So it does this 100 times very fast sequentially. I was thinking about opening 1 time a connection and then keep that connection open and just close it when the program ends. Apparently this is not done and Java coders prefer to make these connections and close them after data insertion is finished, combined with data connection pooling in some frameworks.

Is this correct? Opening and closing connections to databases so many times seem weird to me? I would have a static method that makes this connection and keep it open and then just another method that inserts data, but this apparently is wrong :-)

Thanks!
 
Marshal
Posts: 68917
275
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please always tell us the source of such information, so we can read it for ourselves.

Sounds like something you want to do in a separate th‍read (at which point it ceases to be a “beginning” topic, so I shall add you to different fora). Not sure what you would do but find out about completable futures. One reference is Urma Fusco and Mycroft Modern Java in Action (Manning). It's also the older edition of that book, called Java8 in Action.
 
Saloon Keeper
Posts: 21975
150
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you have some general confusion here. Let me try and clear it up.

Creating a JDBC connection from scratch is a relatively expensive operation. So if you need Connections frequently, it is more efficient to use them wisely. What constitutes "wisely", however, depends on the environment that you are running in.

In a single-user application, it's generally best to create one Connection, hold on to it for relatively long periods of time, and use it repeatedly. Now "relatively" is somewhat subjective. If the application runs for days at a time, but database operations are only done once a day, holding on to the Connection for a week or more is not a good idea. Connections don't only consume Java resources, they consume OS resources and there are only a limited (although usually very large) number of connection reply ports and OS memory resources to back them up. So you'd want to create the Connection at whatever time of day it's needed, do all your database work, then release the Connection until the next day. Whereas in a short-run desktop editor, you might just create the Connection at startup, hold onto it until the user quits and release it as part of program shutdown.

In web applications, the rules are different. You can potentially have many different application users needing to do database operations. In web applications, the key is to get in, work quickly and get out, and that's not just about databases, it's about all webapp resources, since if they are efficiently recycled you wouldn't need 1000 connections for 1000 users if only 15 users at a time were using the database. This is such a common scenario that Connection Pools are defined at the web application server level - outside of the webapp itself. Pool Connections look almost identical to regular Connections. You obtain them via the pool's getConnection function, use them, and then return them to the pool using the Pool Connection's close() method, which differs from the normal Connection in that a Pool close simply returns the Connection to the pool instead of destroying it, and thus minimizes the overhead of creating new Connections.

One other thing to note is that often if a Connection sits unused for a long period of time, it will time out and the underlying components will be destroyed by the database server and/or host OS. If you try to use such a broken Connection, that will generally throw an Exception (unless it's a smart Connection that can re-connect itself, and those aren't that common). So in such cases, you might as well close the Connection when you're done with it and create a new one when needed and avoid possible unpleasant surprises.
 
B Vancoullie
Greenhorn
Posts: 12
1
IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:Please always tell us the source of such information, so we can read it for ourselves.

Sounds like something you want to do in a separate th‍read (at which point it ceases to be a “beginning” topic, so I shall add you to different fora). Not sure what you would do but find out about completable futures. One reference is Urma Fusco and Mycroft Modern Java in Action (Manning). It's also the older edition of that book, called Java8 in Action.



sorry for that

i was reading this: https://stackoverflow.com/questions/9428573/is-it-safe-to-use-a-static-java-sql-connection-instance-in-a-multithreaded-syste

"You should always acquire and close the connection, statement and resultset in the shortest possible scope, preferably inside the very same try-with-resources block as where you're executing the query according the following JDBC idiom:"

thanks
 
Campbell Ritchie
Marshal
Posts: 68917
275
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for the source. That advice does sound good. If you are accessing the database from multiple threads, you can have concurrency problems, getting incomplete updates from each of the threads (preventable with transactions) and you can overwhelm the capacity of the database to open new connections as required.
 
Marshal
Posts: 25452
65
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It depends on what kind of "multithreaded system" you're using. Like Tim said earlier, if you have a web service of some kind then it's going to be a multithreaded system and it's going to have a connection pool to work with.

If you're writing your own multithreaded system, then you would also be well advised to include a connection pool in the design. And then the Stack Overflow advice which you referred to is correct, although "closing" the connection just returns it to the pool.

But all of this is just echoing the top-ranked answer over there, which covers all of the issues related to using JDBC in a web application.
 
B Vancoullie
Greenhorn
Posts: 12
1
IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the replies, this makes all sense now. Seems like I need to create a database connection pool and take a connection from it and close it when done and hand it back to the pool. I'm easing into spring so I hope Spring will help me with that :-)

Cheers

 
Paul Clapham
Marshal
Posts: 25452
65
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
By now you've probably already found out, but I would be surprised if connection pools weren't available from Spring.
 
Good heavens! What have you done! Here, try to fix it with this tiny ad:
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic