import java.sql.Connection;
import java.sql.PreparedStatement;
import java.time.Instant;
import java.util.Properties;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.logging.Logger;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Example {

    // singleton implementation, safe-thread

    private static class Singleton {
        private static final Example INSTANCE = new Example();
    }

    public static final Example getInstance() {
        return Singleton.INSTANCE;
    }

    // class implementation

    private static final String DB_DRIVER_CLASS;
    private static final String DB_JDBC_URL;
    private static final String DB_USER;
    private static final String DB_PASSWORD;

    static {
        DB_DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";
        DB_JDBC_URL = "jdbc:oracle:thin:@//localhost:1521/XE";
        DB_USER = "user";
        DB_PASSWORD = "pass";
    }

    private ComboPooledDataSource pool;
    Connection connection;
    private boolean isConnected;

    {
        this.pool = new ComboPooledDataSource();

        try {
            this.pool.setDriverClass(DB_DRIVER_CLASS);
            this.pool.setJdbcUrl(DB_JDBC_URL);
            this.pool.setUser(DB_USER);
            this.pool.setPassword(DB_PASSWORD);

            this.pool.setMinPoolSize(1);
            this.pool.setAcquireIncrement(5);
            this.pool.setMaxPoolSize(3);
            // this.pool.setUnreturnedConnectionTimeout(1);
            // this.pool.setDebugUnreturnedConnectionStackTraces(true);
            // this.pool.setMaxStatements(180);

            this.isConnected = true;
        } catch (Exception e) {
            this.isConnected = false;
            e.printStackTrace();
        }
    }

    public boolean isConnected() {
        return this.isConnected;
    }

    // if false, get connection is limited up to this.pool.setMaxPoolSize(?);
    // this call is block until connections are freed
    public Connection getConnection(boolean recycle) throws Exception {
        if (recycle) {
            if (this.connection == null && recycle)
                this.connection = this.pool.getConnection();
            return this.connection;
        } else {
            return this.pool.getConnection();
        }
    }

    // main

    public static void main(String[] args) throws Exception {
        Properties properties = new Properties(System.getProperties());
        properties.setProperty("ACS.logstdout", "04");
        properties.setProperty("net.sf.ehcache.skipUpdateCheck", "false");
        properties.setProperty("com.mchange.v2.log.MLog", "com.mchange.v2.log.FallbackMLog");
        properties.setProperty("com.mchange.v2.log.FallbackMLog.DEFAULT_CUTOFF_LEVEL", "OFF");
        properties.setProperty("java.util.logging.SimpleFormatter.format", "%1$tFT%1$tT %4$s [] %5$s%6$s%n");
        System.setProperties(properties);

        final boolean recycle = false;
        final AtomicInteger counter = new AtomicInteger(0);
        final Logger logger = Logger.getAnonymousLogger();

        ExecutorService executorService = Executors.newFixedThreadPool(50);

        final String sql = "insert into table1 (recordid) values(?)";

        int limit = 10;
        while (limit-- > 0) {
            executorService.submit(() -> {

                final int localCounter = counter.getAndIncrement();
                final long milli = Instant.now().toEpochMilli();
                final String threadName = Thread.currentThread().getName();

                final String message = String.format("threadId = %s\t-> counter = %d", threadName, localCounter);


                try (Connection connection = Example.getInstance().getConnection(recycle)) {
                    logger.info(String.format("%s\t-> %s", message, "connected"));

                    try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                        int inner = 20;
                        while (inner-- > 0) {
                            String recordid = String.format("%s_%d_%d_%d", threadName, localCounter, milli, inner);
                            preparedStatement.setString(1, recordid);
                            preparedStatement.addBatch();
                            // logger.info(String.format("%s\t-> %s", message, sql));
                        }

                        int insertions = preparedStatement.executeBatch().length;
                        logger.info(String.format("%s\t-> %s : %d", message, "inserted", insertions));
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                } catch (Exception e) {
                    logger.severe(String.format("%s\t-> %s", message, e.toString()));
                    e.printStackTrace();
                }

                logger.info(String.format("%s\t-> %s", message, "exiting"));
            });
        }

        executorService.shutdown();
        // Thread.sleep(480_000);
    }
}

/*

2017-06-16T12:18:27 INFO [] threadId = pool-1-thread-4  -> counter = 3  -> connected
2017-06-16T12:18:27 INFO [] threadId = pool-1-thread-8  -> counter = 7  -> connected
2017-06-16T12:18:27 INFO [] threadId = pool-1-thread-2  -> counter = 1  -> connected
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-2  -> counter = 1  -> inserted : 20
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-4  -> counter = 3  -> inserted : 20
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-8  -> counter = 7  -> inserted : 20
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-4  -> counter = 3  -> exiting
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-7  -> counter = 4  -> connected
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-8  -> counter = 7  -> exiting
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-6  -> counter = 6  -> connected
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-1  -> counter = 0  -> connected
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-2  -> counter = 1  -> exiting
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-7  -> counter = 4  -> inserted : 20
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-1  -> counter = 0  -> inserted : 20
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-7  -> counter = 4  -> exiting
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-9  -> counter = 8  -> connected
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-1  -> counter = 0  -> exiting
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-10 -> counter = 9  -> connected
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-6  -> counter = 6  -> inserted : 20
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-6  -> counter = 6  -> exiting
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-5  -> counter = 5  -> connected
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-10 -> counter = 9  -> inserted : 20
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-9  -> counter = 8  -> inserted : 20
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-10 -> counter = 9  -> exiting
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-9  -> counter = 8  -> exiting
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-3  -> counter = 2  -> connected
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-3  -> counter = 2  -> inserted : 20
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-5  -> counter = 5  -> inserted : 20
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-5  -> counter = 5  -> exiting
2017-06-16T12:18:28 INFO [] threadId = pool-1-thread-3  -> counter = 2  -> exiting


*/