Archive for category Hibernate

How to display hibernate sql parameter values – P6Spy


1. Download P6Spy library

Get the “p6spy-install.jar“, you can download it from

  1. P6Spy official website.
  2. P6Spy at Sourceforge.net

2. Extract it

Extract the p6spy-install.jar file, look for p6spy.jar and spy.properties

3. Add library dependency

Add p6spy.jar into your project library dependency along with commons-dbcp-1.4.jar

4. Modify P6Spy properties file

Modify your database configuration file. You need to replace your existing JDBC driver with P6Spy JDBC driver – ” com.p6spy.engine.spy.P6SpyDriver<!– SPY SETUP–>
<bean id=”dataSource” destroy-method=”close”>
<constructor-arg>
<ref local=”myDataSourceTarget”/>
</constructor-arg>
</bean>

<bean id=”myDataSourceTarget” destroy-method=”close”>

<property name=”driverClassName”>
<value>com.p6spy.engine.spy.P6SpyDriver</value>
</property>

<property name=”url”>
<value>jdbc:oracle:thin:@hostname:port:servicename</value>
</property>

<property name=”username”>
<value>username</value>
</property>

<property name=”password”>
<value>pw</value>
</property>

</bean>”

5. Modify P6Spy properties file

Modify the P6Spy properties file – “spy.properties# useful for debugging sql

module.log=com.p6spy.engine.logging.P6LogFactory

executionthreshold=
outagedetection=false
outagedetectioninterval=
filter=false
include     =
exclude     =
sqlexpression =
autoflush   = true
dateformat=
includecategories=
excludecategories=info,debug,result,batch

stringmatcher=
stacktraceclass=

reloadproperties=false
reloadpropertiesinterval=60

useprefix=false

appender=com.p6spy.engine.logging.appender.FileLogger
logfile     = c:\\temp\\spy.log

append=false

log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender
log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout
log4j.appender.STDOUT.layout.ConversionPattern=p6spy – %m%n

log4j.logger.p6spy=DEBUG,STDOUT

realdriver=oracle.jdbc.driver.OracleDriver”

Change the Log file location
Change the log file location in logfile property, all SQL statements will log into this file.

Windows

logfile     = c:/spy.log

*nix

logfile     = /srv/log/spy.log

6. Copy “spy.properties” to project classpath

Copy “spy.properties” to your project root folder, make sure your project can locate “spy.properties”, else it will prompt “spy.properties” file not found exception.

7. Done

Run your application and do some database transaction, you will notice all the SQL statements sent from application to database will be logged into a file you specified in “spy.properties”.

Sample log file as following.

insert into stock_transaction (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME)
values (?, ?, ?, ?, ?, ?)|
insert into stock_transaction (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME)
values (10.0, 1.1, '2009-12-30', 1.2, 11, 1000000)

Conclusion

P6Spy is really useful in reducing the developers’ debugging time. As long as your project is using JDBC driver for connection , P6Spy can fit into it and log all SQL statements and parameter values for you.

2 Comments

Hibernate Criteria setResultTransformer


Problem: How to loop through a resultset with the column alias names defined in the native SQL, instead of accessing the resultset by column index in hibernate

Solution:

Sometimes, accessing the resultset by the names of the columns is much convenient than accessing the result set by index, say a row’s first column as get(0) and second column by get(1).

In this case, when using hibernate, calling setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).list(),  causes it to return a list of Maps.

For example,

Query query1 = session.createSQLQuery(query.toString()).setParameter(“PRODID”, prodid).setParameter(“PRODTYPE”, prodtype);

//Convert the Query object to a list of maps

List iter1 = query1.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).list();
Map<String,Object> map= new HashMap<String,Object>();
for (Object object : iter1)
{
map= (Map<String,Object>)object;

logger.debug(map.get(“PRODID”));

logger.debug(map.get(“PRODTYPE”));

logger.debug(map.get(“PRODEXP”));

logger.debug(map.get(“PRODCOST”));

}

1 Comment

java.lang.ClassNotFoundException: org.apache.commons.collections.map.LRUMap


Problem: Another issue, this time with JUnit 4 and Spring3, 
Hibernate application, where in i encountered the following  
issue that 
java.lang.ClassNotFoundException: 
org.apache.commons.collections.map.LRUMap 

Solution:  Add the following dependency to the pom.xml to resolve 
java.lang.ClassNotFoundException: 
org.apache.commons.collections.map.LRUMap 

<dependency> 
<groupId>commons-collections</groupId>
 <artifactId>commons-collections</artifactId>
 <version>3.1</version> </dependency> 

, ,

1 Comment

Hibernate Annotations Named native Query


Problem: How to use named native queries in Hibernate/JPA Annotated Entities

Solution:

In the entity class, you can use these below annotations. Continuing with the earlier post, regarding the oracle connect sql, if chosen to

use the sql from an entity will look like below

