Call mysql user define function using JAVA

mysql function :-

DELIMITER $$

DROP FUNCTION IF EXISTS `country`.`test_function`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `test_function`() RETURNS int(11)
BEGIN
    declare total int;

    select count(*) into total from country;

    return total;
    END$$

DELIMITER ;



java code :-

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Types;

public class MySqlFunction {

    static Connection connection = null;
    static CallableStatement pstat = null;
    static String connectionURL = "jdbc:mysql://localhost:3306/country";
   
    public static void main(String[] args) {
        try{
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(connectionURL,"root","dreams");
        pstat = connection.prepareCall("{ ? = call test_function()}");
        int count = 0;
        pstat.registerOutParameter(1, Types.INTEGER);
        pstat.execute();
        count = pstat.getInt(1);
        System.out.println(count);
              
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}

Learn more java tutorial and java blog visit : Visions Developer

Call Mysql procedure from JAVA

mysql procedure :-

DELIMITER $$

DROP PROCEDURE IF EXISTS `country`.`test_country`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_country`(
        IN  p_id                           INT(11)       ,
        IN  p_country_name                      VARCHAR(30)
     )
BEGIN
    INSERT INTO country
    (id,name)
    VALUES
         (
           p_id,
           p_country_name
         ) ;
    END$$

DELIMITER ;


java code :

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;


public class MySqlProcedure {

    static Connection connection = null;
    static CallableStatement pstat = null;
    static String connectionURL = "jdbc:mysql://localhost:3306/country";
   
    public static void main(String[] args) {
        try{
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(connectionURL,"root","dreams");
        pstat = connection.prepareCall("{ call test_country(?,?)}"); // test_country is procedure name
        pstat.setInt(1, 5);
        pstat.setString(2, "test");
        pstat.executeUpdate();
       
        }catch(Exception e){
            e.printStackTrace();
        }

    }

}

Learn more java tutorial and java blog visit : Visions Developer