Category Archives: ORM

Custom SQL queries using Hibernate

Posted on by

Database Schema
student
id int, auto_increment, primary key
fname char(20)
lname char(20)
age int

grade
id int, auto_increment, primary key
studentid int,
grade int

1) Use Maven to get the hibernate and H2 jars 

<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/maven-v4_0_0.xsd">

	<modelVersion>4.0.0</modelVersion>
	<groupId>com.mycompany.app</groupId>
	<artifactId>my-module</artifactId>
	<version>1</version>

	<dependencies>
		<dependency>
	        <groupId>org.hibernate</groupId>
	        <artifactId>hibernate-core</artifactId>
	        <version>4.1.6.Final</version>
		</dependency>
		<dependency>
		    <groupId>com.h2database</groupId>
		    <artifactId>h2</artifactId>
		    <version>1.3.168</version>
		</dependency>
	</dependencies>
	<build>
		<defaultGoal>compile</defaultGoal>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-dependency-plugin</artifactId>
				<version>2.5.1</version>
				<executions>
					<execution>
						<id>copy1</id>
						<phase>compile</phase>
						<goals>
							<goal>copy-dependencies</goal>
						</goals>
						<configuration>
							<outputDirectory>.\lib</outputDirectory>
							<!-- other configurations here -->
						</configuration>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>
</project>

2) Place the hibernate config file in the “src” folder.
hibernate.cfg.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 settings -->
        <property name="connection.driver_class">org.h2.Driver</property>
        <property name="connection.url">jdbc:h2:file:schoolDB;DB_CLOSE_DELAY=-1;MVCC=TRUE</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.H2Dialect</property>

        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">create</property>

        <!-- Names the annotated entity class -->
        <mapping class="com.tutorial.Student"/>
        <mapping class="com.tutorial.Course"/>
        <mapping class="com.tutorial.Grade"/>

    </session-factory>

</hibernate-configuration>

3) CustomSQL.java

package com.customsql;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistryBuilder;
import org.hibernate.transform.Transformers;

public class CustomSQL {
	public static class gradeClass
	{
		int grade;
		int id;
		
		public int getGRADE() {
			return grade;
		}
		public void setGRADE(int grade) {
			this.grade = grade;
		}
		public int getID() {
			return id;
		}
		public void setID(int id) {
			this.id = id;
		}
	}
	public static void main(String[] arg) {
		Configuration configure = new Configuration().configure();
		ServiceRegistryBuilder serviceRegistry = new ServiceRegistryBuilder().applySettings(configure.getProperties());
		SessionFactory sessionFactory = configure.buildSessionFactory(serviceRegistry.buildServiceRegistry());
		
		Session session = sessionFactory.openSession();
		@SuppressWarnings("unchecked")
		List<gradeClass> list = session.createSQLQuery("select student.id, grade from student inner join grade on student.id = grade.studentid")
			.setResultTransformer(Transformers.aliasToBean(gradeClass.class)).list();
		for (gradeClass g : list) {
			System.out.println(g.getGRADE());
		}
	}
}

Spring and Hibernate

Posted on by

1) Create a dynamic web project called “schoolDBTest”
2) Use maven to get the required jar files

