ตอนที่ 21 : Java เรียกใช้ Call MySQL Stored Procedure ด้วย (JDBC)

มิถุนายน 13, 2018 2:57 am โดย admin
0
29

ตอนที่ 21 : Java เรียกใช้ Call MySQL Stored Procedure ด้วย (JDBC) ในหัวข้อนี้เราจะมาเรียนรู้วิธีการใช้ Java Application เรียกใช้งาน Stored Procedure ของ MySQL Database ด้วยชุดคำสั่งของ JDBC (com.mysql.jdbc.Driver) ซึ่งในการเรียกใช้งาน Stored Procedure ด้วย JDBC เราสามารถใช้คำสั่ง CALL procedure_name() ได้ทันที ถ้าหากมีค่า Parameters ก็จะต้องทำการ Pass ค่าไปกับ Query ด้วย สามารถประยกุต์ใช้ได้ทั้งแบบการ Query ด้วย Prepare Statement โดยทั้ง 2 วิธีสามารถใช้ได้เหมือนกัน และหลังจากที่ CALL แล้วก็สามารถที่จะอ่าน Result ที่ Stored Procedure นั้นได้ส่งกลับมาให้ สามารถประยุกต์ใช้กับ Java ทุก ๆ รุปแบบ ไม่ว่าจะเป็น JSP , Java GUI หรือ Application ในรุปแบบอื่น ๆ ที่ใช้ Library ของ JDBC (com.mysql.jdbc.Driver)

Call MySQL Stored Procedure

CALL procedure_name(agr1,agr2,...)

โครงสร้างของตาราง customer

Table : customer

Stored Procedure บน MySQL

Example 1 : การใช้ Java เรียก Stored Procedure แบบ Query ข้อมูลออกมา

Stored Procedure Name : getCustomer()

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2))
BEGIN
   SELECT * FROM customer WHERE COUNTRY_CODE = pCountryCode;
END //
DELIMITER ;

Code (Java)

package com.java.myapp;

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

public class MyClass {

