82nCP5g4

1import java.sql.*;
2import java.util.ArrayList;
3
4public class Database {
5
6    private String path;
7    private Connection conn = null;
8
9    public Database(String path) {
10        this.path = path;
11    }
12
13    public void connect() {
14        try {
15            // db parameters
16            String url = "jdbc:sqlite:" + this.path;
17            // create a connection to the database
18            conn = DriverManager.getConnection(url);
19            System.out.println("Connection to SQLite has been established.");
20        } catch (SQLException e) {
21            System.out.println(e.getMessage());
22        }
23    }
24
25    public void createInitTables() {
26        // SQLite connection string
27        String url = "jdbc:sqlite:" + this.path;
28
29        // SQL statement for creating a new table
30        String bookmakers_sql = "CREATE TABLE IF NOT EXISTS bookmakers (\n"
31                + "    id integer PRIMARY KEY,\n"
32                + "    name text NOT NULL,\n"
33                + "    infoUrl text NOT NULL,\n"
34                + "    detailsUrl text NOT NULL\n"
35                + ");";
36
37        // SQL statement for creating a new table
38        String users_sql = "CREATE TABLE IF NOT EXISTS users (\n"
39                + "    id integer PRIMARY KEY,\n"
40                + "    username text NOT NULL,\n"
41                + "    password text NOT NULL\n"
42                + ");";
43
44        try (Connection conn = DriverManager.getConnection(url);
45             Statement stmt = conn.createStatement()) {
46            // create a new table
47            stmt.execute(bookmakers_sql);
48            stmt.execute(users_sql);
49        } catch (SQLException e) {
50            System.out.println(e.getMessage());
51        }
52    }
53
54    public void insertUsers(String username, String password) {
55        String sql = "INSERT INTO users (username, password) VALUES(?, ?)";
56
57        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
58            pstmt.setString(1, username);
59            pstmt.setString(2, password);
60            pstmt.executeUpdate();
61        } catch (SQLException e) {
62            System.out.println(e.getMessage());
63        }
64    }
65
66    public void insertBookmaker(String name, String infoUrl, String  detailsUrl) {
67        String sql = "INSERT INTO bookmakers (name, infoUrl, detailsUrl) VALUES(?, ?, ?)";
68
69        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
70            pstmt.setString(1, name);
71            pstmt.setString(2, infoUrl);
72            pstmt.setString(3, detailsUrl);
73            pstmt.executeUpdate();
74        } catch (SQLException e) {
75            System.out.println(e.getMessage());
76        }
77    }
78
79    public ArrayList<Bookmaker> selectAllBookmakers(){
80        ArrayList<Bookmaker> bookmakers = new ArrayList<>();
81        String sql = "SELECT name, infoUrl, detailsUrl FROM bookmakers";
82
83        try (Statement stmt  = conn.createStatement();
84             ResultSet rs    = stmt.executeQuery(sql)){
85
86            while (rs.next()) {
87                Bookmaker bookmaker = new Bookmaker(rs.getString("name"), rs.getString("infoUrl"), rs.getString("detailsUrl"));
88                bookmakers.add(bookmaker);
89            }
90            return bookmakers;
91        } catch (SQLException e) {
92            System.out.println(e.getMessage());
93        }
94        return new ArrayList<>();
95    }
96
97    public void closeConnection() {
98        try {
99            if (conn != null) {
100                conn.close();
101            }
102        } catch (SQLException ex) {
103            System.out.println(ex.getMessage());
104        }
105    }
106}