Different ways to get the Connection object using Hibernate

Getting the java.sql.Connection object using Session:

---------------------------------------------------------------------------

org.hibernate.Session is nothing but get the one physical connection from the database. 

By using Session we can get the Connection object several ways.

1) session.connection() - this method is deprecated. it is not available on Hibernate4.

2) session.doWork() - this method doesn't return Connection object, but inside what ever operation we want to do using connection object we can do. 

3) session.doReturningWork() - this method returns dynamic type value. In the below example i am return Connection object. 

4) SessionImpl.connection() - Down casting the Session object to SessionImpl. By using SessionImpl we will get the Connection object.

5) ConnectionProvider.getConnection() - Using ConnectionProvider getConnection() method.

Full Example:

package com.varasofttech.client;

import java.sql.Connection;
import java.sql.SQLException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.engine.jdbc.connections.spi.ConnectionProvider;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.internal.SessionImpl;
import org.hibernate.jdbc.ReturningWork;
import org.hibernate.jdbc.Work;

import com.varasofttech.util.HibernateUtil;

public class Application {
    
    public static void main(String[] args) {
        
        // Different ways to get the Connection object using Session
        
        SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
        Session session = sessionFactory.openSession();
        
        // Way1 - using doWork method
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                // do your work using connection
            }
            
        });
        
        // Way2 - using doReturningWork method
        Connection connection = session.doReturningWork(new ReturningWork<Connection>() {
            @Override
            public Connection execute(Connection conn) throws SQLException {
                return conn;
            }
        });
        
        // Way3 - using Session Impl
        SessionImpl sessionImpl = (SessionImpl) session;
        connection = sessionImpl.connection();
        // do your work using connection
        
        // Way4 - using connection provider
        SessionFactoryImplementor sessionFactoryImplementation = (SessionFactoryImplementor) session.getSessionFactory();
        ConnectionProvider connectionProvider = sessionFactoryImplementation.getConnectionProvider();
        try {
            connection = connectionProvider.getConnection();
            // do your work using connection
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Read More

CRUD operations using JDBC

CRUD(Create, Read, Update and Delete)

operations using JDBC

===============================================

CRUD stands for Create, Read, Update, Delete.

Student Table

CREATE TABLE STUDENT(StudentID NUMBER PRIMARY KEY, StudentName VARCHAR2(30), Age NUMBER(3));

Create Statement: 

INSERT INTO STUDENT(StudentID, StudentName, Age) VALUES(1, 'Ranga', 24);

READ Statement:

SELECT * FROM STUDENT WHERE StudentID = 1;

UPDATE Statement:

UPDATE STUDENT SET StudentName='RangaReddy', Age=25 WHERE StudentID =1

DELETE Statement:

DELETE FROM STUDENT WHERE StudentID = 1

Generating the Primary Key Values

In Oracle, by using Sequence we can generate the Automatic Primary key values

Creating a Sequence:

CREATE SEQUENCE Student_Id_Seq START WITH 1 INCREMENT BY 1;

By using sequence, insert the Student data

INSERT INTO STUDENT(StudentID, StudentName, Age) VALUES(Student_Id_Seq.nextval, 'Ranga', 25);

We can also get the Primary key values by using Max() function

int studentID = "SELECT MAX(STUDENTID) FROM STUDENT";

then adding 1 to the studentID

finally the studentID is "SELECT MAX(STUDENTID) FROM STUDENT" + 1

============================================================================================ 

Now we can see the CRUD operations by using JDBC 

Student.java
=================================================
package com.ranga;

import java.io.Serializable;

@SuppressWarnings("serial")
public class Student implements Serializable {
    private long studentId;
    private String studentName;
    private int age;
      
    public Student(long studentId, String studentName, int age) {
        super();
        this.studentId = studentId;
        this.studentName = studentName;
        this.age = age;
    }
    public Student() {
        super();
    }
    public long getStudentId() {
        return studentId;
    }
    public void setStudentId(long studentId) {
        this.studentId = studentId;
    }
    public String getStudentName() {
        return studentName;
    }
    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "Student [studentId=" + studentId + ", studentName="
                + studentName + ", age=" + age + "]";
    }  
}

-----------------------------------------------------------------------------------------------------------------------------------
DBUtil.java
 ==================================================
package com.ranga;

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

public class DBUtil {
    private static Connection con;
    private static String username ="ranga";
    private static String password = "ranga";
    private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
    private static String driverClass = "oracle.jdbc.driver.OracleDriver";
   
    static {
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
    }
   
