Creating and Calling Stored Procedure from Java

{question}

How to create and call stored procedures from JAVA?

{question}

{answer}

This topic describes how to create or call a stored procedure from Java code.

As SingleStore Database is wire compatible with MySQL, the below syntax is based off of MySQL, which works with the SingleStore Database.  For database connection, the MariaDB JDBC driver is being used.

Connection details:

Cluster Connection details needed to be updated in this section of the syntax,

String sdbUrl = "jdbc:mariadb://HOSTNAME:PORT/DBNAME";
Connection con = DriverManager.getConnection(sdbUrl, "username", "password");

Below syntax can get us started with creating a stored procedure from JAVA:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateStoredProcedureExamplee {
public static void main(String args[]) throws SQLException {

//Registering the Driver
DriverManager.registerDriver(new org.mariadb.jdbc.Driver());

//Getting the connection
String sdbUrl = "jdbc:mariadb://HOSTNAME:PORT/DBNAME";
Connection con = DriverManager.getConnection(sdbUrl, "root", "OUhrYURa0eEQ1bn7");
System.out.println("Connection established to SingleStore....");

//Creating the Statement
Statement stmt = con.createStatement();

//Query to create stored procedures
String query = "CREATE OR REPLACE PROCEDURE testing.thisworks() AS"
+ " BEGIN "
+ " ECHO SELECT 1; "
+ " END";
//Executing the query
stmt.execute(query);
System.out.println("Procedure Created......");
}
}

Below syntax can get us started with calling a stored procedure from JAVA:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CallngStoredProcedureExample {
public static void main(String args[]) throws SQLException {

//Registering the Driver
DriverManager.registerDriver(new org.mariadb.jdbc.Driver());

//Getting the connection
String sdbUrl = "jdbc:mariadb://HOSTNAME:PORT/DBNAME";
Connection con = DriverManager.getConnection(sdbUrl, "root", "password");
System.out.println("Connection established to SingleStore....");

//Preparing a CallableStatement to call the retrieveData procedure
CallableStatement cstmt = con.prepareCall("{call thisworks()}");

//Executing the CallableStatement
cstmt.execute();
cstmt.close();
System.out.println("Stored procedure called successfully!");
}
}

Click here to know more about how to connect with Java/JDBC

We have videos related to the topic:

Getting Started with SingleStore in JAVA

SingleStore Stored Procedures in JAVA

Associated topic:

JDBC Connector Setup Instructions with Optional GSSAPI

Click here to learn about how to connect with other application development tools.

{answer}