1. Program to fecth the Output from SQL server using the stored procedure without 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();
}
}
}
}
