MariaDB: Enable Java Stored Procedure

Question

I am trying to figure out how to use Java for writing stored procedures in MariaDB. There is a patch floating around stating that it is possible. I am unsure if this had become available standard.

If any of you have used Java to successfully write a stored procedure for MariaDB / MySQL please let me know.

Answer 1

I assume the question is about using Java as a language for stored procedures, rather than SQL. It it not possible. Yes there was some work by Antony Curtis, that would make this kind of stored procedures possible. Alas, it is not part of MySQL/MariaDB or any distribution. Thus you won't be able to use it, at least right now.

Link to Antony's presentation about using external languages with MySQL

Answer 2

Hej Martin,

i did this some month ago. I used a JDBC Connection to a MySQL Database, but MariaDB is the Drop-in replacement, so i think it will also work on a MariaDB Instance.

Here is my working example code:

JDBCConnection class

package de.professional_webworkx.blog.sp.connector;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.Driver;

public class JDBCConnector {

    public static JDBCConnector INSTANCE;

    /*
     * We need some connection information
     * CHANGE THEM!
     */
    private static final String USER    = "USER";
    private static final String PASS    = "PASS";
    private static final String HOST    = "localhost";
    private static final String DB      = "DATABASE";
    private static final int    PORT    = 3306;
    private static final String URL     = "jdbc:mysql://"+HOST+":"+PORT+"/"+DB;
    private Connection connection;

    private java.sql.PreparedStatement statement;

    private JDBCConnector() {
        connect();
    }

    private void connect() {

        try {
            Driver driver = (Driver)Class.forName("com.mysql.jdbc.Driver").newInstance();
            DriverManager.registerDriver(driver);
            connection = DriverManager.getConnection(URL, USER, PASS);
        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public void closeConnection() {
        if(connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                System.err.println("Something went wrong while closing the connection");
            }
        }
    }

    public void getCustomerCount() {
        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("SELECT COUNT(*) FROM customer;");
            while(rs != null && rs.next()){
                System.out.println(rs.getInt(1));
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void addStoredProcedure() {
        try {
            String sql = "create procedure myProc() "
                    + "BEGIN "
                    + "SELECT COUNT(*) as total FROM customer;"
                    + "END";
            statement = connection.prepareStatement(sql);
            statement.execute();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public static JDBCConnector getInstance() {
        if(INSTANCE == null) {
            INSTANCE = new JDBCConnector();
        }

        return INSTANCE;
    }
}

And start it:

package de.professional_webworkx.blog.sp;

import de.professional_webworkx.blog.sp.connector.JDBCConnector;


public class App 
{
    public static void main( String[] args )
    {
        JDBCConnector connector = JDBCConnector.getInstance();
        // create the Stored Procedure in your DB
        connector.addStoredProcedure();
    }
}

I also pushed this example code to GitHub