    public static Connection getConnection() throws SQLException{                            
        return con = DriverManager.getConnection(url, username, password);
    }

    public static void beginTransaction(){
        if(con!=null){
            try {
                con.setAutoCommit(false);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public static void commit(){
        if(con!=null){
            try {
                con.commit();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public static void rollback(){
        if(con!=null){
            try {
                con.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public static void closeDBUtil(ResultSet rs, Statement stmt, Connection con){
        try {
            if(rs!=null) {
                rs.close();
                rs =null;
            }
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
       
        try {
            if(stmt!=null) {
                stmt.close();
                stmt =null;
            }
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
       
        try {
            if(con!=null) {
                con.close();
                con =null;
            }
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
       
    }    
}
--------------------------------------------------------------------------------------------------------------------------------

App1.java
=================================================

package com.ranga;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author ranga
 * @date 16-Jul-2013
 */

public class App1 {
    public static void main(String args[]) throws Exception {
        // Creating a Student object and setting the values
        Student st = new Student();
        st.setStudentName("Ranga");
        st.setAge(20);
        // inserting Student object and getting the studentId
        long studentId = createStudent(st);
        // fetching the Student object by using studentId
        Student student = getStudent(studentId);
        // displaying student data before update
        System.out.println("Before Update : " + student.toString());
        // updating the student object values
        student.setStudentName("RangaReddy");
        student.setAge(25);
        // updating the modified student
        updateStudent(student);
        student = getStudent(studentId);
        // displaying the updated data
        System.out.println("After Update : " + student);
        // deleting the student
        deleteStudent(studentId);
    }

    private static void deleteStudent(long studentId) throws SQLException {
        Connection con = DBUtil.getConnection();
        String sql = "DELETE FROM STUDENT WHERE StudentID = ?";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setLong(1, studentId);
        DBUtil.beginTransaction();
        int res = pstmt.executeUpdate();
        if (res != 0) {
            DBUtil.commit();
        } else {
            DBUtil.rollback();
        }
        DBUtil.closeDBUtil(null, pstmt, con);
    }

    private static void updateStudent(Student stu) throws SQLException {
        String sql = "UPDATE STUDENT SET StudentName=?, Age=? WHERE StudentID =?";
        Connection con = DBUtil.getConnection();
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setString(1, stu.getStudentName());
        pstmt.setInt(2, stu.getAge());
        pstmt.setLong(3, stu.getStudentId());
        DBUtil.beginTransaction();
        int result = pstmt.executeUpdate();
        if (result != 0) {
            DBUtil.commit();
        } else {
            DBUtil.rollback();
        }
        DBUtil.closeDBUtil(null, pstmt, con);
    }

    private static Student getStudent(long studentId) throws SQLException {
        String sql = "SELECT * FROM STUDENT WHERE StudentID = " + studentId;
        Connection con = DBUtil.getConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        DBUtil.beginTransaction();
        Student student = null;
        if (rs.next()) {
            student = new Student();
            student.setStudentId(rs.getLong(1));
            student.setStudentName(rs.getString(2));
            student.setAge(rs.getInt(3));
            DBUtil.commit();
        } else {
            DBUtil.rollback();
        }
        DBUtil.closeDBUtil(rs, stmt, con);
        return student;
    }

    private static long createStudent(Student stu) throws Exception {
        long studentId = generateStudentId() + 1;
        String sql = "INSERT INTO STUDENT(StudentID, StudentName, Age) VALUES(?, ?, ?)";
        Connection con = DBUtil.getConnection();
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setLong(1, studentId);
        pstmt.setString(2, stu.getStudentName());
        pstmt.setInt(3, stu.getAge());
        DBUtil.beginTransaction();
        int result = pstmt.executeUpdate();
        if (result != 0) {
            DBUtil.commit();
        } else {
            DBUtil.rollback();
        }
        DBUtil.closeDBUtil(null, pstmt, con);
        return studentId;
    }

    private static long generateStudentId() {
        String query = "SELECT MAX(StudentID) from STUDENT";
        Connection con = null;
        Statement stmt = null;
        long res = 0;
        try {
            con = DBUtil.getConnection();
            stmt = con.createStatement();
            res = stmt.executeUpdate(query);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        DBUtil.closeDBUtil(null, stmt, con);
        return res;
    }
}
---------------------------------------------------------------------------------------------------------------------------
Output:

Before Update : Student [studentId=2, studentName=Ranga, age=20]
After Update : Student [studentId=2, studentName=RangaReddy, age=25]

Download 

Read More