一、目的
1)熟悉JDBC的体系结构和常用编程接口;
2)能熟练使用Connection对象连接到常用数据库;
3)能熟练使用Statement、PrepareStatement及CallableStatement对象访问数据库,执行SQL语句并接收返回结果;
4)能熟练使用ResultSet对象处理返回的数据;
5)理解数据库连接池的设计思想,理解数据源的概念,能熟练配置和使用数据源以提高数据访问效率;
6)理解DAO设计模式的基本思想,能设计自己的VO对象和DAO对象以分离数据访问逻辑。
二、原理
1、JDBC的体系结构和和常用编程接口;
2、使用DriverManager方式连接并访问数据库的基本方法;
3、数据源的概念,以及使用数据源方式连接并访问数据库的方法;
4、DAO设计模式的基本思想,VO类及DAO类的基本设计。
三、实现过程及步骤
1、安装和启动MySql服务,创建GuestBook数据库及User表,表中含ID、用户名、密码、邮箱等字段;
2、创建用户注册页面及注册处理Servlet,能将注册用户信息插入User表;
代码如下:
package jdbc;
import javax.naming.NamingException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
@WebServlet("/RegisterServlet")
public class RegisterServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws IOException {
String username = request.getParameter("username");
String password = request.getParameter("password");
String email = request.getParameter("email");
Connection conn = null;
PreparedStatement sm = null;
try {
conn = Conn.getConnection();
sm = conn.prepareStatement("INSERT into guestbook.user(username, password, email) values(?,?,?)");
sm.setString(1,username);
sm.setString(2,password);
sm.setString(3,email);
sm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} catch (NamingException e) {
e.printStackTrace();
} finally {
Conn.close(sm,conn);
}
}
}
注册页面:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>register</title>
</head>
<body>
<form action="RegisterServlet" method="post">
Please enter your registration information<br>
Username:<input type="text" name="username"><br>
Password: <input type="password" name="password"><br>
Email: <input type="email" name="email"><br>
<input type="submit" value="提交">
</form>
</body>
</html>
运行结果:
注册的信息能插入到数据库中:
3、开发用户登录页面及登录验证Servlet,从User表中检索用户信息进行验证,验证通过则转入成功页面,否则提示重新登录;
代码如下:
package jdbc;
import javax.naming.NamingException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String username = request.getParameter("username");
String password = request.getParameter("password");
Connection con = null;
PreparedStatement sm = null;
ResultSet rs = null;
try {
con = Conn.getConnection();
sm = con.prepareStatement("SELECT * FROM user WHERE username = ?");
sm.setString(1,username);
rs = sm.executeQuery();
rs.next();
String passwaordTemp = rs.getString(3);
if(passwaordTemp.equals(password))
{
response.sendRedirect("success.html");
} else {
response.setCharacterEncoding("GBK");
RequestDispatcher rd = getServletContext().getRequestDispatcher("/Login.html");
PrintWriter pw = response.getWriter();
pw.println("<p>Error!</p>");
rd.include(request,response);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (NamingException e) {
e.printStackTrace();
} finally {
Conn.close(rs,sm,con);
}
}
}
登录页面:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Login</title>
</head>
<body>
<form action="LoginServlet" method="post">
Enter your username and psaaword to log in<br>
Username:<input name="username" type="text"><br>
password:<input name="password" type="password"><br>
<input type="submit" value="Login">
</form>
</body>
</html>
登录成功页面:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>LoginSuccess</title>
</head>
<body>
<b>You have logged in successfully!</b>
</body>
</html>
运行结果:
成功登录页面:
登录失败页面:
4、使用DataSource方式重新实现用户注册和登录验证的Servlet,写出数据源配置方法,及在Servlet中检索和使用数据源的方法
(1)数据源配置:
数据配置要在Tomcat的conf/context.xml中进行配置:
<Resource name="jdbc/zhai" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
auth= "Container" username= "root" password= "*********"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/guestbook"/>
(2)使用数据源的方法:
先创建应用程序上下文对象context,从上下文对象中检索数据源对象Datasourse,从数据源对象获取数据库连接对象Connection,使用Connection对象操作数据库。
package jdbc;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Conn {
public static Connection getConnection() throws SQLException, NamingException {
Context context = new InitialContext();
DataSource dataSource = (DataSource) context.lookup("java:/comp/env/jdbc/haha");
Connection conn = dataSource.getConnection();
return conn;
}
public static void close(ResultSet rs, PreparedStatement sm, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (sm != null) {
try {
sm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement sm, Connection conn) {
close(null, sm, conn);
}
}
5、使用DAO模式设计数据访问层,写出VO类及DAO类的核心代码,以及在Servlet中调用数据访问层的方法。
VO类:存储数据库内的用户数据
package VO;
public class VO {
String id;
String username;
String password;
String email;
public String getId() {
return id;
}
public VO(String username, String password, String email) {
this.username = username;
this.password = password;
this.email = email;
}
public String getUsernname() {
return username;
}
public String getPassword() {
return password;
}
public String getEmail() {
return email;
}
}
DAO类:使用DAO类将数据库操作的代码封装起来。
package DAO;
import VO.VO;
import javax.naming.NamingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public interface DAO {
Connection getConnection() throws NamingException, SQLException;
void close(ResultSet rs, PreparedStatement ps, Connection conn);
void register(VO VO) throws SQLException, NamingException;
Boolean login(String username, String passward) throws SQLException, NamingException;
}
登录类:如果输入信息正确则跳转到登录成功页面,输入信息错误跳转到登录失败页面。对数据库的操作通过调用DAO接口实现。
package Servlet;
import DAO.DAO;
import DAO.EDAO;
import javax.naming.NamingException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String username = request.getParameter("username");
String password = request.getParameter("password");
DAO DAO = new EDAO();
try {
boolean judge =DAO.login(username,password);
if(judge){
response.sendRedirect("success.html");
}
else{
response.setCharacterEncoding("GBK");
RequestDispatcher requestDispatcher = getServletContext().getRequestDispatcher("/Login.html");
PrintWriter printWriter = response.getWriter();
printWriter.println("please enter again!");
requestDispatcher.include(request,response);
printWriter.close();
}
} catch (SQLException e) {
e.printStackTrace();
} catch (NamingException e) {
e.printStackTrace();
}
}
}
注册类:
package Servlet;
import DAO.DAO;
import DAO.EDAO;
import VO.VO;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;
@WebServlet("/RegisterServlet")
public class RegisterServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String username = request.getParameter("username");
String password = request.getParameter("password");
String email = request.getParameter("email");
VO VO = new VO(username,password,email);
DAO DAO = new EDAO();
try {
DAO.register(VO);
System.out.println("haha");
} catch (SQLException e) {
e.printStackTrace();
} catch (NamingException e) {
e.printStackTrace();
}
}
}