Techo

I often face problem with databases where there are no sequences in the database, obvious solution is to use a table to keep the counts of the sequences by selecting the current sequence number and than updating the count. Hibernate also provides a solution which is as follows

For Example I will use a MessageStatus class which contains id which need to be incremented with every insert

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import org.hibernate.annotations.Parameter;
import org.hibernate.annotations.Type;

@Entity
@Table(name=”MESSAGESTATUS”,schema=”INTEGRATION”)
public class MessageStatus {
private long id;
public MessageStatus()
{
super();
}

@Column(name=”ID”)
@GeneratedValue(generator=”status”)
@GenericGenerator(name=”status”,strategy=”MessageStatusIdentifierGenerator”,
parameters={@Parameter(name=”name”,value=”MESSAGESTATUSID_GEN”),@Parameter(name=”table”,value=”SEQUENCE”),
@Parameter(name=”schema”,value=”MOIINTEGRATION”),@Parameter(name=”pkColumnName”,value=”SEQ_NAME”),
@Parameter(name=”valueColumnName”,value=”SEQ_COUNT”),@Parameter(name=”pkColumnValue”,value=”SEQ_MESSAGE”),
@Parameter(name=”initialValue”,value=”1″),@Parameter(name=”allocationSize”,value=”1″)})
@Column(name=”ID”)
public long getId()
{
return id;
}

public void setId(long id)
{
this.id = id;
}

}
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.hibernate.HibernateException;
import org.hibernate.MappingException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.SessionImplementor;
import org.hibernate.id.Configurable;
import org.hibernate.id.IdentifierGenerator;
import org.hibernate.id.IdentifierGeneratorFactory;
import org.hibernate.type.Type;

import com.datel.fawriintegration.common.messaging.model.message.api.MessageStatus;

public class MessageStatusIdentifierGenerator implements IdentifierGenerator, Configurable
{

private IdentifierGenerator identifierGenerator;

private Type identifierType;

private String tableName;
private String sequenceName;

private String schema;

private String pkColumnName;

private String valueColumnName;

private String pkColumnValue;

private int initialValue;

private int allocationSize;

private String selectString;

private String updateString;

public Serializable generate(SessionImplementor session, Object entity) throws HibernateException
{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Statement st = null;
if (entity instanceof MessageStatus)
{

MessageStatus es = (MessageStatus) entity;
if (es.getId() == 0)
{
if (identifierGenerator != null)
{
return identifierGenerator.generate(session, entity);
} else
{
conn = session.connection();
try
{
boolean autoCommit = conn.getAutoCommit();
conn.setAutoCommit(true);
ps = conn.prepareStatement(selectString);
rs = ps.executeQuery();
Long sequenceNumber = null;
if (rs.next())
{
sequenceNumber = rs.getLong(valueColumnName);
Long nextNumber = sequenceNumber + allocationSize;
String tempUpdateString = updateString + nextNumber + ” where ” + pkColumnName + ” = ‘”
+ pkColumnValue + “‘”;
st = conn.createStatement();
st.executeUpdate(tempUpdateString);
// session.executeUpdate(updateString, null);
} else
{
throw new HibernateException(“The database returned no generated identity value”);
}
conn.setAutoCommit(autoCommit);
st.close();
rs.close();
ps.close();
conn.close();
return sequenceNumber;
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
try
{
if (st != null)
{
st.close();
}
if (rs != null)
{
rs.close();
}
if (ps != null)
{
ps.close();
}
if (conn != null)
{
conn.close();
}
} catch (Exception ex)
{
ex.printStackTrace();
}

}
}
} else
{
return es.getId();
}
}
return null;
}

public void configure(Type type, Properties params, Dialect d) throws MappingException
{
this.identifierType = type;
if (d.supportsSequences())
{
sequenceName = params.getProperty(“sequencename”);
identifierGenerator = IdentifierGeneratorFactory.create(“sequence”, type, params, d);
} else
{
tableName = params.getProperty(“table”);
schema = params.getProperty(“schema”);
pkColumnName = params.getProperty(“pkColumnName”);
valueColumnName = params.getProperty(“valueColumnName”);
pkColumnValue = params.getProperty(“pkColumnValue”);
initialValue = Integer.parseInt(params.getProperty(“initialValue”));
allocationSize = Integer.parseInt(params.getProperty(“allocationSize”));
selectString = “SELECT ” + valueColumnName + ” from ” + schema + “.” + tableName + ” where ” + pkColumnName
+ ” = ‘” + pkColumnValue + “‘”;
selectString = d.transformSelectString(selectString);
updateString = “UPDATE ” + schema + “.” + tableName + ” SET ” + valueColumnName + ” = “;
}
}
}
Sorry for providing no comments, I guess the codes a self explanatory )