课程管理系统基本搭建

拿到一个题目，关于数据的增删改查，要求在网页中实现

大体分为以下几个步骤：

1、连接数据库：

要连接数据库，首先得先建立一个数据库表，建立表头信息。然后在eclipse里进行操作。先建一个.java文件，将数据库连接，分为四步：1.加载驱动程序2.数据库连接字符串3.数据库登录名和密码4.最后关闭。代码如下：

 1 package com.jaovo.msg.Util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 
 9 public class DBUtil {
10     
11     public  static  Connection getConnection() {
12         try {
13             Class.forName("com.mysql.jdbc.Driver").newInstance();
14         } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
15             e.printStackTrace();
16         }
17         String user = "root";
18         String password = "root";
19         String url = "jdbc:mysql://localhost:3306/login? ?characterEncoding=utf-8&useSSL=true";
20         Connection connection = null;
21         try {
22              connection = DriverManager.getConnection(url,user,password);
23         } catch (SQLException e) {
24             e.printStackTrace();
25         }
26         return connection;
27     }
28     
29     public static void close(Connection connection ) {
30         try {
31             if (connection != null) {
32                 connection.close();
33             }
34             
35         } catch (SQLException e) {
36             e.printStackTrace();
37         }
38     }
39     public static void close(PreparedStatement preparedStatement ) {
40         try {
41             if (preparedStatement != null) {
42                 preparedStatement.close();
43             }
44             
45         } catch (SQLException e) {
46             e.printStackTrace();
47         }
48     }
49     public static void close(ResultSet resultSet ) {
50         try {
51             if (resultSet != null) {
52                 resultSet.close();
53             }
54             
55         } catch (SQLException e) {
56             e.printStackTrace();
57         }
58     }
59     
60 }

2、然后再建一个新类，里面创建对象，属性设为private，然后分别建立set和get函数。

 1 package com.jaovo.msg.model;
 2 
 3 public class infor {
 4     private String name;
 5     private String teacher;
 6     private String address;
 7     public String getName() {
 8         return name;
 9     }
10     public void setName(String name) {
11         this.name = name;
12     }
13     public String getTeacher() {
14         return teacher;
15     }
16     public void setTeacher(String teacher) {
17         this.teacher = teacher;
18     }
19     public String getAddress() {
20         return address;
21     }
22     public void setAddress(String address) {
23         this.address = address;
24     }
25     
26     public infor() {}
27     
28     public infor(String name,String teacher,String address) {
29         this.name = name;
30         this.teacher = teacher;
31         this.address = address;
32     }
33 
34 }

作为JavaBean，必须要注意的是，必须要有一个空的构造函数，要不然会报错，就像第26行public infor ( ) { };

