mysql 触发器 实例
package com.raytech.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
public class Test {
/**
drop procedure if exists getcabinetview;
create procedure getcabinetview(
in cabip varchar(20),
out alarm int(5),
out fault int(5),
out filesum int(5),
out filein int(5),
out fileout int(5),
out filelend int(5),
out fileask int(5),
out filetrans int(5)
)
begin
set alarm=(select count(cabinetip) from alarminfo where cabinetip=cabip);
set fault=(select count(rid) from faultinfo where cabinetip=cabip);
set filesum=(select count(rfidinfo) from rfidinfo where cabinetip=cabip);
set filein=(select count(rfidinfo) from rfidinfo where inflag=1 and cabinetip=cabip);
set fileout=(select count(rfidinfo) from rfidinfo where inflag=0 and lendflag=1 and cabinetip=cabip );
set filelend=(select count(rfidinfo) from rfidinfo where lendflag=0 and cabinetip=cabip);
set fileask=(select count(rfidinfo) from rfidlendinfo where cabinetip=cabip and doneflag=0);
set filetrans=(select count(rfidinfo) from wronglocationinfo where cabinetIp=cabip);
select alarm,fault,filesum,fileout,filein,filelend,fileask,filetrans;
end
*/
public void getCabinetViewByIp(String cabinetIp) {
Connection con=HibernateSessionFactory.getSessionFactory().openSession().connection();
try {
CallableStatement st=con.prepareCall(“{call getcabinetview(?,?,?,?,?,?,?,?,?)}”);
st.setString(1, cabinetIp);
st.registerOutParameter(2, Types.INTEGER);
st.registerOutParameter(3, Types.INTEGER);
st.registerOutParameter(4, Types.INTEGER);
st.registerOutParameter(5, Types.INTEGER);
st.registerOutParameter(6, Types.INTEGER);
st.registerOutParameter(7, Types.INTEGER);
st.registerOutParameter(8, Types.INTEGER);
st.registerOutParameter(9, Types.INTEGER);
ResultSet rs=st.executeQuery();
if(rs!=null){
while(rs.next()){
System.out.println(+rs.getInt(1)+” “+rs.getInt(2)+” “+rs.getInt(3)+” “+
rs.getInt(4)+” “+rs.getInt(5)+” “+rs.getInt(6)+” “+
rs.getInt(7)+” “+rs.getInt(8));
}
}else{
System.out.println(“rs==null”);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
new Test().getCabinetViewByIp(“192.168.1.231”);
}
}
