Java Robski

Extract primary key from hibernate entity

2012-01-10T10:40:00.001-05:00

Here is a quick one, I will show you how to extract the primary key and the value from a hibernate entity.

It took me a while to look at the API and see what I need to use, but after swimming in the API it was a couple of lines that got it all done.

First thing that you need to do is load the entity


Parent parent= parentDao.getEntityById(1L);

Once you load the entity call the following methods (code for the methods will follow)


System.out.println("print primary key: " +  HibUtil.extractPrimaryColumn(parent.getClass()) );
System.out.println("print primary key value: " +  HibUtil.extractPrimaryKeyValue(parent,Parent.class) );

Extract primary column name
Here is the code for the first method -> extractPrimaryColumn

Nothing hard about this. First, we load the PersistentClass based on our entity. If all the hibernate mappings are set up correctly there shouldn't be any problems.

Once we have the PC we then call this method to get the name of the primary key -> getIdentifierProperty().getName();


    public static String extractPrimaryColumn(Object entity)
    {
        PersistentClass localPC = extractPersistentClass(entity);

        //Get pk column and value
        String name = null;
        try
        {
            name = localPC.getIdentifierProperty().getName();
        }
        catch (PropertyNotFoundException pnfe)
        {
            logger.info("Hib util, property not found",pnfe);
        }
        catch (MappingException me)
        {
            logger.info("mapping not found",me);
        }
        catch (HibernateException he)
        {
            logger.info("hibernate exception........but why??",he);
        }
        catch (NullPointerException npe)
        {
            logger.info("we have a problem getting a value for this entity",npe);
        }

        return name;
    }

Extract the value
Second method uses almost the same logic -> extractPrimaryKeyValue

After we get the PC we call the following methiod to get the value of the key -> pkValue = (Long) localPC.getIdentifierProperty().getGetter(clazz).get(entity);


public static Long extractPrimaryKeyValue(Object entity, Class clazz)
    {
        PersistentClass localPC = extractPersistentClass(entity);

        //Get pk column and value
        Long pkValue = null;
        try
        {
            pkValue = (Long) localPC.getIdentifierProperty().getGetter(clazz).get(entity);
        }
        catch (PropertyNotFoundException pnfe)
        {
            logger.info("Hib util, property not found",pnfe);
        }
        catch (MappingException me)
        {
            logger.info("mapping not found",me);
        }
        catch (HibernateException he)
        {
            logger.info("hibernate exception........but why??",he);
        }
        catch (NullPointerException npe)
        {
            logger.info("we have a problem getting a value for this entity",npe);
        }

        return pkValue;
    }

In my next post I will expend and show you how I use this code.

Cheers

Robert
Lead Architect at Translucent Computing

Restrictions.sqlRestriction - extract column name from property

2010-01-10T23:35:00.000-05:00

I been looking around to see if the hibernate API covers this...and I found nothing!!

So I had to create my own solution!!

Description

The application I am working on has a complex search where the user is able to search different entities and their properties. This can include dates, strings, and numbers. The search is like a Google search...using object and their properties.

Problem

In the API that I created I had to use Restrictions.sqlRestriction as I couldn't do certain things with the Criteria API. The problem that I found is that when dealing with criteria we are using properties to apply different restrictions...but in the sqlRestriction we have to use DB column name. So now I had a problem...how do I apply object properties to sqlRestriction.

Solution

