HfjZnTf3

1import java.sql.*;
2
3public class Database {
4
5    private static final String dbname = "student.db";
6    private Connection conn;
7
8    public Database() {
9        connect();
10        createStudentsTable();
11        createResultsTable();
12    }
13
14    private Connection connect() {
15        if (conn != null) return conn;
16        String connection_url = "jdbc:sqlite:" + dbname;
17        try {
18            conn = DriverManager.getConnection(connection_url);
19            System.out.println("Connection with database established");
20
21        } catch (SQLException e) {
22            System.out.println(e.getMessage());
23        }
24
25        return conn;
26    }
27
28    private boolean executeStatement(String sql) {
29        try {
30            Statement stmt = conn.createStatement();
31            stmt.execute(sql);
32            return true;
33        } catch (SQLException e) {
34            System.out.println(e.getMessage());
35        }
36        return false;
37    }
38
39    private void createStudentsTable() {
40        String sql = "CREATE TABLE IF NOT EXISTS students (" +
41                "id integer," +
42                "name text," +
43                "country text," +
44                "PRIMARY KEY(id)" +
45                ");";
46        executeStatement(sql);
47    }
48
49    private void createResultsTable() {
50        String sql = "CREATE TABLE IF NOT EXISTS results (" +
51                "semester integer," +
52                "courseid integer," +
53                "studentid intger," +
54                "cgpa integer," +
55                "FOREIGN KEY(studentid) REFERENCES students(id)" +
56                ");";
57        executeStatement(sql);
58    }
59
60    private void insertStudent(int id, String name, String country) {
61        String sql = "INSERT INTO STUDENTS VALUES(?, ?, ?)";
62        PreparedStatement stmt = null;
63
64        try {
65            stmt = conn.prepareStatement(sql);
66            stmt.setInt(1, id);
67            stmt.setString(2, name);
68            stmt.setString(3, country);
69            stmt.executeUpdate();
70        } catch (SQLException e) {
71            System.out.println(e.getMessage());
72        }
73    }
74
75    public ResultSet getStudentResult(int id) {
76        ResultSet rs = null;
77        String sql = "select students.id, students.name, results.cgpa from students, results " +
78                "where students.id = results.studentid and students.id = ?";
79        PreparedStatement stmt = null;
80
81        try {
82            stmt = conn.prepareStatement(sql);
83            stmt.setInt(1, id);
84            rs = stmt.executeQuery();
85            System.out.println("execution successful");
86        } catch (SQLException e) {
87            System.out.println(e.getMessage());
88        }
89
90        return rs;
91    }
92
93
94    public static void main(String[] args) throws SQLException {
95        Database db = new Database();
96        db.insertStudent(4, "Abir", "Nigeria");
97        
98        // this is what you will write inside your action listener
99        ResultSet rs = db.getStudentResult(2);
100        while (rs.next()) {
101            String name = rs.getString("name");
102            int id = rs.getInt("id");
103            float cgpa = rs.getFloat("cgpa");
104
105            System.out.println(name + " " + id + " " + cgpa);
106        }
107    }
108}