设计思想:连接数据库,实现sql语句,对成员变量进行封装,实现增的方法,最后描绘登陆界面用JSP来书写
源代码:
//信1605-3 20163578 于丁一 package com.jaovo.sk.dao; import java.util.List; import com.jaovo.sk.model.User; public interface IUserDao { public void add(User user); public void delete(int id); public void update(User user); public User load(int id); public User load(String kecheng); public List<User> load(); }
//信1605-3 20163578 于丁一 package com.jaovo.sk.dao; 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 com.jaovo.sk.Util.DBUtil; import com.jaovo.sk.Util.UserException; import com.jaovo.sk.model.User; import sun.net.www.content.text.plain; public class UserDaoImpl implements IUserDao { @SuppressWarnings("resource") @Override public void add(User user) { //鑾峰緱閾炬帴瀵硅薄 Connection connection = DBUtil.getConnection(); //鍑嗗�sql璇�彞 String sql = "select count(*) from t_user where kecheng = ?"; //鍒涘缓璇�彞浼犺緭瀵硅薄 PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, user.getkecheng()); //鎺ユ敹缁撴灉闆? resultSet = preparedStatement.executeQuery(); //閬嶅巻缁撴灉闆? while(resultSet.next()) { if (resultSet.getInt(1) > 0) { throw new UserException("鐢ㄦ埛宸插瓨鍦?") ; } } sql = "insert into t_user(kecheng,jiaoshi,didian) value (?,?,?)"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, user.getkecheng()); preparedStatement.setString(2, user.getjiaoshi()); preparedStatement.setString(3, user.getdidian()); preparedStatement.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { //鍏抽棴璧勬簮 DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } } @Override public void delete(int id) { Connection connection = DBUtil.getConnection(); String sql = "delete from t_user where id = ?"; PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, id); preparedStatement.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(preparedStatement); DBUtil.close(connection); } } @Override public void update(User user) { Connection connection = DBUtil.getConnection(); //鍑嗗�sql璇�彞 String sql = "update t_user set jiaoshi = ? , didian=? where id = ?"; //鍒涘缓璇�彞浼犺緭瀵硅薄 PreparedStatement preparedStatement = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, user.getjiaoshi()); preparedStatement.setString(2, user.getdidian()); preparedStatement.setInt(3, user.getId()); preparedStatement.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(preparedStatement); DBUtil.close(connection); } } @Override public User load(int id) { Connection connection = DBUtil.getConnection(); //鍑嗗�sql璇�彞 String sql = "select * from t_user where id = ?"; //鍒涘缓璇�彞浼犺緭瀵硅薄 PreparedStatement preparedStatement = null; ResultSet resultSet = null; User user = null; try { preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, id); resultSet = preparedStatement.executeQuery(); while(resultSet.next()) { user = new User(); user.setId(id); user.setkecheng(resultSet.getString("kecheng")); user.setjiaoshi(resultSet.getString("jiaoshi")); user.setdidian(resultSet.getString("didian")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return user; } @Override public User load(String kecheng) { // TODO Auto-generated method stub return null; } @Override public List<User> load() { Connection connection = DBUtil.getConnection(); //鍑嗗�sql璇�彞 String sql = "select * from t_user "; //鍒涘缓璇�彞浼犺緭瀵硅薄 PreparedStatement preparedStatement = null; ResultSet resultSet = null; //闆嗗悎涓�彧鑳芥斁鍏�ser瀵硅薄 List<User> users = new ArrayList<User>(); User user = null; try { preparedStatement = connection.prepareStatement(sql); resultSet = preparedStatement.executeQuery(); while(resultSet.next()) { user = new User(); user.setId(resultSet.getInt("id")); user.setkecheng(resultSet.getString("kecheng")); user.setjiaoshi(resultSet.getString("jiaoshi")); user.setdidian(resultSet.getString("didian")); users.add(user); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { DBUtil.close(resultSet); DBUtil.close(preparedStatement); DBUtil.close(connection); } return users; } }
//信1605-3 20163578 于丁一 package com.jaovo.sk.model; public class User { private int id; private String kecheng; private String jiaoshi; private String didian; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getkecheng() { return kecheng; } public void setkecheng(String kecheng) { this.kecheng = kecheng; } public String getjiaoshi() { return jiaoshi; } public void setjiaoshi(String jiaoshi) { this.jiaoshi = jiaoshi; } public String getdidian() { return didian; } public void setdidian(String didian) { this.didian = didian; } }
//信1605-3 20163578 于丁一 package com.jaovo.sk.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 { //1 閸旂姾娴囨す鍗炲З Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } String user = "root"; String password = "root"; String url = "jdbc:mysql://localhost:3306/jaovo_sk"; Connection connection = null; try { //2 閸掓稑缂撻柧鐐�复鐎电�钖刢onnection connection = DriverManager.getConnection(url,user,password); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return connection; } //閸忔娊妫寸挧鍕�爱閻ㄥ嫭鏌熷▔锟? public static void close(Connection connection ) { try { if (connection != null) { connection.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static void close(PreparedStatement preparedStatement ) { try { if (preparedStatement != null) { preparedStatement.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static void close(ResultSet resultSet ) { try { if (resultSet != null) { resultSet.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
//信1605-3 20163578 于丁一 package com.jaovo.sk.Util; public class UserException extends RuntimeException{ /** * */ private static final long serialVersionUID = 7067516052133635189L; public UserException() { super(); // TODO Auto-generated constructor stub } public UserException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) { super(message, cause, enableSuppression, writableStackTrace); // TODO Auto-generated constructor stub } public UserException(String message, Throwable cause) { super(message, cause); // TODO Auto-generated constructor stub } public UserException(String message) { super(message); // TODO Auto-generated constructor stub } public UserException(Throwable cause) { super(cause); // TODO Auto-generated constructor stub } }
add.jsp:
<%@page import="com.jaovo.sk.Util.UserException"%>
<%@page import="com.jaovo.sk.dao.UserDaoImpl"%>
<%@page import="com.jaovo.sk.model.User"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<%
//接收客户端传递过来的参数
String kecheng = request.getParameter("kecheng");
String jiaoshi = request.getParameter("jiaoshi");
String didian = request.getParameter("didian");
if(kecheng == null || "".equals(kecheng.trim())){
request.setAttribute("error", "课程名称不能为空");
%>
<jsp:forward page="addInput.jsp"></jsp:forward>
<%
}
if(jiaoshi.equals("王建民")||jiaoshi.equals("刘立嘉")||jiaoshi.equals("刘丹")||jiaoshi.equals("王辉")||jiaoshi.equals("杨子光"))
{
}
else{
request.setAttribute("error", "教师不为指定教师");
%>
<jsp:forward page="addInput.jsp"></jsp:forward>
<%
}
if(didian.startsWith("一教")||didian.startsWith("二教")||didian.startsWith("三教")||didian.startsWith("基教"))
{
}
else{
request.setAttribute("error", "地点不为指定地点");
%>
<jsp:forward page="addInput.jsp"></jsp:forward>
<%
}
User user = new User();
user.setkecheng(kecheng);
user.setjiaoshi(jiaoshi);
user.setdidian(didian);
UserDaoImpl userDao = new UserDaoImpl();
try{
userDao.add(user);
%>
用户保存成功!!<br>
<a href="addInput.jsp">继续添加</a><br>
<a href="#">用户列表</a>
<%
}catch(UserException e){
%>
<h2 style="color:red ; font-size:50px">发生错误 : <%=e.getMessage() %></h2>
<%
}
%>
</html>
addInput.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>用户添加页面</title>
</head>
<body>
<%=request.getAttribute("error") %>
<form action="add.jsp" method="get">
<table align="center" border="1" width="500">
<tr>
<td>课程名称: </td>
<td>
<input type="text" name="kecheng" />
</td>
</tr>
<tr>
<td>教师名称:</td>
<td>
<input type="text" name="jiaoshi" />
</td>
</tr>
<tr>
<td>教课地点:</td>
<td>
<input type="text" name="didian" />
</td>
</tr>
<tr align="center">
<td colspan="2">
<input type="submit" value="提交" />
<input type="reset" value="重置" />
</td>
</tr>
</table>
</form>
</body>
</html>
init.jsp:
-- mysal -u root -p root;
-- show databases;
-- drop database jaovo_shop;
create database jaovo_sk;
use jaovo_sk;
GRANT ALL ON jaovo_sk.* to "jaovo"@"localhost" IDENTIFIED BY "root";
create table t_user(
id int(10) primary key auto_increment,
kecheng varchar(255),
jiaoshi varchar(255),
didian varchar(255),
type int(2),
status int(2)
);
create table t_message(
id int(10) primary key auto_increment,
title varchar(254),
content text,
post_date datetime,
user_id int(10),
CONSTRAINT FOREIGN KEY(user_id) REFERENCES t_user(id)
);
create table t_comment(
id int(10) primary key auto_increment,
content text,
post_date datetime,
user_id int(10),
msg_id int(10),
CONSTRAINT FOREIGN KEY(user_id) REFERENCES t_user(id),
CONSTRAINT FOREIGN KEY(msg_id) REFERENCES t_message(id)
);
结果截图:
项目计划总结:
时间记录日志:
缺陷记录日志:
在细节方面漏掉了;
在实现规定老师,规定地点的功能上花费了一些时间。
程序书写还是比较慢,应该经常练习,提升自己的指法