I created my own Restriction class that uses the SQL and property Name.
I re used some of the code from different part of hibernate API.
So now you can use property name as so {properyName} in your sql (Very simlar how you use the {alias}...not you don't have to use the alias as the property name will get it for you.

public class CustomRestriction
{
public static Criterion sqlRestriction(String sql, String propertyName)
{
    return new CustomSQLCriterion(sql, propertyName, ArrayHelper.EMPTY_OBJECT_ARRAY, ArrayHelper.EMPTY_TYPE_ARRAY);
}

}

public class CustomSQLCriterion implements Criterion
{
private final String sql;
String propertyName;
private final TypedValue[] typedValues;

public String toSqlString(
        Criteria criteria,
        CriteriaQuery criteriaQuery)
        throws HibernateException
{
    //extract alis and colum name
    String[] columns = criteriaQuery.getColumnsUsingProjection(criteria, propertyName);
    StringBuffer fragment = new StringBuffer();

    //if we have multiple columns....which i will not
    if (columns.length > 1)
    {
        fragment.append('(');
    }

    //get session factory
    SessionFactoryImplementor factory = criteriaQuery.getFactory();

    //get column name
    for (int i = 0; i < columns.length; i++)         {             fragment.append(columns[i]);         }          if (columns.length > 1)
    {
        fragment.append(')');
    }

    // repleace propery with column name
    return StringHelper.replace(sql, "{propertyName}", fragment.toString());
}

public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery)
        throws HibernateException
{
    return typedValues;
}

public String toString()
{
    return sql;
}

protected CustomSQLCriterion(String sql, String propertyName, Object[] values, Type[] types)
{
    this.sql = sql;
    this.propertyName = propertyName;

    typedValues = new TypedValue[values.length];
    for (int i = 0; i < typedValues.length; i++)
    {
        typedValues[i] = new TypedValue(types[i], values[i], EntityMode.POJO);
      }
     }
    }

table structure

lets say this is your table structure

Table Test
 first_name
 last_name

Class Test
{
 String fristName;
 Sting lastName;
}

Usage-

CustomRestriction.sqlRestriction("{propertyName} = " + "'test'", "firstName");

Output - which will be used to construct your query

first_name = 'test'

SQL server -> Execution plan, parameter sniffing and query hints

2009-10-17T12:43:00.000-04:00

Background info………

Recently one of our stored procedures had performance problems. The issue was complicated because performance was inconsistent on the same dataset using the same procedure parameters. After some digging it came apparent that this was due to how the SQL server prepares the execution plan for each query inside the stored proc. I could run the same procedure on the same dataset and get a difference of 40 seconds on execution.

Investigation……

When I hooked up the SQL server profiler I was able to extract the execution plan for the query in the store procedure that was causing the problem. I was able to find that depending of the execution sequence this query would have 2 different exestuation plans.

Sequence 1: I recompile the stored proc and run it on small dataset first then on a bigger dataset, the proc would be really slow on the bigger dataset.

Sequence 2: I recompile the stored proc and run it on the bigger dataset first (same data, same parameters) the sorted proc runs fast (40 seconds difference)

Something to keep in mind that the stored proc only ran slow in a jasper report, if I re run the proc in the SQL server it would be fast. So when testing, be sure to test it inside a report.

Execution plan……

As you know the DB will try to create an execution plan for query(non trivial) that is executed, unless there is one that already exists in the cache, in that case it will re use an execution plan. SQL server uses cost-based technique to determine the processing strategy. The query will use metadata of the database objects and current statistics to decide with indexes and what join strategies to use.

The overall time required to execute a query is the sum of the time required to generate the execution plan and the execution of the query.

The cost incurred in the generation of the execution plan depends on the process of generating the execution plan, the process of caching the plan and the reusability of the plan.

When it comes to execution plans for procedures they are no generated when you create the procedure, it gets created and cached first time you run it.

…..there is much much more, but this should be enough to see the problem in our case

Parameter sniffing…..

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation.

The problem with parameter sniffing is that if you have procedures that return wide range of data the execution of the procedure will change………dramatically in our case.

If you run a procedure for the first time with small dataset, there will be execution plans created for queries in the procedure…. not necessarily efficient ones. So that if you re-run the same proc with bigger dataset the performance will suffer do to re-use of inefficient execution plan.

In some cases this can be fixed by creating local variable in the procedure and assigning the parameter variables to the local ones. This will confuse the query analysis and it will create an execution plan based on statistics. This might or might not be good. Depending on your situation, but it’s worth trying.

a) Sniffing enabled--- execution plan based on the parameter passed

CREATE PROCEDURE GetUser

@UserName nvarchar(20)

BEGIN

select * from user where username = @UserName

END

b) Sniffing disabled --- execution plan based on statistic due to local variable

CREATE PROCEDURE GetUser

@UserName nvarchar(20)

BEGIN

DECLARE @myUserName nvarchar(20)

SET @myUserName = @UserName

select * from user where username = @myUserName

END

Query hints………..option (recompile)

If the disabling of the parameter sniffing didn’t fix the problem, and the execution plan created is still slow, another option is to play around with the query hints. What I end up using is a hint that recompiles the specified query when it’s executed every time: option (recompile). This option is not placed on the whole procedure but only on specific query inside the procedure.

