trigger调用（java）存储过程——HSQL_DB存储过程学习笔记(1)

1 trigger调用存储过程

       createprocedure insert_student(name VARCHAR(50))

       MODIFIESSQL DATA

       beginATOMIC

       DECLAREid INTEGER;

       --DECLAREname VARCHAR(50);

       setid=Select count(*) from Student;

       --setname = '123';

       INSERTINTO  Student VALUES(id, name);

       end

       创建trigger

       CreateTrigger trgBorrowRecord AFTER INSERT

       onBorrowRecord

       beginATOMIC

      callinsert_student();

       end

       触发trigger

       INSERTINTO "PUBLIC"."BORROWRECORD"

       ("BORROWRECORD", "STUDENTID", "BORROWDATE","RETURNDATE" )

       VALUES(default ,1 ,now() ,now() )

 

2 trgger 调用java式存储过程

 

       CREATEPROCEDURE AddMethod(IN id1 INT,IN id2 INT)

         READS SQL DATA

         LANGUAGE JAVA

         EXTERNAL NAME'CLASSPATH:com.merchantrun.hsqldb.java.Add.AddMethod'

 

       packagecom.merchantrun.hsqldb.java;

 

       publicclass Add {

      

              publicstatic void AddMethod(int add1, int add2){

                     System.out.println("add1"+ add1);

                     System.out.println("add2"+ add2);

                     intintresult = add1 + add2;

                     /*result[0]= "" + intresult;

                     System.out.println("result[0]"+ result[0]);*/

              }

       }

 

       CreateTrigger trgBorrowRecord AFTER INSERT

       onBorrowRecord

       beginATOMIC

       callAddMethod(1，2);

       end

 

3 记录表更新操作的记录【实例】

  a.创建表：

       CREATETABLE NAMERECORD(

              idInteger,

              nameVARCHAR(50)

       );

 

       createtable nameupdatelog(

              idInteger,

              oldNameVARCHAR(50),

              newNameVARCHAR(50),

              updateTimeDateTime

       );

 

 b.在NAMERECORD表中创建触发器trgNameUpdate用于监听事件

       CreateTrigger trgNameUpdate AFTER UPDATE on NAMERECORD

       REFERENCING NEW AS newrow

        OLD ROW AS oldrow

       FOREACH ROW

       beginATOMIC

              callUPDATE_Log(newrow.id, oldrow.NAME, newrow.NAME);

       end

 

  c.创建步骤b中调用的java式存储过程

         CREATE PROCEDURE UPDATE_Log(IN id1 INT,INoldName VARCHAR(50), IN newName VARCHAR(50))

         READS SQL DATA

         LANGUAGE JAVA

         EXTERNAL NAME'CLASSPATH:com.merchantrunglobal.console.UpdateLog.NameUpdateLog'

       //java代码

       packagecom.merchantrunglobal.console;

       importjava.sql.CallableStatement;

       importjava.sql.Connection;

       importjava.sql.Date;

       importjava.sql.SQLException;

       importjava.util.Properties;

 

       importorg.hsqldb.jdbc.JDBCDriver;

 

       publicclass UpdateLog {

              publicstatic void NameUpdateLog(int id, String oldName, String newName)

                            throws SQLException {

                     Connectionconnection =

                            getConnection();

                     StringcallString = "call update_NAMEUPDATELOG(?, ?,?,?)";

                    

                     CallableStatementcall = connection.prepareCall(callString);

                     call.setString(2, oldName);

                     call.setString(3, newName);

                     call.setInt(1, id);

                     call.setDate(4, new Date(newjava.util.Date().getTime()));

                     call.execute();

                     connection.close();

              }

 

      

              privatestatic Connection getConnection() throws SQLException {

                     Properties p = new Properties();

                     p.setProperty("user", "SA");

                     p.setProperty("password","");

                     Connection connection = JDBCDriver

                                   .getConnection(

                                                 "jdbc:hsqldb:file:/home/jack/hsqldb/db1;shutdown=true;",

                                                 p);//TODO

                     return connection;

              }

       }

  d.创建上步骤java代码调用的存储过程update_NAMEUPDATELOG

       createprocedure update_NAMEUPDATELOG(id Integer, oldName VARCHAR(50), newNameVARCHAR(50), updateTime Datetime)

       MODIFIESSQL DATA

       beginATOMIC

       INSERTINTO NAMEUPDATELOG VALUES(id, oldName, newName, updateTime);

       end

 

 tips://TODO 的地方需要修改