Articles‎ > ‎

MS SQL Server - JDBC/Hibernate connectivity

1.MS SQL Introduction 

Microsoft SQL Server is a relational database management system ( RDBMS ), It supports RDBMS normalization rules, tabular data management, transaction processing, business intelligence and analytics applications for enterprise applications. It is one of the leading database along with Oracle Database and IBM's DB2. 

SQL Server 2017 Express edition
Microsoft provides SQL Server 2017 Express edition for free. You can build small, data-driven web and mobile applications up to 10 GB in size with this free, entry-level database.

Management Studio 
It is also known as SQL Server Management Studio (SSMS) is a database client. It is used to access, configure, manage and administer MS SQL Server. 

2. Installation and Configuration

MS SQL Server 
  1. Download MS SQL Express Server from https://www.microsoft.com/en-in/sql-server/sql-server-editions-express 
  2. Install it by executing setup.exe file
Management Studio ( MS SQL Server Client )

3. Database Schema SQL Statements  


3.1 Create Database

You can create multiple databases in MS SQL Server. Database contains Tables, Views, Indexes, Stored Procedures, Stored Functions, Triggers etc. Generally one application has one database. Before start developing your application you must create a database.

You can create a database from Management Studio  by following steps:
  1. Open Microsoft SQL Management Studio
  2. Connect to the database engine using database administrator credentials
  3. Expand the server node
  4. Right click Databases and select New Database
  5. Enter a database name and click OK to create the database
  
3.2 Create Table 

Table is created using CREATE DDL SQL statement.

CREATE TABLE Marksheet ( ROLLNO int, NAME varchar2(50),PRIMARY KEY (ROLLNO),   PHYSICS int,CHEMISTRY int,MATHS int);  

3.3 Alter Table 

Columns and indexes of a table can be altered by ALTER DDL SQL statement.

An index is a pointer to the table, index is used to search records from table. One table may contain one or more indexes.  One Index may contain one or more columns.

For example add new column to a table:

ALTER TABLE Marksheet ADD MOBILE varchar(50);   

For example alter a column in a  table:

ALTER TABLE Marksheet ALTER COLUMN MOBILE int (50);   

3.4 Delete Table

Table is deleted using DELETE DDL SQL statement.

DELETE TABLE Marksheet 


3.5 Create View

A view is virtual table that is created by a query, query may be a join query  of two or more tables.  Just like a regular table you can execute a SELECT statement on view. View is used to simplify data retrieval from complex join queries for critical reports.

create view  STUDENT_DEPARTMENT 
as SELECT ID , FIRST_NAME , LAST_NAME , MOBILE_NUMBER , LOGIN_ID , PASSWORD , ADDRESS , DEPARTMENT_NAME 
from STUDENT join DEPARTMENT on STUDENT.DEPARTMENT_ID=DEPARTMENT.DEP_ID

//execute a query on view
SELECT * FROM STUDENT_DEPARTMENT ;

3.6 Create Stored Procedure 

Procedure contains variables, parameters, control statements, SQL statements and exception handling.  Since procedure is stored in database that is why it is called stored-procedure. You may write custom logic inside a procedure to manipulate database and extract data from database.

Here is procedure to get count of total number of users from USER  table.


CREATE PROCEDURE (@count AS INT)  user_count AS 
BEGIN 
  SELECT * FROM STUDENT
END 


You can execute a stored procedure from management studio using EXEC statement 

EXEC user_count 


3.7 Create Stored Function 

Function  contains variables, parameters, control statements, SQL statements and exception handling. A function must return a value.  You may write custom logic inside a function to manipulate database and extract data from database.

Here is function that returns sum to two numbers .

CREATE FUNCTION sum(@a AS INT , @b AS INT) RETURNS INT AS 
BEGIN 
  RETURN (@a+@b) 
END;

You can use a function in a SQL statement.

SELECT sum();


3.8 Exception Handling in Stored Procedure / Function 

Exception can be handled in MS SQL Server stored procedure and function using TRY an CATCH block.  

Here is example to show error handing

DECLARE @a int;  
DECLARE @b int;  
BEGIN TRY  
  SET @a=8;  
  SET @b=@a/0;  /* exception will be raised when number is divided by ZERO  */  
END TRY  
BEGIN CATCH  
  Print Error_Message()  
END CATCH 

4. Maven Dependency 

Maven project require following dependencies in POM.XML file for MS SQL JDBC database driver and Hibernate libraries:


