Hibernate filter concepts using SQL Query

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"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping package="com.cv.hibernate.filter.model"> <class name="EmployeeBean" table="Employee_FILTER"> <id name="id" column="EID" /> <property name="firstName" column="FIRSTNAME" /> <property name="lastName" column="LASTNAME" /> <property name="email" column="EMAIL" /> <filter name="hibernateFilterInPostgres" condition="EID>=:p1 and EID<=:p2" /> </class> <sql-query name="SQLSelectQueryByID"> <return class="EmployeeBean" /> <![CDATA[ select * from Employee_FILTER where EID=:p1]]> </sql-query> <filter-def name="hibernateFilterInPostgres"> <filter-param name="p1" type="int" /> <filter-param name="p2" type="int" /> </filter-def> </hibernate-mapping>

package com.cv.hibernate.filter; import java.util.List; import org.apache.log4j.Logger; import org.hibernate.Filter; import org.hibernate.Query; import org.hibernate.Session; import com.cv.hibernate.filter.model.EmployeeBean; import com.cv.hibernate.filter.util.HibernateUtil; /**  * @author Chandra Vardhan  */ public class FilterTest {   private final static Logger logger = Logger.getLogger(FilterTest.class);   public static void main(String args[]) throws Exception {     HibernateUtil.saveOrUpdate();     Session session = HibernateUtil.getSession();     Filter filter = session.enableFilter("hibernateFilterInPostgres");     filter.setParameter("p1", 100);     filter.setParameter("p2", 103);     Query query = session.createQuery("from EmployeeBean");     List 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 = (EmployeeBean) list.get(i);         logger.info(eb.getId() + " " + eb.getFirstName() + " "             + eb.getLastName() + " " + eb.getEmail());       }     } else {       logger.info("No Records found for the specified input...");     }     Query query2 = session.getNamedQuery("SQLSelectQueryByID");     query2.setInteger("p1", 104);     List list2 = query2.list();     if (list != null && !list.isEmpty()) {       logger.info("Return type of the class : " + list.get(0).getClass());       for (int i = 0; i < list2.size(); ++i) {         EmployeeBean eb4 = (EmployeeBean) list2.get(i);         logger.info(eb4.getId() + " " + eb4.getFirstName() + " "             + eb4.getLastName() + " " + eb4.getEmail());       }     } else {       logger.info("No Records found for the specified input...");     }     session.disableFilter("hibernateFilterInPostgres");     Query q2 = session.createQuery("select count(*) from EmployeeBean");     list = q2.list();     logger.info("hello===count of rcords is=====" + list.toString());     HibernateUtil.closeSession();   } }

package com.cv.hibernate.filter.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.filter.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.filter.model.EmployeeBean; /**  * 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 = (Session) threadLocal.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 = (Session) threadLocal.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.hibernate.filter</groupId> <artifactId>HibernateFilterAndSQLQuery</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> </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>