JavaWeb连接数据库

JAY.LIN 收录于 Javaweb

2024-12-28 约 1149 字预计阅读 3 分钟

https://bing.ee123.net/img/rand?artid=123352417

JavaWeb连接数据库

前提条件

配置好编程环境:jdk(包含jre)

配置还Web环境: Tomcat

配置还数据库环境: MySQL, mysql-connector-java-8.0.16.jar

配置好编译器环境: Idea2018企业版

总体布局:

前端部分:JSP页面

在jsp页面上写上一下代码

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title>$Title$</title>
  </head>
  <body>
  <form method="post" action="http://localhost:8080/Login">
    账号:<input type="text" name="id"><br>
    密码:<input type="password" name="pwd"><br>
    <input type="submit" value="连接数据库">  <input type="reset" value="重新输入">
  </form>
  </body>
</html>

用于展示前端页面

创建数据库的类

为连接数据库作准备

package DateBaseModel;

public class DateBaseModel {
    private Integer id;
    private String Name;
    private String password;

    public DateBaseModel(Integer id, String name, String password) {
        this.id = id;
        Name = name;
        this.password = password;
    }

    public DateBaseModel(String name, String password) {
        Name = name;
        this.password = password;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return Name;
    }

    public void setName(String name) {
        Name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

Web连接数据库

注册驱动, 在WEB-INF文件夹下新建一个lib目录,把 mysql-connector-java-8.0.16.jar 复制粘贴到这里,然后右击加为library

package dbpackage;

import DateBaseModel.DateBaseModel;

import java.sql.*;
import java.util.ArrayList;

public class Linkdb {
    Connection conn = null;

    public Linkdb() throws ClassNotFoundException, SQLException {

        //注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        this.conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/SchoolTownDB?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC", "root", "qq124519");

    //释放资源
    conn.close();

后台对数据库的操作

//在login表里面添加数据
    public void insert(String name, String pwd) throws SQLException {
        PreparedStatement prep = conn.prepareStatement("insert into SchoolTownDB.login value (null ,?,?)");
        prep.setString(1, name);
        prep.setString(2, pwd);
        prep.execute();

    }

    //查询的方法
    public ArrayList<DateBaseModel> getAlluser() throws SQLException {
        ArrayList<DateBaseModel> useList = new ArrayList<DateBaseModel>();
        PreparedStatement prep = conn.prepareStatement("select * from SchoolTownDB.login");
        prep.execute();

        ResultSet resultSet = prep.executeQuery();

        //判断
        while (resultSet.next()) {
            int myid = resultSet.getInt("id");
            String myname = resultSet.getString("name");
            String mypwd = resultSet.getString("password");

            useList.add(new DateBaseModel(myid, myname, mypwd));
        }
        return useList;
    }

    //在表里查询数据
    public DateBaseModel getUser(int id) throws SQLException {
        PreparedStatement prep = conn.prepareStatement("select * from SchoolTownDB.login where id =?");
        prep.setInt(1, id);
        prep.execute();

        ResultSet resultSet = prep.executeQuery();
        if (resultSet.next()) {
            int myid = resultSet.getInt("id");
            String myname = resultSet.getString("name");
            String mypwd = resultSet.getString("password");
            return new DateBaseModel(myid, myname, mypwd);
        }
        return null;
    }

    //登录的方法
    public DateBaseModel login(int id,String pwd) throws SQLException {
        PreparedStatement prep = conn.prepareStatement("select SchoolTownDB.login.password from SchoolTownDB.login where id=?");
        prep.setInt(1,id);
        //执行,把id的密码给查出来
        prep.execute();
        ResultSet resultSet=prep.executeQuery();
        if(resultSet.next()){
            String mypwd = resultSet.getString("password");
            //判断输入的密码和数据库的密码是否相等
            if (pwd.equals(mypwd)){
                return getUser(id);
            }
            else {
                return null;
            }
        }
        else {
            return null;
        }
	}

用Servlet实现前后交互

package ServletPackage;

import DateBaseModel.DateBaseModel;
import dbpackage.Linkdb;

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;
import java.util.ArrayList;

@WebServlet(name = "LoginServlet",urlPatterns = "/Login")
public class LoginServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        //设置一下编码解决网络乱码问题
        response.setContentType("text/html;charset=utf-8");
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");//设置编码

        int id = Integer.parseInt(request.getParameter("id"));
        String pwd = request.getParameter("pwd");
        PrintWriter out = response.getWriter();
        //out.println("输入的id账号为:" + id + "输入密码为:" + pwd);

        try {
            Linkdb database = new Linkdb();
            DateBaseModel usedate = database.login(id, pwd);
            if (usedate == null) {
                out.println("该用户不存在或者密码错误"+"<a href=\"http://localhost:8080\">返回登录</a>");
            } else {
                out.println("数据库连接成功" + usedate.getName());
                ArrayList<DateBaseModel> usedata = database.getAlluser();
                out.println("总表里面共有:" + usedata.size());
                for (DateBaseModel each : usedata) {
                    out.println(each.getId() + "------" + each.getName() + "-------" + each.getPassword());
                }
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }
}

启动

于是,一个关于服务器连接数据库的小案例成功了.