Program 1: Establishing a Connection with SQL Server
This program establishes a connection between Java and Microsoft SQL Server using JDBC.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class SQLServerConnectionExample {
public static void main(String[] args) {
String url =
"jdbc:sqlserver://localhost:1433;" +
"databaseName=jdbc_demo;" +
"encrypt=true;" +
"trustServerCertificate=true";
String username = "sa";
String password = "password";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
System.out.println(
"Connected to SQL Server successfully.");
con.close();
} catch (SQLException e) {
System.out.println(
"Connection failed: " +
e.getMessage());
}
}
}Execute and Output:
PS D:\test> javac -classpath ".;D:\test\mssql-jdbc.jar" SQLServerConnectionExample.java
PS D:\test> java -classpath ".;D:\test\mssql-jdbc.jar" SQLServerConnectionExample
Connected to SQL Server successfully.Code language: CSS (css)Program 2: Creating an Employee Table
This program creates an employee table in SQL Server using the JDBC Statement interface.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateSQLServerEmployeeTable {
public static void main(String[] args) {
String url =
"jdbc:sqlserver://localhost:1433;" +
"databaseName=jdbc_demo;" +
"encrypt=true;" +
"trustServerCertificate=true";
String username = "sa";
String password = "password";
String sql =
"IF NOT EXISTS (" +
"SELECT * FROM sysobjects " +
"WHERE name='employee' " +
"AND xtype='U') " +
"CREATE TABLE employee (" +
"employee_id INT PRIMARY KEY, " +
"employee_name VARCHAR(100), " +
"department VARCHAR(50), " +
"salary DECIMAL(10,2))";
try {
Connection con =
DriverManager.getConnection(
url, username, password);
Statement stmt =
con.createStatement();
stmt.executeUpdate(sql);
System.out.println(
"Employee table created successfully.");
stmt.close();
con.close();
} catch (SQLException e) {
System.out.println(
"Error: " + e.getMessage());
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mssql-jdbc.jar" CreateSQLServerEmployeeTable.java
PS D:\test> java -classpath ".;D:\test\mssql-jdbc.jar" CreateSQLServerEmployeeTable
Employee table created successfully.Code language: CSS (css)Program 3: Inserting Records Using PreparedStatement
This program inserts an employee record into SQL Server using a parameterized SQL query and PreparedStatement.
import java.sql.*;
public class SQLServerPreparedStatementExample {
public static void main(String[] args) {
String url =
"jdbc:sqlserver://localhost:1433;" +
"databaseName=jdbc_demo;" +
"encrypt=true;" +
"trustServerCertificate=true";
String sql =
"INSERT INTO employee " +
"(employee_id, employee_name, department, salary) " +
"VALUES (?, ?, ?, ?)";
try {
Connection con =
DriverManager.getConnection(
url, "sa", "password");
PreparedStatement ps =
con.prepareStatement(sql);
ps.setInt(1, 101);
ps.setString(2, "Ravi");
ps.setString(3, "IT");
ps.setDouble(4, 50000);
ps.executeUpdate();
System.out.println(
"Employee inserted successfully.");
ps.close();
con.close();
} catch (SQLException e) {
System.out.println(
"Error: " + e.getMessage());
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mssql-jdbc.jar" SQLServerPreparedStatementExample.java
PS D:\test> java -classpath ".;D:\test\mssql-jdbc.jar" SQLServerPreparedStatementExample
Employee inserted successfully.Code language: CSS (css)Program 4: Retrieving Records Using ResultSet
This program retrieves employee records from SQL Server using Statement and ResultSet.
import java.sql.*;
public class SQLServerResultSetExample {
public static void main(String[] args) {
String url =
"jdbc:sqlserver://localhost:1433;" +
"databaseName=jdbc_demo;" +
"encrypt=true;" +
"trustServerCertificate=true";
try {
Connection con =
DriverManager.getConnection(
url, "sa", "password");
Statement stmt =
con.createStatement();
ResultSet rs =
stmt.executeQuery(
"SELECT * FROM employee");
System.out.println(
"Employee Details");
System.out.println(
"-------------------------");
while (rs.next()) {
System.out.println(
rs.getInt("employee_id") + " " +
rs.getString("employee_name") + " " +
rs.getString("department") + " " +
rs.getDouble("salary"));
}
rs.close();
stmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mssql-jdbc.jar" SQLServerResultSetExample.java
PS D:\test> java -classpath ".;D:\test\mssql-jdbc.jar" SQLServerResultSetExample
Employee Details
-------------------------
101 Ravi IT 50000.0Code language: CSS (css)Program 5: Updating Records Using PreparedStatement
This program updates the salary of an employee using PreparedStatement.
import java.sql.*;
public class SQLServerUpdateExample {
public static void main(String[] args) {
String url =
"jdbc:sqlserver://localhost:1433;" +
"databaseName=jdbc_demo;" +
"encrypt=true;" +
"trustServerCertificate=true";
String sql =
"UPDATE employee " +
"SET salary = ? " +
"WHERE employee_id = ?";
try {
Connection con =
DriverManager.getConnection(
url, "sa", "password");
PreparedStatement ps =
con.prepareStatement(sql);
ps.setDouble(1, 60000);
ps.setInt(2, 101);
int rows =
ps.executeUpdate();
System.out.println(
rows + " record updated.");
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mssql-jdbc.jar" SQLServerUpdateExample.java
PS D:\test> java -classpath ".;D:\test\mssql-jdbc.jar" SQLServerUpdateExample
1 record updated.Code language: CSS (css)Program 6: Deleting Records Using PreparedStatement
This program deletes an employee record from SQL Server using a parameterized SQL statement.
import java.sql.*;
public class SQLServerDeleteExample {
public static void main(String[] args) {
String url =
"jdbc:sqlserver://localhost:1433;" +
"databaseName=jdbc_demo;" +
"encrypt=true;" +
"trustServerCertificate=true";
String sql =
"DELETE FROM employee " +
"WHERE employee_id = ?";
try {
Connection con =
DriverManager.getConnection(
url, "sa", "password");
PreparedStatement ps =
con.prepareStatement(sql);
ps.setInt(1, 101);
int rows =
ps.executeUpdate();
System.out.println(
rows + " record deleted.");
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mssql-jdbc.jar" SQLServerDeleteExample.java
PS D:\test> java -classpath ".;D:\test\mssql-jdbc.jar" SQLServerDeleteExample
1 record deleted.Code language: CSS (css)Program 7: Performing Batch Insert Using PreparedStatement
This program inserts multiple employee records into SQL Server using JDBC batch processing.
import java.sql.*;
public class SQLServerBatchExample {
public static void main(String[] args) {
String url =
"jdbc:sqlserver://localhost:1433;" +
"databaseName=jdbc_demo;" +
"encrypt=true;" +
"trustServerCertificate=true";
String sql =
"INSERT INTO employee " +
"(employee_id, employee_name, department, salary) " +
"VALUES (?, ?, ?, ?)";
try {
Connection con =
DriverManager.getConnection(
url, "sa", "password");
PreparedStatement ps =
con.prepareStatement(sql);
ps.setInt(1, 201);
ps.setString(2, "Arun");
ps.setString(3, "IT");
ps.setDouble(4, 45000);
ps.addBatch();
ps.setInt(1, 202);
ps.setString(2, "Priya");
ps.setString(3, "HR");
ps.setDouble(4, 42000);
ps.addBatch();
ps.setInt(1, 203);
ps.setString(2, "Kiran");
ps.setString(3, "Finance");
ps.setDouble(4, 48000);
ps.addBatch();
int[] result =
ps.executeBatch();
System.out.println(
result.length +
" records processed in batch.");
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mssql-jdbc.jar" SQLServerBatchExample.java
PS D:\test> java -classpath ".;D:\test\mssql-jdbc.jar" SQLServerBatchExample
3 records processed in batch.Code language: CSS (css)Program 8: Using Transactions with commit() and rollback()
This program performs multiple salary updates inside a SQL Server transaction. If the operations are successful, commit() permanently saves the changes. If an error occurs, rollback() cancels the transaction.
import java.sql.*;
public class SQLServerTransactionExample {
public static void main(String[] args) {
String url =
"jdbc:sqlserver://localhost:1433;" +
"databaseName=jdbc_demo;" +
"encrypt=true;" +
"trustServerCertificate=true";
Connection con = null;
try {
con =
DriverManager.getConnection(
url, "sa", "password");
con.setAutoCommit(false);
PreparedStatement ps =
con.prepareStatement(
"UPDATE employee " +
"SET salary = salary + ? " +
"WHERE employee_id = ?");
ps.setDouble(1, 5000);
ps.setInt(2, 201);
ps.executeUpdate();
ps.setDouble(1, 3000);
ps.setInt(2, 202);
ps.executeUpdate();
con.commit();
System.out.println(
"Transaction committed successfully.");
ps.close();
con.close();
} catch (SQLException e) {
System.out.println(
"Transaction failed.");
if (con != null) {
try {
con.rollback();
System.out.println(
"Transaction rolled back.");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mssql-jdbc.jar" SQLServerTransactionExample.java
PS D:\test> java -classpath ".;D:\test\mssql-jdbc.jar" SQLServerTransactionExample
Transaction committed successfully.Code language: CSS (css)Program 9: Using Savepoint in SQL Server Transaction
In SQL Server, a transaction savepoint is created using SAVE TRANSACTION. JDBC’s Savepoint interface can also be used. This program demonstrates the JDBC approach.
import java.sql.*;
public class SQLServerSavepointExample {
public static void main(String[] args) {
String url =
"jdbc:sqlserver://localhost:1433;" +
"databaseName=jdbc_demo;" +
"encrypt=true;" +
"trustServerCertificate=true";
try {
Connection con =
DriverManager.getConnection(
url, "sa", "password");
con.setAutoCommit(false);
Statement stmt =
con.createStatement();
// First update
stmt.executeUpdate(
"UPDATE employee " +
"SET salary = salary + 2000 " +
"WHERE employee_id = 201");
System.out.println(
"First update completed.");
// Create savepoint
Savepoint savepoint =
con.setSavepoint("AfterFirstUpdate");
System.out.println(
"Savepoint created.");
// Second update
stmt.executeUpdate(
"UPDATE employee " +
"SET salary = salary + 5000 " +
"WHERE employee_id = 202");
System.out.println(
"Second update completed.");
// Roll back to savepoint
con.rollback(savepoint);
System.out.println(
"Rolled back to savepoint.");
// Commit remaining changes
con.commit();
System.out.println(
"Transaction committed.");
stmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mssql-jdbc.jar" SQLServerSavepointExample.java
PS D:\test> java -classpath ".;D:\test\mssql-jdbc.jar" SQLServerSavepointExample
First update completed.
Savepoint created.
Second update completed.
Rolled back to savepoint.
Transaction committed.Code language: CSS (css)Program 10: Executing a Stored Procedure Using CallableStatement
SQL Server provides strong support for stored procedures. This program creates a stored procedure with an IN parameter and an OUT parameter and executes it using JDBC CallableStatement.
Create the Stored Procedure
Execute this SQL in SQL Server Management Studio (SSMS):
USE jdbc_demo;
GO
CREATE OR ALTER PROCEDURE get_employee_name
@employee_id INT,
@employee_name VARCHAR(100) OUTPUT
AS
BEGIN
SELECT @employee_name = employee_name
FROM employee
WHERE employee_id = @employee_id;
END;
GOCode language: PHP (php)import java.sql.*;
public class SQLServerCallableStatementExample {
public static void main(String[] args) {
String url =
"jdbc:sqlserver://localhost:1433;" +
"databaseName=jdbc_demo;" +
"encrypt=true;" +
"trustServerCertificate=true";
try {
Connection con =
DriverManager.getConnection(
url, "sa", "password");
CallableStatement cs =
con.prepareCall(
"{call get_employee_name(?, ?)}");
// IN parameter
cs.setInt(1, 201);
// OUT parameter
cs.registerOutParameter(
2, Types.VARCHAR);
// Execute procedure
cs.execute();
// Retrieve OUT parameter
String employeeName =
cs.getString(2);
System.out.println(
"Employee ID: 201");
System.out.println(
"Employee Name: " +
employeeName);
cs.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mssql-jdbc.jar" SQLServerCallableStatementExample.java
PS D:\test> java -classpath ".;D:\test\mssql-jdbc.jar" SQLServerCallableStatementExample
Employee ID: 201
Employee Name: ArunCode language: CSS (css)