Results 1 to 7 of 7
  1. #1
    int80 is offline Member
    Join Date
    Jul 2008
    Posts
    29
    Rep Power
    0

    Default MySQL + Java - auto incrementation issue

    I've made a program that takes details from a person, and puts them into a MySQL database. I've set an order_id to auto_increment, but I also want a group_order_id: So if 10 people make an order as a group, I want them all to have the same group_order_id, but individual order_ids. I can't set group_order_id to auto increment as it wont give the group their shared IDs.

    I was just wondering if anyone knew how to do this. The end of the order would be triggered by an event such as a button, so maybe I could just check to see what the last group_order_id is, then just do something to that.

    But I want to know if there a way of doing this in mysql? I'm a bit of a database noob, so take it easy ;)

    Java Code:
    mysql> DESCRIBE order_details;
    +-------------+------------------+------+-----+---------+----------------+
    | Field       | Type             | Null | Key | Default | Extra          |
    +-------------+------------------+------+-----+---------+----------------+
    | order_id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    |group_order_id| int(10) unsigned| NO   |     | NULL    |                       |    
    | f_name      | varchar(20)      | YES  |     | NULL    |                |
    | l_name      | varchar(20)      | YES  |     | NULL    |                |
    | date        | varchar(10)      | YES  |     | NULL    |                |
    | no_people   | int(10) unsigned | YES  |     | NULL    |                |
    | hire_length | int(10) unsigned | YES  |     | NULL    |                |
    | Bike_Type   | varchar(25)      | YES  |     | NULL    |                |
    | Frame_Size  | varchar(10)      | YES  |     | NULL    |                |
    | Helmet_Size | int(10) unsigned | YES  |     | NULL    |                |
    | Panniers    | varchar(5)       | YES  |     | NULL    |                |
    | Helmet      | varchar(5)       | YES  |     | NULL    |                |
    | Sex         | varchar(10)      | YES  |     | NULL    |                |
    +-------------+------------------+------+-----+---------+----------------+

    Thanks in advance.

  2. #2
    int80 is offline Member
    Join Date
    Jul 2008
    Posts
    29
    Rep Power
    0

    Default

    I've just worked it out....But still, if anyone knows anything about that, I would still like to hear if it's the same as what I've done...It's quite easy really, just stumped me for a bit.

  3. #3
    Eku
    Eku is offline Senior Member
    Join Date
    May 2008
    Location
    Makati, Philippines
    Posts
    234
    Rep Power
    7

    Default

    Im suggesting you can have a another table for Group that will contain the following.

    group_id = auto increament
    group_description = description
    and others

    your program can validate and fetch from the group_table which group will be the customer in. And your program can also create new groups for some customer. therefore when executing a transaction the group_id will be from the group table NOT from the order details.

    I hope that helps
    Mind only knows what lies near the heart, it alone sees the depth of the soul.

  4. #4
    fishtoprecords's Avatar
    fishtoprecords is offline Senior Member
    Join Date
    Jun 2008
    Posts
    571
    Rep Power
    7

    Red face

    It easy. JDBC has a function specifically for this.

    Java Code:
    int numRows = stmt.executeUpdate(command,  Statement.RETURN_GENERATED_KEYS);
    
    ResultSet rs = null;
    try {
           rs = stmt.getGeneratedKeys();
           if (rs.next()) {
                   autoIncKeyFromApi = rs.getInt(1);
            } else {
                   throw new RuntimeException("PIB, can't find most recent insert we just entered");
            }
            rs.close();
    
    } catch (SQLException ex) {
           ex.printStackTrace();
    }

  5. #5
    jack239 is offline Member
    Join Date
    Jul 2008
    Posts
    35
    Rep Power
    0

    Default

    I think you should go by the solution given by EU in database side as it is very reliable than the jdbc side. What do you think guys?
    New to Java/PHP/Javascript development?
    For free help go to- www.techcubetalk.com

  6. #6
    Eku
    Eku is offline Senior Member
    Join Date
    May 2008
    Location
    Makati, Philippines
    Posts
    234
    Rep Power
    7

    Default

    fishtoprecords 's suggestion is Ok for me, i havent tried that one yet. I used to get the last number by using a select statement with count. or in our case here we have a algorithm in the database where in we use "Select sequence_table from duel". We have a seperate generator for primary key in the database.

    In the case here, (for example just to give you an idea), we have a common database and not everyone accesing the database uses java ^_^ Thats why we have the autoincreament part in the database rather in the software. ^_^ and it is the most common practice that i observe here in our company
    Last edited by Eku; 07-29-2008 at 09:47 AM.
    Mind only knows what lies near the heart, it alone sees the depth of the soul.

  7. #7
    fishtoprecords's Avatar
    fishtoprecords is offline Senior Member
    Join Date
    Jun 2008
    Posts
    571
    Rep Power
    7

    Default

    Quote Originally Posted by jack239 View Post
    I think you should go by the solution given by EU in database side as it is very reliable than the jdbc side. What do you think guys?
    I don't fullly grok his idea. But if he is suggesting using a second table just to hold the unique id, I think this is a very bad idea. You want fewer tables, fewer joins. Tables grow and reproduce themselves, sometimes like bunnies. No need to encourage them.

    What do you do when the second table is out of sync with the first? Don't say it can never happen, if you have two things to do anything, there is a chance for them to get out of sync. Now what do you do?

Similar Threads

  1. How to combine mysql and java?
    By sandeeprao.techno in forum Advanced Java
    Replies: 1
    Last Post: 05-21-2008, 04:41 AM
  2. java to mysql
    By thamizhisai in forum New To Java
    Replies: 12
    Last Post: 04-28-2008, 07:48 AM
  3. java to mysql
    By thamizhisai in forum Advanced Java
    Replies: 1
    Last Post: 04-26-2008, 08:21 AM
  4. MySQL issue: Exception while getting MetaDataInfo
    By sandeepspatil in forum JDBC
    Replies: 2
    Last Post: 07-27-2007, 06:54 AM
  5. how to issue the command of Ctrl-C (copy) in Java
    By bilal_ali_java in forum Advanced Java
    Replies: 0
    Last Post: 07-18-2007, 03:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •