mysql存储过程学习及java调用存储过程

2020-11-09 来源：品趣旅游知识分享网

存储过程虽然经常听到，但是我遇到的项目还没有使用过，比较郁闷，只能选择自己入门了，也顺便与大家分享，希望大家也能如愿，呵呵。首先我用的环境是window xpmysql5，这个大家应该都有的，没有的安装一下吧。好装好了，不要忘记将mysql.exe的路径目录放入

存储过程虽然经常听到，但是我遇到的项目还没有使用过，比较郁闷，只能选择自己入门了，也顺便与大家分享，希望大家也能如愿，呵呵。

首先我用的环境是window xp+mysql5，这个大家应该都有的，没有的安装一下吧。好装好了，不要忘记将mysql.exe的路径目录放入环境变量path中，这样在cmd里输入以下命令就可以进入mysql的命令行模式：mysql -u root -p -> mysql密码即可。

mysql> delimiter //
mysql> create procedure hello()
 -> begin
 -> select 'It is not a HelloWorld';
 -> end
 -> //
Query OK, 0 rows affected (0.01 sec)

delimiter // 是将结束符由默认的；换成了//，如果不这样select......语句就会导致上面的代码出错，不信你可以试一下哈，呵呵。接着在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)

想表中load值进空表文件为：

filed.txt

1,MarketValue
2,P/L
3,EName
4,Nominal
5,Chg

mysql> load data infile 'd:\\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: 0
mysql> 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中查询可看到插入一条新的记录

显示全文

全部栏目

mysql存储过程学习及java调用存储过程