Member Avatar for Anon17

I have a server which uses MySQL and supports many clients, and everything works perfectly fine when the MySQL database is on the same machine as the server.

However, when it's on another machine, due to what I believe is the slow connection there are several requests from different clients all doing MySQL queries, which throws null exceptions and "Operation can't be done after resultset close" exceptions.

Due to the nature of the server there are several running, each on different machines, but all connecting to the same master DB. Due to this I need to fix the remote MySQL access, and I believe threading it would work.

Each client connection to the server cannot have it's own SQLDatabase object (Which does the queries) as that lags up the server quite badly.

Any ideas? Ideally I want to be able to thread the executeQuery MySQL function somehow.

  • 2 Contributors
  • 3 Replies
  • 122 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by masijade
Member Avatar for masijade

That is bad coding. Don't reuse statement objects (i.e. don't make statement and resultset objects instance variables, they should be opened, used, and closed as narrowly as possible, and try not to share connections, use a connection pool and get, use, and close connections from the pool as narrowly as possible.

Closed resultsets and npe's will not result from queries from separate clients to the DB, so I can only assume this is a web app (or something similar) and all the actual DB requests are originating from the same VM, and probably using instance variables for statements and/or resultsets and are possibly sharing connections.

Member Avatar for Anon17

Hmm... I see...

I had an idea to preload all the information into int and string arrays, so the database wouldn't have to do many queries at all (Obviously some things need to be dynamic, but the majority don't).

Would this cause too much memory usage to be worth it, or do you think preloading would be a good idea?

Member Avatar for masijade

Unless the table is huge, or the network insufferably slow, or there are a lot of queries to perform, I don't see the problem in using multiple queries, as the should be more than quick enough. I also, however, don't see a problem in "preloading", depending on the size of the data and whether or not it can change, but store it in an application context object rather than as an instance or class variable in the servlet.

Edit: And, do it in the init method of the servlet, rather than in any of the "do" methods.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.