MySQLÖÐʹÓô洢¹ý³Ì(ÕûÀí)
MySQLÖÐʹÓô洢¹ý³Ì
ʹÓÃCallableStatementsÖ´Ðд洢¹ý³Ì
mysql°æ±¾:5.0
Connector/JµÄ°æ±¾:3.1.1ÒÔÉÏ(java.sql.CallableStatement½Ó¿ÚÒÑÍêȫʵÏÖ,³ýÁËgetParameterMetaData()·½·¨)
MySQLµÄ´æ´¢¹ý³ÌÓï·¨ÔÚMySQL²Î¿¼ÊÖ²áµÄ"´æ´¢¹ý³ÌºÍº¯Êý"Ò»ÕÂ.
http://www.mysql.com/doc/en/Stored_Procedures.html
ÏÂÃæÊÇÒ»¸ö´æ´¢¹ý³Ì,·µ»ØÒ»¸öinOutParamÔö1ºóµÄÖµ,ÒÔResultSetÐÎʽ´«ÈëÒ»¸ö×Ö·û´®²ÎÊýinputParam.
CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam INT)
BEGIN
DECLARE z INT;
SET z = inOutParam + 1;
SET inOutParam = z;
SELECT inputParam;
SELECT CONCAT('zyxw', inputParam);
END
Ҫͨ¹ýconnector/JʹÓÃdemoSpÕâ¸ö´æ´¢¹ý³Ì,Òª¾¹ý¼¸¸ö²½Öè:
1.Connection.prepareCall()
import java.sql.CallableStatement;
...
//
// Prepare a call to the stored procedure 'demoSp'
// with two parameters
//
// Notice the use of JDBC-escape syntax ({call ...})
//
CallableStatement cStmt = conn.prepareCall("{call demoSp(?, ?)}");
cStmt.setString(1, "abcdefg");
Connection.prepareCall()·½·¨·Ç³£ÏûºÄ×ÊÔ´,ÒòΪjdbcÇý¶¯Í¨¹ýÔªÊý¾Ý(metadata)µÄ»ñÈ¡Ö§³ÖÊä³ö²ÎÊý.³öÓÚÖ´ÐÐЧÂʵĿ¼ÂÇ,Ó¦¸Ã¾¡¿ÉÄܼõÉÙ²»±ØÒªµÄprepareCallµ÷ÓÃ,ÖØÓÃCallableStatement¶ÔÏó.
2.×¢²áÊä³ö²ÎÊý(Èç¹ûÓеĻ°)
ÒªµÃµ½Êä³ö²ÎÊýµÄÖµ(´´½¨´æ´¢¹ý³ÌʱÉèÖõÄOUTºÍINOUT),JDBCÒªÇóÕâЩ²ÎÊý±ØÐëÒªÔÚÊý¾Ý¿â²Ù×÷Ö´ÐÐ֮ǰͨ¹ýregisterOutputPrameter()·½·¨ÉèÖÃ.
import java.sql.Types;
...
//
// ÏÂÃæ¸ø³öÁËÉèÖÃÊä³ö²ÎÊýµÄ¼¸¸ö·½·¨
//
// ×¢²áµÚ¶þ¸ö²ÎÊýΪÊä³ö²ÎÊý
//
cStmt.registerOutParameter(2);
//
// ×¢²áµÚ¶þ¸ö²ÎÊýΪÊä³ö²ÎÊý,É趨getObjectµÃµ½µÄ·µ»ØÖµµÄÀàÐÍΪÕûÐÍ
//
cStmt.registerOutParameter(2, Types.INTEGER);
//
// ×¢²áÃûΪ"inOutParam"µÄ²ÎÊýΪÊä³ö²ÎÊý
//
cStmt.registerOutParameter("inOutParam");
//
// ×¢²áÃûΪ"inOutParam"µÄ²ÎÊýΪÊä³ö²ÎÊý,É趨getObjec
Ïà¹ØÎĵµ£º
1. Mysql Åàѵ
1.1. ÅàѵĿµÄ
±¾ÎĵµÊÇÕë¶ÔMySQL Êý¾Ý¿â·½ÃæµÄ»ù´¡Åàѵ£¬ÎªÁËʹÏîÄ¿×é³ÉÔ±Äܹ»´ïµ½Ê¹ÓÃMySQL Êý¾Ý¿âµÄÄ¿µÄ¡£
1.2. Åàѵ¶ÔÏó
¿ª·¢ÈËÔ±
1.3. ³£Óôʼ°·ûºÅ˵Ã÷
³£Óôʣº
Mysql£ºÒ»ÖÖÃâ·ÑµÄ¿çƽ̨µÄÊý¾Ý¿âϵͳ
E:\mysql£º±íʾÊÇÔÚdos ÃüÁî´°¿ÚÏÂÃæ
mysql> ±íʾÊÇÔÚmysql µÄÃüÁîÐÐÏÂ
1.4 ......
1016´íÎó£ºÎļþÎÞ·¨´ò¿ª£¬Ê¹Óúǫ́ÐÞ¸´»òÕßʹÓÃphpmyadmin½øÐÐÐÞ¸´¡£
1044´íÎó£ºÊý¾Ý¿âÓû§È¨ÏÞ²»×㣬ÇëÁªÏµ¿Õ¼äÉ̽â¾ö
1045´íÎó£ºÊý¾Ý¿â·þÎñÆ÷/Êý¾Ý¿âÓû§Ãû/Êý¾Ý¿âÃû/Êý¾Ý¿âÃÜÂë´íÎó£¬ÇëÁªÏµ¿Õ¼äÉ̼ì²éÕÊ»§¡£
1054´íÎ󣺳ÌÐòÎļþ¸úÊý¾Ý¿âÓгåÍ»£¬ÇëʹÓÃÕýÈ·µÄ³ÌÐòÎļþÉÏ´«ÉÏÈ¥¸²¸Ç¡£
1146´íÎó£ºÊý¾Ý±íȱʧ,Çë»Ö ......
×î½üʹÓÃrootÓû§±àдÁ˼¸¸ö´æ´¢¹ý³Ì£¬µ«ÊÇʹÓÃÆÕͨÓû§Í¨¹ýJDBCÁ¬½ÓÖ´ÐÐÈ´±¨´í£º
java.lang.NullPointerException......
»ò
java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with " ......
¡¡ÍøÉÏÓкܶàµÄÎÄÕ½ÌÔõôÅäÖÃMySQL·þÎñÆ÷£¬µ«¿¼Âǵ½·þÎñÆ÷Ó²¼þÅäÖõIJ»Í¬£¬¾ßÌåÓ¦ÓõIJî±ð£¬ÄÇЩÎÄÕµÄ×ö·¨Ö»ÄÜ×÷Ϊ³õ²½ÉèÖòο¼£¬ÎÒÃÇÐèÒª¸ù¾Ý×Ô¼ºµÄÇé¿ö½øÐÐÅäÖÃÓÅ»¯£¬ºÃµÄ×ö·¨ÊÇMySQL·þÎñÆ÷Îȶ¨ÔËÐÐÁËÒ»¶Îʱ¼äºóÔËÐУ¬¸ù¾Ý·þÎñÆ÷µÄ”״̬”½øÐÐÓÅ»¯¡£
mysql> show global status;
¡¡¡¡¿ÉÒÔÁгöMySQL·þÎ ......