@Entity
@SqlResultSetMapping(name = "implicit", entities = @EntityResult(entityClass = pakagepath.Employee.class))
@NamedNativeQuery(name = "namednativeqry", query = "select * from customer start with manager_id = :mgrId connect by prior emp_id = manager_id D relationship_cd = ‘IS MANAGER OF’", resultSetMapping = "implicit")

From the code, this can be called using the 

session.getNamedQuery("nativenamedqry").setString("mgrId",334);

,

Leave a comment

Mapping List Object in Hibernate


Below self explanatory sample application helps to understand how to persist a list object using Hibernate as persistence layer and MySQL as database.

—————————————————————————————————

package project2;

import java.util.ArrayList;

import org.hibernate.Session;
import org.hibernate.Transaction;

import project2.persistence.HibernateUtil;
public class MappingListDemo {

public static void main(String[] args) {

Session session = HibernateUtil.getSessionFactory().openSession();
Transaction tx = session.beginTransaction();

try {
customers cust1 = new customers(“Dave”);

ArrayList list1 = new ArrayList();
list1.add(new custorders(“SOA Using Java Web Services”));
list1.add(new custorders(“Java Persistence with Hibernate”));
cust1.setOrders(list1);
session.save(cust1);

customers cust2 = new customers(“Jim”);
ArrayList list2 = new ArrayList();
list2.add(new custorders(“Java Unleashed”));
list2.add(new custorders(“Design Patterns”));
cust2.setOrders(list2);
session.save(cust2);

tx.commit();

session.flush();
session.close();
} catch (Exception he)

{
System.out.println(“Exception caught: ” + he);
}
}

}

————————————————————————————————–

————————————————————————————————–

package project2;
public class custorders {
String orderinfo;

int id;
public custorders() {
}
public custorders(String d) {
orderinfo = d;
}
public int getId() {
return id;
}

public void setId(int s) {
id = s;
}
public void setOrderinfo(String orderinfo) {
this.orderinfo = orderinfo;
}

public String getOrderinfo() {
return orderinfo;
}
}

————————————————————————————————–

—————————————————————————————————

package project2;
import java.util.List;

public class customers {
String name;
List orders;
int id;

public customers() {
}

public customers(String d) {
name = d;
}
public String getName() {
return name;
}

public void setName(String a) {
name = a;
}
public int getId() {
return id;
}

public void setId(int s) {
id = s;
}
public void setOrders(List orders) {
this.orders = orders;
}

public List getOrders() {
return orders;
}
}

————————————————————————————————–

—————————————————————————————————

<?xml version=”1.0″?>
<!DOCTYPE hibernate-mapping PUBLIC
“-//Hibernate/Hibernate Mapping DTD//EN”
http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd“>

<hibernate-mapping>

<class name=”project2.customers”   table=”customer”>
<id name=”id” >

<generator/>

</id>

<property name=”name” />

<list name=”orders” cascade=”all”>

<key column=”item_id” />
<list-index column=”position” />
<one-to-many />

</list>

</class>

</hibernate-mapping>

————————————————————————————————–

—————————————————————————————————

<?xml version=”1.0″?>
<!DOCTYPE hibernate-mapping PUBLIC
“-//Hibernate/Hibernate Mapping DTD//EN”
http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd“>

<hibernate-mapping>

<class name=”project2.custorders”   table=”custorder”>
<id name=”id”>

<generator/>

</id>

<property name=”orderinfo”  />

</class>

</hibernate-mapping>

————————————————————————————————–

—————————————————————————————————
<!DOCTYPE hibernate-configuration SYSTEM
http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd“>
<hibernate-configuration>
<session-factory>
<property name=”hibernate.connection.driver_class”>com.mysql.jdbc.Driver</property>
<property name=”hibernate.connection.url”>jdbc:mysql://localhost:3306/hibernatetest</property>
<property name=”hibernate.connection.username”>root</property>
<property name=”hibernate.connection.password”></property>
<property name=”hibernate.dialect”>org.hibernate.dialect.MySQLDialect</property>
<!– Use the C3P0 connection pool provider –>
<property name=”hibernate.c3p0.min_size”>5</property>
<property name=”hibernate.c3p0.max_size”>20</property>
<property name=”hibernate.c3p0.timeout”>300</property>
<property name=”hibernate.c3p0.max_statements”>50</property>
<property name=”hibernate.c3p0.idle_test_period”>3000</property>
<!– Show and print nice SQL on stdout –>
<property name=”show_sql”>true</property>
<property name=”format_sql”>true</property>
<!– List of XML mapping files –>

<mapping/>
<mapping/>

<mapping resource=”project2/custorders.hbm.xml”/>
<mapping resource=”project2/customers.hbm.xml”/>
</session-factory>
</hibernate-configuration>

—————————————————————————————————

MySQL query tables used in above examples…

create table custorder(id int not null primary key auto_increment,
orderinfo text,position int,item_id int);

create table customer
( id int not null primary key auto_increment, name VARCHAR(40));

,

Leave a comment