• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

SQL INSERT Statement in java

 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:)
 
Ranch Hand
Posts: 104
Eclipse IDE Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 825
5
Python Ruby Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
author and iconoclast
Posts: 24207
46
Mac OS X Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Gabor,
i think you forgot to call while(resultset.next()){}

Thanks
 
Kemal Sokolovic
Bartender
Posts: 825
5
Python Ruby Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Its working now:) Thank you all for helping me:) The problem was this line:



 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic