MySQL + Java - auto incrementation issue

[int80]

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 ;)

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.

[int80]

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.

[Eku]

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

[fishtoprecords]

It easy. JDBC has a function specifically for this.

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();
}

[jack239]

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?

[Eku]

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

[fishtoprecords]

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?