欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2024-01-16 09:57:04
...

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