3、新建一个操作类，专门写对数据库的操作，在里面写增删改查的函数，根据功能来写不同的函数，每个函数都得调用数据库的连接语句：

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 package com.jaovo.msg.dao;
  2 
  3 import javax.swing.*;
  4 import java.sql.Connection;
  5 import java.sql.PreparedStatement;
  6 import java.sql.ResultSet;
  7 import java.sql.SQLException;
  8 import java.util.ArrayList;
  9 import java.util.List;
 10 
 11 import com.jaovo.msg.Util.DBUtil;
 12 import com.jaovo.msg.model.infor;
 13 
 14 
 15 public class StudentDaoImpl {
 16 
 17     public void add1(String name, String teacher,String address) {
 18     
 19         Connection connection = DBUtil.getConnection();
 20         
 21         PreparedStatement preparedStatement = null;
 22         ResultSet resultSet = null;
 23         try {
 24             String sql = "insert into class1(name,teacher,addess) value (?,?,?)";
 25             preparedStatement = connection.prepareStatement(sql);
 26             preparedStatement.setString(1, name);
 27             preparedStatement.setString(2, teacher);
 28             preparedStatement.setString(3, address);
 29             preparedStatement.executeUpdate();
 30         } catch (SQLException e) {
 31             // TODO Auto-generated catch block
 32             e.printStackTrace();
 33         } finally {
 34             DBUtil.close(resultSet);
 35             DBUtil.close(preparedStatement);
 36             DBUtil.close(connection);
 37         }
 38 
 39     }
 40 
 41 
 42     public void update(String name, String newname,String newteacher,String newaddress) {
 43         Connection connection = DBUtil.getConnection();
 44         
 45         String sql = "select count(*) from class1 where name = ?";
 46         PreparedStatement preparedStatement = null;
 47         ResultSet resultSet = null;
 48         try {
 49             preparedStatement = connection.prepareStatement(sql);
 50             preparedStatement.setString(1, name);
 51             resultSet = preparedStatement.executeQuery();
 52             while (resultSet.next()) {
 53                 if (resultSet.getInt(1) > 0)
 54                 {
 55                     System.out.println("开始修改");
 56                     sql = "update class1 set name=?,teacher=?,address=?  where name=?";
 57                     preparedStatement = connection.prepareStatement(sql);
 58                     preparedStatement.setString(1, newname);
 59                     preparedStatement.setString(2, newteacher);
 60                     preparedStatement.setString(3, newaddress);
 61                     preparedStatement.setString(4, name);
 62                 } preparedStatement.executeUpdate();
 63         }
 64             } catch (SQLException e) {
 65             // TODO Auto-generated catch block
 66             e.printStackTrace();
 67         } finally {
 68             DBUtil.close(resultSet);
 69             DBUtil.close(preparedStatement);
 70             DBUtil.close(connection);
 71         }
 72 
 73     }
 74 
 75 
 76     public void delete(String username) {
 77         Connection connection = DBUtil.getConnection();
 78         String sql = "delete from class1 where name = ?";
 79         PreparedStatement preparedStatement = null;
 80 
 81         try {
 82             preparedStatement = connection.prepareStatement(sql);
 83             preparedStatement.setString(1, username);
 84             preparedStatement.executeUpdate();
 85         } catch (SQLException e) {
 86             // TODO Auto-generated catch block
 87             e.printStackTrace();
 88         } finally {
 89             DBUtil.close(preparedStatement);
 90             DBUtil.close(connection);
 91         }
 92 
 93     }
 94     
 95     public int add(String name,String teacher,String address) {
 96         
 97         Connection connection = DBUtil.getConnection();
 98         
 99         String sql = "select count(*) from class1 where name = ?";
100         
101         PreparedStatement preparedStatement = null;
102         ResultSet resultSet = null;
103         try {
104             preparedStatement = connection.prepareStatement(sql);
105             preparedStatement.setString(1, name);
106             
107             resultSet = preparedStatement.executeQuery();
108             
109             System.out.println("hello");
110             while(resultSet.next()) {
111                 if (resultSet.getInt(1) > 0) 
112                 {
113                     
114                     return 0;
115                 }
116             
117                 else 
118                 {
119                     sql = "insert into class1(name,teacher,address) value (?,?,?)";
120                     preparedStatement = connection.prepareStatement(sql);
121                     preparedStatement.setString(1, name);
122                     preparedStatement.setString(2, teacher);
123                     preparedStatement.setString(3, address);
124                     preparedStatement.executeUpdate();
125                     return 1;
126                 }
127                 }
128         } catch (SQLException e) {
129             // TODO Auto-generated catch block
130             //e.printStackTrace();
131             e.getMessage();
132         }finally {
133             
134             DBUtil.close(resultSet);
135             DBUtil.close(preparedStatement);
136             DBUtil.close(connection);
137         }
138         return 0;
139         
140     }
141     
142     public int load(String name) {
143         // TODO Auto-generated method stub
144         
145         Connection connection = DBUtil.getConnection();
146         String sql = "select * from class1 where name = ?";
147         PreparedStatement preparedStatement = null;
148         ResultSet resultSet = null;
149         try {
150             preparedStatement = connection.prepareStatement(sql);
151             preparedStatement.setString(1, name);
152             resultSet = preparedStatement.executeQuery();
153             while (resultSet.next()) {
154                 System.out.println("课程名称：" + resultSet.getString("name"));
155                 System.out.println("任课教师：" + resultSet.getString("teacher"));
156                 System.out.println("上课地点：" + resultSet.getString("address"));
157                 return 1;
158             }
159 
160         } catch (SQLException e) {
161             // TODO Auto-generated catch block
162             e.printStackTrace();
163         } finally {
164             DBUtil.close(resultSet);
165             DBUtil.close(preparedStatement);
166             DBUtil.close(connection);
167         }
168         return 0;
169     }
170     public String load_del(String name) {
171         // TODO Auto-generated method stub
172         Connection connection = DBUtil.getConnection();
173         String sql = "select * from class1 where name = ?";
174         PreparedStatement preparedStatement = null;
175         ResultSet resultSet = null;
176         try {
177 
178             preparedStatement = connection.prepareStatement(sql);
179             preparedStatement.setString(1, name);
180             resultSet = preparedStatement.executeQuery();
181             while (resultSet.next()) {
182                 System.out.println("课程名称：" + resultSet.getString("name"));
183                 System.out.println("任课教师：" + resultSet.getString("teacher"));
184                 System.out.println("上课地点：" + resultSet.getString("address"));
185                 return ("课程名称：" + resultSet.getString("name")+"      任课教师：" + resultSet.getString("teacher")+"        上课地点：" + resultSet.getString("address"));
186             }
187 
188         } catch (SQLException e) {
189             // TODO Auto-generated catch block
190             e.printStackTrace();
191         } finally {
192             DBUtil.close(resultSet);
193             DBUtil.close(preparedStatement);
194             DBUtil.close(connection);
195         }
196         return "课程不存在！";
197     }
198     
199     public String load_delete(String name) {
200         // TODO Auto-generated method stub
201         Connection connection = DBUtil.getConnection();
202         //System.out.println(111);
203         PreparedStatement preparedStatement = null;
204         ResultSet resultSet = null;
205         try {
206             String sql = "select * from class1 where name like '%"+name+"%'";
207             preparedStatement = connection.prepareStatement(sql);
208             resultSet = preparedStatement.executeQuery();
209             while (resultSet.next()) {
210                 System.out.println("课程名称：" + resultSet.getString("name"));
211                 System.out.println("任课教师：" + resultSet.getString("teacher"));
212                 System.out.println("上课地点：" + resultSet.getString("address"));
213                 return ("课程名称：" + resultSet.getString("name")+"      任课教师：" + resultSet.getString("teacher")+"        上课地点：" + resultSet.getString("address"));
214             }
215 
216         } catch (SQLException e) {
217             // TODO Auto-generated catch block
218             e.printStackTrace();
219         } finally {
220             DBUtil.close(resultSet);
221             DBUtil.close(preparedStatement);
222             DBUtil.close(connection);
223         }
224         return "课程不存在！";
225     }
226     
227     
228     public List<infor> loadlist(String name,String teacher,String address) {
229         // TODO Auto-generated method stub
230         String sql = "select * from class1 where ";
231         if (name != "") {
232             sql += "name like '%" + name + "%'";
233         }
234         if (teacher != "") {
235             sql += "teacher like '%" + teacher + "%'";
236         }
237         if (address != "") {
238             sql += "classroom like '%" + address + "%'";
239         }
240         List<infor> list = new ArrayList<>();
241         Connection connection = DBUtil.getConnection();
242         //System.out.println(111);
243         infor bean = null;
244         PreparedStatement preparedStatement = null;
245         ResultSet resultSet = null;
246         try {
247             //String sql = "select * from class1 where name like '%"+name+"%'";
248             preparedStatement = connection.prepareStatement(sql);
249             resultSet = preparedStatement.executeQuery();
250             while (resultSet.next()) {
251                 String name2 = resultSet.getString("name");
252                 String teacher2 = resultSet.getString("teacher");
253                 String address2 = resultSet.getString("address");
254                 bean = new infor(name2,teacher2,address2);
255                 list.add(bean);
256                 System.out.println("课程名称：" + resultSet.getString("name"));
257                 System.out.println("任课教师：" + resultSet.getString("teacher"));
258                 System.out.println("上课地点：" + resultSet.getString("address"));
259                 //return ("课程名称：" + resultSet.getString("name")+"      任课教师：" + resultSet.getString("teacher")+"        上课地点：" + resultSet.getString("address"));
260             }
261 
262         } catch (SQLException e) {
263             // TODO Auto-generated catch block
264             e.printStackTrace();
265         } finally {
266             DBUtil.close(resultSet);
267             DBUtil.close(preparedStatement);
268             DBUtil.close(connection);
269         }
270         return list;
271     }
272 }

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>

界面：

其他只是界面处理不同，样式一样。

大体上就是这样的步骤，其他具体实现操作已略去。

posted @ 2018-12-09 17:03 枫黎阅读(278) 评论(0) 编辑收藏举报

刷新页面返回顶部

枫黎

课程管理系统基本搭建

公告