Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes Strange behavior when using large queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Strange behavior when using large queries" Watch "Strange behavior when using large queries" New topic
Author

Strange behavior when using large queries

Leonard Connoway
Greenhorn

Joined: Feb 19, 2013
Posts: 21
To begin, I am pretty new at Java, so I apologize for any amateurish mistakes. Basically, what I have tried to do is create a tool that will allow me to copy many items from a database and then randomize them.
Some of the things I am using, I am not entirely confident about because I am only a Freshman computer science major, so I have not actually encountered them in class, so I am not entirely sure if there is a problem or where it is.
If i put a few IDs in the main field and then attempt to randomize their stats, everything will work well, and when it is done, I will get the message saying that it has completed; however, when I try to randomize a list of a couple of hundred of things,
it appears to work, but I do not get a success message which makes me think it is stopping somewhere and just not telling me.
I would appreciate it if anyone could look over this and tell me what errors there are. The following code is just for the randomizing part of my program.
The actual program itself is written using the same style and far too long to put in one post (~1000 lines). If you're wondering, this is for a video game server too.

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Welcome to the Ranch, Leonard!

First of all, you need to edit your post (click the button on your post) and wrap the extremely long lines at 80 (at most 120) characters.
I don't think anyone is going to read that code in its current form.
This way your code is completely unreadable, not only here on the website, but in your development environment as well.

Secondly, please be more precise in describing the faulty behavior of your program. What exactly does "it appears to work" mean?

Also remember that database operations can be really long. A query that works well on one row can take ages when run over a thousand rows.
Writing performant queries (the famous "SQL tuning") can be pretty demanding task and does require some knowledge and experience with relational databases.
Vinod Tiwari
Ranch Hand

Joined: Feb 06, 2008
Posts: 459
    
    1
Hi Leonard - Could you please format / word wrap the content of your post. Secondly please let us know what are you trying to achieve and what is the issue that you have noted?

If possible please post output also. If the SQL code is working from PL/SQL editor then there is no reason why it should not work from Java.

BTW - Welcome to JR.


Vinod Tiwari | Twitter
Leonard Connoway
Greenhorn

Joined: Feb 19, 2013
Posts: 21
Ok, I formatted it so it does not look as terrible anymore. To be more specific, the code I found online for doing things with SQL had this

which I assumed to be a way to print out that everything had successfully completed. When I run this part of my program with a few item IDs,
it will return that message. Where I am not entirely sure if there is a problem though is when I am modifying upwards of 100 things because after I click the button,
I do not get any message printed out unless there is a blank ID error. As I said, most of this code was copy/paste/modify, so I'm not sure if the way I have it written makes
it more likely for something to go wrong for large amounts of IDs. I said it appears to work though because I will look at the first ID, last ID, and a middle ID after execution and
they will have been affected in at least some of the fields (the program is designed for some fields to get a +0 value, so staying the same on some things is expected.) So, what I am asking
is does anyone know a reason why that success message comes up for small groups of numbers but not large ones? It just makes me think that there is an error in there somewhere that's preventing
me from getting the message.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

I'm not familiar with multi-update queries; when you get the result from Statement.execute("update, update, update, update") what exactly is it the result of? The API documentation doesn't mention this particular case.

Also, I think your code would be very much simplified if you used PreparedStatement to do that, rather than generating a giant SQL statement with string concatenation like you did.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

I also don't understand why you're trying to get the generated keys from a large list of Update statements. I wouldn't have thought that any keys would be generated, since you aren't doing any Inserts. (Not that I can see, anyway.)
Leonard Connoway
Greenhorn

Joined: Feb 19, 2013
Posts: 21
Ok, I fixed that. I rewrote it to remove the keys. I tested it, and it worked fine a few times, but then stopped. Like, I randomized 100 items perfectly, then tried to just modify one item, and it stopped doing anything and did not give an error anywhere. Clearly, I have some problem in there or it is an issue with BlueJ. Does anyone have any idea how I could better locate the problem?
 
 
subject: Strange behavior when using large queries
 
Similar Threads
How to update an entity in a collection
Update in For loop.
Adding object into list through user input
How to update a data in database from jsp
ActionForm with indexed properties