Mysql Stored Procedure learning and java calling Stored Procedure

Although I often hear about the stored procedure, I have never used any of the projects I have encountered. I am very depressed. I have to choose my own Quick Start and share with you. I hope you can do that as well. First of all, the environment I use is windowxpmysql5, which should be available to everyone. If not, install it. After installation, do not forget to put the path directory of mysql.exe

Although I often hear about the stored procedure, I have never used any of the projects I have encountered. I am very depressed. I have to choose my own Quick Start and share with you. I hope you can do that as well. First, the environment I use is window xpmysql5, which should be available to all users. If not, install Windows XP. After installation, do not forget to put the path directory of mysql.exe

Although I often hear about the stored procedure, I have never used any of the projects I have encountered. I am very depressed. I have to choose my own Quick Start and share with you. I hope you can do that as well.

First of all, the environment I use is window xp + mysql5, which should be available to everyone. If not, install it. After installation, do not forget to put the path directory of mysql.exe into the environment variable path. In this way, enter the following command in cmd to enter the mysql Command Line Mode: mysql-u root-p-> mysql password.

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

Delimiter // indicates that the Terminator is replaced by the default;/. If this is not the case, select ...... the statement will cause the above Code error. If you don't believe it, you can try it. Then query the above process in mysql hello ():

mysql> call hello()//+------------------------+| It is not a HelloWorld |+------------------------+| It is not a HelloWorld |+------------------------+1 row in set (0.00 sec)

Let's try another small entry-level instance.

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)

If you want to enter an empty table file with the load value in the table, the file is:

Filed.txt

1,MarketValue2,P/L3,EName4,Nominal5,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: 0mysql> select *from mapping//+----------+-------------+| cFieldID | cFieldName  |+----------+-------------+|        1 | MarketValue ||        2 | P/L         ||        3 | EName       ||        4 | Nominal     ||        5 | Chg         |+----------+-------------+5 rows in set (0.02 sec)

Now, the resume inserts a record into mapping and returns the sum of records.

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    ->  //

Find 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       |+----------+-------------+

The following is the java code used to call the MySQL stored procedure:

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();    }}

You can see a new record is inserted in the query to MySQL.