DAO(Data Access Object,数据访问对象), JSP Model1采用JSP+JavaBean的技术,将页面显示和业务逻辑分开。其中,JSP实现流程控制和页面显示,JavaBean对象封装数据和业务逻辑。JSP Model1的工作原理如下图所示。
纯JSP开发程序存在以下问题:
1.所有的JDBC代码写在JSP页面中,维护困难;
2.JSP中不应该使用任何sql包,即:不能再JSP中直接使用java.sql.*,这些数据处理包应该放在专门的类中。
3.规范化的数据处理模式为DAO模式。
显示层:使用JSP/Servlet进行页面效果的显示。
业务层(Business Object):会将多个原子性的DAO操作进行组合,组合成一个完整的业务逻辑。
数据层(DAO):提供多个原子性的DAO操作,如增加、修改、删除等,都属于原子性的操作。
对于一些大的系统,并且业务关联较多的系统,BO才会发挥作用,而如果业务操作较为简单,可以不使用BO,而完全通过DAO完成操作。
值对象VO类:对应数据库表的相应列。
数据库管理类:负责数据库连接的建立、关闭相关数据库资源
DAO接口:定义数据操作
DAO实现类:实现接口里的操作,访问数据库,操作对象是VO类
JSP页面:实例化DAO实现类,接收参数,调用实现类里的具体操作。
UserDao.java
package cn.dao.dao;
import java.util.*;
import cn.dao.vo.*;
public interface UserDao {
public ArrayList<User> list();
public boolean add(User user);
public boolean update(User user);
public int verify(String username,String password); //查询所有用户信息
public ArrayList<User> queryFenYe(int cPage);//分页
public int getPageCount();//计算分页的总页数
public User findbyId(int id);
}
UserDaoImplete.java
package cn.dao.dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import cn.dao.vo.User;
import cn.jdbc.tools.JDBCUtil;
public class UserDaoImplete implements UserDao {
Connection conn;
PreparedStatement pstmt;
ResultSet rs = null;
int pageSize = 2 ;//一页显示多少条记录的数
@Override
public ArrayList<User> list() {
// TODO 返回用户表中的所有行
String sql = "select * from user";
ArrayList<User> list = new ArrayList<User>();
try {
conn = JDBCUtil.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
String name = rs.getString("username");
String pwd = rs.getString("password");
String gender = rs.getString("gender");
String hobby = rs.getString("hobby");
String email = rs.getString("email");
User u = new User(0,name,pwd,gender,hobby,email);
list.add(u);
//下面这样写和上面写法相同
//User u = new User();
//u.setId(rs.getString("id"));
//u.setUsername(rs.getString("username"));
//list.add(u);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil.release(rs, pstmt, conn);
}
return list;
}
@Override
public boolean add(User user) {
// TODO Auto-generated method stub
int row = 0 ;
String sql = "insert into user(username,password,gender,hobby,email) values(?,?,?,?,?)";
try {
conn = JDBCUtil.getConnection();//建立连接
pstmt = conn.prepareStatement(sql);//处理语句
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getPassword());
pstmt.setString(3, user.getGender());
pstmt.setString(4, user.getHobby());
pstmt.setString(5, user.getEmail());
row = pstmt.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil.release(pstmt, conn);
}
return row>0 ? true : false;
}
@Override
public boolean update(User user) {
// TODO Auto-generated method stub
return false;
}
@Override
public int verify(String username, String password) {
// TODO 用户的登录验证,用户名和密码都正确返回值是1,用户名正确密码错误返回值是2,其他的返回-1
int flag = -1;
String sql = "select password from user where username=?";
try {
conn = JDBCUtil.getConnection();//建立连接
pstmt = conn.prepareStatement(sql);//处理语句
pstmt.setString(1, username);
rs = pstmt.executeQuery();
if(rs.next()) {//用户名存在
if(password.equals(rs.getString("password"))){//用户输入的密码和查找到的用户名对于的密码进行比较
flag = 1;
}else {
flag = 2; //密码错误
}
}else { //用户名不存在
flag = -1;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtil.release(rs, pstmt, conn);
}
return flag;
}
@Override
public ArrayList<User> queryFenYe(int cPage) {
// TODO 分页查询,得到当前页的用户集合
String sql = "select * from user limit ?,?";
ArrayList<User> list = new ArrayList<User>();
try {
conn = JDBCUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, (cPage-1)*pageSize);
pstmt.setInt(2, pageSize);
rs = pstmt.executeQuery();
while(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("username");
String pwd = rs.getString("password");
String gender = rs.getString("gender");
String hobby = rs.getString("hobby");
String email = rs.getString("email");
User u = new User(id,name,pwd,gender,hobby,email);
list.add(u);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.release(pstmt, conn);
}
return list;
}
@Override
public int getPageCount() {
// TODO 求分页的总页数
String sql = "select count(*) from user";
int recordCount = 0 ; //表中数据记录总数(多少个用户信息)
int pageCount = 0 ; //分页的总页数
try {
conn = JDBCUtil.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()) {
recordCount = rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtil.release(pstmt, conn);
}
//pageCount = (recordCount+pageSize-1)/pageSize;
pageCount = recordCount%pageSize==0 ? recordCount/pageSize : recordCount/pageSize+1;
return pageCount;
}
@Override
public User findbyId(int id) {
// TODO
return null;
}
}
User.java
package cn.dao.vo;
import java.io.Serializable;
public class User implements Serializable {
private int id;
private String username;
private String password;
private String gender;//性别
private String hobby;//爱好
private String email;
public User() {
super();
}
public User(int id, String username, String password, String gender, String hobby, String email) {
super();
this.id = id;
this.username = username;
this.password = password;
this.gender = gender;
this.hobby = hobby;
this.email = email;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getHobby() {
return hobby;
}
public void setHobby(String hobby) {
this.hobby = hobby;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
JDBCUtil.java
package cn.jdbc.tools;
import java.sql.*;
public class JDBCUtil {
//加载驱动,并建立数据库连接
public static Connection getConnection() throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
//2、通过DriverManager获取数据库链接
String url = "jdbc:mysql://localhost:3306/jspone";
String username = "root";
String password = "root12345";
Connection conn = DriverManager.getConnection(url,username,password);
return conn;
}
//释放数据库连接资源
public static void release(PreparedStatement pstmt ,Connection conn) {
if(pstmt!=null) {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
pstmt = null;
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
conn=null;
}
}
public static void release(ResultSet rs,PreparedStatement pstmt,Connection conn) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
release(pstmt,conn);
}
}
a1_form_zhuce_.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="a1_user_insert.jsp" method="post">
<h3>用户注册表单</h3>
<p>姓名:<input type="text" size="20" name="uname"></p>
<p>密码:<input type="password" name="pwd"></p>
<p>
性别:
男<input type="radio" name="sex" value="男" checked="checked">
女<input type="radio" name="sex" value="女">
</p>
<p>
爱好:
篮球<input type="checkbox" name="duo" value="篮球">
足球<input type="checkbox" name="duo" value="足球">
手球<input type="checkbox" name="duo" value="手球">
爬山<input type="checkbox" name="duo" value="爬山">
游泳<input type="checkbox" name="duo" value="游泳">
</p>
<p>E-maile<input type="email" name="email"></p>
<p>
<input type="submit" value="提交">
<input type="reset" value="重置">
<input type="button" value="普通">
</p>
</form>
</body>
</html>
a1_user_insert.jsp
<%@page import="cn.dao.dao.UserDaoImplete"%>
<%@page import="cn.dao.vo.User"%>
<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
//1、获取表单输入的数据
request.setCharacterEncoding("utf-8");
String name = request.getParameter("uname");
String pwd = request.getParameter("pwd");
String gender = request.getParameter("sex");
String message = "" ;
String []favour = request.getParameterValues("duo");
for(int i = 0 ; i < favour.length ; i++){
message = message + favour[i]+",";
}
String email = request.getParameter("email");
//2、封装到User里面
User user = new User(0,name,pwd,gender,message,email);
UserDaoImplete udi = new UserDaoImplete();
boolean flag = udi.add(user);
if(flag){
%>
<h1>注册成功!2秒后自动跳转到登陆页面,如果没有跳转,请点击<a href="a1_denglu.jsp">登陆</a></h1>
<%
response.setHeader("refresh", "2,URL=a1_denglu.jsp");
}else{
%>
<h1>注册失败!请重新注册!2秒后自动跳转到你注册页面,如果没有跳转,请点击<a href="a1_form_zhuce_.jsp">注册</a></h1>
<%
response.setHeader("refresh", "2,URL=a1_form_zhuce_.jsp");
}
%>
</body>
</html>
a1_denglu.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<style>
h3{
text-align: center;
}
.user{
color: #999;
}
form{
text-align: center;
}
form .rem{
margin-left: 30px;
}
</style>
<body>
<%@ include file="a1_denglu_top.jsp"%><!-- 静态包含 -->
<%-- <jsp:include page="a1_denglu_top.jsp"></jsp:include> --%> <!-- 这是动态包含,运行时的包含 -->
<h3>欢迎进入清华大学用户登陆页面</h3>
<form type="text" action="a1_do_login.jsp" method="post">
<p> 用户名:<input type="text" name="username" value="请输入用户名" class="user"
onfocus="if(this.value=='请输入用户名'){this.value='';this.style.color='#424242'} "
onblur="if(this.value==''){this.value='请输入用户名' ; this.style.color='#999'}">
</p>
<p> 密 码:<input type="password" name="password"> </p>
<input type="submit" value="登陆" class="rem">
<input type="reset" value="重置" class="rem">
</form>
<%@ include file="a1_denglu_bottom.jsp"%>
</body>
<script type="text/javascript">
</script>
</html>
a1_denglu_top.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>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<center>
<img src="image/baner.jpg" width=100%/>
</center>
</body>
</html>
a1_denglu_bottom.jsp
<%@ page contentType="text/html; charset=UTF-8" %>
<table width="100%" cellspacing="0" cellpadding="4" align="center" bordercolor="#A6CAF0" border=1>
<tr>
<td> <div align="center">
<p>清华大学软件系 <br>
学校地址:北京市黄泉路1880号花拳绣腿<br/>
电话:100010001
</p>
</div></td>
</tr>
</table>
a1_do_login.jsp
<%@page import="cn.dao.dao.UserDaoImplete"%>
<%@page import="cn.dao.vo.User"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
//1、获取表单输入的数据
request.setCharacterEncoding("utf-8");
String name = request.getParameter("username");
String pwd = request.getParameter("password");
//2、调用实现类里的方法
UserDaoImplete udi = new UserDaoImplete();
int flag = udi.verify(name, pwd);
if(flag==1){
%>
<h1>登陆成功!2秒后自动跳转到商城主页面页面,如果没有跳转,请点击<a href="a1_user_list.jsp">主页面</a></h1>
<%
response.setHeader("refresh", "2,URL=a1_user_list.jsp");
}else if(flag==2){
%>
<h1>密码错误!2秒后自动跳转到登陆页面,如果没有跳转,请点击<a href="a1_denglu.jsp">再次尝试登陆</a></h1>
<%
response.setHeader("refresh", "2,URL=a1_denglu.jsp");
}else{
%>
<h1>用户名不正确!2秒后自动跳转到登陆页面,如果没有跳转,请点击<a href="a1_denglu.jsp">再次尝试登陆</a></h1>
<%
response.setHeader("refresh", "2,URL=a1_denglu.jsp");
}
%>
</body>
</html>
a1_user_list.jsp
<%@page import="java.util.*"%>
<%@page import="cn.dao.dao.UserDaoImplete"%>
<%@page import="cn.dao.vo.User"%>
<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<table width="800px" border="1px" align="center">
<tr>
<td>用户ID</td>
<td>用户名</td>
<td>性别</td>
<td>爱好</td>
<td>邮箱</td>
<td>修改</td>
<td>删除</td>
</tr>
<%
UserDaoImplete udi = new UserDaoImplete();
ArrayList<User> list = udi.list();
for(User u : list){
%>
<tr>
<td><%=u.getId()%></td>
<td><%=u.getUsername()%></td>
<td><%=u.getGender()%></td>
<td><%=u.getHobby()%></td>
<td><%=u.getEmail()%></td>
<td><a href="a1_user_edit.jsp?id=<%=u.getId() %>">修改该用户</a></td>
<td><a href="a1_user_delete.jsp?id=<%=u.getId() %>">删除该用户</a></td>
</tr>
<%
}
%>
</table>
</body>
</html>
a1_user_list_FenYe.jsp
<%@page import="java.util.*"%>
<%@page import="cn.dao.dao.UserDaoImplete"%>
<%@page import="cn.dao.vo.User"%>
<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<table width="800px" border="1px" align="center">
<tr>
<td>用户ID</td>
<td>用户名</td>
<td>性别</td>
<td>爱好</td>
<td>邮箱</td>
<td>修改</td>
<td>删除</td>
</tr>
<%
int curPage = 1;//当前页
String strcurPage = request.getParameter("page");
if(strcurPage!=null){
curPage = Integer.parseInt(strcurPage);
}
UserDaoImplete udi = new UserDaoImplete();
int pageCount = udi.getPageCount();//获取分页的页数
ArrayList<User> list = udi.queryFenYe(curPage);
for(User u : list){
%>
<tr>
<td><%=u.getId()%></td>
<td><%=u.getUsername()%></td>
<td><%=u.getGender()%></td>
<td><%=u.getHobby()%></td>
<td><%=u.getEmail()%></td>
<td><a href="a1_user_edit.jsp?id=<%=u.getId() %>">修改该用户</a></td>
<td><a href="a1_user_delete.jsp?id=<%=u.getId() %>">删除该用户</a></td>
</tr>
<%
}
%>
<tr>
<td align="center" colspan="7">共<%=pageCount %>页,当前第<%=curPage %>页
<%
for(int i= 1; i <= pageCount ; i++){
%>
<a href="a1_user_list_FenYe.jsp?page=<%=i%>"><%=i %></a>
<%
}
%>
<a href="a1_user_list_FenYe.jsp?page=1">第一页</a>
<a href="a1_user_list_FenYe.jsp?page=<%=curPage-1%>">上一页</a>
<a href="a1_user_list_FenYe.jsp?page=<%=curPage+1%>">下一页</a>
<a href="a1_user_list_FenYe.jsp?page=<%=pageCount %>">最后一页</a>
</td>
</tr>
</table>
</body>
</html>