<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/maven-v4_0_0.xsd">

	<modelVersion>4.0.0</modelVersion>
	<groupId>com.mycompany.app</groupId>
	<artifactId>my-module</artifactId>
	<version>1</version>

	<dependencies>
		<dependency>
			<groupId>org.codehaus.jackson</groupId>
			<artifactId>jackson-mapper-asl</artifactId>
			<version>1.7.1</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>3.1.2.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
			<version>3.1.2.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>3.1.2.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-orm</artifactId>
			<version>3.1.2.RELEASE</version>
		</dependency>
		<dependency>
	        <groupId>org.hibernate</groupId>
	        <artifactId>hibernate-core</artifactId>
	        <version>4.1.6.Final</version>
		</dependency>
		<dependency>
		    <groupId>com.h2database</groupId>
		    <artifactId>h2</artifactId>
		    <version>1.3.168</version>
		</dependency>
		<dependency>
			<groupId>jstl</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-validator</artifactId>
			<version>4.3.0.Final</version>
		</dependency>
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.2.2</version>
		</dependency>
		<dependency>
			<groupId>cglib</groupId>
			<artifactId>cglib</artifactId>
			<version>2.2.2</version>
		</dependency>
	</dependencies>
	<build>
		<defaultGoal>compile</defaultGoal>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-dependency-plugin</artifactId>
				<version>2.5.1</version>
				<executions>
					<execution>
						<id>copy1</id>
						<phase>compile</phase>
						<goals>
							<goal>copy-dependencies</goal>
						</goals>
						<configuration>
							<outputDirectory>.\WebContent\WEB-INF\lib</outputDirectory>
							<!-- other configurations here -->
						</configuration>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>
</project>

3) Create the student database model

package com.schoolDB.database.model;

import javax.persistence.*;

@Entity
@Table(name="Student")
public class Student {
	int studentID;
	String firstName;
	String lastName;
	
	public Student()
	{
		
	}
	public Student(String firstName, String lastName) {
		this.firstName = firstName;
		this.lastName = lastName;
	}

	@Id
	@GeneratedValue()
	public int getStudentID() {
		return studentID;
	}
	public void setStudentID(int studentID) {
		this.studentID = studentID;
	}
	
	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;
	}
}

4) Create student dao

package com.schoolDB.database.dao;

import java.util.List;

import com.schoolDB.database.model.Student;

public interface StudentDao {
	public void deleteStudent(int id);
	public void addStudent(Student student);
	public Student getStudent(int id);
	public void editStudent(Student student);
	public List<Student> listStudents();
}

package com.schoolDB.database.dao;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.schoolDB.database.model.Student;

@Repository
public class StudentDaoImpl implements StudentDao{
	@Autowired
	private SessionFactory sessionFactory;
	
	public void addStudent(Student student)
	{
		Session s = sessionFactory.openSession();
		Transaction tx = s.beginTransaction();
		s.save(student);
		tx.commit();
		s.close();
	}
	
	public void deleteStudent(int id)
	{
		Session s = sessionFactory.openSession();
		Transaction tx = s.beginTransaction();
		Query q = s.createQuery("delete from Student where id=?").setInteger(0, id);
		q.executeUpdate();
		tx.commit();
	}
	
	public List<Student> listStudents() {
		Session s = sessionFactory.openSession();
		Transaction tx = s.beginTransaction();
		@SuppressWarnings("unchecked")
		List<Student> list = s.createQuery("from Student").list();
		tx.commit();
		s.close();
		return list;
	}

	public Student getStudent(int id) {
		Session s = sessionFactory.openSession();
		Transaction tx = s.beginTransaction();
		Query q = s.createQuery("from Student where studentid = ?");
		q.setMaxResults(1);
		q.setInteger(0, id);
		Student student = (Student)q.uniqueResult();
		tx.commit();
		s.close();
		return student;
	}

	public void editStudent(Student student) {
		Session s = sessionFactory.openSession();
		Transaction tx = s.beginTransaction();
		s.update(student);
		tx.commit();
		s.close();
	}
}

5) Create MVC student model

package com.schoolDB.web.model;

import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;

public class StudentModel {
	
	public StudentModel() {
	}
	
	@NotNull
	@Size(min=1, message="First name cannot be empty.")
	private String firstName;
	
	@NotNull
	@Size(min=1, message="Last name cannot be empty.")
	private String lastName;
	
	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;
	}
}

6) Create student service

package com.schoolDB.database.service;

import java.util.List;

import com.schoolDB.database.model.Student;
import com.schoolDB.web.model.StudentModel;

public interface StudentService {
	public void deleteStudent(int id);
	public void addStudent(StudentModel student);
	public Student getStudent(int id);
	public void editStudent(int studentid, StudentModel student);
	public List<Student> listStudents();
}

