java通过jdbc连接数据库并在前端实现增删查改

最新推荐文章于 2024-05-08 20:55:45 发布

Sept_C

最新推荐文章于 2024-05-08 20:55:45 发布

阅读量6.4k

点赞数 13

分类专栏：数据库文章标签：数据库 servlet java 前端

本文链接：https://blog.csdn.net/qq_28329883/article/details/53395873

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

因为数据库课程老师要求使用Java通过jdbc连接数据库，并且在前端实现增删查改的功能，所以就在网上找了个模板，改了一些，加了一些东西，勉强能用，不足的地方还请大家多多指教。
网上那个模板似乎不能在网上显示数据库的数据，是因为几个servlet类没有收到index.jsp的get/post请求，加了几个按钮，目前基本能使用。接下来就把我完成的东西分享出来给大家。

package javadatabase;

1、DBConnection类
主要是用来加载驱动，登录数据库啥的。

package javadatabase;
   import java.sql.*;
     /**
    *Created by caijie on 2016/10/25. 
    */
     public class DBConnection {

  /**
     * 驱动类名称
     */
    private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";

   /**
     * 数据库连接字符串
     */
   private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/Wechat?useUnicode=true&characterEncoding=utf-8&useSSL=false";

   /**
     * 数据库用户名
     */
   private static final String USER_NAME = "root";

   /**
     * 数据库密码
     */
    private static final String PASSWORD = "caijie";

   /**
     * 数据库连接类
     */
    private Connection conn = null;

   // 加载驱动
    public DBConnection() {
        try {
            Class.forName(DRIVER_CLASS);
        } catch (Exception e) {
            System.out.println("加载驱动错误");
            System.out.println(e.getMessage());
        }
        try {
            conn = DriverManager.getConnection(DATABASE_URL, USER_NAME, PASSWORD);
        } catch (Exception e) {
            System.out.println("取得连接错误");
            System.out.println(e.getMessage());
        }
    }

   // 取得连接
    public Connection getConnection() {
        return this.conn;
    }

   public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

   public static void close(PreparedStatement pstmt) {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2、ShowLine类
主要是用来获取数据库数据以及执行查询、修改和删除功能。

package javadatabase;

/**
 * Created by caijie on 2016/10/25.
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class ShowLine {
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;
    private Connection conn;

    public void ExcuteDel(String sql) {

        try {
            conn = new DBConnection().getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            // 查询数据库对象,返回记录集(结果集)
            pstmt = conn.prepareStatement(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            int rows = pstmt.executeUpdate(sql);
            if (rows >= 1) {
                System.out.println("成功删除.....");
            } else {
                System.out.println("删除失败.....");
            }

        } catch (Exception e) {
            // TODO: handle exception
        }
    }

    public void ExcuteMod(String sql) {

        try {
            conn = new DBConnection().getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            // 查询数据库对象,返回记录集(结果集)
            pstmt = conn.prepareStatement(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            int rows = pstmt.executeUpdate(sql);
            if (rows >= 1) {
                System.out.println("成功修改.....");
            } else {
                System.out.println("修改失败.....");
            }

        } catch (Exception e) {
            // TODO: handle exception
        }
    }
    public void ExcuteAdd(String sql){

        try {
            conn = new DBConnection().getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            // 查询数据库对象,返回记录集(结果集)
            pstmt = conn.prepareStatement(sql);
        }catch (Exception e)
        {
            e.printStackTrace();
        }

        try {
            int rows = pstmt.executeUpdate(sql);
            if(rows >= 1){
                System.out.println("成功添加.....");
            } else {
                System.out.println("添加失败.....");
            }

        } catch (Exception e) {
            // TODO: handle exception
        }
    }

    public ArrayList<User> getUserList(String sql){

        ArrayList<User> list = new ArrayList<User>();

        // 取得数据库操作对象
        try {
            conn = new DBConnection().getConnection();
        } catch (Exception e) {
           e.printStackTrace();
        }

        try {

            // 查询数据库对象,返回记录集(结果集)
            //pstmt = conn.prepareStatement(sql);
            pstmt = conn.prepareCall(sql);
            rs = pstmt.executeQuery();

            // 循环记录集，查看每一行每一列的记录
            while (rs.next()) {
                String UserN = rs.getString(1);

                String UserId = rs.getString(2);

                String Signature = rs.getString(3);

                String Portrait = rs.getString(4);

                Boolean Sex = rs.getBoolean(5);

                String Place = rs.getString(6);

                User user = new User();

                user.setUserN(UserN);
                user.setUserId(UserId);
                user.setSignature(Signature);
                user.setPortrait(Portrait);
                user.setSex(Sex);
                user.setPlace(Place);

                list.add(user);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return list;
    }
}

3、User类
对数据库对象属性的封装

package javadatabase;

/**
 * Created by caijie on 2016/10/19.
 */
public class User {
    private String UserN;
    private String UserId;
    private String Signature;
    private String Portrait;
    private Boolean Sex;
    private String Place;

    public void setUserId(String userId) {
        this.UserId = userId;
    }

    public void setSignature(String signature) {
        this.Signature = signature;
    }

    public void setUserN(String userN) {

        this.UserN = userN;
    }

    public void setPortrait(String portrait) {
        this.Portrait = portrait;
    }

    public void setSex(Boolean sex) {
        this.Sex = sex;
    }

    public void setPlace(String place) {
        this.Place = place;
    }

    public String getUserN() {
        return UserN;
    }

    public String getUserId() {
        return UserId;
    }

    public String getSignature() {
        return Signature;
    }

    public String getPortrait() {
        return Portrait;
    }

    public Boolean getSex() {
        return Sex;
    }

    public String getPlace() {
        return Place;
    }
}

package servlet;

1、UserServlet.java
查询表中数据显示在前端

package servlet;

import javadatabase.ShowLine;
import javadatabase.User;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;


public class UserServlet extends HttpServlet {

    ArrayList<User> list;
    public void init() throws ServletException
    {
        // 执行必需的初始化
    }
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        doGet(req, resp);
    }
    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        this.doPost(request, response);
    }
    @Override
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        ShowLine Temp = new ShowLine();

        this.list = Temp.getUserList("call u_search()");//这儿使用的存储过程，改成查询语句就行了

        //System.out.print(list.get(0));

        request.setAttribute("list", list);

        request.getRequestDispatcher("index.jsp").forward(request, response);
    }
}

