Repository navigation
Expand file tree
/
Copy pathStudentDAO.java
More file actions
164 lines (149 loc) · 4.07 KB
/
Copy pathStudentDAO.java
File metadata and controls
164 lines (149 loc) · 4.07 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
package dao;
import java.sql.*;
import java.util.*;
import vo.*;
import util.*;
public class StudentDAO {
//For database operations
private Connection conn;
private Statement stmt;
private ResultSet rs;
//Retrieve all students
public List<Student> getAllStudents() {
List<Student> list = new ArrayList();
String sSql = "select * from Student0";
try {
rs = executeQuery(sSql);
while(rs.next()) {
Student temp = new Student();
temp.setSid(rs.getString(1));
temp.setSname(rs.getString(2));
temp.setIage(rs.getInt(3));
list.add(temp);
}
} catch(Exception e) {
System.out.println(e.toString());
} finally {
close();
}
return list;
}
//Find a student by id
public Student findStudentByID(String sID) {
String sSql = "select * from Student0 where id = '" + sID + "'";
try {
rs = executeQuery(sSql);
while(rs.next()) {
Student temp = new Student();
temp.setSid(rs.getString(1));
temp.setSname(rs.getString(2));
temp.setIage(rs.getInt(3));
return temp;
}
} catch(Exception e) {
System.out.println(e.toString());
} finally {
close();
}
return null;
}
//Add a student
public void add(Student student) {
String sSql = "insert into Student0(id, name, age) values('"
+ student.getSid() + "', '" + student.getSname() + "', " + student.getIage() + ")";
try {
executeUpdate(sSql);
} catch(Exception e) {
System.out.println(e.toString());
} finally {
close();
}
}
//Delete a student
public void delete(String sID) {
String sSql = "delete from Student0 where id = '" + sID + "'";
try {
executeUpdate(sSql);
} catch(Exception e) {
System.out.println(e.toString());
} finally {
close();
}
}
//Edit a student
public void edit(Student student) {
String sSql = "update Student0 set name = '" + student.getSname() + "', age = "
+ student.getIage() + " where id = '" + student.getSid() + "'";
try {
executeUpdate(sSql);
} catch(Exception e) {
System.out.println(e.toString());
} finally {
close();
}
}
//Execute sql update
public int executeUpdate(String sSql) throws SQLException, ClassNotFoundException {
return getStatement().executeUpdate(sSql);
}
//Execute sql query
public ResultSet executeQuery(String sSql) throws SQLException, ClassNotFoundException {
return getStatement().executeQuery(sSql);
}
//Obtain statement
public Statement getStatement() throws SQLException, ClassNotFoundException {
if(stmt == null) {
stmt = DBConnection.getConnection().createStatement();
}
return stmt;
}
public void close() {
if(rs != null) {
try {
rs.close();
} catch(Exception e) {
System.out.println(e.toString());
}
}
if(stmt != null) {
try {
stmt.close();
} catch(Exception e) {
System.out.println(e.toString());
}
}
if(conn != null) {
try {
conn.close();
} catch(Exception e) {
System.out.println(e.toString());
}
}
}
public void creatDB(){
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c= DriverManager.getConnection("jdbc:sqlite:d://ch07db.s3db");
//Class.forName("com.mysql.jdbc.Driver");
//c = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");
System.out.println("Opened database successfully");
stmt = c.createStatement();
//String sql = "CREATE TABLE COMPANY " + "(ID INT PRIMARY KEY NOT NULL,"
// + " NAME TEXT NOT NULL, " + " AGE INT NOT NULL, "
// + " ADDRESS CHAR(50), " + " SALARY REAL)";
String sql ="create table Student0 "
+ " (id DECIMAL(20), "
+ " name CHAR(20), "
+ " age DECIMAL(3)) ";
stmt.executeUpdate(sql);
stmt.close();
c.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(0);
}
System.out.println("Table created successfully");
}
}