Java Tutorial/Database/StoredProcedure

Call a function with one IN/OUT parameter; the function returns a VARCHAR

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{? = call myfuncinout(?)}");
    
    // Register the types of the return value and OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);
    cs.registerOutParameter(2, Types.VARCHAR);
    // Set the value for the IN/OUT parameter
    cs.setString(2, "a string");
    // Execute and retrieve the returned values
    cs.execute();
    String retValue = cs.getString(1);           // return value
    String outParam = cs.getString(2);           // IN/OUT parameter
  }
}

Call a function with one IN parameter; the function returns a VARCHAR

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{? = call myfuncin(?)}");
    cs.registerOutParameter(1, Types.VARCHAR);
    // Set the value for the IN parameter
    cs.setString(2, "a string");
    // Execute and retrieve the returned value
    cs.execute();
    String retValue = cs.getString(1);
  }
}

Call a function with one OUT parameter; the function returns a VARCHAR

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{? = call myfuncout(?)}");
    // Register the types of the return value and OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);
    cs.registerOutParameter(2, Types.VARCHAR);
    // Execute and retrieve the returned values
    cs.execute();
    String retValue = cs.getString(1); // return value
    String outParam = cs.getString(2); // OUT parameter
  }
}

Call a procedure with one IN/OUT parameter

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{call myprocinout(?)}");
    
    // Register the type of the IN/OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);
    // Set the value for the IN/OUT parameter
    cs.setString(1, "a string");
    // Execute the stored procedure and retrieve the IN/OUT value
    cs.execute();
    String outParam = cs.getString(1);            // OUT parameter
    System.out.println(outParam);
  }
}

Call a procedure with one IN parameter

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    // Call a procedure with no parameters
    CallableStatement cs = connection.prepareCall("{call myprocin(?)}");
   
    // Set the value for the IN parameter
    cs.setString(1, "a string");
    cs.execute();
  }
}

Call a procedure with one OUT parameter

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{call myprocout(?)}");
    
    // Register the type of the OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);
    cs.execute();
    String outParam = cs.getString(1);     // OUT parameter
    System.out.println(outParam);
  }
}

Calling a Function in a Database: call functions with IN, OUT, and IN/OUT parameters.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    CallableStatement cs = connection.prepareCall("{? = call myfunc}");
    // Register the type of the return value
    int intValue = 0;
    cs.registerOutParameter(1, intValue);
    // Execute and retrieve the returned value
    cs.execute();
    String retValue = cs.getString(1);
  }
}

Getting the Stored Procedure Names in a Database: retrieves the names of all stored procedures in a database.

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    DatabaseMetaData dbmd = connection.getMetaData();
    // Get all stored procedures in any schema and catalog
    ResultSet resultSet = dbmd.getProcedures(null, null, "%");
    // Get stored procedure names from the result set
    while (resultSet.next()) {
        String procName = resultSet.getString(3);
        System.out.println(procName);
    }
  }
}

Stored procedure with Input/Output parms and a ResultSet

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class Main {
  public static void main(String[] argv) throws Exception {
  }
  public static int storedProcWithResultSet() throws Exception {
    Connection conn = null;
    CallableStatement cs = conn.prepareCall("{? = call proc (?,?,?,?,?,?,?)}");
    // register input parameters
    cs.setString(2, "");
    cs.setString(3, "");
    cs.setString(4, "123");
    // regsiter ouput parameters
    cs.registerOutParameter(5, java.sql.Types.CHAR);
    cs.registerOutParameter(6, java.sql.Types.CHAR);
    cs.registerOutParameter(7, java.sql.Types.CHAR);
    // Procedure execution
    ResultSet rs = cs.executeQuery();
    ResultSetMetaData rsmd = rs.getMetaData();
    int nbCol = rsmd.getColumnCount();
    while (rs.next()) {
      for (int i = 1; i <= nbCol; i++) {
        System.out.println(rs.getString(i));
        System.out.println(rs.getString(i));
      }
    }
    // OUTPUT parameters
    System.out.println("return code of Stored procedure = : " + cs.getInt(1));
    for (int i = 5; i <= 7; i++)
      System.out.println("parameter " + i + " : " + cs.getString(i));
    return cs.getInt(1);
  }
}