我使用MySQL数据库的自定义函数实现了像Oracle一样的sequence序列功能，这个功能在项目中是做一个单号生成的模块，
目前是在测试并发访问1000的时候让每个请求响应的时间不超过100毫秒，但是目前的结果是只有三分之二的响应时间能达到要求。以下是我的代码以及sql自定义函数。求大神帮忙优化。
方法
@RequestMapping(value ="/testP.location")
@ResponseBody
public String testPolicyNo(){
String p_type="P";
String productNO="1999";
Date date=new Date();
SimpleDateFormat simple=new SimpleDateFormat("yyyy");
String sys_year=simple.format(date).toString();
String department="0101L";
String creator="aaa";
long startTime=System.currentTimeMillis();
String policyNo = iGenerateIDService.generatePolicyNo(p_type,productNO,sys_year,department,creator);
long endTime=System.currentTimeMillis();
long t=endTime-startTime;
String tt="w-----"+String.valueOf(t);
log.debug(tt);
//System.out.println("保单号"+policyNo);
return null;
}
@Override
public String generatePolicyNos(String p_type, String productNO, String sys_year, String department,String creator) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String policyNo="";
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");
ps=conn.prepareStatement("select lpad(nextval(?,?,?,?,?),?,'0')");
ps.setString(1, productNO);
ps.setString(2, p_type);
ps.setString(3, sys_year);
ps.setString(4, department);
ps.setString(5, creator);
ps.setInt(6, 7);
rs=ps.executeQuery();
while(rs.next()){
policyNo=p_type+productNO+sys_year+department+rs.getString(1);
}
return policyNo;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "";
}
sql函数：
1：建表语句
CREATE TABLE tbl_seq_mysql (p_type varchar(5) NOT NULL,productNO varchar(50) NOT NULL,sys_year varchar(5) NOT NULL,department varchar(5) NOT NULL,currentValue int(50) NOT NULL DEFAULT '2',increment int(11) NOT NULL DEFAULT '2',creator varchar(50) NOT NULL,created_date varchar(50) NOT NULL,
PRIMARY KEY (p_type,productNO,sys_year,department,currentValue)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
2：函数1 nextval
CREATE DEFINER=shopdev@% FUNCTION next_value(m varchar(50),a varchar(5),b varchar(5),c
varchar(5),d varchar(50)) RETURNS int(11)
begin
UPDATE tbl_seq_mysql
SET currentValue = currentValue + increment
WHERE
p_type = a
AND productNO = m
AND sys_year = b
AND department = c
AND creator=d;
return current_value(m,a,b,c,d);
end
3：函数currentval
CREATE DEFINER=shopdev@% FUNCTION current_value(n varchar(50),e varchar(5),f varchar
(5),g varchar(5),h varchar(50)) RETURNS int(11)
BEGIN
DECLARE _cur int;
DECLARE _curr int;
select COUNT(1) INTO _cur from tbl_seq_mysql where creator=h and department=g and productNO= n and p_type=e and sys_year=f;
IF _cur=0
THEN
insert into tbl_seq_mysql
(p_type,productNO,sys_year,department,creator,created_date) VALUES
(e,n,f,g,h,NOW());
END IF;
set _curr=(select currentValue from tbl_seq_mysql where creator=h and department=g and productNO= n and p_type=e and sys_year=f);
return _curr;
end