2、AddServlet.java
执行点击添加按钮之后的功能

package servlet;

import javadatabase.ShowLine;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * Created by caijie on 2016/10/29.
 */
public class AddServlet extends HttpServlet {
    private static String name;
    private static String id;
    private static String signature;
    private static String portrait;
    private static String sex;
    private static String place;
    private boolean flag = false;

    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        this.doPost(request, response);
    }

    @Override
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        this.name = request.getParameter("usern");
        this.id = request.getParameter("id");
        this.signature = request.getParameter("signature");
        this.portrait = request.getParameter("portrait");
        this.sex = request.getParameter("sex");
        this.place = request.getParameter("place");

        String sql = "insert into user (usern,id,signature,portrait,sex,place) values" +
                "('" + name + "','" + id + "','" + signature + "','" + portrait + "','" + sex + "','" + place + "')";

        //System.out.print(sql);

        ShowLine db = new ShowLine();

        if(flag) {
            db.ExcuteAdd(sql);
            flag = false;
        }

        else
            flag = true;

        request.getRequestDispatcher("add.jsp").forward(request, response);

    }

}

3、DelServlet.java
执行点击删除功能后的一系列操作。

package servlet;

/**
 * Created by caijie on 2016/10/19.
 */

import javadatabase.ShowLine;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;


public class DelServlet extends HttpServlet {
    private static String sno;

    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        this.sno = request.getParameter("id");

        this.doPost(request, response);
    }

    @Override
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        String sql = "delete from user where Id = '" + sno + "' ";

        System.out.print(sql);

        ShowLine db = new ShowLine();

        db.ExcuteDel(sql);

    request.getRequestDispatcher("userservlet").forward(request, response);

    }

}

4、ModServlet.java
执行修改功能

package servlet;

import javadatabase.ShowLine;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * Created by Jason_Cai on 2016/11/28.
 */
public class ModServlet extends HttpServlet {
    private static String choice;
    private static String clumn;
    private static String change;
    private boolean flag = false;
    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        this.choice = request.getParameter("id");
        this.clumn = request.getParameter("eid");