package com.schoolDB.database.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.schoolDB.database.dao.StudentDao;
import com.schoolDB.database.model.Student;
import com.schoolDB.web.model.StudentModel;

@Service
public class StudentServiceImpl implements StudentService{

	@Autowired
	StudentDao studentdao;
	
	public List<Student> listStudents()
	{
		return studentdao.listStudents();
	}
	
	public void addStudent(StudentModel student) {
		studentdao.addStudent(new Student(student.getFirstName(), student.getLastName()));
	}
	
	public void deleteStudent(int id)
	{
		studentdao.deleteStudent(id);
	}

	public Student getStudent(int id) {
		return studentdao.getStudent(id);
	}

	public void editStudent(int studentid, StudentModel student) {
		Student s = new Student(student.getFirstName(), student.getLastName());
		s.setStudentID(studentid);
		studentdao.editStudent(s);
	}
}

7) Create MVC student controller

package com.schoolDB.web.controller;

import java.util.HashMap;
import java.util.Map;
import java.util.Set;

import javax.validation.*;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.schoolDB.database.model.Student;
import com.schoolDB.database.service.StudentService;
import com.schoolDB.web.model.StudentModel;

@Controller
@RequestMapping(value="/student")
public class StudentController {
	Validator validator;

	@Autowired
	StudentService studentservice;
	
	public StudentController()
	{
		
	}
	
	@Autowired
	public StudentController(Validator validator)
	{
		this.validator = validator;
	}
	
	@RequestMapping(method=RequestMethod.GET)
	public String studentIndex(Map<String, Object> map)
	{
		map.put("studentList", studentservice.listStudents());
		return "student/index";
	}
	
	@RequestMapping(value="add", method=RequestMethod.GET)
	public String studentAdd(Model model)
	{
		model.addAttribute("studentModel", new StudentModel());
		return "student/studentForm";
	}
	
	@RequestMapping(value="add", method=RequestMethod.POST)
	public @ResponseBody Map<String, String> create(@RequestBody StudentModel student)
	{
		Map<String, String> s = new HashMap<String, String>();
		Set<ConstraintViolation<StudentModel>> failures = validator.validate(student);
		if (!failures.isEmpty()) {
			for (ConstraintViolation<StudentModel> constraintViolation : failures) {
				s.put(constraintViolation.getPropertyPath().toString(), constraintViolation.getMessage());
			}
		}
		else
			studentservice.addStudent(student);
		return s;
	}
	
	@RequestMapping(value="delete/{studentid}", method=RequestMethod.GET)
	public String delete(@PathVariable String studentid)
	{
		studentservice.deleteStudent(Integer.parseInt(studentid));
		return "redirect:../";
	}
	
	@RequestMapping(value="edit", method=RequestMethod.GET)
	public String wrongEdit()
	{
		return "redirect:../"; 
	}
	
	@RequestMapping(value="edit/{studentid}", method=RequestMethod.GET)
	public String studentEdit(@PathVariable String studentid, Model model)
	{
		Student student = (Student)studentservice.getStudent(Integer.parseInt(studentid));
		StudentModel m = new StudentModel();
		m.setFirstName(student.getFirstName());
		m.setLastName(student.getLastName());
		model.addAttribute("studentModel", m);
		return "student/studentForm";
	}
	
	@RequestMapping(value="edit/{studentid}", method=RequestMethod.POST)
	public @ResponseBody Map<String, String> studentUpdate(@PathVariable String studentid, @RequestBody StudentModel student)
	{
		Map<String, String> s = new HashMap<String, String>();
		Set<ConstraintViolation<StudentModel>> failures = validator.validate(student);
		if (!failures.isEmpty()) {
			for (ConstraintViolation<StudentModel> constraintViolation : failures) {
				s.put(constraintViolation.getPropertyPath().toString(), constraintViolation.getMessage());
			}
		}
		else
			studentservice.editStudent(Integer.parseInt(studentid), student);
		return s;
	}
}

