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
Download MS SQL Express Server from https://www.microsoft.com/en-in/sql-server/sql-server-editions-express
Install it by executing setup.exe file
Management Studio ( MS SQL Server Client )
Download database client from https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
Install it by executing setup.exe file
Connect and execute a query using Management Studio, refer https://docs.microsoft.com/en-us/sql/ssms/tutorials/connect-query-sql-server?view=sql-server-2017
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:
Open Microsoft SQL Management Studio
Connect to the database engine using database administrator credentials
Expand the server node
Right click Databases and select New Database
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(); }