        this.doPost(request, response);
    }

    @Override
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        this.change = request.getParameter("change");

        String sql = "update user set "+clumn+" = '"+change+"' where "+clumn+" = '"+choice+"' ";

        //System.out.print(sql);

        ShowLine db = new ShowLine();

        if(flag) {
            db.ExcuteMod(sql);
            flag = false;
        }

        else
            flag = true;

        request.getRequestDispatcher("modify.jsp").forward(request, response);
    }
}

Java代码以上就结束了
剩下的是web.xml文件和jsp文件
1、添加数据的界面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>添加</title>

</head>
<body>
<form action = "/addservlet" method = post>
    <input type="text" value="用户名*" readonly>
    <input type="text" name="usern" />
    <input type="text" value="Id*" readonly>
    <input type="text" name="id"/>
    <input type="text" value="签名" readonly>
    <input type="text" name="signature"/>
    <input type="text" value="头像" readonly>
    <input type="text" name="portrait" />
    <input type="text" value="性别*" readonly>
    <input type="text" name="sex"/>
    <input type="text" value="来自*" readonly>
    <input type="text" name="place"/>
    <input type="submit" value="确定"/>
</form>

<form action = "/userservlet" method = post>
    <input type="submit" value="返回">
</form>



</body>
</html>

2、开始界面

<%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%String path = request.getContextPath();
  String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%>
<html>

<head>
  <base href="<%=basePath%>">
  <title>list</title>
  <meta http-equiv="pragma" content="no-cache">
  <meta http-equiv="cache-control" content="no-cache">
  <meta http-equiv="expires" content="0">
  <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
  <meta http-equiv="description" content="This is my page">
  <!--<link rel="stylesheet" type="text/css" href="styles.css">-->
</head>

<body>

<form action = "/userservlet" method = post>
  <input type="submit" value="查询user表">
</form>


<form action = "/addservlet" method = post>
  <input type="submit" value="添加user表">
</form>

<table border="1">
  <tr>
    <td>姓名</td>
    <td>Id</td>
    <td>签名</td>
    <td>头像</td>
    <td>性别</td>
    <td>来自</td>
    <td>&nbsp;</td>
  </tr>


  <c:forEach items="${list}" var="user">
    <tr>
      <td><a href="/modservlet?id=${user.getUserN()}&eid=usern">${user.getUserN()}</a></td>
      <td><a href="/modservlet?id=${user.getUserId()}&eid=id">${user.getUserId() }</a></td>
      <td><a href="/modservlet?id=${user.getSignature()}&eid=signature">${user.getSignature() }</a></td>
      <td><a href="/modservlet?id=${user.getPortrait()}&eid=portrait">${user.getPortrait() }</a></td>
      <td><a href="/modservlet?id=${user.getSex()}&eid=sex">${user.getSex() }</a></td>
      <td><a href="/modservlet?id=${user.getPlace()}&eid=place">${user.getPlace() }</a></td>
      <td><a href="/delservlet?id=${user.getUserId()}">删除</a></td>
    </tr>
  </c:forEach>
</table>


</body>
</html>

3、修改界面

<%--
  Created by IntelliJ IDEA.
  User: Jason_Cai
  Date: 2016/11/28
  Time: 20:47
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>修改</title>
</head>

<body>
<form action = "/modservlet" method = post>
    <input type="text" name="change" />
    <input type="submit" value="确定"/>
</form>
<form action = "/userservlet" method = post>
    <input type="submit" value="返回">
</form>
</body>
</html>

4、web.xml
servlet类的注册和mapping

<%--
  Created by IntelliJ IDEA.
  User: Jason_Cai
  Date: 2016/11/28
  Time: 20:47
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>修改</title>
</head>

<body>
<form action = "/modservlet" method = post>
    <input type="text" name="change" />
    <input type="submit" value="确定"/>
</form>
<form action = "/userservlet" method = post>
    <input type="submit" value="返回">
</form>
</body>
</html>

最后提醒下大家，由于每个人使用的数据库不一样，我这是按照我写的数据库来写的，如果移植到大家的电脑上的话可能需要改jsp文件和java文件里的东西，有不懂的欢迎私信讨论。

Sept_C

关注

13
点赞
踩
72

收藏

觉得还不错? 一键收藏
2
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录