8) Create MVC student view
Create the following folder structure /WEB-INF/views/student
Put the following files in the student folder
index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
    
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Students</title>
</head>
<body>
<a href="add/">Add Student</a>
<br><br>
List of Students
<table border="1">
<tr>
	<th>first name</th>
	<th>last name</th>
</tr>
<c:forEach items="${studentList}" var="student">
	<tr>
		<td>${student.firstName }</td>
		<td>${student.lastName }</td>
		<td><a href="delete/${student.studentID}">Delete</a></td>
		<td><a href="edit/${student.studentID}">Edit</a></td>
	</tr>
</c:forEach>
</table>

</body>
</html>

studentForm.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Student Add</title>
<script type="text/javascript" src="<c:url value="/resources/jquery.js" />"></script>
<script type="text/javascript">
$.fn.serializeObject = function()
{
   var o = {};
   var a = this.serializeArray();
   $.each(a, function() {
       if (o[this.name]) {
           if (!o[this.name].push) {
               o[this.name] = [o[this.name]];
           }
           o[this.name].push(this.value || '');
       } else {
           o[this.name] = this.value || '';
       }
   });
   return o;
};

$(document).ready(function() {
	$("#studentModel").submit(function() {
		$("#firstname_error").hide();
		$("#lastname_error").hide();
		var student = JSON.stringify($('form').serializeObject());
		$.ajax({
			'type':'POST',
			'url':'',
			'contentType': 'application/json',
			'data': student,
			'dataType': 'json',
			'success': function(data) {
				if(data.firstName != null)
				{
					$("#firstname_error").show();
					$("#firstname_error").html(data.firstName);
				}
				if(data.lastName != null)
				{
					$("#lastname_error").show();
					$("#lastname_error").html(data.lastName);
				}
				
				if(data.firstName == null && data.lastName == null)
					window.location = "../";
			}
		});
		return false;
	});
});
</script>
</head>
<body>
	<form:form modelAttribute="studentModel" action="add" method="post">
		<form:label id="firstNameLabel" for="firstName" path="firstName">First Name: </form:label>
		<form:input path="firstName" value="${student.firstName}" /><form:errors path="firstName" />
		<div id="firstname_error" style="display:none"></div>
		<br>
		<form:label id="lastNameLabel" for="lastName" path="lastName">Last Name: </form:label>
		<form:input path="lastName" value="${student.lastName}" /><form:errors path="lastName" />
		<div id="lastname_error" style="display:none"></div>
		<br>
		<input id="submit" type="submit" value="submit" />
	</form:form>
</body>
</html>

9) Create configuration files in WEB-INF folder
servlet-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">
 
    <!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
 
    <!-- Scans within the base package of the application for @Components to configure as beans -->
    <!-- @Controller, @Service, @Configuration, etc. -->
    <context:component-scan base-package="com.schoolDB" />
 
    <!-- Enables the Spring MVC @Controller programming model -->
    <mvc:annotation-driven />
    <mvc:view-controller path="/" view-name="welcome"  />
	<mvc:resources mapping="/resources/**" location="/WEB-INF/resources/" />
    
 	<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix" value="/WEB-INF/views/"/>
		<property name="suffix" value=".jsp"/>
	</bean>
	
    
	<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="org.h2.Driver"/>
		<property name="url" value="jdbc:h2:file:/schoolDBTest/schoolDBTest;DB_CLOSE_DELAY=-1;MVCC=TRUE"/>
		<property name="username" value="sa"/>
		<property name="password" value=""/>
	</bean>
	
	<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
		<property name="dataSource" ref="myDataSource"/>
		<property name="annotatedClasses">
			<list>
				<value>com.schoolDB.database.model.Student</value>
			</list>
		</property>
		<property name="hibernateProperties">
			<value>
				hibernate.dialect=org.hibernate.dialect.H2Dialect
				hibernate.format_sql=true
				hibernate.show_sql=true
				hibernate.hbm2ddl.auto=update
			</value>
		</property>
	</bean>
