jsp连接sql数据库的步骤(用jsp连接sql做一个登录界面)

0x01 准备数据库表1. 创建数据库并创建表

自行创建一个数据库，然后创建一张user表：

——————————–Tablestructurefor`user`——————————DROPTABLEIFEXISTS`user`;CREATETABLE`user`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`username`varchar(50)NOTNULLDEFAULT’default’,`password`varchar(50)NOTNULLDEFAULT’123456′,`role`int(10)NOTNULLDEFAULT’1′,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=latin1;

role字段说明：0：管理员
1：普通用户
默认注册的时候，是只能注册成普通用户。

2. 插入三条数据——————————–Recordsofuser——————————INSERTINTO`user`VALUES(‘1′,’lisa’,’123123′,’0′);INSERTINTO`user`VALUES(‘2′,’tony’,’123123′,’1′);INSERTINTO`user`VALUES(‘3′,’tom’,’123123′,’1′);0x02 初始化工程1. 新建一个JavaWeb项目

（1）添加自己的 Tomcat 进来，选择 Web Application ，同时，如果没有选择创建 web.xml 文件的话，记得勾选一下：

2. 初始化工程目录

（1）在WEB-INF文件夹下，创建一个lib文件夹，将MySQL驱动复制进入：

（2）设置MySQL驱动为需要加载的驱动：

3. 新建好包结构

（1）我的包结构如下，直接在 src 文件夹里创建即可：

└─com└─java├─dao│└─impl├─domain├─servlet└─utils0x03 编写后端代码1. 代码及项目结构总览

