*
The moose likes JDBC and the fly likes How to use preparedStatements to store data to two different tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to use preparedStatements to store data to two different tables " Watch "How to use preparedStatements to store data to two different tables " New topic
Author

How to use preparedStatements to store data to two different tables

Luis Villamarin
Greenhorn

Joined: May 24, 2012
Posts: 21

I have two different tables in a MySQL DB. I have successfully inserted data to to the parent table. However, I 'm having problems to the second table which has the FK from the first table.

I'm new to JDBC and haven't been able to find documentation on how to add data to two tables using the same insert method. I suppose I have to use two different preparedStatement within the same Insert method but I'm not really, plus it doesn't work.

Any ideas on how to solve this where I can research to find the solution.

This is my insert method:
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30352
    
150

There are a few problems here. You do need to use two different prepared statements. However:
  • You call executeUpdate() twice - once after each prepared statement's parameters are set. Which means you are missing ps2.executeUpdate()
  • ps2 is independent. It starts out with one again for the parameter numbers
  • It is good practice to close all resources you open including prepared statements


  • [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
    Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
    Luis Villamarin
    Greenhorn

    Joined: May 24, 2012
    Posts: 21

    Thanks for the response. I have followed your suggestions however the application still only saving information to one table. Would it make sense to create another class for the second time?

    Here is an updated version of the code:

    Jeanne Boyarsky
    internet detective
    Marshal

    Joined: May 26, 2003
    Posts: 30352
        
    150

    No. It is ok to update more than one table in a class/connection. I usually close the prepared statement before opening another one, but I don't know that would matter.

    I assume the table it updates is the first one. You mentioned there is a primary key between tables. Which field is it on? Because I don't see a field set in the second insert that looks like a primary key to the first.

    Also, do you get any error messages? Does it throw an exception?
    chris webster
    Bartender

    Joined: Mar 01, 2009
    Posts: 1657
        
      14

    Couple of points.

    You have a column in your SQL called "account_type_account_name": are you sure about this? It looks like it should be two separate columns - ACCOUNT_TYPE and ACCOUNT_NAME. You should not merge columns in this way because you are making it hard to use these pieces of information individually.

    Also, as Jeanne says, it's not clear what your PK is on the USER table. I recommend you use a surrogate key i.e. a numeric ID populated via MySQL's auto-increment feature. This USER_ID should then also be used as the foreign key in your ACCOUNTS table (assuming that's the FK relationship you want - again it's not clear what/where your keys are).

    There are lots of good reasons for choosing a surrogate key with this kind of data e.g. it's guaranteed to be unique (you could have two users with the same name), it won't change (a user might change their name), it is independent of any changes to your business rules (because the business users don't care about it), it takes up less space, it's more efficient when searching, and it is much easier to use as a foreign key, especially when you have a tree of several table relationships.


    No more Blub for me, thank you, Vicar.
    Luis Villamarin
    Greenhorn

    Joined: May 24, 2012
    Posts: 21

    Jeanne and Chris,

    Thanks for the responses.

    @Chris - I used MySQL Workbench to create the DB and I have three tables that come into action for the problem that I'm presenting. The field account_type_account_name is the name of the 'account_name' in the table 'account_type'. Therefore the field is account_type_account_name is a FK in the account table (which is the one I'm trying to populate).

    @Chris and @Jeanne - The User table PK is emailAddress, and the Account table PK is account ID which generated using java.util.UUID, and the account_type table PK is account_name. The account_name is FK in the account table and the emailAddress is FK in the account table. The account_type table already has information stored in it. For the moment the account_type table has only 3 Rows.

    I have worked the insert method and I added some other fields and referenced the fields that were missing. This is how it looks right now, however the 2nd table is still no populated with data after the form was filled. It is not giving me any stacktrace.



    [Thumbnail for ERD-png.png]

     
    It is sorta covered in the JavaRanch Style Guide.
     
    subject: How to use preparedStatements to store data to two different tables