	public static void main(String[] args) {
		
		Connection connect = null;
		Statement s = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect =  DriverManager.getConnection("jdbc:mysql://localhost/mydatabase" +
					"?user=root&password=root");
			
			s = connect.createStatement();
			
			String CountryCode = "US";
			
			String sql = "CALL getCustomer('" + CountryCode + "') ";
			
			ResultSet rec = s.executeQuery(sql);
			
			while((rec!=null) && (rec.next()))
            {
                System.out.print(rec.getString("CUSTOMER_ID"));
                System.out.print(" - ");
                System.out.print(rec.getString("NAME"));
                System.out.print(" - ");
                System.out.print(rec.getString("EMAIL"));
                System.out.print(" - ");
                System.out.print(rec.getString("COUNTRY_CODE"));
                System.out.print(" - ");
                System.out.print(rec.getFloat("BUDGET"));
                System.out.print(" - ");
                System.out.print(rec.getFloat("Used"));
                System.out.println("");
            }
             
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// Close
		try {
			if(connect != null){
				s.close();
				connect.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
}

Result
Java Stored Procedure MySQL
หรือจะใช้แบบ Parameter Query หรือ Prepare Statement ก็ได้เช่นเดียวกัน

Code (Java)

package com.java.myapp;

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

public class MyClass {

	public static void main(String[] args) {
		
		Connection connect = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect =  DriverManager.getConnection("jdbc:mysql://localhost/mydatabase" +
					"?user=root&password=root");
		
			// Stored Procedure
			String command = "CALL getCustomer(?)";
			CallableStatement stmt = connect.prepareCall (command);
			
			// Parameters
			String CountryCode = "US";
			stmt.setString(1, CountryCode);  // IN
			
			// Execute
			ResultSet rec = stmt.executeQuery();
			while((rec!=null) && (rec.next()))
            {
                System.out.print(rec.getString("CUSTOMER_ID"));
                System.out.print(" - ");
                System.out.print(rec.getString("NAME"));
                System.out.print(" - ");
                System.out.print(rec.getString("EMAIL"));
                System.out.print(" - ");
                System.out.print(rec.getString("COUNTRY_CODE"));
                System.out.print(" - ");
                System.out.print(rec.getFloat("BUDGET"));
                System.out.print(" - ");
                System.out.print(rec.getFloat("USED"));
                System.out.println("");
            }
	
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
            connect.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
}

Example 2 : การใช้ Java เรียก Stored Procedure แบบ Query เพื่อ Insert ข้อมูล

Stored Procedure Name : insertCustomer()

DROP PROCEDURE IF EXISTS insertCustomer;

DELIMITER //
CREATE PROCEDURE insertCustomer(IN pCustomerID VARCHAR(4),
	IN pName VARCHAR(150),
	IN pEmail VARCHAR(150),
	IN pCountryCode VARCHAR(2),
	IN pBudget DECIMAL(18,2),
	IN pUsed DECIMAL(18,2))
BEGIN
   INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
	VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
END //
DELIMITER ;

Code (Java)

package com.java.myapp;

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

public class MyClass {

	public static void main(String[] args) {
		
		Connection connect = null;
		Statement s = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect =  DriverManager.getConnection("jdbc:mysql://localhost/mydatabase" +
					"?user=root&password=root");
			
			s = connect.createStatement();

			String strCustomerID = "C005";
			String strName = "Fun Wipa";
			String strEmail = "fun.wipa@thaicreate.com";
			String strCountryCode = "TH";
			Double dBudget = 1000000.00;
			Double dUsed = 0.00;
			
			String sql = "CALL insertCustomer('"+strCustomerID+"', '"+strName+"', '"+strEmail+"', '"+strCountryCode+"', '"+dBudget+"', '"+dUsed+"') ";
             s.execute(sql);
            
             System.out.println("Record Inserted Successfully");
             
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// Close
		try {
			if(connect != null){
				s.close();
				connect.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
}

หรือจะใช้แบบ Parameter Query หรือ Prepare Statement ก็ได้เช่นเดียวกัน

Code (Java)

package com.java.myapp;

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

public class MyClass {

	public static void main(String[] args) {

		Connection connect = null;
		PreparedStatement pre = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect =  DriverManager.getConnection("jdbc:mysql://localhost/mydatabase" +
					"?user=root&password=root");
			
			String sCustomerID = "C005";
			String sName = "Fun Wipa";
			String sEmail = "fun.wipa@thaicreate.com";
			String sCountryCode = "TH";
			Double dBudget = 1000000.00;
			Double dUsed = 0.00;

			String sql = "CALL insertCustomer(?,?,?,?,?,?) ";
			pre = connect.prepareStatement(sql);
			pre.setString(1, sCustomerID);
			pre.setString(2, sName);
			pre.setString(3, sEmail);
			pre.setString(4, sCountryCode);
			pre.setDouble(5, dBudget);
			pre.setDouble(6, dUsed);
			
			pre.executeUpdate();
			
            System.out.println("Record Inserted Successfully");
             
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// Close
		try {
			if(connect != null){
				pre.close();
				connect.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		
	}
	
}

ส่วนวิธีการ UPDATE และ DELETE ก็ใช้หลักการเดียวกับการ INSERT ข้อมูล

สำหรับตัวอย่างที่ 3 และ 4 จะเป็นการอ่านจาก OUTPUT หรือ OUT
ตอนที่ 9 : การใช้พารามิเตอร์ชนิด OUT เพื่อส่งค่ากลับ (MySQL : Stored Procedure)

Example 3 : การใช้ Java เรียก Stored Procedure และอ่านค่า OUTPUT พร้อมกับ SELECT ข้อมูล

Stored Procedure Name : getCustomer()

DROP PROCEDURE IF EXISTS getCustomer;

DELIMITER //
CREATE PROCEDURE getCustomer(IN pCountryCode VARCHAR(2),
	OUT pRowFound INT)
BEGIN

	# Return SELECT
	SELECT * FROM customer WHERE COUNTRY_CODE = sCountryCode;
	
	# Return  pRowFound
	SET pRowFound  = FOUND_ROWS();
        
END //

DELIMITER ;

Code (Java)

package com.java.myapp;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class MyClass {

	public static void main(String[] args) {
		
		Connection connect = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect =  DriverManager.getConnection("jdbc:mysql://localhost/mydatabase" +
					"?user=root&password=root");
		
			// Stored Procedure
			String command = "CALL getCustomer(?,?)"; 
			CallableStatement stmt = connect.prepareCall (command);
			
			// Parameters
			String CountryCode = "US";
			stmt.setString(1, CountryCode);  // IN
			stmt.registerOutParameter(2, Types.INTEGER); // OUT 		
				
			// Execute
			ResultSet rec  = (ResultSet) stmt.executeQuery();
			
			// Get return SELECT
			while((rec!=null) && (rec.next()))
			{
				System.out.print(rec.getString("CUSTOMER_ID"));
				System.out.print(" - ");
				System.out.print(rec.getString("NAME"));
				System.out.print(" - ");
				System.out.print(rec.getString("EMAIL"));
				System.out.print(" - ");
				System.out.print(rec.getString("COUNTRY_CODE"));
				System.out.print(" - ");
				System.out.print(rec.getFloat("BUDGET"));
				System.out.print(" - ");
				System.out.print(rec.getFloat("Used"));
				System.out.println("");
			}
			
			// Get return parameters (pRowFound NUMBER)
			Integer pRowFound = stmt.getInt(2);			
			System.out.println("pRowFound : " + pRowFound.toString());
	
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			connect.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
}

Java Stored Procedure MySQL

จากตัวอย่างนี้จะ Return ค่าทั้ง SELECT และ Parameters ที่เป็น OUT

Example 4 : การใช้ Java เรียก Stored Procedure และอ่านค่า OUTPUT ที่มีตั้งแต่ 2 ค่าขึ้นไป

Stored Procedure Name : insertCustomer()

DROP PROCEDURE IF EXISTS insertCustomer;

DELIMITER //
CREATE PROCEDURE insertCustomer(IN pCustomerID VARCHAR(4),
	IN pName VARCHAR(150),
	IN pEmail VARCHAR(150),
	IN pCountryCode VARCHAR(2),
	IN pBudget DECIMAL(18,2),
	IN pUsed DECIMAL(18,2),
	OUT pResult INT,
	OUT pMessage VARCHAR(500))
BEGIN
	
	# Declare Variable
	DECLARE errCode CHAR(5) DEFAULT '00000';
	DECLARE errMsg TEXT;
	DECLARE effRows INT;

	# Declare Handler Exception
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	    BEGIN
	      GET DIAGNOSTICS CONDITION 1
		errCode = RETURNED_SQLSTATE, errMsg = MESSAGE_TEXT;
	    END;

	# Statement 
	INSERT INTO customer (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
		VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);

	# Set Result 
	IF errCode = '00000' THEN
		GET DIAGNOSTICS effRows = ROW_COUNT;
		SET pResult  = effRows;
		SET pMessage = 'Insert Data Successfully';
	ELSE
		SET pResult  = 0;
		SET pMessage = CONCAT('Error, Code = ',errCode,', Message = ',errMsg);
	END IF;

END //
DELIMITER ;

Code (Java)

package com.java.myapp;

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

public class MyClass {

	public static void main(String[] args) {
		
		Connection connect = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect =  DriverManager.getConnection("jdbc:mysql://localhost/mydatabase" +
					"?user=root&password=root");
			
			// Stored Procedure
			String command = "CALL insertCustomer(?,?,?,?,?,?,?,?)"; 
			CallableStatement stmt = connect.prepareCall (command);
			
			// Parameters
			String strCustomerID = "C005";
			String strName = "Fun Wipa";
			String strEmail = "fun.wipa@thaicreate.com";
			String strCountryCode = "TH";
			Double dBudget = 100000.00;
			Double dUsed = 0.00;
            
			stmt.setString(1, strCustomerID);  // IN
			stmt.setString(2, strName);  // IN
			stmt.setString(3, strEmail);  // IN
			stmt.setString(4, strCountryCode);  // IN
			stmt.setDouble(5, dBudget);  // IN
			stmt.setDouble(6, dUsed);  // IN
			stmt.registerOutParameter(7, Types.INTEGER); // OUT 		
			stmt.registerOutParameter(8, Types.VARCHAR); // OUT
				
			// Execute
			stmt.execute();
			
			// Get return parameters (pResult NUMBER)
			Integer pResult = stmt.getInt(7);			
			System.out.println("pResult : " + pResult.toString());

			// Get return parameters (pMessage VARCHAR2)
			String pMessage = stmt.getString(8);			
			System.out.println("pMessage : " + pMessage);
	
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			connect.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
}

Java Stored Procedure MySQL

กรณีที่ Insert ข้อมูลสำเร็จ

Java Stored Procedure MySQL

กรณีที่ Insert ข้อมูลไม่สำเร็จ

แหล่งที่มา : thaicreate.com/tutorial