CREATE PROCEDURE GetUser

@UserName nvarchar(20)

BEGIN

select * from user where username = @UserName option (recompile)

END

You have to be careful when using this option as in some case the creation of the execution plan could be costly and provide bigger over head.

Conclusion……….

In the end I was able to use query hint recompile to fix my problem and get a good constant execution of the procedure. I don’t have to care about different data ranges as every time the procedure executes it creates a low cost execution plan for the troubled query.

Hibernate - DB audit Logging

2008-03-27T12:54:00.001-04:00

Hibernate -DB audit logging (How about this....................)
(Leave me a note if you have any comments improvements, corrections)

I looked around the web to see if there is any logging that can be done on the entities that are persisted using hibernate.

I found one interesting post on the hibernate site, one thing was missing:
The logging done in this article is based on entities names (the process will parse out the method name and the class name, and it will use that as the names of the columns and tables).

http://www.hibernate.org/318.html

I needed the logging to be more precise.
I needed the logger to match exact table name and the exact column name that the value was saved to. Yes you can do if you have your java entities names mapped to the DB tables and column names, as in the article above.

in the above example if you have Table called TEST_TABLE, the extracted value that will be saved using the audit logger will be TestTable(most likely that will be the name of the class that mapps to the table).
The same will be for the column: FIRST_NAME will be mapped to firstName.
I want to log the column name as it's represented in the DB
So if I have a Column called FIRST_NAME, i want to logged in the DB as FIRST_NAME = Robert.

SO ...Logging it the right way.........
here is what i did

1) DB structure

Action log DB structure, this where i will save all my changes.

Table: ACTION_LOG
Column: ACTION_LOG_ID - > primary key
Column: USER_ID -> number
Column: TABLE_NAME - > varchar
Column: PRIMARY_KEY - > varchar
Column: CONCAT_VALUES- > varchar (values that are saved)
Column: COMMAND -> varchar
Column: TIME_STAMP

Test Entity - This will be my test entity i will try to persist and audit

Table: TEST_TABLE
Column 1: TEST_TABLE_ID -> primary key
Column 2: FIRST_NAME-> varchar2
Column 3: POSITION - > varchar2

2) Hibernate Mappings (using annotations)

Hibernate mapping of ACTION_LOG

@Entity
public class ActionLog implements Serializable {
private static final long serialVersionUID = -9176091358716821364L;
private Long logId;
private Long userId;
private Timestamp timeStamp;/
private String tableName;
private String pkColumn;
private String command;
private String concatValues;

Hibernate mapping for TEST_TABLE

@Entity
public class TestTable
private String firstName;
private String position;
private Long testTableId;

3) Create logger DAO and Service
I use layered architecture in my application, so I create a DAO and a service for the ActionLog class which i will use in my logger when i persist my data.

You will notice that my actionLog DAO is empty. All my basic methods for saving/updating/deleting are located in my base class that the DAO is extanding.

public interface ActionLogService {

void createActionLog(ActionLog actionLog);
}

public class AuditLogServiceImpl implements ActionLogService{

@Autowired
private AuditLogDao auditLogDao;

public AuditLogServiceImpl () {
}

public void createActionLog (ActionLog actionLog) {
auditLogDao.add (actionLog);
}

}

public interface ActionLogDao {}

@Repository("auditLogDAO")
public class AuditLogDAOImpl extends BaseDAOHibImpl implements AuditLogDao {}

4) Create HibernateInterceptor

We will use the hibernate interceptor to log the changes.
As you can see we log on :
onFlushDirty
onSave
onDelete

Every time you try to perform a hibernate update, save or delete one of the methods will run and checks if an audit needs to be done

Couple things to notice that i have here
1)user_id, currently it's set to -1. I save this value in my db logger.
In my code implementation I inject a session bean that holds the user id
2)I use annotations to register my bean

