SQL INSERT Statement in java

[Gabor Beke]

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.

String sqlQuery = "INSERT INTO addbook (name, phone, email) VALUES (?,?,?)";
               PreparedStatement stmt = conn.prepareStatement(sqlQuery);
               stmt.setString( 1, nameText ); //nameText variable contains the text of the jtextfield)
               stmt.setString( 2, phoneText );//phoneText variable contains the text of the jtextfield)
               stmt.setString( 3, emailText );//emailText  variable contains the text of the jtextfield)
               stmt.executeUpdate();
            JOptionPane.showMessageDialog(null,"You have successfully added it!");

Any help would be kindly appreciated:)

[Stuie Clarky]

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.

[Kemal Sokolovic]

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.

[Ernest Friedman-Hill]

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.

[Rakesh Keerthi]

Hi Gabor,
i think you forgot to call while(resultset.next()){}

Thanks

[Kemal Sokolovic]

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]

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 Vashko]

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]

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:

Statement s = conn.createStatement ();
   s.executeUpdate ("DROP TABLE IF EXISTS addbook");
//creating table
   s.executeUpdate (
               "CREATE TABLE addbook("
               + "name CHAR(100), "
               + "ID INT NOT NULL AUTO_INCREMENT, "
               + "PRIMARY KEY (ID),"
               + "phone CHAR(40),"
               + "email CHAR(100))");
               
   s.executeUpdate (
               "INSERT INTO nevjegyzek (name, phone,  email)"
               + " VALUES"
               + "('Test Jack ', '123456789', '[e-mail]),"
               + "('Test John', '214574895', '[e-mail]'),"
               + "('Test Peter', '748951459', '[e-mail]'),"
               + "('Test Jackson', '485496374', '[e-mail]')");

         JOptionPane.showMessageDialog(null,"You have successfully added it!"); 

With preparedstatement:

String sqlQuery = "INSERT INTO addbook (name, phone, email) VALUES (?,?,?)";  
              
               PreparedStatement stmt = conn.prepareStatement(sqlQuery);  

               stmt.setString( 1, nameText ); //nameText variable contains the text of the jtextfield)  
               stmt.setString( 2, phoneText );//phoneText variable contains the text of the jtextfield)  
               stmt.setString( 3, emailText );//emailText  variable contains the text of the jtextfield)  
               stmt.executeUpdate(); 
 
               JOptionPane.showMessageDialog(null,"You have successfully added it!"); 

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?

String sqlQuery = ("insert into addbook (name,phone, email) values('"+nameText+"', '"+phoneText+"', '"+emailText+")");

[Martin Vashko]

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:

catch (SQLException ex) {
}

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]

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 Vashko]

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]

Its working now:) Thank you all for helping me:) The problem was this line:

 s.executeUpdate ("DROP TABLE IF EXISTS addbook");