<!-- MS SQL Server driver dependency -->
<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
   <artifactId>mssql-jdbc</artifactId>
  <version>7.0.0.jre8</version>
</dependency>

<!-- Hibernate dependency --> 
<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-core</artifactId>
  <version>3.3.2.GA</version>
</dependency>


5. JDBC Connectivity 

Create connection

Connection can be created by DriverManager.getConnection() method. Use database url  "jdbc:sqlserver://localhost:1433;databaseName=Demo;integratedSecurity=true" to connect with server and get connection.

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // Load driver
Connection conn = DriverManager.getConnection( "jdbc:sqlserver://localhost:1433;databaseName=Demo;integratedSecurity=true" ); //create connection 
PreparedStatement ps = conn.prepareStatement("select max(ID) from student"); //Create statement


Execute Select Query 

Create a statement and execute select query. 

PreparedStatement ps = conn.prepareStatement("select * from student");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
System.out.println(rs.getString(4));
System.out.println(rs.getString(5));
System.out.println(rs.getString(6));
System.out.println(rs.getString(7));
}


Execute INSERT/UPDATE/DELETE Query 

Execute INSERT/UPDATE/DELETE statement using Statement or PreparedStatement .

PreparedStatement ps = conn.prepareStatement("insert into student(ID,FIRST_NAME,LAST_NAME,MOBILE_NUMBER,LOGIN_ID,PASSWORD,ADDRESS)values(?,?,?,?,?,?,?)");
ps.setInt(1, nextPK());
ps.setString(2, bean.getFirst_Name());
ps.setString(3, bean.getLast_Name());
ps.setString(4, bean.getMobile_Number());
ps.setString(5, bean.getLogin_ID());
ps.setString(6, bean.getPassword());
ps.setString(7, bean.getAddress());
int i = ps.executeUpdate(); //Execute query 
System.out.println(i);

//Update query 
PreparedStatement ps = conn.prepareStatement("update student set FIRST_NAME=?,LAST_NAME=?,MOBILE_NUMBER=?,LOGIN_ID=?,PASSWORD=?,ADDRESS=? where ID=?");

//Delete query 
PreparedStatement ps = conn.prepareStatement("delete from student where ID=?");


Execute Stored Procedure and Stored Function

//Stored Procedure
String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://localhost:1433;databaseName=Demo;integratedSecurity=true";

Class.forName(driver);
Connection conn = DriverManager.getConnection(url);
CallableStatement cs = conn.prepareCall("{CALL studTest(?)}");
cs.registerOutParameter(1,Types.INTEGER);
cs.execute();
System.out.println(cs.getInt(1));

//Stored Function
Class.forName(driver);
Connection conn = DriverManager.getConnection(url);
CallableStatement cs = conn.prepareCall("{?=CALL search()}");
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println(cs.getInt(1));

6. Hibernate connectivity  

Configuration ( Hibernate.cfg.xml ) 

<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.url">jdbc:sqlserver://localhost:1433;databaseName=Demo;integratedSecurity=true</property>
<property name="hibernate.connection.driver_class"> com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
<property name="hibernate.connection.pool_size">40</property>
<property name="hibernate.connection.autocommit">false</property>
<property name="hibernate.hbm2ddl.auto">update</property>
<property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
</session-factory>


Create connection

SessionFactory sf = new Configuration().configure().buildSessionFactory();
Session session = sf.openSession();

Execute Select Query

public User get(long pk) throws Exception {
  SessionFactory sf = new Configuration().configure().buildSessionFactory();
  Session session = sf.openSession();
  User u = session.get(User.class,pk);
  session.close();
  return u;
}

Execute INSERT/UPDATE/DELETE Query

//Insert statement
public void add(User bean) throws Exception {
  SessionFactory sf = new Configuration().configure().buildSessionFactory();
  Session session = sf.openSession();
  Transaction tx = session.beginTransaction();
  session.save(bean);
  tx.commit();
  session.close();
}

//Update statement 
public void update(User bean) throws Exception {
    SessionFactory sf = new Configuration().configure().buildSessionFactory();
    Session session = sf.openSession();
    Transaction tx = session.beginTransaction();
    session.update(bean);
    tx.commit();
    session.close();
}

//Delete statement
public void delete(User bean) throws Exception { SessionFactory sf = new Configuration().configure().buildSessionFactory(); Session session = sf.openSession(); Transaction tx = session.beginTransaction(); session.delete(bean); tx.commit(); session.close(); }






Comments