eclipse下javaweb连接mysql数据库-以用户登陆为例
目录
下载jdbc驱动程序(jar)
下载mysql的jar包，地址：https://mvnrepository.com/artifact/mysql/mysql-connector-java
找到自己需要的版本
这里还有其他几个jar，一起引入，其中servlet-api.jar来自本地的tomcat
jar导入到程序中
将驱动程序复制到应用项目下的/WEB-INF/lib文件夹下
【右键项目】----【Build Path】----【Configure Build Path】
选择Library，然后Add JARs…
【右键项目】----【Refresh】，刷新之后，可以看到项目下多了Referenced Library目录
创建数据库
使用navicat，在自己的连接中新建数据库实例，然后新建一张表，如图所示
entity实体类
【entity右键】----【New】----【Class】
新建实体类，对照数据库字段自己写字段，其中get和set可以自动生成
全选，然后点击Generate
package entity;
public class User {
private Integer usernum;
private String name;
private String password;
private int role;
public Integer getUsernum() {
return usernum;
}
public void setUsernum(Integer usernum) {
this.usernum = usernum;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getRole() {
return role;
}
public void setRole(int role) {
this.role = role;
}
}
util连接数据库类
com.mysql.jdbc.Driver是mysql-connector–java 5 中的驱动方式
com.mysql.cj.jdbc.Driver是mysql-connector–java 6 以后的驱动方式
只需要修改以下三行，关于数据库的配置，其他可默认不修改
public static final String url=“jdbc:mysql://localhost:3306/usertest?xx”;`
public static final String username=“root”;
public static final String password=“xxx”;
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Driver;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
public static final String driver="com.mysql.jdbc.Driver";
public static final String url="jdbc:mysql://localhost:3306/usertest?characterEncoding=utf8&useSSL=true";
public static final String username="root";
public static final String password="xxx";
public static Connection con=null;
static{
try {
Class.forName(driver);//得到DriverManager，在下面建立连接时使用
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getCon(){
if(con == null){
try {
con = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
}
return con;
}
//关闭的方法
public static void close(Statement statement,Connection conn){
if(statement !=null){
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn !=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String args[]){
new DBUtil().getCon();
}
}
dao接口类
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import entity.User;
import util.DBUtil;
public class UserDao {
//数据库连接对象
public User login(String username,String password) {
User u=null;
Connection connection =null;
PreparedStatement pstmt=null;
ResultSet resultSet=null;
//赋值
try {
connection=DBUtil.getCon();
//静态sql语句
String sql = "select * from sysuser where name=? and password=?";
pstmt = (PreparedStatement) connection.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
resultSet = pstmt.executeQuery();
if(resultSet.next()){
u=new User();
u.setName(resultSet.getString("name"));
u.setPassword(resultSet.getString("password"));
System.out.println("登录成功！");
}else{
System.out.println("用户名或者密码错误！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
// DBUtil.close(pstmt, connection);
}
return u;
}
public boolean addUser(User user) {
Connection connection = null;
PreparedStatement psmt = null;
try {
connection = DBUtil.getCon();
String sql ="insert into user(usernum,name,password,role)values(?,?,?,?);";
psmt = (PreparedStatement) connection.prepareStatement(sql);
//运用实体对象进行参数赋值
psmt.setInt(1, user.getUsernum());
psmt.setString(2, user.getName());
psmt.setString(3,user.getPassword());
psmt.setInt(4, user.getRole());
psmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
return false;
}finally {
//DBUtil.close(psmt, connection);
}
return true;
}
}
servlet处理类
package servlet;
import java.io.IOException;
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 javax.servlet.http.HttpSession;
import dao.UserDao;
import entity.User;
/**
* Servlet implementation class LoginServlet
*/
@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public LoginServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}
/**
* @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);
String account = request.getParameter("username");
String psd = request.getParameter("password");
HttpSession sessionzxl = request.getSession();
String username = request.getParameter("username");
String password = request.getParameter("password");
UserDao userDAO=new UserDao();
User user = userDAO.login(username, password);
if(user != null){
sessionzxl.setAttribute("user", user);
request.getRequestDispatcher("success.jsp").forward(request, response);;
}else{
request.getRequestDispatcher("error.jsp").forward(request, response);
}
}
}
jsp页面
login.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=ISO-8859-1">
<title>登录界面</title>
<style type="text/css">
</style>
</head>
<body>
<div style="text-align:center;margin-top:120px">
<form action="LoginServlet" method="post">
<table style="margin-left:40%">
<marquee width="200"scrolldelay="250">用户登录</marquee>
<tr>
<td>登录名：</td>
<td><input name="username" type="text" size="21"></td>
</tr>
<tr>
<td>密码:</td>
<td><input name="password" type="password" size="21"></td>
</tr>
</table>
<input type="submit" value="登录">
<input type="reset" value="重置">
</form>
<br>
</div>
</body>
</html>
success.jsp
jsp页面导入实体类的方式：<%@ page import=“entity.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">
<%@ page import="entity.User"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<html>
<head>
<base href="<%=basePath%>">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>My JSP 'success.jsp' starting page</title>
</head>
<body>
<%User user = (User)session.getAttribute("user");%>
"Congratulations! Login success!";<br>
username:<%= user.getName()%> <br>
password:<%= user.getPassword() %> <br>
basePath: <%=basePath%><br>
path:<%=path%><br>
</body>
</html>
error.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">
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<html>
<head>
<base href="<%=basePath%>">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>My JSP 'fail.jsp' starting page</title>
</head>
<body>
Login Failed! <br>
basePath: <%=basePath%><br>
path:<%=path%><br>
<a href="login.jsp">请重新登录</a>
</body>
</html>s s
web.xml
下面看web.xml配置：如果是比较旧的tomcat版本，不支持WebServlet注解方式，就需要在web.xml里面配置servlet-mapping映射。
我用的是Tomcat8.5，Servlet是3.1， 支持servlet注解。所以不需要在web.xml里面配置servlet。在Servlet的java文件里可以看到自动生成的注解：
@WebServlet("/LoginServlet")
但是Servlet3.0以下，在web.xml里面配置servlet，如下：
<servlet>
<!-- 类名 -->
<servlet-name>LoginServlet</servlet-name>
<!-- 所在的包 -->
<servlet-class>servlet.LoginServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>LoginServlet</servlet-name>
<!-- 访问的网址 -->
<url-pattern>/LoginServlet</url-pattern>
</servlet-mapping>
最终项目结构
运行结果
- servers可以看tomcat情况
- console可以查看项目运行情况，是否有bug
总结：
- jsp页面负责用户操作，调用servlet
- servlet负责承接前端的调用，以及调用dao内的函数
- dao负责数据库交互，增删改查的sql（使用util连接数据库，使用entity承载数据）
- util只负责连接数据库
- entity实体类负责承接与数据库交互的实体数据