// JDBC Example; LoadTransactions Class
// Source code file LoadKids.java
// Populate table from comma delimited
// file transactions.txt into the database
// table kids

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class LoadTransactions {
    public static void main(String[] args) {
        // Define local variables.
        Connection c = null;
        Statement s = null;
        Scanner fromFile = null;
        String sql1 = null, sql2 = null;
        String line = null, buyer = null, seller = null,
            timestamp = null;
        String[ ] fields;
        double amount;

        try {
            // Define Connection and Statement objects.
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:transactions.db");
            s = c.createStatement();

            // Instantiate scanner to read from file.
            fromFile = new Scanner(new File ("transactions.txt"));

            // Create kids table.
            sql1 = "create table if not exists " +
                   "transactions(id integer, " +
                   "buyer varchar(20), " +
                   "seller varchar(20), " +
                   "amount float, " +
                   "timestamp varchar(25));";
            System.out.println("sql1: " + sql1);
            s.executeUpdate(sql1);

            // Read and throw away header line.
            fromFile.nextLine( );

            // Populate kids table.
            for (int id = 1001; fromFile.hasNextLine( ); id++) {
                line = fromFile.nextLine( );
                fields = line.split(",");
                buyer = fields[0].trim( );
                seller = fields[1].trim( );
                amount = Double.parseDouble(fields[2].trim( ));
                timestamp = fields[3].trim( );
                sql2 = String.format(
                        "insert into transactions " +
                        "(id, buyer, seller, amount, timestamp) " +
                        "values (%d, '%s', '%s', %.2f, '%s');",
                        id, buyer, seller, amount, timestamp);
                System.out.println(sql2);
                s.executeUpdate(sql2);
            }

            // Close Connection
            c.close( );
        }
        catch (FileNotFoundException e) {
            System.out.println("File queries.sql not found.");
            System.err.println( e.getClass( ).getName( ) +
                    ": " + e.getMessage( ) );
        }
        catch(SQLException e) {
            System.out.println("SQLException.");
            System.err.println( e.getClass( ).getName( ) +
                    ": " + e.getMessage( ) );
        }
        catch (ClassNotFoundException e ) {
            System.err.println( e.getClass().getName( ) +
                    ": " + e.getMessage( ));
        }
        finally {
            fromFile.close( );
        }
    }
}