Java Program to connect SQL server and fetch results from database using stored procedure

1. Program to fecth the Output from SQL server using the stored procedure without parameter

2. Program to fecth the Output from SQL server using the stored procedure with parameter

package SqlOutput;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Scanner;

public class SQLServerConnection {

public static void main(String[] args) {

// Connection URL

String connectionUrl = "jdbc:sqlserver://myserver;"

+ "trustServerCertificate=true;"

+ "IntegratedSecurity=true;"

+ "Database=Stocks;"

+ "loginTimeout=30;";

Scanner scanner = new Scanner(System.in);

// Prompt the user for input

System.out.print("Enter Stock Name: ");

String name = scanner.nextLine();

System.out.println("Stock Name you entered is : " + name +" ");

//System.out.print("Enter your age: ");

//int age = scanner.nextInt();

// Display the input back to the user

// System.out.println("Hello, " + name + "! You are " + age + " years old.");

// Establish the connection

try (Connection connection = DriverManager.getConnection(connectionUrl))

{

// System.out.println("Connected to the SQL Server database successfully.");

CallableStatement stmt = connection.prepareCall("{call sp_StockInfo(?)}");

stmt.setString(1, name);

// Execute the stored procedure

ResultSet rs = stmt.executeQuery();

// Process the result set

while (rs.next()) {

System.out.println("Name: " + rs.getString("Name")+ " Quantity: " + rs.getString("Quantity"));

}

}

catch (SQLException e)

{

e.printStackTrace();

}

}

}

3. Program to fecth the multi results Output from SQL server using the stored procedure with parameters

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.util.Scanner;

public class SQLServerConnection {

public static void main(String[] args) {

// Connection URL

String connectionUrl = "jdbc:sqlserver://DESKTOP-H8VE8AJ;"

+ "trustServerCertificate=true;"

+ "IntegratedSecurity=true;"

+ "Database=Stocks;"

+ "loginTimeout=30;";

try

(

Scanner scanner = new Scanner(System.in)

)

{

// Prompt the user for input

System.out.print("Enter Stock Name: ");

String name = scanner.nextLine();

System.out.println("Stock Name you entered is : " + name +" ");

// Establish the connection

try (Connection connection = DriverManager.getConnection(connectionUrl))

{

//System.out.println("Connected to the SQL Server database successfully.");

CallableStatement stmt = connection.prepareCall("{call sp_Stock_Details(?)}");

stmt.setString(1, name);

// Execute the stored procedure

Boolean results = stmt.execute();

while (results) {

ResultSet rs = stmt.getResultSet();

ResultSetMetaData rsmd = rs.getMetaData();

int columnCount = rsmd.getColumnCount();

while (rs.next()) {

for (int i = 1; i <= columnCount; i++) {

String columnName = rsmd.getColumnName(i);

Object columnValue = rs.getObject(i);

System.out.println(columnName + ": " + columnValue);

}

}

results = stmt.getMoreResults();

}

}

catch (SQLException e)

{

e.printStackTrace();

}

}

}

}