当前位置>>
工作中一直都是用swing，很少接触数据库，最近事少就帮着把公司服务器用csv存储用户资料信息，转移到mysql中去存储，才得以学习下mysql的存储过程。
首先在mysql中练习下存储过程的小例子：
mysql> delimiter //mysql> create procedure hello() -> begin -> select 'It is not a HelloWorld'; -> end -> //Query OK, 0 rows affected (0.01 sec)其中“delimiter //”的意思是定义结束符号为“//”，以此来替换mysql中的“；”
在mysql中查询上面的过程hello():
mysql> call hello()//+------------------------+| It is not a HelloWorld |+------------------------+| It is not a HelloWorld |+------------------------+1 row in set (0.00 sec)建立一个简单的测试用表：
mysql> DROP TABLE IF EXISTS `userinfo`.`mapping`; -> CREATE TABLE `userinfo`.`mapping` ( -> `cFieldID` smallint(5) unsigned NOT NULL, -> `cFieldName` varchar(30) NOT NULL, -> PRIMARY KEY (`cFieldID`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -> //Query OK, 0 rows affected (0.14 sec)向table mapping中插入一些初始化的数据：
mysql> load data infile 'd:\\userInfo\\field.txt' into table mapping -> fields terminated by ',' lines terminated by '\r\n' //Query OK, 5 rows affected (0.02 sec)Records: 5 Deleted: 0 Skipped: 0 Warnings: 0mysql> select *from mapping//+----------+-------------+| cFieldID | cFieldName |+----------+-------------+| 1 | MarketValue || 2 | P/L || 3 | EName || 4 | Nominal || 5 | Chg |+----------+-------------+5 rows in set (0.02 sec)现在简历一个向mapping中插入一条记录并返回记录的总和
mysql> drop procedure if exists mappingProc; -> create procedure mappingProc(out cnt int) -> begin -> declare maxid int; -> select max(cFieldID)+1 into maxid from mapping; -> insert into mapping(cFieldID,cFieldName) values(maxid,'hello'); -> select count(cFieldID) into cnt from mapping; -> end -> //查找mappingProc():
mysql> call mappingProc(@a)//mysql> select @a//+------+| @a |+------+| 6 |+------+mysql> select * from mapping//+----------+-------------+| cFieldID | cFieldName |+----------+-------------+| 1 | MarketValue || 2 | P/L || 3 | EName || 4 | Nominal || 5 | Chg || 6 | hello |+----------+-------------+下面是java代码用来调用MySQL的存储过程：
package kissJava.sql;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Types;
public class SQLUtils {
String url = "jdbc:mysql://127.0.0.1:3306/userInfo"; String userName = "root"; String password = "zhui007";
public Connection getConnection() {
Connection con=null; try{ DriverManager.registerDriver(new com.mysql.jdbc.Driver()); con = DriverManager.getConnection(url, this.userName, this.password); }catch(SQLException sw){ 
} return con; } public void testProc(){ Connection conn = getConnection(); CallableStatement stmt = null; try{ stmt = conn.prepareCall("{call mappingProc(?)}"); stmt.registerOutParameter(1, Types.INTEGER); stmt.execute(); int i= stmt.getInt(1); System.out.println("count = " + i); }catch(Exception e){ System.out.println("hahad = "+e.toString()); }finally{ try { stmt.close(); conn.close(); }catch (Exception ex) { System.out.println("ex : "+ ex.getMessage()); } } } public static void main(String[] args) { new SQLUtils().testProc(); }
}在到MySQL中查询可看到插入一条新的记录
转载自:http://www.blogjava.net/kissjava/archive/2008/07/16/215177.html
