import java.io.PrintWriter;
import java.sql.*;
import java.util.*;
/**
 * 
 * 
 * @author $Author$
 * @version $Revision$
 */
public class Inserter {
	
	public static final String JDBC_URL_PREFIX="jdbc:postgresql://";
	public static final String DB_HOST="asiklosi.ionlab.inland";
	public static final String DB_NAME="smsbar";
	
	public static final String DB_USERNAME="teszt";
	public static final String DB_PASSWORD="teszt";
	
	public static final int DB_CONNECTION_NUMBER=20;
	
	public static final int INSERT_NUMBER=200;
	public static final String MAX_ID_QUERY="SELECT MAX(id) as top FROM test";
	public static final String FAKE_INSERT="INSERT INTO test (id) VALUES (?)";
	
	public static final int THREAD_LOOP_NUMBER=200;
	
	public static final int CONCURRENT_INSERTER_NUMBER=10;
	
	private static Vector dbConnectionPool=new Vector();
	private static Vector takenConnectionPool=new Vector();

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
    	Inserter ins=new Inserter();
    }
    
    public Inserter() throws ClassNotFoundException, SQLException{
		Class.forName("org.postgresql.Driver");
         DriverManager.setLogWriter(new PrintWriter(System.out));
		//establish all connections with database
		for (int i = 0; i < DB_CONNECTION_NUMBER; i++) {
				Connection con =
					DriverManager.getConnection(
						JDBC_URL_PREFIX
							+ DB_HOST
							+ "/"
							+ DB_NAME,
						DB_USERNAME,
						DB_PASSWORD);
				dbConnectionPool.add(con);
		}
		System.out.println("CONNECTIONS OPEN ("+dbConnectionPool.size()+")");
		
		for(int i=0;i<CONCURRENT_INSERTER_NUMBER;i++){
			Inserter.InsertThread it=new Inserter.InsertThread();
			it.start();
		}
    }
    
    public static Connection getConnection(){
		Connection con;
    	synchronized(dbConnectionPool){
    		con=(Connection)dbConnectionPool.get(0);
    		dbConnectionPool.remove(con);
    		takenConnectionPool.add(con);
    	}
		return con;
    }
    
    public static void releaseConnection(Connection con){
    	synchronized(dbConnectionPool){
	    	takenConnectionPool.remove(con);
	    	dbConnectionPool.add(con);
    	}
    }
    
    private static int getTopId(Connection con) throws SQLException{
    	
		Statement qStmt=con.createStatement();
		ResultSet RS=qStmt.executeQuery(MAX_ID_QUERY);
		
		int id=0;
		
		while(RS.next()){
			id=RS.getInt("top");
		}
		return id;
    }
    
    private class InsertThread extends Thread{
    	public void run(){
    		try {
    			for(int i=0;i<Inserter.THREAD_LOOP_NUMBER;i++){
					Inserter.doInserts(Inserter.INSERT_NUMBER);
    			}
            } catch (SQLException e) {
                e.printStackTrace();
            }
    	}
    }
    
    public static void doInserts(int number) throws SQLException{
    	
    	for(int i=0;i<number;i++){
	    	Connection con=getConnection();
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
	    	con.setAutoCommit(false);
	    	
	    	//System.out.println("EQUALS SERIAL : "+(con.getTransactionIsolation()==Connection.TRANSACTION_SERIALIZABLE));
	    	
			int id=getTopId(con);
			
			System.out.println("Current top id (before insert):"+id);
			
			PreparedStatement stmt=con.prepareStatement(FAKE_INSERT);		
			stmt.setInt(1,id+1);
			stmt.executeUpdate();
			con.commit();
			System.out.println("Current top id (after insert):"+getTopId(con));
			con.setAutoCommit(true);
			releaseConnection(con);
    	}
    }
}