import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;

public class AutoCommitTest
{

    private static final String DBNAME = "testdb";
    public String framework = "embedded";
    public String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    public String protocol = "jdbc:derby:";
    protected Connection conn;

    private static final Random random = new Random();

    /**
     * 
     */
    public AutoCommitTest()
    {
        super();
        connect();
        try
        {
            test2();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    public static String randomAscii(int count)
    {
        String result = "";
        for (int i = 0; i < count; i++)
        {
            result += (char) (random.nextInt(95) + 32); //char in range 32 -> 127
        }

        return result.replaceAll("'", "''");
    }

    /**
     * This demonstrates the problem 
     */
    private void test() throws SQLException
    {
        conn.setAutoCommit(false);
        for (int i = 0; i < 100000; i++)
        {
            int id = random.nextInt(100000);

            ResultSet qID = query("SELECT id FROM test WHERE id = " + id);

            boolean insert = true;
            if (qID.next())
            {
                //update
                //code missing here, but this demonstrates the problem anyway
            }
            else
            {
                //insert
                String sql = "INSERT INTO test(id, s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12,s13,s14,s15,i1,i2,i3,i4,i5,i6) VALUES("
                        + id
                        + ",'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + "'"
                        + randomAscii(50)
                        + "',"
                        + random.nextInt()
                        + ","
                        + random.nextInt()
                        + ","
                        + random.nextInt()
                        + ","
                        + random.nextInt()
                        + ","
                        + random.nextInt()
                        + ","
                        + random.nextInt() + ")";
                //   System.out.println(sql);
                execute(sql);
            }

            if ((i % 100) == 0)
                System.out.println(i);
        }
        conn.commit();
        conn.setAutoCommit(true);
    }

    /**
     * this test *doesn't* cause an error
     */
    private void test2() throws SQLException
    {
        conn.setAutoCommit(false);
        for (int i = 0; i < 100000; i++)
        {
            int id = random.nextInt(100000);

            //insert
            String sql = "INSERT INTO test(id, s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12,s13,s14,s15,i1,i2,i3,i4,i5,i6) VALUES("
                    + id
                    + ",'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + "'"
                    + randomAscii(50)
                    + "',"
                    + random.nextInt()
                    + ","
                    + random.nextInt()
                    + ","
                    + random.nextInt()
                    + ","
                    + random.nextInt()
                    + ","
                    + random.nextInt()
                    + ","
                    + random.nextInt()
                    + ")";
            execute(sql);

            if ((i % 100) == 0)
                System.out.println(i);
        }
        conn.commit();
        conn.setAutoCommit(true);
    }

    void connect()
    {
        try
        {
            Class.forName(driver).newInstance();
        }
        catch (InstantiationException e)
        {
            System.out.println("Database instantiation error");
            e.printStackTrace();
        }
        catch (IllegalAccessException e)
        {
            System.out.println("Database access error");
            e.printStackTrace();
        }
        catch (ClassNotFoundException e)
        {
            System.out.println("Derby driver not found");
            e.printStackTrace();
        }

        try
        {
            conn = DriverManager.getConnection(protocol + DBNAME + ";create=true");
        }
        catch (SQLException e1)
        {
            e1.printStackTrace();
        }

        execute("DROP TABLE test"); 
        execute("CREATE TABLE test(id int NOT NULL, s1 varchar(50), s2 varchar(50), s3 varchar(50), s4 varchar(50),s5 varchar(50),"
                + "s6 varchar(50), "
                + "s7 varchar(50), "
                + "s8 varchar(50), "
                + "s9 varchar(50), "
                + "s10 varchar(50), "
                + "s11 varchar(50), "
                + "s12 varchar(50), "
                + "s13 varchar(50), "
                + "s14 varchar(50), "
                + "s15 varchar(50), "
                + "i1 int, i2 int, i3 int, i4 int, i5 int, i6 int)");

    }

    private void execute(String sql)
    {
        try
        {
            Statement stmt = conn.createStatement();
            stmt.execute(sql);
        }
        catch (SQLException e2)
        {
            e2.printStackTrace();
        }

    }

    private ResultSet query(String sql)
    {
        ResultSet result = null;
        Statement stmt;
        try
        {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sql);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return result;
    }

    public static void main(String[] args)
    {
        new AutoCommitTest();
    }
}