Native SQL createSQLquery Basic Operation in hibernate

Click here to download eclipse supported ZIP file




 
<?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="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property> 
		hibernate word is optional <property name="connection.url">jdbc:oracle:thin:@localhost:1521:ORCL</property> 
		<property name="hibernate.connection.username">kcv</property> <property name="hibernate.connection.password">kcv</property> 
		<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property> 
		<property name="show_sql">true</property> <mapping resource="Employee.hbm.xml"/> 
		</session-factory> -->
   <session-factory>
      <property name="hibernate.current_session_context_class">thread</property>
      <property name="hbm2ddl.auto">create</property>
      <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
      <property name="connection.url">jdbc:postgresql://localhost:5432/hibernate</property>
      <property name="connection.username">postgres</property>
      <property name="connection.password">password</property>
      <property name="connection.driver_class">org.postgresql.Driver</property>
      <property name="show_sql">true</property>
      <property name="format_sql">true</property>
      <property name="use_sql_comments">true</property>
      		<mapping resource="EmployeeBean.hbm.xml" />
   </session-factory>
</hibernate-configuration>


 
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.cv.hibernate.nativesql.model.EmployeeBean"
table="EmployeeBean_Native_SQL">
<id name="id" type="long">
<column name="EID" precision="10" scale="0" />
</id>
<property name="firstName" type="string">
<column name="FIRSTNAME" length="29" />
</property>
<property name="lastName" type="string">
<column name="LASTNAME" length="29" />
</property>
<property name="email" type="string">
<column name="EMAIL" length="39" />
</property>
</class>

</hibernate-mapping>


 

    
package com.cv.hibernate.nativesql.model;

/**
 @author Chandra Vardhan
 */
public class EmployeeBean {
  private long id;
  private String firstName;
  private String lastName;
  private String email;

  public long getId() {
    return id;
  }

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

  public String getFirstName() {
    return firstName;
  }

  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }

  public String getLastName() {
    return lastName;
  }

  public void setLastName(String lastName) {
    this.lastName = lastName;
  }

  public String getEmail() {
    return email;
  }

  public void setEmail(String email) {
    this.email = email;
  }

}


 

    
package com.cv.hibernate.nativesql;

import java.util.Iterator;

import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.type.FloatType;
import org.hibernate.type.IntegerType;
import org.hibernate.type.LongType;
import org.hibernate.type.ShortType;

import com.cv.hibernate.nativesql.util.HibernateUtil;

/**
 @author Chandra Vardhan
 */
public class NativeSQLIterateNotSupport {
  private final static Logger logger = Logger
      .getLogger(NativeSQLIterateNotSupport.class);

