使用MySQL存储过程实现唯一单号的自增生成

建议先学习MySQL的存储过程

https://blog.csdn.net/wangxueqing52/article/details/81155897

1.创建测试表orders

SQL语句为

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `order_no` int(50) NULL DEFAULT NULL,
  `station` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '站点名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES (1, 903000001, '903');

2.创建存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_orderNo`(in orderNamePre char(3),out newOrderNo varchar(9))
BEGIN    
  DECLARE maxNo INT DEFAULT 0 ;     -- 定义该站点中当前订单号最大的号  后六位
  DECLARE oldOrderNo VARCHAR (9) DEFAULT '' ;  -- 定义该站点中的最近的一个物流单号
          
  SELECT IFNULL(order_no, '') INTO oldOrderNo     -- 取去当前站点的最近的订单号
  FROM orders     
  WHERE SUBSTRING(order_no, 1, 3) = orderNamePre
  ORDER BY id DESC LIMIT 1 ;   
      
  IF oldOrderNo != '' THEN     
    SET maxNo = CONVERT(SUBSTRING(oldOrderNo,4), DECIMAL) ;   -- 如果当前站点有订单号 则该站点最大号为最近的物流单号的后六位 强转为数字
  END IF ;    
	
  SELECT CONCAT(orderNamePre, LPAD((maxNo + 1), 6, '0')) INTO newOrderNo ;   -- 将站点名902 与生成的物流单号拼接一起  并返回物流单号  其中LPAD是左拼接方法 将0拼在物流单号的左边  拼成6位数
			
	IF oldOrderNo = '' THEN     
    INSERT INTO orders (order_no, station) VALUES (newOrderNo, orderNamePre) ;  -- 如果当前站点不存在 则插入一条站点记录
  END IF ;    	
	UPDATE orders SET order_no = newOrderNo WHERE SUBSTRING(order_no, 1, 3) = orderNamePre;  -- 更新当前站点名下的物流单号
		
  SELECT     
    newOrderNo ;    
END

3.测试

4.使用java代码调用存储过程

 

(1)、无返回值的存储过程调用 

  public class JdbcTemplateTest {   
    private JdbcTemplate jdbcTemplate;   
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {   
    this.jdbcTemplate = jdbcTemplate;   
    }   
    public void test(){   
       this.jdbcTemplate.execute("call generate_orderNo('902')");   
    }   
  }   

(2)、有返回值的存储过程（非结果集）

public void test() { 
  String param2Value = (String) jdbcTemplate.execute( 
     new CallableStatementCreator() { 
        public CallableStatement createCallableStatement(Connection con) throws SQLException { 
           String storedProc = "{call generate_orderNo(?,?)}";// 调用的sql 
           CallableStatement cs = con.prepareCall(storedProc); 
           cs.setString(1, "p1");// 设置输入参数的值 
           cs.registerOutParameter(2,OracleTypes.Varchar);// 注册输出参数的类型 
           return cs; 
        } 
     }, new CallableStatementCallback() { 
         public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { 
           cs.execute(); 
           return cs.getString(2);// 获取输出参数的值 
     } 
  }); 
}

(3)、有返回值的存储过程（结果集）

public void test() { 
  List resultList = (List) jdbcTemplate.execute( 
     new CallableStatementCreator() { 
        public CallableStatement createCallableStatement(Connection con) throws SQLException { 
           String storedProc = "{call generate_oderNo(?,?)}";// 调用的sql 
           CallableStatement cs = con.prepareCall(storedProc); 
           cs.setString(1, "p1");// 设置输入参数的值 
           cs.registerOutParameter(2, OracleTypes.CURSOR);// 注册输出参数的类型 
           return cs; 
        } 
     }, new CallableStatementCallback() { 
        public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException { 
           List resultsMap = new ArrayList(); 
           cs.execute(); 
           ResultSet rs = (ResultSet) cs.getObject(2);// 获取游标一行的值 
           while (rs.next()) {// 转换每行的返回值到Map中 
              Map rowMap = new HashMap(); 
              rowMap.put("id", rs.getString("id")); 
              rowMap.put("name", rs.getString("name")); 
              resultsMap.add(rowMap); 
           } 
           rs.close(); 
           return resultsMap; 
        } 
  }); 
  for (int i = 0; i < resultList.size(); i++) { 
     Map rowMap = (Map) resultList.get(i); 
     String id = rowMap.get("id").toString(); 
     String name = rowMap.get("name").toString(); 
     System.out.println("id=" + id + ";name=" + name); 
  } 
}