• 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
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

How to use preparedStatements to store data to two different tables

 
Greenhorn
Posts: 21
Android Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:
 
author & internet detective
Posts: 41083
848
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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

  •  
    Luis Villamarin
    Greenhorn
    Posts: 21
    Android Chrome Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    author & internet detective
    Posts: 41083
    848
    Eclipse IDE VI Editor Java
    • Likes 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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?
     
    Bartender
    Posts: 2407
    36
    Scala Python Oracle Postgres Database Linux
    • Likes 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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.
     
    Luis Villamarin
    Greenhorn
    Posts: 21
    Android Chrome Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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.

    ERD-png.png
    [Thumbnail for ERD-png.png]
     
    Greenhorn
    Posts: 4
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    I think the issue may be that your 2nd update is in a return statement, this may be preventing the commit that jdbc does automatically behind the scenes. It is typically not a good idea to put return statements in try catch blocks because it interrupts the intended flow...in this case your cleanup code will only run in the case of an exception.
     
    Rancher
    Posts: 4801
    50
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    There's no issue with a return statement in a try/catch block.
    It's prefectly valid, and has no effect on the flow.
    A finally block will always execute before the try/catch/finally block exits.
     
    This. Exactly this. This is what my therapist has been talking about. And now with a tiny ad:
    Free, earth friendly heat - from the CodeRanch trailboss
    https://www.kickstarter.com/projects/paulwheaton/free-heat
    reply
      Bookmark Topic Watch Topic
    • New Topic