在这里插入图片描述2. 编写实体类packagecom.java.domain;importjava.util.Objects;/***@Authorshaonaiyi@163.com*@Date2022/3/611:03*@Description用户实体类*/publicclassUser{//用户idprivateintid;//用户名privateStringusername;//密码privateStringpassword;//用户角色（0：管理员；1：普通用户）privateintrole;publicintgetId(){returnid;}publicvoidsetId(intid){this.id=id;}publicStringgetUsername(){returnusername;}publicvoidsetUsername(Stringusername){this.username=username;}publicStringgetPassword(){returnpassword;}publicvoidsetPassword(Stringpassword){this.password=password;}publicintgetRole(){returnrole;}publicvoidsetRole(introle){this.role=role;}@Overridepublicbooleanequals(Objecto){if(this==o)returntrue;if(o==null||getClass()!=o.getClass())returnfalse;Useruser=(User)o;returnid==user.id&&role==user.role&&Objects.equals(username,user.username)&&Objects.equals(password,user.password);}@OverridepublicinthashCode(){returnObjects.hash(id,username,password,role);}@OverridepublicStringtoString(){return”User{” “id=” id “,username='” username ‘\” “,password='” password ‘\” “,role=” role ‘}’;}}3. 编写Dao层接口packagecom.java.dao;importcom.java.domain.User;/***@Authorshaonaiyi@163.com*@Date2022/3/614:32*@Description用户dao层接口*/publicinterfaceUserDao{//登录Userlogin(Stringusername,Stringpassword);//注册Booleanregister(Useruser);}4. 编写数据库连接工具类packagecom.java.utils;importjava.sql.*;/***@Authorshaonaiyi@163.com*@Date2022/3/612:40*@Description数据库连接工具类*/publicclassJdbcUtil{/***1、获取Connection*@returnConnection*@throwsException*/publicstaticConnectiongetConnection()throwsException{Class.forName(“com.mysql.jdbc.Driver”);Connectionconnection=DriverManager.getConnection(“jdbc:mysql://localhost:3306/javaweb”,”root”,”123456″);returnconnection;}/***2、释放资源*@paramresultSet*@paramstatement*@paramconnection*/publicstaticvoidrelease(ResultSetresultSet,Statementstatement,Connectionconnection){if(resultSet!=null){try{resultSet.close();}catch(SQLExceptione){e.printStackTrace();}}if(statement!=null){try{statement.close();}catch(SQLExceptione){e.printStackTrace();}}if(connection!=null){try{connection.close();}catch(SQLExceptione){e.printStackTrace();}}}//publicstaticvoidmain(String[]args)throwsException{//Connectionconnection=JdbcUtil.getConnection();//System.out.println(connection);//}}5. 编写Dao层接口实现类packagecom.java.dao.impl;importcom.java.dao.UserDao;importcom.java.domain.User;importcom.java.utils.JdbcUtil;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;/***@Authorshaonaiyi@163.com*@Date2022/3/614:32*@Description用户dao层实现类*/publicclassUserDaoImplimplementsUserDao{/***登录*@paramusername用户名*@parampassword密码*@return用户对象*/publicUserlogin(Stringusername,Stringpassword){Useruser=null;Connectionconnection=null;PreparedStatementpreparedStatement=null;ResultSetresultSet=null;try{connection=JdbcUtil.getConnection();Stringsql=”select*fromuserwhereusername=?andpassword=?”;preparedStatement=connection.prepareStatement(sql);preparedStatement.setString(1,username);preparedStatement.setString(2,password);resultSet=preparedStatement.executeQuery();if(resultSet.next()){user=newUser();user.setId(resultSet.getInt(“id”));user.setUsername(resultSet.getString(“username”));user.setPassword(resultSet.getString(“password”));user.setRole(resultSet.getInt(“role”));System.out.println(“登录成功” user.toString());}else{System.out.println(“用户名或者密码错误”);}}catch(Exceptione){e.printStackTrace();}finally{JdbcUtil.release(resultSet,preparedStatement,connection);}returnuser;}/***注册*@paramuser用户对象*/publicBooleanregister(Useruser){Connectionconnection=null;PreparedStatementpreparedStatement=null;ResultSetresultSet=null;intresult=0;try{connection=JdbcUtil.getConnection();Stringsql=”insertintouser(username,password)values(?,?);”;preparedStatement=connection.prepareStatement(sql);preparedStatement.setString(1,user.getUsername());preparedStatement.setString(2,user.getPassword());result=preparedStatement.executeUpdate();}catch(Exceptione){e.printStackTrace();}finally{JdbcUtil.release(resultSet,preparedStatement,connection);}//三目表达式，result等1则人会true，否则返回falsereturnresult==1?true:false;}}6. 修改web.xml文件

（1）设置欢迎首页

<!–设置启动首页为login.jsp–><welcome-file-list><welcome-file>login.jsp</welcome-file></welcome-file-list>

（2）设置登陆和注册Servlet

<!–设置servlet–><servlet><servlet-name>loginServlet</servlet-name><servlet-class>com.java.servlet.LoginServlet</servlet-class></servlet><!–设置servlet的url–><servlet-mapping><servlet-name>loginServlet</servlet-name><url-pattern>/login</url-pattern></servlet-mapping><!–设置servlet–><servlet><servlet-name>registerServlet</servlet-name><servlet-class>com.java.servlet.RegisterServlet</servlet-class></servlet><!–设置servlet的url–><servlet-mapping><servlet-name>registerServlet</servlet-name><url-pattern>/register</url-pattern></servlet-mapping>7. 编写LoginServlet代码packagecom.java.servlet;importcom.java.dao.UserDao;importcom.java.dao.impl.UserDaoImpl;importcom.java.domain.User;importjavax.servlet.ServletException;importjavax.servlet.annotation.WebServlet;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;/***@Authorshaonaiyi@163.com*@Date2022/3/612:40*@Description登录Servlet*/@WebServlet(name=”/login”)publicclassLoginServletextendsHttpServlet{@OverrideprotectedvoiddoGet(HttpServletRequestreq,HttpServletResponseresp)throwsServletException,IOException{doPost(req,resp);}@OverrideprotectedvoiddoPost(HttpServletRequestreq,HttpServletResponseresp)throwsServletException,IOException{req.setCharacterEncoding(“UTF-8”);resp.setContentType(“text/html;charset=UTF-8”);//获取前端页面传过来的值Stringusername=req.getParameter(“username”);Stringpassword=req.getParameter(“password”);Stringrole=req.getParameter(“role”);//执行查询数据库逻辑UserDaouserDao=newUserDaoImpl();Useruser=userDao.login(username,password);//如果根据用户名和密码能查得到值if(user!=null){introleDb=user.getRole();//权限的选择跟数据库不匹配时，且不是管理员用户时，返回无权限if(roleDb!=Integer.parseInt(role)&&roleDb!=0){req.setAttribute(“message”,”无权限登录”);req.getRequestDispatcher(“/defeat.jsp”).forward(req,resp);}else{req.setAttribute(“user”,user);req.getRequestDispatcher(“/success.jsp”).forward(req,resp);}}else{//用户名或者密码错误执行以下代码req.setAttribute(“message”,”用户名或者密码错误”);req.getRequestDispatcher(“/defeat.jsp”).forward(req,resp);}}}8. 编写RegisterServlet代码packagecom.java.servlet;importcom.java.dao.UserDao;importcom.java.dao.impl.UserDaoImpl;importcom.java.domain.User;importjavax.servlet.ServletException;importjavax.servlet.annotation.WebServlet;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;/***@Authorshaonaiyi@163.com*@Date2022/3/616:38*@Description注册servlet*/@WebServlet(name=”/register”)publicclassRegisterServletextendsHttpServlet{@OverrideprotectedvoiddoPost(HttpServletRequestreq,HttpServletResponseresp)throwsServletException,IOException{doPut(req,resp);}@OverrideprotectedvoiddoPut(HttpServletRequestreq,HttpServletResponseresp)throwsServletException,IOException{req.setCharacterEncoding(“UTF-8”);resp.setContentType(“text/html;charset=UTF-8”);//id不需要传进来，角色默认注册是普通用户，无法注册管理员Stringusername=req.getParameter(“username”);Stringpassword=req.getParameter(“password”);Useruser=newUser();user.setUsername(username);user.setPassword(password);UserDaouserDao=newUserDaoImpl();if(userDao.register(user)){System.out.println(“注册成功”);//注册成功，跳转登录页面req.getRequestDispatcher(“register-success.jsp”).forward(req,resp);}else{System.out.println(“注册失败”);req.setAttribute(“message”,”注册失败”);req.getRequestDispatcher(“register.jsp”).forward(req,resp);}}}

至此，后端代码已经编写好了，还需要完成前端页面代码。

0x04 编写前端代码1. 编写login.jsp代码<%–CreatedbyIntelliJIDEA.User:shaonaiyiDate:2022/3/6Time:14:47TochangethistemplateuseFile|Settings|FileTemplates.–%><%@pagecontentType=”text/html;charset=UTF-8″language=”java”%><html><head><title>登录界面</title><styletype=”text/css”>body{background-position:center;background-repeat:no-repeat;}</style></head><body><divstyle=”text-align:center;margin-top:120px”><h2>登录页面</h2><formaction=”/login”method=”post”><tablestyle=”margin-left:40%”><tr><td>用户名：</td><td><inputtype=”text”size=”21″name=”username”/></td></tr><tr><td>密码：</td><td><inputtype=”text”size=”21″name=”password”/></td></tr><tr><td>角色：</td><td><inputtype=”radio”name=”role”value=”0″checked=”checked”>管理员<inputtype=”radio”name=”role”value=”1″>普通用户</td></tr></table><inputtype=”submit”value=”登录”/><inputtype=”reset”value=”重置”/></form><ahref=”register.jsp”>跳转注册</a></div></body></html>2. 编写success.jsp代码<%@pageimport=”com.java.domain.User”%><%–CreatedbyIntelliJIDEA.User:shaonaiyiDate:2022/3/6Time:14:59TochangethistemplateuseFile|Settings|FileTemplates.–%><%@pagecontentType=”text/html;charset=UTF-8″language=”java”%><html><head><title>登录成功</title></head><body><h1>登录成功！</h1><br><fontcolor=”red”><%Useruser=(User)request.getAttribute(“user”);Stringusername=user.getUsername();%>欢迎您：<%out.print(username);%></font></body></html>3. 编写defeat.jsp代码<%–CreatedbyIntelliJIDEA.User:shaonaiyiDate:2022/3/6Time:14:59TochangethistemplateuseFile|Settings|FileTemplates.–%><%@pagecontentType=”text/html;charset=UTF-8″language=”java”%><html><head><title>登录失败</title></head><body><h1>登录失败！</h1><fontcolor=”red”><%if(request.getAttribute(“message”)!=null){out.print(request.getAttribute(“message”));}%></font><br><ahref=”login.jsp”>重新登录</a></body></html>4. 编写register.jsp代码<%–CreatedbyIntelliJIDEA.User:shaonaiyiDate:2022/3/6Time:16:37TochangethistemplateuseFile|Settings|FileTemplates.–%><%@pagecontentType=”text/html;charset=UTF-8″language=”java”%><html><head><title>注册界面</title><styletype=”text/css”>body{background-repeat:no-repeat;background-position:center;}</style></head><body><divstyle=”text-align:center;margin-top:120px”><formaction=”/register”method=”post”><tablestyle=”margin-left:40%”><h2>用户注册</h2><tr><td>用户名：</td><td><inputname=”username”type=”text”size=”21″></td></tr><tr><td>密码:</td><td><inputname=”password”type=”password”size=”21″></td></tr></table><inputtype=”submit”value=”注册”><inputtype=”reset”value=”重置”></form><ahref=”login.jsp”>跳转登录</a></form></div></body></html>5. 编写register-success.jsp代码<%@pageimport=”com.java.domain.User”%><%–CreatedbyIntelliJIDEA.User:shaonaiyiDate:2022/3/6Time:14:59TochangethistemplateuseFile|Settings|FileTemplates.–%><%@pagecontentType=”text/html;charset=UTF-8″language=”java”%><html><head><title>注册成功</title></head><body><h1>注册成功！</h1><br><ahref=”login.jsp”>跳转登录</a><ahref=”register.jsp”>继续注册</a></body></html>0xFF 总结本篇教程内容比较长，适合初学者学习，但是也需要有一点基础，比如环境的配置，但如果你稍微有点基础，操作一下这篇文章是非常不错的，特别是代码。关于JavaWeb其他文章参考：JavaWeb项目如何配置Servlet使用Eclipse打开IDEA写的JavaWeb项目【全网唯一教程】