Using select where query with hibernate

Question

I have a database and java application using hibernate functionalities to query database. There is a class called NCS_User with parameters mapped to database in a xml file correctly. I am using the following code to extract data from the database which has been populated externally.

package testhibernate;

public class NCS_User {

    private String PERSONIDNO;
    private String FULLNAME;
    private int ROLEID;
    private String ROLENAME;
    private int SCHEMEID;
    private String SCHEMETITLE;

    /**
        * creates the system default users [only used by spring]
        * 
        * @param PERSONIDNO
        * @param FULLNAME
        * @param ROLEID
        * @param ROLENAME
        * @param SCHEMEID
        * @param SCHEMETITLE
        */
    private NCS_User(final int ROLEID) {
            this.ROLEID = ROLEID;
    }

    public String getFULLNAME() {
            return FULLNAME;
    }

    public int getROLEID() {
            return ROLEID;
    }

    public String getROLENAME() {
            return ROLENAME;
    }

    public int getSCHEMEID() {
            return SCHEMEID;
    }

    public String getSCHEMETITLE() {
            return SCHEMETITLE;
    }

    public String getPERSONIDNO() {
            return PERSONIDNO;
    }

    public void setFULLNAME(final String FULLNAME) {
            this.FULLNAME = FULLNAME;
    }

    public void setROLEID(final int ROLEID) {
            this.ROLEID = ROLEID;
    }

    public void setROLENAME(final String ROLENAME) {
            this.ROLENAME = ROLENAME;
    }

    public void setSCHEMEID(final int SCHEMEID) {
            this.SCHEMEID = SCHEMEID;
    }

    public void setSCHEMETITLE(final String SCHEMETITLE) {
            this.SCHEMETITLE = SCHEMETITLE;
    }

    public void setPERSONIDNO(final String PERSONIDNO){
            this.PERSONIDNO = PERSONIDNO;
    }
}



package testhibernate;

import org.hibernate.Session;
import org.hibernate.*;
import org.hibernate.cfg.*;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

import java.util.*;
/**
 *
 * @author Administrator
 */
public class TestHibernate {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        System.out.println("Entering main loop");
        Session session = null;
        int qroleId=0;
        int qschemeId=0;
        String trueLogin="S1234567D";
        try{
            System.out.println("Establishing connection");
            // This step will read hibernate.cfg.xml and prepare hibernate for use
            SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
            session =sessionFactory.openSession();
            String SQL_QUERY ="Select ncs_user.PERSONIDNO, ncs_user.ROLEID, ncs_user.SCHEMEID from NCS_User ncs_user where ncs_user.PERSONIDNO="+trueLogin;
            Query query = session.createQuery(SQL_QUERY);
            System.out.println("Query created");

            for(Iterator it=query.iterate();it.hasNext();){
                Object[] row = (Object[]) it.next();
                qroleId = new Integer(row[1].toString());
                System.out.println("Role Id: " + qroleId);
                qschemeId = new Integer(row[2].toString());
                System.out.println("Scheme Id: " + qschemeId);
            }
        }
        catch(Exception e){
            System.out.println(e.getMessage());
        }
        finally{
        }
    }
}

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>

  <session-factory>

    <property name="dialect">org.hibernate.dialect.Oracle12cDialect</property>
    <property name="connection.driver_class">oracle.jdbc.OracleDriver</property>
    <property name="connection.url">jdbc:oracle:thin:@localhost:1521:orcl</property>
    <property name="connection.username">system</property>
    <property name="connection.password">******</property>

    <property name="cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>

    <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
    <property name="current_session_context_class">thread</property>
    <property name="hibernate.show_sql">true</property>

    <!-- Mapping files -->
    <mapping resource="NCS_User.hbm.xml"/>
  </session-factory>

</hibernate-configuration>



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

<hibernate-mapping>
  <class name="TestHibernate.NCS_User" table="NCS_User">
   <id name="ROLEID" type="long" column="ROLEID" >
   <generator class="assigned"/>
  </id>

  <property name="FULLNAME">
   <column name="FULLNAME" />
  </property>
  <property name="PERSONIDNO">
  <column name="PERSONIDNO"/>
  </property>
  <property name="ROLENAME">
  <column name="ROLENAME"/>
  </property>
  <property name="SCHEMEID">
  <column name="SCHEMEID"/>
  </property>
  <property name="SCHEMETITLE">
  <column name="SCHEMETITLE"/>
  </property>
 </class>
</hibernate-mapping>

However, when I try to print the qroleID and qschemeId, I don't get any values. In command prompt, I get an error "Could not parse mapping document from resource NCS_User.hbm.xml" This is after "establishing connection" gets printed out. Could someone please help me understand what I'm doing wrong over here? Thank You.

I think you should use SQLQuery rather than Query as your are firing native SQL query not Hibernate query. — Pramod Kumar
– Pramod Kumar, Commented Jul 11, 2012 at 5:38
Does the query work with list() instead using iterate()? If i remember correctly, they once had some issues there. Whats the hibernate version you are using? — Ortwin Angermeier
– Ortwin Angermeier, Commented Jul 11, 2012 at 6:18
I was using row[0] to store PERSONIDNO initially but not printing it anymore. With SQLQuery also I don't get any result. I am using hibernate version 3.3.2 — user1439090
– user1439090, Commented Jul 11, 2012 at 6:39

Mikko Maunu · Accepted Answer · 2012-07-16 18:37:01Z

1

Package names (also as a part of name attribute value) are case sensitive.

You have mismatch between:

<class name="TestHibernate.NCS_User" table="NCS_User">

and

package testhibernate;
...
public class NCS_User

answered Jul 16, 2012 at 18:37

Mikko Maunu

42.3k11 gold badges143 silver badges142 bronze badges

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

Comments

testing · Accepted Answer · 2012-07-11 10:51:39Z

0

use createSQLQuery insted of createQuery

String SQL_QUERY = "Select ncs_user.PERSONIDNO as pId, ncs_user.ROLEID as rId,    ncs_user.SCHEMEID as sId from NCS_User ncs_user where ncs_user.PERSONIDNO="+trueLogin";
SQLQuery query = session.createSQLQuery(SQL_QUERY);

 query.addScalar("pId", Hibernate.LONG)
 query.addScalar("rId", Hibernate.LONG)
 query.addScalar("sId", Hibernate.LONG)
 List<Object[]> result = query.list(); 
    for (Object[] objects : result) {
            long pId = ((Integer) objects[0]).longValue();
        long rId = ((Integer) objects[1]).longValue();
            long sId = ((Integer) objects[2]).longValue();
        }

may be this will help you. And i am not sure about this code.

edited Jul 11, 2012 at 10:51

answered Jul 11, 2012 at 10:42

testing

911 gold badge2 silver badges7 bronze badges

1 Comment

user1439090 Over a year ago

I tried this code but still the same result. I've edited the question to include my whole code.

Collectives™ on Stack Overflow

Using select where query with hibernate

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related