gef1gYth

1import java.sql.SQLException;
2
3public class App {
4    public static crud db_crud = new crud();
5
6    public static void main(String[] args) throws SQLException {
7
8        // insert three new rows
9        db_crud.insert("Aryan", 30000);
10        db_crud.insert("Robert", 40000);
11        db_crud.insert("Jerry", 50000);
12        String sql = "INSERT INTO employees(name, capacity) VALUES(%s,%d)";
13        String sql_sentence = String.format(sql, "Juan", 110200);
14        System.out.printf(sql_sentence);
15        System.out.println();
16        db_crud.selectAll();
17        db_crud.conn.close();
18    }
19}
20
21import java.sql.Connection;
22import java.sql.DriverManager;
23import java.sql.PreparedStatement;
24import java.sql.SQLException;
25import java.sql.Statement;
26import java.sql.ResultSet;
27
28public class crud {
29    public Connection conn = null;
30
31    public crud() {
32        connect();
33    }
34
35    private void connect() {
36        // SQLite connection string
37        String url = "jdbc:sqlite:F:/sqlite-tools-win32-x86-3360000/SSSIT.db";
38
39        try {
40            conn = DriverManager.getConnection(url);
41        } catch (SQLException e) {
42            System.out.println(e.getMessage());
43        }
44    }
45
46    public void createNewTable() {
47        // SQL statement for creating a new table
48        String sql = "CREATE TABLE IF NOT EXISTS employees (\n" + " id integer PRIMARY KEY,\n"
49                + " name text NOT NULL,\n" + " capacity real\n" + ");";
50
51        try {
52            Statement stmt = this.conn.createStatement();
53            stmt.execute(sql);
54        } catch (SQLException e) {
55            System.out.println(e.getMessage());
56        }
57    }
58
59    public void insert(String name, double capacity) {
60        String sql = "INSERT INTO employees(name, capacity) VALUES(?,?)";
61        createNewTable();
62
63        try {
64            PreparedStatement pstmt = conn.prepareStatement(sql);
65            pstmt.setString(1, name);
66            pstmt.setDouble(2, capacity);
67            pstmt.executeUpdate();
68        } catch (SQLException e) {
69            System.out.println(e.getMessage());
70        }
71    }
72
73    public void selectAll() {
74        String sql = "SELECT * FROM employees";
75
76        try {
77            Statement stmt = conn.createStatement();
78            ResultSet rs = stmt.executeQuery(sql);
79
80            // loop through the result set
81            while (rs.next()) {
82                System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" + rs.getDouble("capacity"));
83            }
84        } catch (SQLException e) {
85            System.out.println(e.getMessage());
86        }
87    }
88
89}
90