/ 中存储网

Java程序中使用update操作MySQL存储过程的方法

2014-07-13 16:35:49 来源:中存储网
例子一:
  1、新建表test
  create table test(
  field1 int not null
  )
  TYPE=MyISAM ;
  insert into test(field1) values(1);
  2、删除已存在的存储过程
  -- 删除储存过程
  delimiter // -- 定义结束符号
  drop procedure p_test;//
  3、mysql存储过程定义
  create procedure p_test()
  begin
  declare temp int;
  set temp = 0;
  update test set field1 =temp;
  end
  //
  4、调用方法
  call p_test();
  import java.sql.*;
  public class Test Conn{
  private Connection getConn(){
  Connection conn = null;
  try {
  Class.forName("org.gjt.mm.mysql.Driver");
  try {
  conn = DriverManager.getConnection("jdbc:mysql://127.0.0. 1:3306/test?useUnicode=true&
  characterEncoding=GBK","root","ntsky");
  } catch (SQLException e1) {
  e1.printStackTrace();
  }
  }
  catch (ClassNotFoundException e) {
  e.printStackTrace();
  }
  return conn;
  }
  public void testC() {
  Connection conn = getConn();
  ResultSet rs = null;
  CallableStatement cs = null;
  String a = null;
  try {
  CallableStatement cStmt = conn.prepareCall("{call p_test()}");
  cStmt.executeUpdate();
  } catch (Exception e) {
  System.out.println("hahad" + e.getMessage());
  } finally {
  try {
  conn.close();
  } catch (Exception ex) {
  System.out.println("ex : " + ex.getMessage());
  }
  }
  }
  public static void main(String[] args) {
  new TestConn().testC();
  }
  }

  例子二: 一、创建MySQL存储过程示例
  --选择数据库
  mysql> use test;
  Database changed
  --创建示例用表
  mysql> create table zzm(
  -> id int primary key auto_increment,
  -> name varchar(10)
  -> );
  Query OK, 0 rows affected (0.20 sec)
  mysql> insert into zzm(name) values('zhang');
  Query OK, 1 row affected (0.08 sec)
  mysql> insert into zzm(name) values('zeng');
  Query OK, 1 row affected (0.05 sec)
  mysql> insert into zzm(name) values('ming');
  Query OK, 1 row affected (0.05 sec)
  mysql> select * from zzm;
  +----+-------+
  | id | name |
  +----+-------+
  | 1 | zhang |
  | 2 | zeng |
  | 3 | ming |
  +----+-------+
  3 rows in set (0.00 sec)
  --更改命令结束符(因为在procedure中经常要用到默认的命令结束符--分号(;)
  --所以在创建procedure的时候需要定义新的结束符以说明创建procedure的命令结束)
  --这里将结束符号改成美元符号--$
  mysql> delimiter $
  --创建存储过程p3
  --此存储过程的过程名是p3,该过程包含两个参数,
  --一个是输入类型的(以IN标示),参数名是nameid,类型是int,
  --一个是输出类型的(以OUT标示),参数名是person_name,类型是varchar(10)
  --此存储过程的作用是查询出zzm表的全部内容,会输出结果集(data set),然后
  --再查询表中记录的ID是nameid的字段name,将其输出到第二个输出类型的参数里面,这个查询
  --不会输出结果集。
  mysql> create procedure p3(IN nameid int, OUT person_name varchar(10))
  -> begin
  -> select * from test.zzm;
  -> select zzm.name into person_name from test.zzm where zzm.id = nameid;
  -> end
  -> $
  Query OK, 0 rows affected (0.00 sec)
  --创建完成,查看数据库中所有已经创建的存储过程
  mysql> show procedure status $
  --调用存储过程
  mysql> call p3(3,@name) $
  +----+-------+
  | id | name |
  +----+-------+
  | 1 | zhang |
  | 2 | zeng |
  | 3 | ming |
  +----+-------+
  3 rows in set (0.00 sec)
  Query OK, 0 rows affected (0.00 sec)
  mysql> select @name $
  +-------+
  | @name |
  +-------+
  | ming |
  +-------+
  1 row in set (0.00 sec)
  二、在Java程序中调用存储过程的程序示例
  view plaincopy to clipboardprint?
  import java.sql.CallableStatement;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.Types;
  public class Main {
  public static void main(String[] args) {
  /* JDBC连接MySQL数据库的参数 */
  String driverName = "com.mysql.jdbc.Driver";
  String userName = "root";
  String userPasswd = "nicegiving";
  String dbName = "test";
  String url = "jdbc:mysql://localhost/" + dbName + "?user=" + userName
  + "&password=" + userPasswd;
  Connection connection = null;
  CallableStatement stmt = null;
  try {
  // 加载数据库驱动程序
  Class.forName(driverName).newInstance();
  // 连接数据库
  connection = DriverManager.getConnection(url);
  // 调用存储过程,此存储过程有2个参数
  stmt = connection.prepareCall("{call p3(?,?)}");
  // 第一个参数是输入的,在此设置第一个参数的值:将第一个参数设置成整数值3
  stmt.setInt(1, 3);
  // 第二个参数是输出的,在此设置第二个参数的输出类型为VARCHAR
  stmt.registerOutParameter(2, Types.VARCHAR);
  // 执行存储过程
  boolean hadResults = stmt.execute();
  // 如果有查询语句的话,此执行过程会返回结果集,在此处理结果集里面的东西
  System.out.println("Data from table:");
  while (hadResults) {
  ResultSet rs = stmt.getResultSet();
  while(rs.next()){
  String id = rs.getString(1);
  String name = rs.getString(2);
  System.out.println("ID = "+id+"tName = " + name);
  }
  hadResults = stmt.getMoreResults();
  }
  // 获取存储过程的返回值
  System.out.println("nData from procedure:");
  String name = stmt.getString(2); // 获得第二个参数,因为第二个参数是输出类型的
  System.out.println("Name = " + name);
  } catch (Exception e) {
  System.out.println(e.toString());
  } finally {
  try {
  stmt.close();
  connection.close();
  } catch (Exception ex) {
  System.out.println(ex.getMessage());
  }
  }
  }
  }
  --程序执行结果--
  Data from table:
  ID = 1 Name = zhang
  ID = 2 Name = zeng
  ID = 3 Name = ming
  Data from procedure:
  Name = ming 例子三:
  一、建表
  DROP TABLE IF EXISTS `user`;
  CREATE TABLE `user` (
  `ID` int(11) NOT NULL auto_increment,
  `NAME` varchar(16) NOT NULL default '',
  `REMARK` varchar(16) NOT NULL default '',
  PRIMARY KEY (`ID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  二、建立存储过程
  1、获取用户信息
  delimiter $
  CREATE DEFINER=`root`@`localhost` PROCEDURE `getUserList`()
  BEGIN
  select * from user;
  END$
  2、通过传入参数创建用户
  CREATE DEFINER=`root`@`localhost` PROCEDURE `insertUser`(nameVar varchar(16),remarkVar varchar(16))
  BEGIN
  insert into user(name,remark) values(nameVar,remarkVar);
  END$
  3.根据用户id获取用户信息
  create procedure getUserById(in idInt int,out nameVar varchar(16),out remarkVar varchar(16))
  begin
  select name,remark into nameVar,remarkVar from user where user.id=idInt;
  end $

  三、调用
  代码:
  package com.database;
  import java.sql.*;
  public class MysqlProcedure2 {
  public Connection getConnection()
  {
  Connection conn=null;
  try{
  String driverName="com.mysql.jdbc.Driver";
  String userName="root";
  String password="123";
  String dbName="test";
  String url="jdbc:mysql://localhost:3306/"+dbName+"?user=" +userName+"&password="+password;
  Class.forName(driverName).newInstance();
  conn=DriverManager.getConnection(url);
  }catch(Exception e){e.printStackTrace();}
  return conn;
  }
  public void testGetUserList()
  {
  try{
  Connection conn=this.getConnection();
  CallableStatement stmt=conn.prepareCall("{call getUserList()}");
  ResultSet rs=stmt.executeQuery();
  int i=1;
  while(rs.next())
  {
  int id=rs.getInt(1);
  String name=rs.getString(2);
  String remark=rs.getString(3);
  System.out.println("record "+i);
  i++;
  System.out.println("Id="+id+"t"+"name="+name+"t" +"remark="+remark);
  }
  }catch(Exception e){e.printStackTrace();}
  }
  public void testInsertUser(String name,String remark)
  {
  try{
  Connection conn=this.getConnection();
  CallableStatement stmt=conn.prepareCall("{call insertUser(?,?)}");
  stmt.setString(1, name);
  stmt.setString(2, remark);
  boolean b=stmt.execute();
  }catch(Exception e){e.printStackTrace();}
  }
  public void testGetUserById(int id)
  {
  try{
  Connection conn=this.getConnection();
  CallableStatement stmt=conn.prepareCall("{call getUserById(?,?,?)}");
  stmt.setInt(1, id);
  stmt.registerOutParameter(2, Types.VARCHAR);
  stmt.registerOutParameter(3, Types.VARCHAR);
  boolean b=stmt.execute();
  String name=stmt.getString(2);
  String remark=stmt.getString(3);
  System.out.println("name="+name+"t"+"remark="+rem ark);
  }catch(Exception e){e.printStackTrace();}
  }
  public static void main(String[]args)
  {
  MysqlProcedure2 mp=new MysqlProcedure2();
  mp.testGetUserList();
  mp.testInsertUser("wsh", "student");
  mp.testGetUserById(1);
  }
  }
  希望以上的几个例子能给大家帮助!!!!!!!!!!!