Auto increment for non Primary Key

[sam creep]

Hi.
 We need to have an auto-increment on some non primary key columns to maintain some data. How can we achieve the same. I have heard about custom generators but could not get through on how to use them.
 My use-case are -

1. to generate sequential invoice nos for a purchase in a serial order in the format IN-XXXXX-2012 (XXXXX can vary in length & 2012 is the year).
2. to generate sequential nos for a series of items purchased, more like a serial no.

I would prefer not to use any db specific stuff like sequences.

Please can somebody suggest some way to implement this.

Thanks

[sam creep]

Hi.
 Please could somebody update.

[edge]

this isn't really an ORM issued but anyway....

There are a lot of ways of solving this - one suggestion is to create an entity that holds a counter and you increment it and store it while creating the invoice.

If you do this in one transaction then your increment will only be saved if the invoice is also saved.

You'll probably need to reset it on 1st of Jan to 1 or something like that

Just be careful if more than one thread is incrementing - then you'll need to synchronise it

[sam creep]

Hi.
 Thanks for the info.. However this will make it blocking as a bottleneck any way to make it NIO ?
Also what if I want to use the column as in pt2 as a PK. Any generic way of doing it as I want to avoid using sequences / IDENTITY that makes it db specific. I need the values generated sequentially.

Thanks.

[Rob Bygrave]

I assume you need incrementing numbers without gaps.  If you don't mind gaps then I don't know why you are avoiding DB sequences/identity as they are built for purpose (Oracle sequences support clustering etc).

In the past when I needed incrementing numbers without gaps (accounting cheque numbers) then these were generated in advance via a background process.  So a block of cheque numbers were generated (rows inserted) as a single transaction - this was done in advance of the cheques being needed/used/allocated. Individual cheques could be allocated on the fly etc.

If you really do need to not have gaps then I believe you are going to have some transactional bottleneck with that.

[sam creep]

Hi.
 I need sequential numbers without gaps, thanks for the idea, guess there is no generic way though. I shall try to see if I can pre-generate the same as I want to reduce runtime I/O and reduce bottlenecks.

[sam creep]

Hi,

 I have created a custom generator and am trying to implement it in my code. Please could you guide me on how to invoke it.

ValGen.java
package rtest.models;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import play.db.ebean.Model;

@Entity()
@Table(name="ValGen")
public class ValGen extends Model
{
    @Id
    public int pid;
    public String pname;
    public String pphone;
    public String pphoto;
}

IdGenerator.java
package rtest.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.avaje.ebean.Ebean;

public class IdGenerator
{

    public synchronized int generate()
    {
        Connection conn = Ebean.currentTransaction().getConnection();
        try
        {
            PreparedStatement ps = conn.prepareStatement("SELECT nextval ('s_valgen') as nextval");

            ResultSet rs = ps.executeQuery();
            if (rs.next())
            {
                int id = rs.getInt("nextval");
                return id;
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

        return -1;
    }
}

Thanks

[sam creep]

Hi.
 Please could somebody update.

[sam creep]

Please can somebody share the code to implement an a custom generator.