Hibernate sql query example program

Step 1: Download the hibernate 3.6 final distribution.

Click to download Hibernate 3.6 final distribution download.

Step 2: Required jar files are,

required library files for hibernate

Step 3: Create a Java project in the eclipse and Create a registration configuration xml file inside src folder (which has the connection information like url, username password etc…)

Create a file named “registration.cfg.xml” and paste the below code,

Note: Filename can be anything but extension must be .cfg.xml.

[xml]
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
<!– Database connection configurations –>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/signup</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password"></property>

<!– Making autocommit false –>
<property name="hibernate.connection.autocommit">false</property>

<!– Displaying sql queries when running the program –>
<property name="show_sql">true</property>
<property name="format_sql">true</property>
<property name="use_sql_comments">true</property>

<!– For JDBC connections –>
<property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>

<!– Mapping file –>
<mapping resource="registration.hbm.xml"/>

</session-factory>

</hibernate-configuration>
[/xml]

Note: Url, Username Password and dialect informations are available for all the databases in the configuration file which is in “project -> etc (contains all the properties of all databases)”  of hibernate 3.6 distribution final folder (which is downloaded in step 1).

Step 4: Create a table with this attribute in mysql,

username(varchar) 20 primary key, email(varchar) 30, password (varchar) 20

Step 5: Create a Pojo class (Registration) for table attributes (username, email and password) and paste the below code,

[java]

public class Registration {
public Registration(){}
private String userName;
private String emailId;
private String password;
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getEmailId() {
return emailId;
}
public void setEmailId(String emailId) {
this.emailId = emailId;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}

}
[/java]

Step 6: Create a mapping xml file which map the column and the attribute of the above Pojo class (setter and getter).

Create a file named “registration.hbm.xml” and paste the below code,

Note: File name can be anything, but extension must be .hbm.xml

[xml]
<?xml version="1.0" encoding="UTF-8"?>

<!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="Registration" table="Registration">
<id name="userName" column="USERNAME" type="string">
<generator class="assigned"></generator>
</id>
<property name="emailId" column="EMAILID" type="string"></property>
<property name="password" column="PASSWORD" type="string"></property>
</class>

</hibernate-mapping>
[/xml]

Copy the doctype(3rd line) from the file (project -> core ->source ->main ->resources -> org ->hibernate ->(mapping file)) for the above mapping file, it is not a mandatory one, to ensure the things right we can do this.

Step 7: Create a business class named “RegisterDetails” and paste the below code,

[java]
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class RegisterDetails {

public static void main(String[] args) {
Configuration cfg = new Configuration();
cfg.configure("registration.cfg.xml");
SessionFactory sf = cfg.buildSessionFactory();
Session s = sf.openSession();
Transaction tx = s.beginTransaction();

Query registeredDetails = s.createSQLQuery("select * FROM Registration");
List registreredList = registeredDetails.list();

for(Iterator listIterator = registreredList.iterator();listIterator.hasNext();){
Object[] values = (Object[]) listIterator.next();
for(int i=0;i<values.length;i++){
System.out.println(values[i]);
}
}

tx.commit();
s.close();

}

}
[/java]

Run the program and the values are fetched from the db and the query will be displayed in the console, since we have configured show sql true in our xml configuration file.

Output:

Hibernate:
/* dynamic native SQL query */ select
*
FROM
Registration
naveendra
test@ngdeveloper.com
test@123
naveendra1
test@ngdeveloper.com
test@123

Easy Understand :

  • Hibernate configuraion xml file which is to hold the connection details such as url, username, password, dialect(to generate queries) and driver class details.
  • Pojo class for the required attributes.
  • Hibernate mapping xml file to map the pojo class attributes with the table columns.
  • Business class to write the business code, we have written value selection details here.

Download the Source Code:

HibernateSqlQuery

Recommended Books:

Leave a Reply