使用testng多线程来测试成交编号重复的问题

1.首先编写一个测试用的 function

 1 CREATE OR REPLACE FUNCTION getDealmainNo_test(dealdate        IN varchar2,
 2                                          productcodename in varchar2)
 3 /***********************************************************
 4   * linc 2018-01-09
 5   * 方法：获取序列编号
 6   * 入参：成交日期，产品代码
 7   * 返回：序列编号
 8   ***********************************************************/
 9  RETURN varchar2 AS
10   today     varchar2(10);
11   seqno     integer;
12   begindate varchar2(20) := dealdate || ' 00:00:00';
13   enddate   varchar2(20) := dealdate || ' 23:59:59';
14   seqnoname varchar2(30);
15   pragma autonomous_transaction;
16 BEGIN
17   seqno := 0;
18   select to_char(sysdate, 'yyyy-mm-dd') into today from dual;
19   if today = dealdate then
20     execute immediate 'select seq_' || productcodename ||
21                       '_no.nextval from dual'
22       into seqno;
23   else
24     begin
25       select nvl(max(to_number(substr(s.tno, -6))), 0) + 1
26         into seqno
27         from (select s.tno
28                 from table_A s
29                where s.dealdate between
30                      to_date(begindate, 'yyyy-mm-dd hh24:mi:ss') and
31                      to_date(enddate, 'yyyy-mm-dd hh24:mi:ss')
32                  and s.tcode = productcodename) s  ;
33 
34     exception
35       when others then
36         return '';
37     end;
38   end if;
39   seqnoname := productcodename || replace(dealdate, '-', '') ||
40                lpad(seqno, 6, '0');
41   RETURN seqnoname;
42 END getDealmainNo_test;

2.使用java对func进行调用每次获得一个最大的成交编号；

  1 package main.java.javaprictice;
  2 
  3 import main.java.C3P0XmlSimplify;
  4 import org.testng.annotations.Test;
  5 
  6 import java.sql.*;
  7 import java.text.SimpleDateFormat;
  8 import java.util.Date;
  9 
 10 /**
 11  * Created by linbo.yang on 2018/1/9.
 12  */
 13 public class CallOracleFunc {
 14     Connection conn = null;
 15     CallableStatement callableStatement = null;
 16     PreparedStatement ps = null;
 17     ResultSet rs;
 18 
 19     public Integer save(String no) {
 20         
 21         Integer rowCount=null ;
 22         String sql="insert into table_A(dealdate,tno,tdate,tcode) values(?,?,?,?)  ";
 23         conn = C3P0XmlSimplify.getInstance().getConnection();
 24         //ddate
 25         SimpleDateFormat df =new SimpleDateFormat("yyyy-MM-dd HH24:mm:ss");
 26         String date = df.format(new Date());
 27         Timestamp timestamp = Timestamp.valueOf(date);
 28 //dealdate
 29         SimpleDateFormat df1 =new SimpleDateFormat("yyyy-MM-dd");
 30         String dealdate = df1.format(new Date());
 31 
 32 
 33         try {
 34             ps = conn.prepareStatement(sql);
 35             ps.setDate(1,java.sql.Date.valueOf("2018-01-08"));
 36             ps.setString(2,no);
 37             ps.setTimestamp(3,timestamp);
 38             ps.setString(4,"IBO");
 39             rowCount=  ps.executeUpdate();
 40             System.out.println("rowCount"+rowCount);
 41         } catch (SQLException e) {
 42             e.printStackTrace();
 43         }finally {
 44             C3P0XmlSimplify.releaseSources(conn);
 45             return rowCount ;
 46         }
 47 
 48 
 49     }
 50 
 51 
 52 
 53 
 54 
 55     public String getDealmainNo() {
 56         String string = null;
 57         try {
 58 
 59             conn = C3P0XmlSimplify.getInstance().getConnection();
 60            /*  ?=call getDealmainNo(?,?);
 61            *  第一个问号（？） 代表输入值，但要制定输入类型:callableStatement.registerOutParameter(1, Types.VARCHAR);
 62            *  第二个问号（？） 代表输入值，通过callableStatement.setString（）方法类制定入参的类型 ；
 63            *  。。。。。。。
 64            * */
 65             callableStatement = conn.prepareCall("{?=call getDealmainNo_test(?,?)}");
 66             callableStatement.registerOutParameter(1, Types.VARCHAR);
 67             callableStatement.setString(2, "2018-01-08");
 68             callableStatement.setString(3, "IBO");
 69             callableStatement.execute();
 70             string = callableStatement.getString(1);
 71             System.out.println(string);
 72 
 73         } catch (Exception e) {
 74             e.printStackTrace();
 75         } finally {
 76             C3P0XmlSimplify.releaseSources(conn);
 77             return string;
 78         }
 79     }
 80 @Test(threadPoolSize = 3,invocationCount = 10,timeOut = 100000)
 81     public  void getDealmainNo_() {
 82     CallOracleFunc c=new CallOracleFunc();
 83     String dealmainNo = c.getDealmainNo();
 84     Integer row = c.save(dealmainNo);
 85     System.out.println("dealmainNo="+dealmainNo+"  ;row="+row);
 86 
 87     }
 88 
 89 
 90     public static void main(String[] args) {
 91         CallOracleFunc c=new CallOracleFunc();
 92         String dealmainNo = c.getDealmainNo();
 93         Integer row = c.save(dealmainNo);
 94         System.out.println("dealmainNo="+dealmainNo+"  ;row="+row);
 95 
 96 
 97     }
 98 
 99 
100 }

--数据的相关脚本

 1    create table table_A(
 2    tid number ,
 3    dealdate date ,
 4    tcode varchar2(100),
 5    tno varchar2(100),
 6    tdate timestamp
 7    ) ;         
 8     drop table table_A ;        
 9    select * from table_A  t where t.tid is null ;
10    delete table_A  t where t.tid is null ;
11  insert into table_A
12    select v.ID, 
13    v.DEALDATE,
14    v.PRODUCTCODE,
15    v.NO, 
16    v.CREATETIME
17     from v_product_deal_main v ;
18     insert into table_A(tno,tdate) values(?,?) ;
19 
20       select nvl(max(to_number(substr(s.tno, -6))), 0) + 1
21         from (select s.tno
22                 from table_A s
23                where s.dealdate between
24                      to_date('2018-01-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
25                      to_date('2018-01-08 23:00:00', 'yyyy-mm-dd hh24:mi:ss')
26                  and s.tcode = 'IBO') s