MariaDB: Enabling Java Stored Procedure

Question

I am trying to figure out how to use Java to write stored procedures in MariaDB. There is a patch floating around, stating that it is possible. I am not sure if this has become standard.

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

Answer 1

I guess the question is about using Java as a stored procedure language, not SQL. It's impossible. Yes, Anthony Curtis did some work, and that would make such stored procedures possible. Alas, it is not part of MySQL / MariaDB or any distribution. Therefore, you cannot use it, at least right now.

Link to Antony presentation on using external languages ​​with MySQL

Answer 2

Hay Martin

I did it a month ago. I used a JDBC connection with the MySQL database, but MariaDB is a Drop-in replacement, so I think it will also work on the MariaDB instance.

Here is my working code example:

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 run 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 clicked this example code on GitHub