1.MS SQL IntroductionMicrosoft 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 ConfigurationMS SQL Server
Management Studio ( MS SQL Server Client )
3. Database Schema SQL Statements3.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:
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 DependencyMaven 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 ConnectivityCreate connectionConnection 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 QueryCreate 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 QueryExecute 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)); <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 connectionSessionFactory sf = new Configuration().configure().buildSessionFactory();Session session = sf.openSession(); Execute Select Querypublic 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 statementpublic 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();
}
|
Articles >