mysql存储过程及java调用存储过程
来源:互联网 发布:数据库范式转化 编辑:程序博客网 时间:2024/05/19 13:08
首先在mysql中练习下存储过程的小例子:
delimiter //
create procedure hello()
begin
select 'It is not a HelloWorld';
end
//
create procedure hello()
begin
select 'It is not a HelloWorld';
end
//
在mysql中查询上面的过程hello():
call hello();
-----------------------+
It is not a HelloWorld |
-----------------------+
It is not a HelloWorld |
-----------------------+
It is not a HelloWorld |
-----------------------+
It is not a HelloWorld |
建立一个简单的测试用表:
DROP TABLE IF EXISTS `test`.`mapping`;
CREATE TABLE `test`.`mapping` (
`cFieldID` smallint(5) unsigned NOT NULL,
`cFieldName` varchar(30) NOT NULL,
PRIMARY KEY (`cFieldID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
向table mapping中插入一些初始化的数据:
mysql> select *from mapping//
+----------+-------------+
| cFieldID | cFieldName |
+----------+-------------+
| 1 | MarketValue |
| 2 | P/L |
| 3 | EName |
| 4 | Nominal |
| 5 | Chg |
+----------+-------------+
+----------+-------------+
| cFieldID | cFieldName |
+----------+-------------+
| 1 | MarketValue |
| 2 | P/L |
| 3 | EName |
| 4 | Nominal |
| 5 | Chg |
+----------+-------------+
现在简历一个向mapping中插入一条记录并返回记录的总和
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():
call mappingProc(@a);
select @a;
+------+
| @a |
+------+
| 6 |
+------+
mysql> select * from mapping//
+----------+-------------+
| cFieldID | cFieldName |
+----------+-------------+
| 1 | MarketValue |
| 2 | P/L |
| 3 | EName |
| 4 | Nominal |
| 5 | Chg |
| 6 | hello |
+----------+-------------+
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的存储过程:
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/test";
String userName = "root";
String password = "sunlong";
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.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/test";
String userName = "root";
String password = "sunlong";
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中查询可看到插入一条新的记录
- mysql存储过程学习及java调用存储过程
- mysql存储过程学习及java调用存储过程
- mysql存储过程学习及java调用存储过程
- mysql存储过程及java调用存储过程
- mysql存储过程学习及java调用存储过程
- MySQL存储过程及java中存储过程的调用
- mysql存储过程学习及java调用存储过程
- mysql存储过程及调用
- MySql存储过程及调用
- JAVA调用MYSQL存储过程
- Java调用MySQL存储过程
- Java 调用MySQL存储过程
- java 调用mysql存储过程
- Java 调用Mysql 存储过程
- JAVA调用MYSQL存储过程
- Java调用MySQL存储过程
- 存储过程及java调用
- mysql存储过程调用
- 做人十六别
- PADS 使用记录
- C++初学者指南 第十一篇(3)
- PKU2823 sliding window
- NetScreen Remote Client 在windows 7下的替代产品
- mysql存储过程及java调用存储过程
- 谈某些程序员顽固的思维方式
- IT项目管理工具总结
- 谈某些程序员顽固的思维方式
- oracle shutdown..startup..区别?
- 谈某些程序员顽固的思维方式
- JSP中使用java Bean的一个例子
- Use ADSL connect to internet on Linux OS
- 学习Java路上遇到的问题——can't resolved to a type