SQL Query to Hibernate ORM Query

Question

I am trying to convert my plain SQL statements into proper Hibernate ORM ones. I've read alot about it, but still can't figure it out completely just yet. I hope some of you can help me :)

Here's some of my classes that I think relevant for this task:

WarehouseProduct (my entity class):

package exercise.java.basics.storage;

public class WarehouseProduct {

private int    productID;
private String productName;
private int    productCount;

public WarehouseProduct( final String productName, final int productCount ) {

    this.productName = productName;
    this.productCount = productCount;
}

public WarehouseProduct() {

}

public int getProductID() {
    return this.productID;
}

public void setProductID( final int productID ) {
    this.productID = productID;
}

public String getProductName() {
    return this.productName;
}

public void setProductName( final String productName ) {
    this.productName = productName;
}

public int getProductCount() {
    return this.productCount;
}

public void setProductCount( final int productCount ) {
    this.productCount = productCount;
}

}

my DAO.

package exercise.java.basics.storage;


import javax.transaction.Transactional;

import exercise.java.basics.storage.ProductEnum.Product;


@Transactional
public interface WarehouseDAO {

public void initializeWarehouse();

public void storeProducts( final Product product, final int count );

public void removeProducts( final Product product, final int count );

public void updateStock();

}

storeProduct() method from my DAO implementation:

   public void storeProducts( final Product product, final int count ) {

    //Plain-SQL, works just fine
    Session session = getSessionFactory().getCurrentSession();

    SQLQuery storeProductQuery = session.createSQLQuery( "UPDATE WAREHOUSE SET   product_count = " + count //$NON-NLS-1$
            + " WHERE product_name = '" + product + "';" ); //$NON-NLS-1$ //$NON-NLS-2$

    storeProductQuery.executeUpdate();



    //Hibernate attempt, doesn't work just yet
    session.get( "WarehouseProduct.class", "product_count" ); //$NON-NLS-1$ //$NON-NLS-2$

    Criteria createCriteria = session.createCriteria( WarehouseProduct.class ); // Object.class = Entity

    createCriteria.add( Property.forName( "product_name" ).like( product ) );

    createCriteria.list();

}

When testing I don't use both (plain sql / hibernate) attempts at once of course.

I think that I am like halfway there already, but still couldn't figure out the complete hibernate approach and that's where I hope you can help me.

Basically all I need is a transformation of the plain sql string in the storeProduct() method to proper hibernate commands.

Would greatly appreciate any help you can give me.

best regards daZza

EDIT: Here's the mapping:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="exercise.java.basics.storage.WarehouseProduct" table="WAREHOUSE">
    <id name="productID" type="integer">
        <column name="product_ID"  not-null="true"/>
        <generator class="identity" />
    </id>
    <property name="productName" type="string">
        <column name="product_name" length="100"/>
    </property>
    <property name="productCount" type="integer">
        <column name="product_count"/>
    </property>
</class>
</hibernate-mapping>

As to the problem, it's in the incomplete code imho. I am pretty sure that my hibernate commands are still missing something and/or are simply wrong.

This is the source SQL string I want to translate to hibernate: "UPDATE WAREHOUSE SET product_count = " + count + " WHERE product_name = '" + product + "';"

Could you share your Hibernate Entity mapping, in xml probably, because I see no JPA/Hibernate Annotations in your Entity class. And also the error/issue you have now. — K.C.
– K.C., Commented Feb 16, 2014 at 10:05

user3315806 · Accepted Answer · 2014-02-16 12:34:34Z

2

You can use the Restrictions class to get your result. use the following:

createCriteria.add(Restrictions.like("productName", product.getProductName()));

Note that I used the field name in your Product object rather than the column name in the database.Your way may also work by changing

createCriteria.add( Property.forName( "product_name" ).like( product ) );

to

createCriteria.add( Property.forName( "product_name" ).like( product.getProductName() ) );

I noticed that you were are matching "product_name" with "product" which is the entire object and not the field you are matching against.

answered Feb 16, 2014 at 12:34

user3315806

213 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

daZza Over a year ago

Thanks. And I can also use this restriction class to make an update to the table? That's the most confusing thing for me about hibernate... There no select/update/delete/... function to be found :D

user3315806 Over a year ago

For updates, I would recommend using session.createQuery for HQL queries or session.createSQLQuery for native SQL. You can add parameters to the query by using semi-colon, eg. where product_name = :productName (for native SQL). You can then set the values of the parameters using query.setParameter("procuctName", product.getProductName()) or use specific datatypes such as query.setLong(...) See the docs for Native SQL and HQL queries.

daZza · Accepted Answer · 2014-02-17 13:20:39Z

I read even more examples and documentations and came up with the following, which I think is working now. At least there are no error messages and the hibernate logger messages look good.

Unfortunately I can't check if the data within the database is correct, as I didn't figure out how to convert hibernate query results (objects) to readable stuff like a string.

I also changed my primary key in the hibernate mapping file to be the productName instead of an auto incremented value. The reason behind that was mainly hibernates get() function, as it seems to need the PK als the second attribute. The auto generated key was kinda random so I probably would have required another select before the update to identify the PK of the row with the corresponding product. As I don't know how to convert the results of a hibernate select query that wouldn't have worked.

So here's my new class:

 public void storeProducts( final Product product, final int count ) {

    Session session = getSessionFactory().getCurrentSession();

    WarehouseProduct productToStore = (WarehouseProduct) session.get(  WarehouseProduct.class,
            String.valueOf( product ) );
    productToStore.setProductCount( productToStore.getProductCount() + count );
    session.update( productToStore );

}

Collectives™ on Stack Overflow

SQL Query to Hibernate ORM Query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related