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

存储过程虽然经常听到，但是我遇到的项目还没有使用过，比较郁闷，只能选择自己入门了，也顺便与大家分享，希望大家也能如愿，呵呵。 首先我用的环境是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中查询可看到插入一条新的记录

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网