Projection: Count Distinct : Projections « Hibernate « Java Tutorial

Home
Java Tutorial
1.Language
2.Data Type
3.Operators
4.Statement Control
5.Class Definition
6.Development
7.Reflection
8.Regular Expressions
9.Collections
10.Thread
11.File
12.Generics
13.I18N
14.Swing
15.Swing Event
16.2D Graphics
17.SWT
18.SWT 2D Graphics
19.Network
20.Database
21.Hibernate
22.JPA
23.JSP
24.JSTL
25.Servlet
26.Web Services SOA
27.EJB3
28.Spring
29.PDF
30.Email
31.J2ME
32.J2EE Application
33.XML
34.Design Pattern
35.Log
36.Security
37.Apache Common
38.Ant
39.JUnit
Java Tutorial » Hibernate » Projections 




File: Main.java

import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;

public class Main {
  public static void main(String[] argsthrows Exception {
    HibernateUtil hibernateUtil = new HibernateUtil();
    hibernateUtil
        .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");

    Session session = hibernateUtil.getSession();

    Survey survey = new Survey();
    survey.setName("Survey");
    survey.setPurchaseDate(new Date());

    session.save(survey);

    
    survey = new Survey();
    survey.setName("Survey1");
    survey.setPurchaseDate(new Date());    
    session.save(survey);
    
    session.flush();
    
    Criteria crit = session.createCriteria(Survey.class);
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.countDistinct("Id"));
    crit.setProjection(projList);
    
    
    List results = crit.list();
    System.out.println(results);
    
    session.close();
    hibernateUtil.checkData("select * from survey");
  }
}

File: Survey.hbm.xml

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
    <class name="Survey" table="SURVEY" dynamic-update="false" dynamic-insert="false">
        <cache usage="read-write"/>
    <id name="Id" type="int" unsaved-value="0">
      <column name="ID" sql-type="int" not-null="true"/> 
      <generator class="increment"></generator>
    </id>        
        <property name="name" type="java.lang.String" update="true" insert="true" access="property" column="name"/>
        <property name="purchaseDate" type="date" column="purchasedate" /> 
    </class>
</hibernate-mapping>

File: Survey.java

import java.util.Date;

public class Survey {
  private int id;

  private String name;

  private Date purchaseDate;

  public Date getPurchaseDate() {
    return purchaseDate;
  }

  public void setPurchaseDate(Date purchaseDate) {
    this.purchaseDate = purchaseDate;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public int getId() {
    return id;
  }

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

File: HibernateUtil.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

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

public class HibernateUtil {
  Session session;
  Statement st;
  public HibernateUtil() throws Exception{
    SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
    session = sessionFactory.openSession();

    // Load the JDBC driver.
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    // Establish the connection to the database.
    String url = "jdbc:hsqldb:data/tutorial";

    Connection conn = DriverManager.getConnection(url, "sa""");
    System.out.println("Got Connection.");
    st = conn.createStatement();
  }
  public Session getSession(){
    return session;
  }
  public void executeSQLCommand(String sqlthrows Exception {
    st.executeUpdate(sql);
  }

  public void checkData(String sqlthrows Exception {
    ResultSet rs = st.executeQuery(sql);
    ResultSetMetaData metadata = rs.getMetaData();

    for (int i = 0; i < metadata.getColumnCount(); i++) {
      System.out.print("\t"+ metadata.getColumnLabel(i + 1))
    }
    System.out.println("\n----------------------------------");

    while (rs.next()) {
      for (int i = 0; i < metadata.getColumnCount(); i++) {
        Object value = rs.getObject(i + 1);
        if (value == null) {
          System.out.print("\t       ");
        else {
          System.out.print("\t"+value.toString().trim());
        }
      }
      System.out.println("");
    }
  }
}

File: hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <!-- Database connection settings -->
        <property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
        <property name="connection.url">jdbc:hsqldb:data/tutorial</property>
        <property name="connection.username">sa</property>
        <property name="connection.password"></property>

        <!-- JDBC connection pool (use the built-in-->
        <property name="connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.HSQLDialect</property>
        <!-- Enable Hibernate's current session context -->
        <property name="current_session_context_class">org.hibernate.context.ManagedSessionContext</property>

        <property name="hibernate.cache.use_second_level_cache">false</property>
        <property name="hibernate.cache.use_query_cache">false</property>
        
        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>        
        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>
        
        <!-- Mapping files -->
        <mapping resource="Survey.hbm.xml"/>
    </session-factory>
</hibernate-configuration>
  Download:  HibernateCountDistinctProjection.zip( 4,861 k)














21.15.Projections
21.15.1.Select Specific Fields in a Entity with Criteria and Projections
21.15.2.Scalar SQL Query
21.15.3.Projection: Min
21.15.4.Projection: Max
21.15.5.Group Field
21.15.6.Projection: Count Distinct
21.15.7.AVG Projection
21.15.8.Hibernate demo code from Hibernate in action
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.