· 8 years ago · Apr 14, 2018, 08:26 PM
1StudentsDBConn.java
2-----------------------------------------------------
3
4import java.sql.*;
5import java.util.ArrayList;
6import java.util.HashMap;
7import java.util.List;
8import java.util.Map;
9
10public class StudentsDBConn {
11 static final private String TABLE_NAME = "studenttable";
12 static final private String FIELD_ID = "id";
13 static final private String FIELD_FIRSTNAME = "fname";
14 static final private String FIELD_LASTNAME = "lname";
15
16 private Connection conn;
17
18 // the constructor will make a connection with the database ont he server
19 public StudentsDBConn() throws SQLException{
20 String driverName = "com.mysql.jdbc.Driver"; // this is the fully qualifies name of the class we want to load
21 try { // Class.forName(...) throws a ClassNotFoundException so we deal with that here
22 Class.forName(driverName); // loading the class we specified in driverName
23 String url = "jdbc:mysql://eu-cdbr-sl-lhr-01.cleardb.net/ibmx_7aae882800d7265"; // the url of the database (on the server)
24 String username = "b87b3718783680"; // the username for the database
25 String password = "d8e92cf6"; // the password for the database
26
27 Connection dbConn = DriverManager.getConnection(url,username,password); // creating a connection with the server
28
29 this.conn = dbConn; // saving that connection as a property of the class
30
31 System.out.println("connected");
32 }
33 catch(ClassNotFoundException e){ // catching the possibly thrown exception
34 System.out.println(e);
35 }
36
37
38 }
39
40
41 // method to end to connection with the database on the server
42 public void closeConnection() throws SQLException{
43 this.conn.close(); // closing the server connection
44 System.out.println("connection closed");
45 }
46
47 // method to create a table named what we saved in TABLE_NAME with the fields in the constants FIELD_...
48 public void createTable() throws SQLException{
49 // creating the sql string that represents creating a table
50 // syntax: CREATE TABLE IF NOT EXITS tableName (field1 data_type1, field2 data_type2,...)
51 String createString = "CREATE TABLE IF NOT EXISTS "+TABLE_NAME;
52 createString += "(";
53 createString += FIELD_ID+" int NOT NULL AUTO_INCREMENT PRIMARY KEY, ";
54 createString += FIELD_FIRSTNAME+" varchar(255), ";
55 createString += FIELD_LASTNAME+" varchar(255)";
56 createString += ")";
57
58 PreparedStatement createStatement = this.conn.prepareStatement(createString); // creating an executable sql statement
59 createStatement.executeUpdate(); // executing the sql statement
60
61 System.out.println("table created");
62
63 }
64
65 // method for inserting data into our table
66 public void insert(String firstname, String lastname) throws SQLException{
67 // creating the sql string that represents inserting into a table
68 // syntax: INSERT INTO tableName (field1,field2,...) VALUES (value1,value2,...)
69 String insertString = "INSERT INTO "+TABLE_NAME;
70 insertString += "(";
71 insertString += FIELD_FIRSTNAME+","+FIELD_LASTNAME;
72 insertString += ")";
73 insertString += " VALUES ";
74 insertString += "(";
75 insertString += "'"+firstname+"','"+lastname+"'";
76 insertString += ")";
77
78 PreparedStatement insertStatement = this.conn.prepareStatement(insertString); // creating an executable sql statement
79 insertStatement.executeUpdate();// executing the sql statement
80
81 System.out.println("data inserted");
82 }
83
84 // method to retrieve information from our table
85 public List<Map<String,String>> getAllData() throws SQLException{
86 // creating the sql string that represents selecting data from a table
87 // syntax: SELECT fields FROM tableName. (more on extended syntax bellow)
88 String selectString = "SELECT * FROM "+TABLE_NAME;
89 // there are many variations of the SELECT statement.
90 // the symbol * means select all fields
91 // we can have for instance SELECT fname,lname FROM ... to only get those fields (in this case excluding the id field):
92 //String selectString = "SELECT fname,lname FROM "+TABLE_NAME;
93 // we can limit the resutls by using LIMT for instance:
94 //String selectString = "SELECT * FROM "+TABLE_NAME+" LIMIT 2"; // to only get the first 2 results of the query
95 // we can order the result by usinf ORDER BY, and can get it in ascending - ASC, or descending - DESC order:
96 //String selectString = "SELECT * FROM "+TABLE_NAME+" ORDER BY fname ASC"; // order by ascending order of a specified field
97 //String selectString = "SELECT * FROM "+TABLE_NAME+" ORDER BY fname DESC"; // order by descending order of a specified field
98 // we can limit and order but in this case we mist order first and then limit:
99 //String selectString = "SELECT * FROM "+TABLE_NAME+" ORDER BY fname DESC LIMIT 2 "; // order and limit
100 // we can use complex conditions in the where clause using AND, OR, NOT:
101 // String selectString = "SELECT * FROM "+TABLE_NAME+" WHERE id=1 OR lname='mavriz'"
102 // String selectString = "SELECT * FROM "+TABLE_NAME+" WHERE id=1 AND NOT lname='mavriz'"
103 // note that we use a single = and not == like in java
104 PreparedStatement selectStatement = this.conn.prepareStatement(selectString);// creating an executable sql statement
105 ResultSet response = selectStatement.executeQuery();// executing the sql statement and saving the result. note that here we use executeQuery because we are expecting to get data from the table and not just do work on table
106
107 // we want to save the information we received. we will save it in a list of maps
108 Map<String, String> row; // each row of data will be saved in a map with the key being the field name and the value being the data saved for that field in the row
109 List<Map<String,String>> data = new ArrayList<>();
110
111 while(response.next()){ // response.next(moves to the next row in the data we received from the server)
112 row = new HashMap<>(); // craeting a new map for the new row
113 row.put(FIELD_ID, response.getString(FIELD_ID)); // saving the id field
114 row.put(FIELD_FIRSTNAME, response.getString(FIELD_FIRSTNAME)); // saving the fname field
115 row.put(FIELD_LASTNAME, response.getString(FIELD_LASTNAME)); // saving the lname field
116 data.add(row); // adding the map we just made to the list
117 }
118
119 System.out.println("data has been received");
120
121 return data; // we return the data we got from the server
122 }
123
124 // method for update entries in the table
125 public void update(String id, String firstname, String lastname) throws SQLException{
126 // creating the sql string that represents updating data in a table
127 // syntax: UPDATE tableName SET field1=value1,field2=value2,... WHERE condition
128 String updateString = "UPDATE "+TABLE_NAME+" SET ";
129 updateString += FIELD_FIRSTNAME+"='"+firstname+"',";
130 updateString += FIELD_LASTNAME+"='"+lastname+"' ";
131 updateString += "WHERE "+FIELD_ID+"="+id;
132 // we can have a complex condition int the where clause
133 // for instance WHERE id=3 AND fname='shahaf'
134 PreparedStatement updateStatement = this.conn.prepareStatement(updateString);// creating an executable sql statement
135 updateStatement.executeUpdate();// executing the sql statement
136
137 System.out.println("data updated");
138 }
139
140 // method for deleting data from a table
141 public void delete(String id) throws SQLException{
142 // creating the sql string that represents deleting a row from a table
143 // syntax: DELETE FROM tableName WHERE condition
144 String deleteString = "DELETE FROM "+TABLE_NAME+" WHERE "+FIELD_ID+"="+id;
145 // here as well we can have a complex condition in the where clause
146 PreparedStatement deleteStatement = this.conn.prepareStatement(deleteString);// creating an executable sql statement
147 deleteStatement.executeUpdate();// executing the sql statement
148
149 System.out.println("data deleted");
150 }
151
152 // method for deleting an entire talbe
153 public void dropTable() throws SQLException{
154 // creating the sql string that represents deleting a table
155 // syntax: DROP TABLE tableName
156 String dropString = "DROP TABLE "+TABLE_NAME; // in SQL drop means deleting an entire entity like a table or a whole database
157 PreparedStatement dropStatement = this.conn.prepareStatement(dropString);// creating an executable sql statement
158 dropStatement.executeUpdate();// executing the sql statement
159
160 System.out.println("table dropped");
161 }
162
163
164}
165
166
167
168
169
170-------------------------------------------------------------
171
172
173Tester.java
174-------------------------------------------------------------
175import java.sql.SQLException;
176import java.util.List;
177import java.util.Map;
178
179public class Tester {
180 public static void main(String[] args){
181 try {
182 StudentsDBConn conn = new StudentsDBConn();
183
184 //conn.createTable();
185 //conn.insert("lau","goldman");
186 //conn.update("11","tzahi","mavriz");
187 //conn.delete("11");
188
189 List<Map<String,String>> data = conn.getAllData();
190 for(Map<String,String> row : data){
191 for(Map.Entry<String, String> field : row.entrySet()){
192 System.out.print(field.getKey()+": "+field.getValue()+"\t");
193 }
194 System.out.println();
195 }
196
197 //conn.dropTable();
198
199 conn.closeConnection();
200 }
201 catch(SQLException e){
202 System.out.println(e);
203 }
204 }
205}