Z2jskm86

1// Source code file LoadGames.java
2// Load data from CSV file games.csv
3// into the database table games
4
5import java.io.File;
6import java.io.FileNotFoundException;
7import java.sql.Connection;
8import java.sql.DriverManager;
9import java.sql.SQLException;
10import java.sql.Statement;
11import java.util.Scanner;
12
13public class LoadGames {
14
15	public static void main(String[] args) {
16        Connection c = null;
17        Statement s = null;
18        Scanner fromFile = null;
19        String sql1 = null, sql2 = null;
20        String line = null;
21        String[] fields;
22        String name = null, rating = null, condition = null, platform = null;
23        double price = 0.0;
24
25        try {
26            // Define Connection and Statement objects.
27            Class.forName("org.sqlite.JDBC");
28            c = DriverManager.getConnection("jdbc:sqlite:games.db");
29            s = c.createStatement();
30
31            // Instantiate scanner to read from file.
32            fromFile = new Scanner(new File ("games.csv"));
33            
34            // Create kids table.
35            sql1 = "create table if not exists " +
36                "games(gameid integer, " +
37                "name varchar(20), " +
38                "rating varchar(1), " +
39                "condition varchar(5), " +
40                "platform varchar(20), " +
41                "price double);";
42            System.out.println("sql1: " + sql1);
43            s.executeUpdate(sql1);
44            
45            // Read and throw away header line.
46            fromFile.nextLine();
47            
48            // Populate kids table.
49            for (int id = 1001; fromFile.hasNextLine(); id++) {
50                line = fromFile.nextLine();
51                fields = line.split(",");
52                name = fields[0].trim();
53                rating = fields[1].trim();
54                condition = fields[2].trim();
55                platform = fields[3].trim();
56                price = Double.parseDouble(fields[4].trim());
57                sql2 = String.format(
58                    "insert into games (gameid, name, rating, condition, platform, price) " +
59                    "values (%d, '%s', '%s', '%s', '%s', %.2f);", 
60                    id, name, rating, condition, platform, price);
61                System.out.println(sql2);
62                s.executeUpdate(sql2);
63            }
64            c.close( );
65        }
66        catch (FileNotFoundException e) {
67            System.out.println("File queries.sql not found.");
68            System.err.println( e.getClass().getName() + 
69                ": " + e.getMessage() );
70        }
71        catch(SQLException e) {
72            System.out.println("SQLException.");
73            System.err.println( e.getClass().getName() + 
74                ": " + e.getMessage() );
75        }
76        catch (ClassNotFoundException e ) {
77            System.err.println( e.getClass().getName() + 
78                ": " + e.getMessage() );
79        }
80        finally {
81            fromFile.close( );
82        }
83    }
84}