</beans>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>schoolDBTest</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  
	<servlet>
		<servlet-name>appServlet</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>/WEB-INF/servlet-context.xml</param-value>
		</init-param>
    	<load-on-startup>1</load-on-startup>
  	</servlet>
  	<servlet-mapping>
  		<servlet-name>appServlet</servlet-name>
  		<url-pattern>/</url-pattern>
	</servlet-mapping>
</web-app>

10) Create folder called resources under WEB-INF. Put jquery.js in resources folder

Open browser to http://localhost:8080/schoolDBTest/student/

Hibernate

Posted on by

1) Create a project called “SchoolDB” in eclipse
2) Use Maven to get the hibernate and H2 jars 

<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/maven-v4_0_0.xsd">

	<modelVersion>4.0.0</modelVersion>
	<groupId>com.mycompany.app</groupId>
	<artifactId>my-module</artifactId>
	<version>1</version>

	<dependencies>
		<dependency>
	        <groupId>org.hibernate</groupId>
	        <artifactId>hibernate-core</artifactId>
	        <version>4.1.6.Final</version>
		</dependency>
		<dependency>
		    <groupId>com.h2database</groupId>
		    <artifactId>h2</artifactId>
		    <version>1.3.168</version>
		</dependency>
	</dependencies>
	<build>
		<defaultGoal>compile</defaultGoal>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-dependency-plugin</artifactId>
				<version>2.5.1</version>
				<executions>
					<execution>
						<id>copy1</id>
						<phase>compile</phase>
						<goals>
							<goal>copy-dependencies</goal>
						</goals>
						<configuration>
							<outputDirectory>.\lib</outputDirectory>
							<!-- other configurations here -->
						</configuration>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>
</project>

3) Place the hibernate config file in the “src” folder.
hibernate.cfg.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 settings -->
        <property name="connection.driver_class">org.h2.Driver</property>
        <property name="connection.url">jdbc:h2:file:schoolDB;DB_CLOSE_DELAY=-1;MVCC=TRUE</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.H2Dialect</property>

        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">create</property>

        <!-- Names the annotated entity class -->
        <mapping class="com.tutorial.Student"/>
        <mapping class="com.tutorial.Course"/>
        <mapping class="com.tutorial.Grade"/>

    </session-factory>

</hibernate-configuration>

4) Write the code
Student.java

package com.tutorial;

import javax.persistence.*;

@Entity
public class Student {
	int studentID;
	String firstName;
	String lastName;
	
	public Student()
	{
		
	}
	public Student(String firstName, String lastName) {
		this.firstName = firstName;
		this.lastName = lastName;
	}

	@Id
	@GeneratedValue()
	public int getStudentID() {
		return studentID;
	}
	public void setStudentID(int studentID) {
		this.studentID = studentID;
	}
	
	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;
	}
}

Course.java

package com.tutorial;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class Course {
	public Course()
	{
		
	}
	public Course(String courseID, String courseName) {
		this.courseID = courseID;
		this.courseName = courseName;
	}
	String courseID;
	String courseName;
	
	@Id
	public String getCourseID() {
		return courseID;
	}
	public void setCourseID(String courseID) {
		this.courseID = courseID;
	}
	public String getCourseName() {
		return courseName;
	}
	public void setCourseName(String courseName) {
		this.courseName = courseName;
	}
}

Grade.java

package com.tutorial;

import javax.persistence.*;

@Entity
public class Grade {
	public Grade()
	{
		
	}
	public Grade(Student student, Course course, int grade) {
		this.student = student;
		this.course = course;
		this.grade = grade;
	}
	int gradeID;
	Student student;
	Course course;
	int grade;
	
