This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes SQL INSERT Statement in java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL INSERT Statement in java" Watch "SQL INSERT Statement in java" New topic
Author

SQL INSERT Statement in java

Gabor Beke
Greenhorn

Joined: Jul 11, 2012
Posts: 16
Hello Folks:P

I'm trying to figure out for days know how to do this. I'm making a database program that has an address book GUI. You can give there your name, address, and phone number through JTextfield, and when you click on a Jbutton its execute the sql statment. My primary key is name, or it should be a number? like ID with INT type and set it to autoincrement? My problem is that my records keep overwriting themselves in the database. How to make it to insert it to a new row not to overwrite the first one?

Here is the part of the code that I have the problem with. The rest of the program works fine.


Any help would be kindly appreciated
Stuie Clarky
Ranch Hand

Joined: Nov 09, 2012
Posts: 76

What your code is doing is resetting the string stmt each line to be equal to the name, phone, etc.

I'd suggest you read the javadoc for StringBuilder, as this is one way of creating a single string object from multiple different lines.


OCAJP 7
Kemal Sokolovic
Bartender

Joined: Jun 19, 2010
Posts: 825
    
    5

My problem is that my records keep overwriting themselves in the database.

How come? I don't think that's possible, given the information you provided. If you try to add a new row with the same key as an existing one you should get an exception that would tell you that the given key already exists.
Still, choosing a name to be the primary key doesn't seem ok to me. You should have something else that can differ them (like an integer ID, or an e-mail address).

What your code is doing is resetting the string stmt each line to be equal to the name, phone, etc.

I'd suggest you read the javadoc for StringBuilder, as this is one way of creating a single string object from multiple different lines.

I don't think you've read the question carefully.


The quieter you are, the more you are able to hear.
Ernest Friedman-Hill
author and iconoclast
Marshal

Joined: Jul 08, 2003
Posts: 24187
    
  34

Stuie Clarky wrote:
I'd suggest you read the javadoc for StringBuilder, as this is one way of creating a single string object from multiple different lines.


Not at all -- PreparedStatement is perfect for this job. Using StringBuilder to build a query would be vastly inferior from many standpoints: security in particular, but also database performance.


[Jess in Action][AskingGoodQuestions]
Rakesh Keerthi
Ranch Hand

Joined: Jul 16, 2012
Posts: 144
Hi Gabor,
i think you forgot to call while(resultset.next()){}

Thanks
Kemal Sokolovic
Bartender

Joined: Jun 19, 2010
Posts: 825
    
    5

Rakesh Keerthi wrote:Hi Gabor,
i think you forgot to call while(resultset.next()){}
Thanks

This is an INSERT statement, why would you do that?
Gabor Beke
Greenhorn

Joined: Jul 11, 2012
Posts: 16
Thank you all for the answers Maybe I found out whats wrong, I didnt make primary key with autoincrement in my table, so when I executed sql statement it couldnt make new row in the database, instead of overwriting the only 1 existing row all the time. Altough I didnt tried out my solution, because I'm at work now, and I dont have here jdk neither jre to test this out. BTW AUTO_INCREMENT is always needed for tables to make new row?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Gabor Beke wrote:BTW AUTO_INCREMENT is always needed for tables to make new row?

No, it isn't.

Besides, an autoincrement column shouldn't ever prevent you from creating new rows (unless you do something really, really weird, such as limit the range of the IDs to just one value, eg. 1 to 1). (Edit: if this sentence looks out of context to you, it is. I have misread the previous post. )

The INSERT VALUES statement, by definition, either creates a new row in the database, or fails. This includes issuing an INSERT with a primary key value that already exists in the database, this would cause an exception (in most databases). If you're not getting any exception, the row is most probably being created in the database, but something other is wrong which makes it look like it isn't the case. It could be lots of things:
- you do not commit the transaction
- some other process is removing rows from the table
- you don't properly read the contents of the table, so you only see the last row
- there is an instead-of trigger defined on the table, which does some other thing than inserting the row (some databases allow such shenanigans)

We need more information. What database are you using? Can you look at the contents of the table using some database client, such as TOAD, before and after the execution of your statement?
Gabor Beke
Greenhorn

Joined: Jul 11, 2012
Posts: 16
First of all thank for the answer Martin!

I'm using MySQL Community Server 5.5.28 hosted on localhost. I'm using Navicat for MySQL to browse and check out the database. I'm trying with the both way to get it work ( with preparedstatement and w/o)



Without preparedstatement:


With preparedstatement:



My question is which one should I use? I would prefer PreparedStatement more because its more readable and easier for me. Is the syntax is correct or did something wrong? I want to make table create automatic through my java program once the connection has been established. Ofc both case I close the connection in the end. The create table syntax is good? primary key is enough for ID or its need Unique or Index? if yes can you show me how should Include those?

If I dont use preparedstatement and I want to insert data retrived from jtextfiled, this syntax should work or not? if not whats the right syntax for doing this?

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Generally I don't see anything wrong with the statements, but I don't know MySQL. If you don't get an exception while executing the statements, I'd say they are ok. How do the catch clauses look like in your code? Make sure you don't do anything like this:That would be a horrible mistake: if an exception occurs, it is silently ignored and you don't have means to find out that anything went wrong, let alone diagnose the problem.

Definitely use PreparedStatement for the INSERT, as has already been suggested by others. Read what Ernest has said earlier, or our page on PreparedStatement.

Use database tools (eg. TOAD, but MySQL certainly has some built-in tool for this too) to have a look what your application does in the DB. Verify that the statements you execute from your application have the expected effect. Remember that if you don't commit, the changes won't be visible and will be rolled back on disconnect. JDBC is in an autocommit mode by default (committing every individual statement), so you'd have to turn it off in your code explicitly to bump into issues like these.

You should close not only connections, but result sets and prepared statements too. Always close the resources in the finally clause, or use the new Java 7 try-with-resources syntax.

Primary key enforces uniqueness and in most databases it implies NOT NULL too, I assume this is true for MySQL as well.

Dropping and recreating the table right after connection means that you'll lose all data from previous runs of your application. Might be good for testing, but definitely not for production code, unless you're creating some kind of temporary tables this way.
Gabor Beke
Greenhorn

Joined: Jul 11, 2012
Posts: 16
Dropping and recreating the table right after connection means that you'll lose all data from previous runs of your application. Might be good for testing, but definitely not for production code, unless you're creating some kind of temporary tables this way.


This would be really the problem as you said. Everytime I write data to the database it will always dismiss the old table and create new one, ofc I cant see my newly insert datas. Thank you for pointing that out for me. I will try it out once I go home, and repost here what happened Btw I misunderstood syntax about creating table. I thought if I drop table if exists its perevent to erease my data,
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

The difference between DROP TABLE and DROP TABLE IF EXISTS is that the first one fails with an error if the table doesn't exist. The second one does nothing in such case. IF the table is there, both of these statements drop it, after that all the table's data is gone.
Gabor Beke
Greenhorn

Joined: Jul 11, 2012
Posts: 16
Its working now Thank you all for helping me The problem was this line:



 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL INSERT Statement in java