首先在mysql中练习下存储过程的小例子:
mysql
>
delimiter
//
mysql > create procedure hello()
-> begin
-> select ' It is not a HelloWorld ' ;
-> end
-> //
Query OK, 0 rows affected ( 0.01 sec)
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)
+ -- ----------------------+
| 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)
-> 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)
-> 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
-> //
-> 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 |
+ -- --------+-------------+
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();
}
}
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中查询可看到插入一条新的记录