	@Id
	@GeneratedValue()
	public int getGradeID() {
		return gradeID;
	}
	public void setGradeID(int gradeID) {
		this.gradeID = gradeID;
	}
	
	@OneToOne(cascade = CascadeType.ALL)
	@JoinColumn(name="studentID")
	public Student getStudent() {
		return student;
	}
	public void setStudent(Student student) {
		this.student = student;
	}
	
	@OneToOne(cascade = CascadeType.ALL)
	@JoinColumn(name="courseID")
	public Course getCourse() {
		return course;
	}
	public void setCourse(Course course) {
		this.course = course;
	}
	public int getGrade() {
		return grade;
	}
	public void setGrade(int grade) {
		this.grade = grade;
	}
	
}

SchoolDB.java

package com.tutorial;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistryBuilder;

public class SchoolDB {
	private SessionFactory sessionFactory;
	
	void CreateStudents()
	{
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		session.save( new Student( "John", "Smith" ) );
		session.save( new Student( "George", "Washington" ) );
		session.getTransaction().commit();
		session.close();
	}
	
	void CreateCourses()
	{
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		session.save( new Course( "M1001", "Math" ) );
		session.save( new Course( "P1001", "Physics" ) );
		session.save( new Course( "S1001", "Science" ) );
		session.getTransaction().commit();
		session.close();
	}
	
	@SuppressWarnings("unchecked")
	void SubmitGrades()
	{
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		
		List <Course> course = session.createQuery("from Course where courseName='Physics'").list();
		List <Student> student = session.createQuery("from Student where firstName='John'").list();
		session.save(new Grade(student.get(0), course.get(0), 90));
		
		course = session.createQuery("from Course where courseName='Math'").list();
		session.save(new Grade(student.get(0), course.get(0), 80));
		
		course = session.createQuery("from Course where courseName='Science'").list();
		student = session.createQuery("from Student where firstName='George'").list();
		session.save(new Grade(student.get(0), course.get(0), 50));
		
		session.getTransaction().commit();
		session.close();
	}
	
	public void run()
	{
		Configuration configure = new Configuration().configure();
		ServiceRegistryBuilder serviceRegistry = new ServiceRegistryBuilder().applySettings(configure.getProperties());
		sessionFactory = configure.buildSessionFactory(serviceRegistry.buildServiceRegistry());
		
		CreateStudents();
		CreateCourses();
		SubmitGrades();
		
		Session session = sessionFactory.openSession();
		@SuppressWarnings("unchecked")
		List <Grade>grades = session.createQuery("from Grade").list();
		System.out.println();
		for (Grade grade : grades) {
			System.out.println(grade.getStudent().getFirstName() + " "+ 
					grade.getStudent().getLastName() + " "+ 
					grade.getCourse().getCourseName() + " " +
					grade.getGrade());
		}
		session.close();
		
		if ( sessionFactory != null ) {
			sessionFactory.close();
		}
	}
	public static void main(String[] args) {
		new SchoolDB().run();
	}
}

Entity Framework

Posted on by

This code uses Microsoft SQL Server Express. Therefore, SQL Sever Express must be installed for this code to work.

1) Create a C# project called “SchoolDB” in Visual Studio 2012

2) Install “Entity Framework” by right-click on “References”, select “Manage NuGet Packages”, search for “Entity Framework” and finally click “Install”

3) Add connectionstring in App.config

  <connectionStrings>
    <add name="schooldb"
         providerName="System.Data.SqlClient"
         connectionString="Server=.\SQLEXPRESS;Database=SchoolDB;Integrated Security=True;"/>
  </connectionStrings>

Sample App.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  </entityFramework>
  <connectionStrings>
    <add name="schooldb"
         providerName="System.Data.SqlClient"
         connectionString="Server=.\SQLEXPRESS;Database=SchoolDB;Integrated Security=True;"/>
  </connectionStrings>
</configuration>

4) Here’s the actual application to insert and select data from the database.
Program.cs

