Jdbc 存储过程 procedure

本文主要是介绍Jdbc 存储过程 procedure，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

今天闲暇之余，就测试使用存储过程来操作数据库和使用 JDBC方式来操作数据库的效率比较。

废话不说了，看如下sql ，创建了表和存储过程供调用。

create table `operators_man` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`old_pid` varchar(100) DEFAULT NULL COMMENT '要摘除的pid',
`from_lid` varchar(100) DEFAULT NULL COMMENT '来源数据的 lid',
`new_pid` varchar(100) DEFAULT NULL COMMENT '要摘入的pid ',
`operator` varchar(100) DEFAULT NULL COMMENT '操作者',
`operator_time` datetime DEFAULT NULL COMMENT '操作时间',
`tempStr0` varchar(100) DEFAULT NULL COMMENT '备用',
`tempStr1` varchar(100) DEFAULT NULL COMMENT '备用',
`tempStr2` varchar(100) DEFAULT NULL COMMENT '备用',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='人物摘除操作记录表';

#
# procedure insert_operators_man
#
drop procedure if exists insert_operators_man;
create procedure insert_operators_man(
   in oldpid varchar(20),
   in fromlid varchar(20),
   in newpid varchar(20),
   in operatorman varchar(20),
   in operatortime varchar(50)
)
begin
   insert into operators_man(old_pid,from_lid,new_pid,operator,operator_time) VALUES ( oldpid,fromlid,newpid,operatorman,operatortime );
end;

#
# procedure update_operators_man
#
drop procedure if exists update_operators_man;
create procedure update_operators_man(
   in opid int(11),
   in oldpid varchar(20),
   in fromlid varchar(20),
   in newpid varchar(20),
   in operatorman varchar(20),
   in operatortime varchar(50)
)
begin
   update operators_man set old_pid=oldpid,from_lid=fromlid,new_pid=newpid,operator=operatorman,operator_time=operatortime where id = opid;
end;

#
# procedure delete_operators_man
#
drop procedure if exists delete_operators_man;
create procedure delete_operators_man(
   in opid int(10)
)
begin
   delete from operators_man where id = opid;
end;

#
# procedure find_operators_man
#
drop procedure if exists find_operators_man;
create procedure find_operators_man(
   in opid int(10)
)
begin
   select * from operators_man where id = opid;
end;

创建完成之后，就去调用，看代码：

       /**
   * insert
   * @return
   * @throws SQLException
   */
   public static List<OperatorMan> find_OperatorMan(Connection conn){
       try {
           CallableStatement cell=(CallableStatement) conn.prepareCall("{call find_operators_man(?)}");
           cell.setInt(1, 2);
           //执行
           List<OperatorMan> list = new ArrayList<OperatorMan>();
           boolean result = cell.execute();
           if(result) {
               ResultSet resultSet = cell.getResultSet();
               while (resultSet!=null && resultSet.next()) {
                   OperatorMan man = new OperatorMan();
                   man.setId(resultSet.getInt("id"));
                   man.setOld_pid(resultSet.getString("old_pid"));
                   man.setFrom_lid(resultSet.getString("from_lid"));
                   man.setNew_pid(resultSet.getString("new_pid"));
                   man.setOperator(resultSet.getString("operator"));
                   man.setOperator_time(resultSet.getDate("operator_time"));
                   man.setTempStr0(resultSet.getString("tempStr0"));
                   man.setTempStr1(resultSet.getString("tempStr1"));
                   man.setTempStr2(resultSet.getString("tempStr2"));
                   //add...
                   list.add(man);
               }
           }
           return list;
       } catch (Exception e) {
           System.out.println(e.getLocalizedMessage());
           return null;
       }
   }