java word模板导入数据库 javaweb导入数据库

eclipse下javaweb连接mysql数据库-以用户登陆为例——javaweb系列(三)

目录

• eclipse下javaweb连接mysql数据库-以用户登陆为例——javaweb系列(三)

• 下载jdbc驱动程序(jar)
• jar导入到程序中
• 创建数据库
• entity实体类
• util连接数据库类
• dao接口类
• servlet处理类
• jsp页面
• web.xml
• 最终项目结构
• 运行结果

下载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实体类负责承接与数据库交互的实体数据