//Get Started with Entity Framework (EF)
//http://msdn.microsoft.com/en-us/data/ee712907

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SchoolDB
{
    public class Student
    {
        public int studentID { get; set; }
        public string firstName { get; set; }
        public string lastName { get; set; }

        public virtual List<Grade> grades { get; set; }
    }

    public class Course
    {
        public string courseID { get; set; }
        public string courseName { get; set; }
    }

    public class Grade
    {
        public int gradeID { get; set; }
        public virtual Student student { get; set; }
        public virtual Course course { get; set; }
        public int grade { get; set; }
    }

    public class SchoolDBContext : DbContext
    {
        public SchoolDBContext()
            : base("schooldb")
        {
        }

        public DbSet<Student> students { get; set; }
        public DbSet<Course> courses { get; set; }
        public DbSet<Grade> grades { get; set; }
    }

    class Program
    {
        static void CreateStudents()
        {
            using (var db = new SchoolDBContext())
            {
                Student s1 = new Student();
                s1.firstName = "John";
                s1.lastName = "Smith";
                db.students.Add(s1);

                Student s2 = new Student();
                s2.firstName = "George";
                s2.lastName = "Washington";
                db.students.Add(s2);

                db.SaveChanges();
            }
        }

        static void CreateCourses()
        {
            using (var db = new SchoolDBContext())
            {
                Course c = new Course();
                c.courseID = "M1001";
                c.courseName = "Math";
                db.courses.Add(c);

                c = new Course();
                c.courseID = "P1001";
                c.courseName = "Physics";
                db.courses.Add(c);

                c = new Course();
                c.courseID = "S1001";
                c.courseName = "Science";
                db.courses.Add(c);

                db.SaveChanges();
            }
        }

        static void SubmitGrades()
        {
            using (var db = new SchoolDBContext())
            {
                var student = from s in db.students
                              where s.firstName == "John" &&
                              s.lastName == "Smith"
                              select s;

                var course = from c in db.courses
                             where c.courseName == "Physics"
                             select c;

                Grade grade = new Grade();
                grade.student = student.First();
                grade.course = course.First();
                grade.grade = 90;
                db.grades.Add(grade);

                course = from c in db.courses
                             where c.courseName == "Math"
                             select c;
                grade = new Grade();
                grade.student = student.First();
                grade.course = course.First();
                grade.grade = 80;
                db.grades.Add(grade);

                student = from s in db.students
                          where s.firstName == "George" &&
                          s.lastName == "Washington"
                          select s;

                grade = new Grade();
                grade.student = student.First();
                grade.course = course.First();
                grade.grade = 100;
                db.grades.Add(grade);

                course = from c in db.courses
                         where c.courseName == "Science"
                         select c;
                grade = new Grade();
                grade.student = student.First();
                grade.course = course.First();
                grade.grade = 50;
                db.grades.Add(grade);

                db.SaveChanges();
            }
        }

        static void ListGrades()
        {
            using (var db = new SchoolDBContext())
            {
                var studentGrades = from s in db.students
                                    where s.firstName == "John"
                                    select s.grades;
                foreach (Grade g in studentGrades.First())
                {
                    Console.WriteLine(g.student.firstName + " " + g.student.lastName + " " +
                        g.course.courseName + " " + g.grade);
                }

                studentGrades = from s in db.students
                                where s.firstName == "George"
                                select s.grades;
                foreach (Grade g in studentGrades.First())
                {
                    Console.WriteLine(g.student.firstName + " " + g.student.lastName + " " +
                        g.course.courseName + " " + g.grade);
                }
            }
        }

        static void Main(string[] args)
        {
            //clear all data
            using (var db = new SchoolDBContext())
            {
                db.Database.ExecuteSqlCommand("delete from grades");
                db.Database.ExecuteSqlCommand("delete from students");
                db.Database.ExecuteSqlCommand("delete from courses");
            }

            CreateStudents();
            CreateCourses();
            SubmitGrades();

            ListGrades();
        }
    }
}