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

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

      工作中一直都是用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: 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中查询可看到插入一条新的记录