@Service("hibernateInterceptor")
public class HibernateInterceptor extends EmptyInterceptor implements ApplicationContextAware{

private static final long serialVersionUID = -3073850856829239762L;

@Autowired
@Qualifier("commonsActionLogger")
private ActionLogger theLogger;

private ApplicationContext context;

/**
* Creates a new instance of HibernateLoggingInterceptor
*/
public HibernateInterceptor () {
}

public boolean onLoad (Object entity, Serializable id, Object[] state, String[] propertyNames, Type[] types) throws CallbackException {
return false;
}

public boolean onFlushDirty(Object entity, Serializable id, Object[] newValues, Object[] oldValues, String[] propertyNames, Type[] types) throws CallbackException {
if (!(entity instanceof LogStopper)){
theLogger.logThis ("Update",entity,propertyNames,getUserId());
}

return false;
}

private Long getUserId() {
return -1L;
}

public boolean onSave (Object entity, Serializable id, Object[] state, String[] propertyNames, Type[] types) throws CallbackException {
if (!(entity instanceof LogStopper)){
theLogger.logThis ("Create",entity,propertyNames,-getUserId());
}
return false;
}

public void onDelete (Object entity, Serializable id, Object[] state, String[] propertyNames, Type[] types) throws CallbackException {
if (!(entity instanceof LogStopper)){
theLogger.logThis ("Delete",entity,propertyNames,getUserId());
}
}

public void setApplicationContext(ApplicationContext arg0)
throws BeansException {
this.context=arg0;

}
}

5)Register the intercept

Register the following property in your sessionFactory

property name="entityInterceptor" ref="hibernateInterceptor"

6) Create THE LOGGER
Here is the fun stuff, here is where we hack in to the session factory and extract data so we can parse out the DB info so we can save the data

I have 2 classes and 1 interface here
AtionLoggecr interface
DatabaseActionLoger -> this is where i save the audited entity to the DB
ParseHibEntity - This is a util class where i hack in to the session factory and get all the info i need to save the audited entity ( I get the table info and the value that is persisted)

public interface ActionLogger {

void logThis (String command, Object entity, String[] properties,Long userId) ;

}

public class DatabaseActionLogger implements ActionLogger{

@Autowired
private ActionLogService actionLogService;

public void logThis(String command, Object entity, String[] properties,Long userId) {

if (!(entity instanceof ActionLog)){

//new actionlog
ActionLog actionLog = new ActionLog();

//prse object, get all the methods
ParseHibEntity phe = new ParseHibEntity(entity,properties);

//set command and table name
actionLog.setCommand(command);
actionLog.setTableName(phe.extractTableName());

//primary key column
actionLog.setPkColumn(phe.extractPrimaryKeyColumn());

//save all contact values
actionLog.setConcatValues(phe.extractColumnValues());

//set time stemp
actionLog.setTimeStamp(new Timestamp(new Date().getTime()));
actionLog.setUserId(userId);

//save the actionlog
actionLogService.createActionLog(actionLog);

}

}
}

