CREATE DEFINER=`ggs`@`%` PROCEDURE `pr_billno_nextval`(`p_table_name` varchar(100), `p_field_name` varchar(100), `p_rule_value` varchar(100),out `l_cur_val` bigint(20)) BEGIN SELECT SEQUENCE_CUR_VALUE INTO l_cur_val FROM RIV_BILL_NO_SEQ WHERE table_name = p_table_name AND field_name = p_field_name AND rule_value = p_rule_value; -- FOR UPDATE; IF l_cur_val IS NOT NULL THEN UPDATE RIV_BILL_NO_SEQ SET SEQUENCE_CUR_VALUE = IF ( (SEQUENCE_CUR_VALUE + SEQUENCE_INCREMENT) > SEQUENCE_MAX_VALUE, IF ( SEQUENCE_CYCLE = TRUE, SEQUENCE_MIN_VALUE, NULL ), SEQUENCE_CUR_VALUE + SEQUENCE_INCREMENT ) WHERE table_name = p_table_name AND field_name = p_field_name AND rule_value = p_rule_value ; END IF; SELECT (l_cur_val+1) into l_cur_val; END
CREATE TABLE `riv_bill_no_seq` ( `TABLE_NAME` varchar(100) NOT NULL, `FIELD_NAME` varchar(100) NOT NULL, `RULE_VALUE` varchar(100) NOT NULL, `SEQUENCE_INCREMENT` int(11) unsigned NOT NULL DEFAULT '1', `SEQUENCE_MIN_VALUE` int(11) unsigned NOT NULL DEFAULT '1', `SEQUENCE_MAX_VALUE` bigint(20) unsigned NOT NULL DEFAULT '18446744073709551615', `SEQUENCE_CUR_VALUE` bigint(20) unsigned DEFAULT '1', `SEQUENCE_CYCLE` tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (`TABLE_NAME`,`FIELD_NAME`,`RULE_VALUE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/** * call function : FUN_BILLNO_NEXTVAL * */ private Long getBillNoNextVal(final String tableName, final String fieldName, final String ruleValue) { Long nextVal = this.jdbcTemplate.getJdbcOperations().execute(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { CallableStatement call = con.prepareCall("{call pr_billno_nextval(?,?,?,?)}"); call.setObject(1, tableName); call.setObject(2, fieldName); call.setObject(3, ruleValue); call.registerOutParameter(4, Types.BIGINT); return call; } }, new CallableStatementCallback<Long>() { @Override public Long doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { cs.execute(); return cs.getLong(4); } }); return nextVal; }
/** * 调用mysql存储过程 * * @return */ @Override public List<Object[]> getUser() { List<Object[]> rtnObjs = new ArrayList<Object[]>(); rtnObjs = this.getJdbcTemplate().execute("{call getuser()}", new CallableStatementCallback<List<Object[]>>() { @Override public List<Object[]> doInCallableStatement( CallableStatement cs) throws SQLException, DataAccessException { List<Object[]> objects = new ArrayList<Object[]>(); ResultSet rs = cs.executeQuery(); while (rs.next()) { Object[] objArr = new Object[4]; objArr[0] = rs.getLong("ID"); objArr[1] = rs.getTimestamp("createtime"); objArr[2] = rs.getString("password"); objArr[3] = rs.getString("username"); objects.add(objArr); } return objects; } }); return rtnObjs; }