U304WYgL

1StudentsDBConn.java
2-----------------------------------------------------
3
4
5import java.sql.*;
6import java.util.ArrayList;
7import java.util.HashMap;
8import java.util.List;
9import java.util.Map;
10
11public class StudentsDBConn {
12    static final private String TABLE_NAME = "studenttable";
13    static final private String FIELD_ID = "id";
14    static final private String FIELD_FIRSTNAME = "fname";
15    static final private String FIELD_LASTNAME = "lname";
16
17    private Connection conn;
18
19    public StudentsDBConn() throws SQLException{
20        String driverName = "com.mysql.jdbc.Driver";
21        try {
22            Class.forName(driverName);
23        }
24        catch(ClassNotFoundException e){
25            System.out.println(e);
26        }
27
28        String url = "jdbc:mysql://eu-cdbr-sl-lhr-01.cleardb.net/ibmx_7aae882800d7265";
29        String username = "b87b3718783680";
30        String password = "d8e92cf6";
31
32        Connection dbConn = DriverManager.getConnection(url,username,password);
33
34        this.conn = dbConn;
35
36        System.out.println("connected");
37
38    }
39
40
41    public void closeConnection() throws SQLException{
42        this.conn.close();
43        System.out.println("connection closed");
44    }
45
46    public void createTable() throws SQLException{
47        String createString = "CREATE TABLE IF NOT EXISTS "+TABLE_NAME;
48        createString += "(";
49        createString += FIELD_ID+" int NOT NULL AUTO_INCREMENT PRIMARY KEY, ";
50        createString += FIELD_FIRSTNAME+" varchar(255), ";
51        createString += FIELD_LASTNAME+" varchar(255)";
52        createString += ")";
53
54        PreparedStatement createStatement = this.conn.prepareStatement(createString);
55        createStatement.executeUpdate();
56
57        System.out.println("table created");
58
59    }
60
61
62    public void insert(String firstname, String lastname) throws SQLException{
63        String insertString = "INSERT INTO "+TABLE_NAME;
64        insertString += "(";
65        insertString += FIELD_FIRSTNAME+","+FIELD_LASTNAME;
66        insertString += ")";
67        insertString += " VALUES ";
68        insertString += "(";
69        insertString += "'"+firstname+"','"+lastname+"'";
70        insertString += ")";
71
72        PreparedStatement insertStatement = this.conn.prepareStatement(insertString);
73        insertStatement.executeUpdate();
74
75        System.out.println("data inserted");
76    }
77
78
79    public List<Map<String,String>> getAllData() throws SQLException{
80        String selectString = "SELECT * FROM "+TABLE_NAME;
81        PreparedStatement selectStatement = this.conn.prepareStatement(selectString);
82        ResultSet response = selectStatement.executeQuery();
83
84        Map<String, String> row;
85        List<Map<String,String>> data = new ArrayList<>();
86
87        while(response.next()){
88            row = new HashMap<>();
89            row.put(FIELD_ID, response.getString(FIELD_ID));
90            row.put(FIELD_FIRSTNAME, response.getString(FIELD_FIRSTNAME));
91            row.put(FIELD_LASTNAME, response.getString(FIELD_LASTNAME));
92            data.add(row);
93        }
94
95        System.out.println("data has been received");
96
97        return data;
98    }
99
100    public void update(String id, String firstname, String lastname) throws SQLException{
101        String updateString = "UPDATE "+TABLE_NAME+" SET ";
102        updateString += FIELD_FIRSTNAME+"='"+firstname+"',";
103        updateString += FIELD_LASTNAME+"='"+lastname+"' ";
104        updateString += "WHERE "+FIELD_ID+"="+id;
105        PreparedStatement updateStatement = this.conn.prepareStatement(updateString);
106        updateStatement.executeUpdate();
107
108        System.out.println("data updated");
109    }
110
111
112    public void delete(String id) throws SQLException{
113        String deleteString = "DELETE FROM "+TABLE_NAME+" WHERE "+FIELD_ID+"="+id;
114        PreparedStatement deleteStatement = this.conn.prepareStatement(deleteString);
115        deleteStatement.executeUpdate();
116
117        System.out.println("data deleted");
118    }
119
120    public void dropTable() throws SQLException{
121        String dropString = "DROP TABLE "+TABLE_NAME;
122        PreparedStatement dropStatement = this.conn.prepareStatement(dropString);
123        dropStatement.executeUpdate();
124
125        System.out.println("table dropped");
126    }
127
128
129}
130
131
132
133-------------------------------------------------------------
134
135
136Tester.java
137-------------------------------------------------------------
138import java.sql.SQLException;
139import java.util.List;
140import java.util.Map;
141
142public class Tester {
143    public static void main(String[] args){
144        try {
145            StudentsDBConn conn = new StudentsDBConn();
146
147            //conn.createTable();
148            //conn.insert("lau","goldman");
149            //conn.update("11","tzahi","mavriz");
150            //conn.delete("11");
151
152            List<Map<String,String>> data = conn.getAllData();
153            for(Map<String,String> row : data){
154                for(Map.Entry<String, String> field : row.entrySet()){
155                    System.out.print(field.getKey()+": "+field.getValue()+"\t");
156                }
157                System.out.println();
158            }
159
160            //conn.dropTable();
161
162            conn.closeConnection();
163        }
164        catch(SQLException e){
165            System.out.println(e);
166        }
167    }
168}