DROP PROCEDURE IF EXISTS `exam_dm`.`getSeqNumOut`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getSeqNumOut`(out time_value varchar(255))
BEGIN
declare rn varchar(255) default null;
declare v_value varchar(255) default null;
declare v_time timestamp default null;
declare time_value varchar(255) ;
DECLARE hasResult INTEGER DEFAULT 1;
declare genCursor CURSOR FOR select keyValue, keyTime from sys_seq where keyName='seqNum';
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET hasResult = 0;
OPEN genCursor;
FETCH genCursor INTO v_value, v_time;
CLOSE genCursor;
if hasResult=0 then
set time_value=CONCAT(date_format(now(), '%Y%m%d'),'1');
insert into sys_seq values('seqNum', '1', now(),time_value);
set rn = CONCAT(date_format(now(), '%Y%m%d'), lpad('1', 3, '0')) ;
else
if date_format(v_time, '%Y%m%d')!=date_format(now(), '%Y%m%d') then
set time_value=CONCAT(date_format(now(), '%Y%m%d'),'1');
update sys_seq set keyValue='1', keyTime = now(),kswyh =time_value;
set rn = CONCAT(date_format(now(), '%Y%m%d'), lpad('1', 3, '0')) ;
else
set time_value=CONCAT(date_format(now(), '%Y%m%d'),(1+v_value));
update sys_seq set keyValue=1+v_value,kswyh =time_value;
set rn = CONCAT(date_format(now(), '%Y%m%d'), lpad(1+v_value, 3, '0')) ;
end if;
end if;
select time_value recordNum;
END$$
DELIMITER ;
=======================
public class TestProc {
/*
* 从service.txt文件中加载以等号分割的信息为键值对
* @return Properties封装的键值对
*/
private static Properties getProperties() {
Properties props = new Properties();
try {
props.load(ajj_hz_serverClient.class.getResourceAsStream("../service.txt"));
} catch (IOException e) {
e.printStackTrace();
}
return props;
}
/*
* JDBC连接
* */
public static Connection getConnection()
{
Properties props = getProperties();
String mysql_ip = props.getProperty("MYSQL_IP");
String mysql_username=props.getProperty("MYSQL_USERNAME");
String mysql_password=props.getProperty("MYSQL_PASSWORD");
try
{
Class.forName("org.gjt.mm.mysql.Driver");
return DriverManager.getConnection(mysql_ip, mysql_username, mysql_password);
}
catch (final ClassNotFoundException e)
{
//TODO 找不到驱动
}
catch (final SQLException e)
{
//TODO 创建连接异常
}
return null;
}
//通过jdbc得到返回考试序列号
@Test
public void testSeqByJdbcOut(){
CallableStatement proc = null;
try {
Connection con = getConnection();
proc = con.prepareCall("{call getSeqNumOut(?)}"); //调用存储过程返回序列号
proc.setString(1, null);
proc.execute();
ResultSet rs1= proc.getResultSet();
String kswyh=null;
while(rs1.next()){
int a= rs1.getRow();
kswyh=rs1.getString(1);
System.out.println(kswyh );
}
//con.commit();
proc.close();
con.close();
System.out.println("kswyh="+kswyh );
} catch (SQLException e) {
e.printStackTrace();
}
}
}