存储过程--得到新序列号

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;

    }