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