DBOper类
package jxk;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBOper {
//定义全局变量
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
//得到数据库连接
public Connection getConnection(String server, String dbname, String user, String pwd) throws Exception {
//MySQL8.0此语句中需要加cj,同时注意WebContent里面jar包的版本
String DRIVER="com.mysql.cj.jdbc.Driver";
//设置时区等参数
String URL="jdbc:mysql://"+server+":3306/"+dbname+"?user="+ user + "&password=" + pwd+
"&useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC"+"&AllowPublicKeyRetrieval=true";
//注册驱动
Class.forName(DRIVER);
//获得数据库连接
conn=DriverManager.getConnection(URL);
//返回连接
return conn;
}
//执行sql语句,可进行查询
public ResultSet executeQuery(String preparedSQL, String[] param) throws Exception {
//得到PreparedStatement对象
pstmt=conn.prepareStatement(preparedSQL);
if(param!=null)
{
for(int i=0;i<param.length;i++)
//为预编译sql设置参数
pstmt.setString(i+1, param[i]);
}
//执行sql语句
rs=pstmt.executeQuery();
return rs;
}
//执行sql语句,可以进行增删改的操作,不能执行查询
public int executeUpdate(String preparedSQL, String[] param) throws SQLException {
//得到PreparedStatement对象
pstmt=conn.prepareStatement(preparedSQL);
if(param!=null)
{
for(int i=0;i<param.length;i++)
//为预编译sql设置参数
pstmt.setString(i+1, param[i]);
}
//执行sql语句
int n=pstmt.executeUpdate();
return n;
}
public void close(){
try{
if(rs!=null)
rs.close();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(conn!=null)
conn.close();
}catch(Exception ee){
ee.printStackTrace();
}
}
}
}
登录
前端代码
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
用户登录:
<form method="post" action="LoginServlet">
用户名:<input type="text" name="userName" /><br/>
密码:<input type="password" name="password" /><br />
<input type="submit" value="登录" />
<input type="reset" value="重置"/>
</form>
</body>
</html>
LoginServlet
package test;//所在包名
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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 jxk.DBOper;
@WebServlet("/LoginServlet")//如无特别需要,禁止修改,与类名一致
public class LoginServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public LoginServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException,ServletException {
response.setContentType("text/html;charset=utf-8");//使客户端浏览器,区分不同种类的数据,此处为html中文类型
PrintWriter out = response.getWriter();//out用于输出到网页上相应内容
out.println("欢迎登录");
String yhm=request.getParameter("userName");//yhm为页面登陆的用户名
String mm=request.getParameter("password");//mm为页面登录的密码
DBOper db=new DBOper();//自定义Java类,创建对象
ResultSet rs=null;//预先声明结果集rs
try { //根据提示用try-catch包围
String server="127.0.0.1";//127.0.0.1是回送地址,指本地机
String dbname="test";//数据库名(test必须已经存在)
String user="root";//user为连接数据库的名字
String pwd="root";//pwd为连接数据库的密码
db.getConnection(server,dbname,user,pwd);//此处进行** 连接数据库 **getConnection是DBOper类内的一个成员函数
String preparedSQL = "select * from userdetail where username=? and userpass=?";//查询userdetail表中符合要求的记录(userdetail为test数据库中的一个表,必须已经创建,里面需要有登录的用户名和密码,用于登录)
rs=db.executeQuery(preparedSQL,new String[]{yhm,mm});
//执行查询 executeQuery()也是DBOper类中的一个成员函数,yhm和mm为登录输入的信息
if(rs!=null&&rs.next())
out.println("登陆成功!");
else
out.println("登陆失败!");
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}finally{
try{
db.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
注册
前端代码
<!DOCTYPE html>
<html>
<script>
</script>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
用户注册:
<form method="post" action="RegisterServlet">
用户名:<input type="text" name="username"> <br/>
密码:<input type="password" name="password1"> <br/>
确认密码:<input type="password" name="password2"> <br/>
<input type="submit" value="注册">
<input type="reset" value="重置">
</form>
</body>
</html>
RegisterServlet
package test;
import java.io.IOException;
import java.io.PrintWriter;
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 jxk.DBOper;
@WebServlet("/RegisterServlet")
public class RegisterServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public RegisterServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
out.println("欢迎进入注册页面!<br/>");
String yhm=request.getParameter("username");
String mm=request.getParameter("password1");
DBOper db=new DBOper();
try {
String server="127.0.0.1";
String dbname="test";
String user="root";
String pwd="root";
db.getConnection(server, dbname, user, pwd);
String preparedSQL="insert into userdetail values(?,?,now(),0,0)";
int n=db.executeUpdate(preparedSQL,new String[] {yhm,mm});//与登陆界面不同之处在这里
if(n>0)
{
out.println("注册成功!");
}
else
out.println("注册失败!");
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}finally{
try{
db.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
修改密码
前端代码
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
修改密码:
<form method="post" action="ChangeServlet">
用户名:<input type="text" name="userName" /><br />
原密码:<input type="password" name="password" /><br />
新密码:<input type="password" name="newpassword" /><br />
<input type="submit" value="确定" />
<input type="reset" value="取消" />
</form>
</body>
</html>
ChangeServlet
package test;
import java.io.IOException;
import java.io.PrintWriter;
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 jxk.DBOper;
@WebServlet("/ChangeServlet")
public class ChangeServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public ChangeServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
out.println("欢迎进入修改密码页面!<br/>");
String yhm=request.getParameter("username");
String ymm=request.getParameter("password1");
String xmm=request.getParameter("password2");
DBOper db=new DBOper();
try{
String server="127.0.0.1";
String dbname="test";
String user="root";
String pwd="root";
db.getConnection(server, dbname, user, pwd);
String preparedSQL="update userdetail set userpass=? where username=?";
int n=db.executeUpdate(preparedSQL, new String[]{xmm,yhm});
//注意?的顺序,与传入参数的顺序有关系
if(n>0)
out.println("修改密码成功!");
else
out.println("修改密码失败!");
}catch(Exception e){
e.printStackTrace();
}finally{
try{
db.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
总结
- 以上前端代码仅为基础的html,未用css及js,读者可自行添加完善。
- 连接数据库需要在WebContent目录下的WEB-INF中lib目录里添加jar包,这里版本是8.0,注意DRIVER和URL中的语句要与之适配。
- Statement和PreparedStatement的区别:
PreparedStatement是Statement的子接口,它的实例对象可以通过调用Connection.preparedStatement(sql)方法获得,相对于Statement对象而言:
PreperedStatement可以避免SQL注入的问题。
Statement会使数据库频繁编译SQL,可能造成数据库缓冲区溢出。PreparedStatement 可对SQL进行预编译,从而提高数据库的执行效率。
并且PreperedStatement对于sql中的参数,允许使用占位符的形式进行替换,简化sql语句的编写。
ParperStatement提高了代码的灵活性和执行效率
比Statement安全
执行的sql语句格式不同
语句更简洁易懂。
4.pstmt.setString(i + 1, param[i])
第一个参数是该参数在语句中的(?)索引。参数标记具有从 1 开始的编号。
第二个参数是要对第一个参数设置的值