public class ParseHibEntity {
private Logger log = Logger.getLogger(this.getClass());
private PersistentClass pc = null;
private Object entity;
private String[] properties;

public static final String DATE_FORMAT = "yyyy-MM-dd";
public static final String DATE_TIME_FORMAT = "yyyy-MM-dd hh:mm:ss a";
/**
* Creates a new instance of ParseHibEntity
*/
public ParseHibEntity(Object entity, String[] properties) {
this.pc = extractPersistentClass(entity.getClass().getName());
this.entity = entity;
this.properties = properties;
}

private PersistentClass extractPersistentClass(String entityName){
PersistentClass pc = null;
LocalSessionFactoryBean sessionFactory = null;
try {
sessionFactory = (LocalSessionFactoryBean)AppCtxHolder.getApplicationContext().getBean("&mySessionFactory");
pc = ((LocalSessionFactoryBean)sessionFactory).getConfiguration().getClassMapping(entityName);
}catch(NullPointerException npe){
log.error("NullPointerException - make sure you get the right session factory " + npe.getMessage(),npe);
}
return pc;
}

public String extractPrimaryKeyValue(){
//Get pk column and value
String pkValue = null;
try{
pkValue = pc.getIdentifierProperty().getGetter(entity.getClass()).get(entity).toString();
}catch(PropertyNotFoundException pnfe){
log.info("PropertyNotFoundException " + pnfe.getMessage());
}catch(MappingException me){
log.info("MappingException " + me.getMessage());
}catch(HibernateException he){
log.info("HibernateException " + he.getMessage());
}catch(NullPointerException npe){
log.info("NullPointerException " + npe.getMessage());
}

return pkValue;
}

public String extractPrimaryKeyColumn(){
String pkColumn = null;
try{
pkColumn = ((Column)pc.getIdentifierProperty().getColumnIterator().next()).getName();
}catch(NullPointerException npe){
log.info("NullPointerException " + npe.getMessage());
}

return pkColumn;
}

public String extractPrimaryKeyColumn(PersistentClass localPC){
String pkColumn = null;
try{
pkColumn = ((Column)localPC.getIdentifierProperty().getColumnIterator().next()).getName();
}catch(NullPointerException npe){
log.info("NullPointerException " + npe.getMessage());
}

return pkColumn;
}

public String extractPrimaryKeyValue(PersistentClass localPC, Object localEntity){
//Get pk column and value
String pkValue = null;
try{
pkValue = localPC.getIdentifierProperty().getGetter(localEntity.getClass()).get(localEntity).toString();
}catch(PropertyNotFoundException pnfe){
log.error("PropertyNotFoundException " + pnfe.getMessage(),pnfe);
}catch(MappingException me){
log.error("MappingException " + me.getMessage(),me);
}catch(HibernateException he){
log.error("HibernateException " + he.getMessage(),he);
}catch(NullPointerException npe){
log.error("NullPointerException " + npe.getMessage(),npe);
}

return pkValue;
}

@SuppressWarnings("unchecked")
public String extractColumnValues(){
StringBuffer sb = new StringBuffer();

//add the pk column and value
sb.append(extractPrimaryKeyColumn() + " = " + extractPrimaryKeyValue() + " | ");
//System.out.println("pc.getProperty (scale) " + pc.getProperty ("scale"));

//loop through all the other properties and get what you need
for (String p: properties){
Property pr = pc.getProperty(p);

//make sure that this is not a collection and not a one to one as these values are not part of the table
if (!pr.getType().isCollectionType() && !(pr.getType() instanceof OneToOneType)){

//make sure that the values are persistent values and not a forumla value
if (pr.isInsertable() || pr.isUpdateable()) {
int scale = 2;

//get the getter for the entity
Getter getter = pr.getGetter(entity.getClass());

//get column value
Object columnValue = getter.get(entity);

//get column name
for (Iterator it3 = pr.getColumnIterator(); it3.hasNext(); ) {
Column column = (Column)it3.next();
sb.append(column.getName());
scale = column.getScale();
}

sb.append(" = ");

//check what kind of type of value this is, it if it an association then get the forign key value from the associated entity
if (columnValue != null){
if (!pr.getType().isAssociationType()){

//if bigD set Scale
if (columnValue instanceof BigDecimal){
columnValue = ((BigDecimal)columnValue).setScale(scale,BigDecimal.ROUND_HALF_DOWN);
}else if (columnValue instanceof java.util.Date){
SimpleDateFormat sdf = null;
if(columnValue instanceof java.sql.Timestamp){
sdf = new SimpleDateFormat(DATE_TIME_FORMAT);
}else{
sdf = new SimpleDateFormat(DATE_FORMAT);
}
columnValue = sdf.format(columnValue);
} else if (pr.getType().getName().equalsIgnoreCase("org.springframework.orm.hibernate3.support.ClobStringType")){
columnValue = "Clob Value";
}

sb.append(columnValue);
}else{
//since it's an association we know that column value is an object
String associatedEntityName = pr.getType().getName();
//associatedEntityName = ((EntityType)pr.getType()).getAssociatedEntityName ();
PersistentClass localPC = extractPersistentClass(associatedEntityName);
String fkValue = extractPrimaryKeyValue(localPC,columnValue);
sb.append(fkValue);
}
}

sb.append(" | ");
}
}
}
return sb.toString();
}

public String extractTableName(){
try {
return pc.getTable().getName();
}catch(NullPointerException npe){
log.error("NullPointerException - table is null " + npe.getMessage(),npe);
return "";
}
}

}

7)Using IT
No all you have to do is persit you data, so if you do the following

TestDAO.save(TestEntity):
the logger will save save the following if no in the ACTION_LOG

LOG_ID = 1
USER_ID = -1
TABLE_NAME = TEST TABLE
PRIMARY_KEY = 1
CONCAT_VALUES = FIRST_NAME = JavaRobski | POSITIONS = Architect
COMMAND = SAVE
TIME_STAMP = 2008-09-01