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

来源：互联网发布：php 数组简写形式编辑：程序博客网时间：2024/04/29 08:43

因为数据库课程老师要求使用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文件里的东西，有不懂的欢迎私信讨论。

0 0