msequence.java

大家共享愉快, 共享愉快, 共享愉快, 共享愉快,共享愉快

				+ "WHERE s.AD_Client_ID=?)";
		int counter = 0;
		boolean success = true;
		//
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement(sql, trxName);
			pstmt.setInt(1, AD_Client_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				String tableName = rs.getString(1);
				s_log.fine("Add: " + tableName);
				MSequence seq = new MSequence (ctx, AD_Client_ID, tableName, trxName);
				if (seq.save())
					counter++;
				else
				{
					s_log.severe ("Not created - AD_Client_ID=" + AD_Client_ID
						+ " - "  + tableName);
					success = false;
				}
			}
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			s_log.log(Level.SEVERE, sql, e);
		}
		try
		{
			if (pstmt != null)
				pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			pstmt = null;
		}
		s_log.info ("AD_Client_ID=" + AD_Client_ID 
			+ " - created #" + counter
			+ " - success=" + success);
		return success;
	}	//	checkClientSequences
	

	/**
	 * 	Create Table ID Sequence
	 * 	@param ctx context
	 * 	@param TableName table name
	 * 	@return true if created
	 */
	public static boolean createTableSequence (Properties ctx, String TableName, String trxName)
	{
		MSequence seq = new MSequence (ctx, 0, trxName);
		seq.setClientOrg(0, 0);
		seq.setName(TableName);
		seq.setDescription("Table " + TableName);
		seq.setIsTableID(true);
		return seq.save();
	}	//	createTableSequence
		

	/**
	 * 	Get Sequence
	 *	@param ctx context
	 *	@param tableName table name
	 */
	public static MSequence get (Properties ctx, String tableName)
	{
		String sql = "SELECT * FROM AD_Sequence "
			+ "WHERE UPPER(Name)=?"
			+ " AND IsTableID='Y'";
		MSequence retValue = null;
		PreparedStatement pstmt = null;
		try
		{
			pstmt = DB.prepareStatement (sql, null);
			pstmt.setString (1, tableName.toUpperCase());
			ResultSet rs = pstmt.executeQuery ();
			if (rs.next ())
				retValue = new MSequence (ctx, rs, null);
			if (rs.next())
				s_log.log(Level.SEVERE, "More then one sequence for " + tableName);
			rs.close ();
			pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			s_log.log(Level.SEVERE, "get", e);
		}
		try
		{
			if (pstmt != null)
				pstmt.close ();
			pstmt = null;
		}
		catch (Exception e)
		{
			pstmt = null;
		}
		return retValue;
	}	//	get
	
	
	/**	Sequence for Table Document No's	*/
	private static final String	PREFIX_DOCSEQ = "DocumentNo_";
	/**	Start Number			*/
	public static final int		INIT_NO = 1000000;	//	1 Mio
	/**	Start System Number		*/
	public static final int		INIT_SYS_NO = 100;	
	/** Static Logger			*/
	private static CLogger 		s_log = CLogger.getCLogger(MSequence.class);
	
	
	/**************************************************************************
	 *	Standard Constructor
	 *	@param ctx context
	 *	@param AD_Sequence_ID id
	 */
	public MSequence (Properties ctx, int AD_Sequence_ID, String trxName)
	{
		super(ctx, AD_Sequence_ID, trxName);
		if (AD_Sequence_ID == 0)
		{
		//	setName (null);
			//
			setIsTableID(false);
			setStartNo (INIT_NO);
			setCurrentNext (INIT_NO);
			setCurrentNextSys (INIT_SYS_NO);
			setIncrementNo (1);
			setIsAutoSequence (true);
			setIsAudited(false);
			setStartNewYear(false);
		}
	}	//	MSequence

	/**
	 * 	Load Constructor
	 *	@param ctx context
	 *	@param rs result set
	 */
	public MSequence (Properties ctx, ResultSet rs, String trxName)
	{
		super(ctx, rs, trxName);
	}	//	MSequence

	/**
	 * 	New Document Sequence Constructor
	 *	@param ctx context
	 *	@param AD_Client_ID owner
	 *	@param tableName name
	 */
	public MSequence (Properties ctx, int AD_Client_ID, String tableName, String trxName)
	{
		this (ctx, 0, trxName);
		setClientOrg(AD_Client_ID, 0);			//	Client Ownership
		setName(PREFIX_DOCSEQ + tableName);
		setDescription("DocumentNo/Value for Table " + tableName);
	}	//	MSequence;
	
	/**
	 * 	New Document Sequence Constructor
	 *	@param ctx context
	 *	@param AD_Client_ID owner
	 *	@param sequenceName name
	 *	@param StartNo start
	 *	@param trxName trx
	 */
	public MSequence (Properties ctx, int AD_Client_ID, String sequenceName, int StartNo, String trxName)
	{
		this (ctx, 0, trxName);
		setClientOrg(AD_Client_ID, 0);			//	Client Ownership
		setName(sequenceName);
		setDescription(sequenceName);
		setStartNo(StartNo);
		setCurrentNext(StartNo);
		setCurrentNextSys(StartNo/10);
	}	//	MSequence;
	
	
	/**************************************************************************
	 * 	Get Next No and increase current next
	 *	@return next no to use
	 */
	public int getNextID()
	{
		int retValue = getCurrentNext();
		setCurrentNext(retValue + getIncrementNo());
		return retValue;
	}	//	getNextNo
	
	/**
	 * 	Get next DocumentNo
	 *	@return document no
	 */
	public String getDocumentNo()
	{
		//	create DocumentNo
		StringBuffer doc = new StringBuffer();
		String prefix = getPrefix();
		if (prefix != null && prefix.length() > 0)
			doc.append(prefix);
		doc.append(getNextID());
		String suffix = getSuffix();
		if (suffix != null && suffix.length() > 0)
			doc.append(suffix);
		return doc.toString();
	}	//	getDocumentNo
	
	/**
	 * 	Validate Table Sequence Values
	 *	@return true if updated
	 */
	public boolean validateTableIDValue()
	{
		if (!isTableID())
			return false;
		String tableName = getName();
		int AD_Column_ID = DB.getSQLValue(null, "SELECT MAX(c.AD_Column_ID) "
			+ "FROM AD_Table t"
			+ " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID) "
			+ "WHERE t.TableName='" + tableName + "'"
			+ " AND c.ColumnName='" + tableName + "_ID'");
		if (AD_Column_ID <= 0)
			return false;
		//
		MSystem system = MSystem.get(getCtx());
		int IDRangeEnd = 0;
		if (system.getIDRangeEnd() != null)
			IDRangeEnd = system.getIDRangeEnd().intValue();
		boolean change = false;
		String info = null;
		
		//	Current Next
		String sql = "SELECT MAX(" + tableName + "_ID) FROM " + tableName;
		if (IDRangeEnd > 0)
			sql += " WHERE " + tableName + "_ID < " + IDRangeEnd;
		int maxTableID = DB.getSQLValue(null, sql);
		if (maxTableID < INIT_NO)
			maxTableID = INIT_NO - 1;
		maxTableID++;		//	Next
		if (getCurrentNext() < maxTableID)
		{
			setCurrentNext(maxTableID);
			info = "CurrentNext=" + maxTableID; 
			change = true;
		}

		//	Get Max System_ID used in Table
		sql = "SELECT MAX(" + tableName + "_ID) FROM " + tableName
			+ " WHERE " + tableName + "_ID < " + INIT_NO;
		int maxTableSysID = DB.getSQLValue(null, sql);
		if (maxTableSysID <= 0)
			maxTableSysID = INIT_SYS_NO - 1;
		maxTableSysID++;	//	Next
		if (getCurrentNextSys() < maxTableSysID)
		{
			setCurrentNextSys(maxTableSysID);
			if (info == null)
				info = "CurrentNextSys=" + maxTableSysID;
			else
				info += " - CurrentNextSys=" + maxTableSysID;
			change = true;
		}
		if (info != null)
			log.fine(getName() + " - " + info);
		return change;
	}	//	validate
	
	
	/**************************************************************************
	 *	Test
	 *	@param args ignored
	 */
	static public void main (String[] args)
	{
		org.compiere.Compiere.startup(true);
		CLogMgt.setLevel(Level.SEVERE);
		CLogMgt.setLoggerLevel(Level.SEVERE, null);
		s_list = new Vector<Integer>(1000);

		/**	Lock Test **
		String trxName = "test";
		System.out.println(DB.getDocumentNo(115, trxName));
		System.out.println(DB.getDocumentNo(116, trxName));
		System.out.println(DB.getDocumentNo(117, trxName));
		System.out.println(DB.getDocumentNo(118, trxName));
		System.out.println(DB.getDocumentNo(118, trxName));
		System.out.println(DB.getDocumentNo(117, trxName));
		
		trxName = "test1";
		System.out.println(DB.getDocumentNo(115, trxName));	//	hangs here as supposed
		System.out.println(DB.getDocumentNo(116, trxName));
		System.out.println(DB.getDocumentNo(117, trxName));
		System.out.println(DB.getDocumentNo(118, trxName));

		
		
		
		
		/** **/
		
		/** Time Test	*/
		long time = System.currentTimeMillis();
		Thread[] threads = new Thread[10];
		for (int i = 0; i < 10; i++)
		{
			Runnable r = new GetIDs(i); 
			threads[i] = new Thread(r);
			threads[i].start();
		}
		for (int i = 0; i < 10; i++)
		{
			try
			{
				threads[i].join();
			}
			catch (InterruptedException e)
			{
			}
		}
		time = System.currentTimeMillis() - time;
		
		System.out.println("-------------------------------------------");
		System.out.println("Size=" + s_list.size() + " (should be 1000)");
		Integer[] ia = new Integer[s_list.size()];
		s_list.toArray(ia);
		Arrays.sort(ia);
		Integer last = null;
		int duplicates = 0;
		for (int i = 0; i < ia.length; i++)
		{
			if (last != null)
			{
				if (last.compareTo(ia[i]) == 0)
				{
				//	System.out.println(i + ": " + ia[i]);
					duplicates++;
				}
			}
			last = ia[i];
		}
		System.out.println("-------------------------------------------");
		System.out.println("Size=" + s_list.size() + " (should be 1000)");
		System.out.println("Duplicates=" + duplicates);
		System.out.println("Time (ms)=" + time + " - " + ((float)time/s_list.size()) + " each" );
		System.out.println("-------------------------------------------");
		
		
		
		/** **
		try
		{
			int retValue = -1;
			Connection conn = DB.getConnectionRW ();
		//	DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
		//	Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@//dev2:1521/dev2", "compiere", "compiere");

			conn.setAutoCommit(false);
			String sql = "SELECT CurrentNext, CurrentNextSys, IncrementNo "
				+ "FROM AD_Sequence "
				+ "WHERE Name='AD_Sequence' ";
			sql += "FOR UPDATE";
			//	creates ORA-00907: missing right parenthesis
		//	sql += "FOR UPDATE OF CurrentNext, CurrentNextSys";
			

			PreparedStatement pstmt = conn.prepareStatement(sql,
				ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			ResultSet rs = pstmt.executeQuery();
			System.out.println("AC=" + conn.getAutoCommit() + ", RO=" + conn.isReadOnly()
				+ " - Isolation=" + conn.getTransactionIsolation() + "(" + Connection.TRANSACTION_READ_COMMITTED
				+ ") - RSType=" + pstmt.getResultSetType() + "(" + ResultSet.TYPE_SCROLL_SENSITIVE  
				+ "), RSConcur=" + pstmt.getResultSetConcurrency() + "(" + ResultSet.CONCUR_UPDATABLE
				+ ")");
			
			if (rs.next())
			{
				int IncrementNo = rs.getInt(3);
				retValue = rs.getInt(1);
				rs.updateInt(1, retValue + IncrementNo);
				rs.updateRow();
			}
			else
				s_log.severe ("no record found");
			rs.close();
			pstmt.close();
			conn.commit();
			conn.close();
			//
			System.out.println("Next=" + retValue);
			
		}
		catch (Exception e)
		{
			e.printStackTrace ();
		}
		
		System.exit(0);
				
		/** **
			
		int AD_Client_ID = 0;
		int C_DocType_ID = 115;	//	GL
		String TableName = "C_Invoice";
		String trxName = "x";
		Trx trx = Trx.get(trxName, true);		
				
		System.out.println ("none " + getNextID (0, "Test"));
		System.out.println ("----------------------------------------------");
		System.out.println ("trx1 " + getNextID (0, "Test"));
		System.out.println ("trx2 " + getNextID (0, "Test"));
	//	trx.rollback();
		System.out.println ("trx3 " + getNextID (0, "Test"));
	//	trx.commit();
		System.out.println ("trx4 " + getNextID (0, "Test"));
	//	trx.rollback();
	//	trx.close();
		System.out.println ("----------------------------------------------");		
		System.out.println ("none " + getNextID (0, "Test"));
		System.out.println ("==============================================");		
		

		trx = Trx.get(trxName, true);		
		System.out.println ("none " + getDocumentNo(AD_Client_ID, TableName, null));
		System.out.println ("----------------------------------------------");
		System.out.println ("trx1 " + getDocumentNo(AD_Client_ID, TableName, trxName));
		System.out.println ("trx2 " + getDocumentNo(AD_Client_ID, TableName, trxName));
		trx.rollback();
		System.out.println ("trx3 " + getDocumentNo(AD_Client_ID, TableName, trxName));
		trx.commit();
		System.out.println ("trx4 " + getDocumentNo(AD_Client_ID, TableName, trxName));
		trx.rollback();
		trx.close();
		System.out.println ("----------------------------------------------");		
		System.out.println ("none " + getDocumentNo(AD_Client_ID, TableName, null));
		System.out.println ("==============================================");		


		trx = Trx.get(trxName, true);		
		System.out.println ("none " + getDocumentNo(C_DocType_ID, null));
		System.out.println ("----------------------------------------------");
		System.out.println ("trx1 " + getDocumentNo(C_DocType_ID, trxName));
		System.out.println ("trx2 " + getDocumentNo(C_DocType_ID, trxName));
		trx.rollback();
		System.out.println ("trx3 " + getDocumentNo(C_DocType_ID, trxName));
		trx.commit();
		System.out.println ("trx4 " + getDocumentNo(C_DocType_ID, trxName));
		trx.rollback();
		trx.close();
		System.out.println ("----------------------------------------------");		
		System.out.println ("none " + getDocumentNo(C_DocType_ID, null));
		System.out.println ("==============================================");		
		/** **/
	}	//	main

	private static Vector<Integer> s_list = null; 
	
	public static class GetIDs implements Runnable
	{
		public GetIDs (int i)
		{
			m_i = i;
		}
		private int m_i;
		
		public void run()
		{
			for (int i = 0; i < 100; i++)
			{
				try
				{
					int no = DB.getNextID(0, "Test", null);
					s_list.add(new Integer(no));
				//	System.out.println("#" + m_i + ": " + no);
				}
				catch (Exception e)
				{
					System.err.println(e.getMessage());
				}
			}
		}
	}	//	GetIDs
	
}	//	MSequence