  public static void main(String args[]) {
    // Am creating the records by this statement.
    HibernateUtil.saveOrUpdate();
    Session session = HibernateUtil.getSession();
    try {
      SQLQuery q1 = session
          .createSQLQuery("select count(*) as cnt,max(eid) as max,min(eid) as min,avg(eid) as avg from EmployeeBean_Native_SQL where eid>=:p1 and eid<=:p2");

      q1.setInteger("p1"101);
      q1.setInteger("p2"110);

      q1.addScalar("cnt", IntegerType.INSTANCE);
      q1.addScalar("max", LongType.INSTANCE);
      q1.addScalar("min", ShortType.INSTANCE);
      q1.addScalar("avg", FloatType.INSTANCE);
      // Exception in thread "main"
      // java.lang.UnsupportedOperationException:
      // SQL queries do not currently support iteration
      // This operation is not supported by hibernate native SQL
      Iterator itr = q1.iterate();
      while (itr.hasNext()) {
        Object element = itr.next();
        System.out.print(element + " ");
      }
    catch (UnsupportedOperationException uoe) {
      logger.info("-------------------------------ERROR-------------------------------");
      System.err
          .println("Hibernate Native SQL queries do not currently support iteration");
    catch (Exception e) {
      System.err.println(e);
    }

    HibernateUtil.closeSession();
  }
}


 

    
package com.cv.hibernate.nativesql;

import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import com.cv.hibernate.nativesql.util.HibernateUtil;

public class NativeSQLQueryInsert {

  /**
   @param args
   */
  private final static Logger logger = Logger.getLogger(NativeSQLQueryInsert.class);

  public static void main(String[] args) {

    Session session = HibernateUtil.getSession();

    SQLQuery query = session.createSQLQuery("insert into EmployeeBean_Native_SQL values(?,?,?,?)");
    int count = 0;
    for (int i = 110; i < 120; i++) {
      query.setInteger(0, i);
      query.setString(1"chote");
      query.setString(2"kodam");
      query.setString(3"meetkodam@gmail.com");

      int res = query.executeUpdate();
      if (res > 0) {
        ++count;
      }
    }

    logger.info("No of records inserted" + count);
    HibernateUtil.closeSession();
  }

}


 

    
package com.cv.hibernate.nativesql;

import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.type.IntegerType;
import org.hibernate.type.StringType;

import com.cv.hibernate.nativesql.util.HibernateUtil;

/**
 @author Chandra Vardhan
 */
public class NativeSQLSelectByIdANDEmail {

  /**
   @param args
   */
  private final static Logger logger = Logger
      .getLogger(NativeSQLSelectByIdANDEmail.class);

  public static void main(String[] args) {

    // Am creating the records by this statement.
    HibernateUtil.saveOrUpdate();
    Session session = HibernateUtil.getSession();

    SQLQuery q1 = session
        .createSQLQuery("select eid,firstname,email from EmployeeBean_Native_SQL where eid=? and email like ?");

    q1.setInteger(0102);
    q1.setString(1"%gmail.com");

    // To ensure the type safety
    q1.addScalar("eid", IntegerType.INSTANCE);
    q1.addScalar("firstname", StringType.INSTANCE);
    q1.addScalar("email", StringType.INSTANCE);

    List list = q1.list();
    if (list != null && !list.isEmpty()) {
      logger.info("Return type of the class is : "
          + list.get(0).getClass());
      for (int i = 0; i < list.size(); ++i) {
        Object[] row = (Object[]) list.get(i);

        String result = null;
        for (int k = 0; k < row.length; k++) {
          if (result == null) {
            result = row[k].toString() " ";
          else {
            result += row[k].toString() " ";
          }
        }
        logger.info(result);
      }
    else {
      logger.info("No Records found for the specified input...");
    }
    HibernateUtil.closeSession();
  }

}


 

    
package com.cv.hibernate.nativesql;

import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.type.IntegerType;

import com.cv.hibernate.nativesql.util.HibernateUtil;

/**
 @author Chandra Vardhan
 */
public class NativeSQLSelectCount {
  private final static Logger logger = Logger
      .getLogger(NativeSQLSelectCount.class);

  public static void main(String args[]) throws Exception {

    // Am creating the records by this statement.
    HibernateUtil.saveOrUpdate();
    Session session = HibernateUtil.getSession();

    SQLQuery q1 = session
        .createSQLQuery("select count(*) as cnt from EmployeeBean_Native_SQL where eid>=:p1 and eid<=:p2");

    q1.setInteger("p1"103);
    q1.setInteger("p2"110);

    q1.addScalar("cnt", IntegerType.INSTANCE);

    List list = q1.list();

    if (list != null && !list.isEmpty()) {
      logger.info("Return type of the class is : "
          + list.get(0).getClass());
      int cnt = ((Integerlist.get(0)).intValue();
      logger.info("count of records : " + cnt);
    else {
      logger.info("No Records found for the specified input...");
    }
    HibernateUtil.closeSession();
  }
}


 

    
package com.cv.hibernate.nativesql;

import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.type.FloatType;
import org.hibernate.type.IntegerType;
import org.hibernate.type.LongType;
import org.hibernate.type.ShortType;

import com.cv.hibernate.nativesql.util.HibernateUtil;

/**
 @author Chandra Vardhan
 */
public class NativeSQLSelectCountMaxMinFind {

  /**
   @param args
   */
  private final static Logger logger = Logger
      .getLogger(NativeSQLSelectCountMaxMinFind.class);

  public static void main(String[] args) {
    // Am creating the records by this statement.
    HibernateUtil.saveOrUpdate();
    Session session = HibernateUtil.getSession();

    SQLQuery q1 = session
        .createSQLQuery("select count(*) as cnt,max(eid) as max,min(eid) as min,avg(eid) as avg from EmployeeBean_Native_SQL where eid>=:p1 and eid<=:p2");

    q1.setInteger("p1"100);
    q1.setInteger("p2"110);

    q1.addScalar("cnt", IntegerType.INSTANCE);
    q1.addScalar("max", LongType.INSTANCE);
    q1.addScalar("min", ShortType.INSTANCE);
    q1.addScalar("avg", FloatType.INSTANCE);

    // Iterator it=q1.iterate(); //Not supported by SQL...

    List list = q1.list();
    if (list != null && !list.isEmpty()) {
      logger.info("return type of the class : " + list.get(0).getClass());
      Object[] obj = (Object[]) list.get(0);

      int cnt = ((Integerobj[0]).intValue();
      long max = ((Longobj[1]).longValue();
      short min = ((Shortobj[2]).shortValue();
      float avg = ((Floatobj[3]).floatValue();

      logger.info("cnt of records :" + cnt);
      logger.info("max of records :" + max);
      logger.info("min of records :" + min);
      logger.info("avg of records :" + avg);
    else {
      logger.info("No Records found for the specified input...");
    }
    // This way is without adding the scalar

    List list2 = q1.list();
    if (list2 != null && !list2.isEmpty()) {
      logger.info("Return type of the class is : "
          + list2.get(0).getClass());

      for (int i = 0; i < list2.size(); ++i) {
        Object[] row = (Object[]) list2.get(i);

        String result = null;
        for (int k = 0; k < row.length; k++) {
          if (result == null) {
            result = row[k].toString() " ";
          else {
            result += row[k].toString() " ";
          }
        }
        logger.info(result);
      }
    else {
      logger.info("No Records found for the specified input...");
    }
    
    HibernateUtil.closeSession();
  }

}


 

    
package com.cv.hibernate.nativesql;

import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import com.cv.hibernate.nativesql.model.EmployeeBean;
import com.cv.hibernate.nativesql.util.HibernateUtil;

/**
 @author Chandra Vardhan
 */
public class NativeSQLSelectQuery {
  private final static Logger logger = Logger
      .getLogger(NativeSQLSelectQuery.class);

  public static void main(String args[]) throws Exception {
    // Am creating the records by this statement.
    HibernateUtil.saveOrUpdate();
    Session session = HibernateUtil.getSession();
    SQLQuery query = session
        .createSQLQuery("select * from EmployeeBean_Native_SQL");
    query.addEntity(EmployeeBean.class);
    List<EmployeeBean> list = query.list();
    if (list != null && !list.isEmpty()) {
      logger.info("return type of the class : " + list.get(0).getClass());
      for (int i = 0; i < list.size(); ++i) {
        EmployeeBean eb = list.get(i);
        logger.info(eb.getId() " " + eb.getFirstName() " "
            + eb.getLastName() " " + eb.getEmail());

      }
    else {
      logger.info("No Records found for the specified input...");
    }

    HibernateUtil.closeSession();
  }
}


 

    
package com.cv.hibernate.nativesql.util;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;

import com.cv.hibernate.nativesql.model.EmployeeBean;

/**
 @author Chandra Vardhan
 */
/**
 * Configures and provides access to Hibernate sessions, tied to the current
 * thread of execution. Follows the Thread Local Session pattern, see
 {@link http://hibernate.org/42.html }.
 */
public class HibernateUtil {
  private final static Logger logger = Logger.getLogger(HibernateUtil.class);

  /**
   * Location of hibernate.cfg.xml file. Location should be on the classpath
   * as Hibernate uses #resourceAsStream style lookup for its configuration
   * file. The default classpath location of the hibernate config file is in
   * the default package. Use #setConfigFile() to update the location of the
   * configuration file for the current session.
   */
  private static String CONFIG_FILE_LOCATION = "hibernate.cfg.xml";
  private static final ThreadLocal<Session> threadLocal = new ThreadLocal<Session>();
  private static Configuration configuration = new Configuration();
  private static SessionFactory sessionFactory;

  static {
    try {
      configuration = configuration.configure(CONFIG_FILE_LOCATION);
      StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
          .applySettings(configuration.getProperties());
      sessionFactory = configuration.buildSessionFactory(builder.build());
      logger.info("%%%% Connection successful %%%%");
    catch (Exception e) {
      logger.error("%%%% Error Creating SessionFactory %%%%");
      e.printStackTrace();
    }
  }

  private HibernateUtil() {
  }

  /**
   * Returns the ThreadLocal Session instance. Lazy initialize the
   <code>SessionFactory</code> if needed.
   
   @return Session
   @throws HibernateException
   */
  public static Session getSession() throws HibernateException {
    Session session = (SessionthreadLocal.get();

    if (session == null || !session.isOpen()) {
      if (sessionFactory == null) {
        rebuildSessionFactory();
      }
      session = (sessionFactory != null? sessionFactory.openSession()
          null;
      threadLocal.set(session);
    }

    return session;
  }

  /**
   * Rebuild hibernate session factory
   
   */
  public static void rebuildSessionFactory() {
    try {
      configuration = configuration.configure(CONFIG_FILE_LOCATION);
      StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
          .applySettings(configuration.getProperties());
      sessionFactory = configuration.buildSessionFactory(builder.build());
      logger.info("%%%% Connection successful %%%%");
    catch (Exception e) {
      logger.error("%%%% Error Creating SessionFactory %%%%");
      e.printStackTrace();
    }
  }

  /**
   * Close the single hibernate session instance.
   
   @throws HibernateException
   */
  public static void closeSession() throws HibernateException {
    Session session = (SessionthreadLocal.get();
    threadLocal.set(null);

    if (session != null) {
      session.close();
      System.exit(0);
    }
  }

  /**
   * return session factory
   
   */
  public static SessionFactory getSessionFactory() {
    return sessionFactory;
  }

  /**
   * return hibernate configuration
   
   */
  public static Configuration getConfiguration() {
    return configuration;
  }

  public static void saveOrUpdate() {
    Session session = getSession();
    for (int i = 100; i <= 105; i++) {
      EmployeeBean eb = new EmployeeBean();
      eb.setId(new Long(i));
      eb.setFirstName("chandra");
      eb.setLastName("vardhan");
      eb.setEmail("meetkodam@gmail.com");
      session.beginTransaction();
      session.saveOrUpdate(eb);
      session.getTransaction().commit();
    }
    logger.info("Saved successful...");
    session.close();
  }
}







#By default enabling Console appender
# Root logger option
log4j.rootLogger=INFO, stdout

# Redirect log messages to console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%-5p [%c]:%L -->> %m%n

# Redirect log messages to a log file
#log4j.appender.file=org.apache.log4j.RollingFileAppender
#log4j.appender.file.File=C:\\servlet-application.log
#log4j.appender.file.MaxFileSize=5MB
#log4j.appender.file.MaxBackupIndex=10
#log4j.appender.file.layout=org.apache.log4j.PatternLayout
#log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n


	
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.cv.natvesql</groupId> <artifactId>NativeSQL</artifactId> <version>1.0</version> <dependencies> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> <version>4.3.5.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-c3p0</artifactId> <version>4.3.5.Final</version> </dependency> <dependency> <artifactId>hibernate-core</artifactId> <groupId>org.hibernate</groupId> <version>4.3.5.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-validator</artifactId> <version>4.2.0.Final</version> </dependency> <dependency> <groupId>org.hibernate.common</groupId> <artifactId>hibernate-commons-annotations</artifactId> <version>4.0.4.Final</version> </dependency> <dependency> <groupId>org.hibernate.javax.persistence</groupId> <artifactId>hibernate-jpa-2.0-api</artifactId> <version>1.0.1.Final</version> </dependency> <dependency> <groupId>javax.validation</groupId> <artifactId>validation-api</artifactId> <version>1.0.0.GA</version> provided </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.6.4</version> </dependency> <dependency> <groupId>org.jboss.logging</groupId> <artifactId>jboss-logging</artifactId> <version>3.1.0.CR2</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.6.4</version> </dependency> <dependency> <groupId>postgresql</groupId> <artifactId>postgresql</artifactId> <version>9.1-901.jdbc4</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-simple</artifactId> <version>1.7.5</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.35</version> </dependency> </dependencies> <build> <plugins> <plugin> <artifactId>maven-compiler-plugin</artifactId> <version>3.3</version> <configuration> <source>1.8</source> <target>1.8</target> </configuration> </plugin> </plugins> </build> </project>




No comments:

Post a Comment