2020年11月4日Java学习日记
课程管理系统的增删改查
1、连接数据库
要连接数据库，首先得建立一个数据库表，建立表头信息。然后在eclipse里进行操作。
在util 层下建立 DBUtil.java 文件，将数据库连接。主要分为四步：
- 加载驱动程序
- 数据库连接字符串
- 数据库登录名和密码
- 关闭数据库
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtil {
public static Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
e.printStackTrace();
}
String user = "root";
String password = "liutianwen0613";
String url = "jdbc:mysql://localhost:3306/login? ?characterEncoding=utf-8&useSSL=true";
Connection connection = null;
try {
connection = DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static void close(Connection connection ) {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(PreparedStatement preparedStatement ) {
try {
if (preparedStatement != null) {
preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(ResultSet resultSet ) {
try {
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2、在 bean 层下建 infor.java 文件,里面创建对象，属性为 private。然后分别建立 set 和 get 函数，属性为 public（可通过右击--源码快速生成）
package bean;
public class infor{
private String name;
private String teacher;
private String address;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTeacher() {
return teacher;
}
public void setTeacher(String teacher) {
this.teacher = teacher;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public infor() {}
public infor(String name,String teacher,String address) {
this.name = name;
this.teacher = teacher;
this.address = address;
}
}
注意：必须要有一个空的构造函数
3、在dao层下建立studentdao.java文件，专门写对数据库的操作，在里面写增删改查的函数，根据功能来写不同的函数，每个函数都得调用数据库的连接语句：
Connection connection = DBUtil.getConnection(); |
用到对数据库操作的语句：
String sql = "select count(*) from class1 where name = ?";//选择语句
sql = "insert into class1(name,teacher,address) value (?,?,?)";//插入语句
语句有很多种，基本用法也不尽相同。
同时还得新定义这两个语句:
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
然后就是写函数体。根据不同的要求写不同的函数。
|
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
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
|
package dao;import javax.swing.*;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import util.DBUtil;import bean.infor;public class studentdao { public void add1(String name, String teacher,String address) { Connection connection = DBUtil.getConnection(); PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { String sql = "insert into class1(name,teacher,addess) value (?,?,?)"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, name); preparedStatement.setString(2, teacher); preparedStatement.setString(3, address); preparedStatement.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } } public void update(String name, String newname,String newteacher,String newaddress) { Connection connection = DBUtil.getConnection(); String sql = "select count(*) from class1 where name = ?"; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, name); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { if (resultSet.getInt(1) > 0) { System.out.println("开始修改"); sql = "update class1 set name=?,teacher=?,address=? where name=?"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, newname); preparedStatement.setString(2, newteacher); preparedStatement.setString(3, newaddress); preparedStatement.setString(4, name); } preparedStatement.executeUpdate(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } } public void delete(String username) { Connection connection = DBUtil.getConnection(); String sql = "delete from class1 where name = ?"; PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, username); preparedStatement.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(preparedStatement); DBUtil.close(connection); } } public int add(String name,String teacher,String address) { Connection connection = DBUtil.getConnection(); String sql = "select count(*) from class1 where name = ?"; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, name); resultSet = preparedStatement.executeQuery(); System.out.println("hello"); while(resultSet.next()) { if (resultSet.getInt(1) > 0) { return 0; } else { sql = "insert into class1(name,teacher,address) value (?,?,?)"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, name); preparedStatement.setString(2, teacher); preparedStatement.setString(3, address); preparedStatement.executeUpdate(); return 1; } } } catch (SQLException e) { // TODO Auto-generated catch block //e.printStackTrace(); e.getMessage(); }finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return 0; } public int load(String name) { // TODO Auto-generated method stub Connection connection = DBUtil.getConnection(); String sql = "select * from class1 where name = ?"; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, name); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { System.out.println("课程名称：" + resultSet.getString("name")); System.out.println("任课教师：" + resultSet.getString("teacher")); System.out.println("上课地点：" + resultSet.getString("address")); return 1; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return 0; } public String load_del(String name) { // TODO Auto-generated method stub Connection connection = DBUtil.getConnection(); String sql = "select * from class1 where name = ?"; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, name); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { System.out.println("课程名称：" + resultSet.getString("name")); System.out.println("任课教师：" + resultSet.getString("teacher")); System.out.println("上课地点：" + resultSet.getString("address")); return ("课程名称：" + resultSet.getString("name")+" 任课教师：" + resultSet.getString("teacher")+" 上课地点：" + resultSet.getString("address")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return "课程不存在！"; } public String load_delete(String name) { // TODO Auto-generated method stub Connection connection = DBUtil.getConnection(); //System.out.println(111); PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { String sql = "select * from class1 where name like '%"+name+"%'"; preparedStatement = connection.prepareStatement(sql); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { System.out.println("课程名称：" + resultSet.getString("name")); System.out.println("任课教师：" + resultSet.getString("teacher")); System.out.println("上课地点：" + resultSet.getString("address")); return ("课程名称：" + resultSet.getString("name")+" 任课教师：" + resultSet.getString("teacher")+" 上课地点：" + resultSet.getString("address")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return "课程不存在！"; } public List<infor> loadlist(String name,String teacher,String address) { // TODO Auto-generated method stub String sql = "select * from class1 where "; if (name != "") { sql += "name like '%" + name + "%'"; } if (teacher != "") { sql += "teacher like '%" + teacher + "%'"; } if (address != "") { sql += "classroom like '%" + address + "%'"; } List<infor> list = new ArrayList<>(); Connection connection = DBUtil.getConnection(); //System.out.println(111); infor bean = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { //String sql = "select * from class1 where name like '%"+name+"%'"; preparedStatement = connection.prepareStatement(sql); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { String name2 = resultSet.getString("name"); String teacher2 = resultSet.getString("teacher"); String address2 = resultSet.getString("address"); bean = new infor(name2,teacher2,address2); list.add(bean); System.out.println("课程名称：" + resultSet.getString("name")); System.out.println("任课教师：" + resultSet.getString("teacher")); System.out.println("上课地点：" + resultSet.getString("address")); //return ("课程名称：" + resultSet.getString("name")+" 任课教师：" + resultSet.getString("teacher")+" 上课地点：" + resultSet.getString("address")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return list; }} |
4、搭建界面
主要用的是jsp，先建立一个主页，展示操作，再分别建立增删改查的jsp：
然后在jsp里面进行传值，调用Java文件的函数，进而操作数据库。
这些jsp文件里并不是都是显示页面，有一些只是进行判断的页面，调用函数进行操作数据库。
这是主页面，分成了上左右三块，左边菜单栏，上边是名称，右边是各种操作界面及结果显示。
代码：
1 <%@ page language="java" contentType="text/html; charset=utf-8" 2 pageEncoding="utf-8"%> 3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 4 <html> 5 <head> 6 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 7 <title>主页</title> 8 <link rel="stylesheet" href="css/page.css" /> 9 <script type="text/javascript" src="js/jquery.min.js"></script> 10 <script type="text/javascript" src="js/index.js"></script> 11 </head> 12 13 <frameset rows="20%,*"> 14 <frame class="top" src="top.jsp"> 15 <frameset cols="20%,*"> 16 <frame src="main_left.jsp"> 17 <frame src="main_right.jsp" name="main_right"> 18 </frameset> 19 </frameset> 20 <body> 21 22 </body> 23 </html>
其他分界面以添加为例
1 <%@ page language="java" contentType="text/html; charset=utf-8" 2 pageEncoding="utf-8"%> 3 <%@ page import="com.jaovo.msg.dao.*" %> 4 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 5 <html> 6 <head> 7 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 8 <title>Insert title here</title> 9 </head> 10 <body> 11 <form method="post" action="inputstuinfo_result.jsp"> 12 13 <div align="center"> 14 <h5>课程名称：<input name="input_name" type="text" placeholder="请输课程名称"></h5> 15 <h5>任课教师：<input name="input_teacher" type="text" placeholder="请输任课教师"></h5> 16 <h5>上课地点：<input name="input_address" type="text" placeholder="请输上课地点"></h5> 17 <input name="submit" type="submit" > 18 </div> 19 </form> 20 </body> 21 </html>
