• java经过jdbc链接数据库并在前端实现增删查改

    2022-01-13
  • 这篇文章主要向大家介绍java经过jdbc链接数据库并在前端实现增删查改,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。
    由于数据库课程老师要求使用Java经过jdbc链接数据库,而且在前端实现增删查改的功能,因此就在网上找了个模板,改了一些,加了一些东西,勉强能用,不足的地方还请你们多多指教。
    网上那个模板彷佛不能在网上显示数据库的数据,是由于几个servlet类没有收到index.jsp的get/post请求,加了几个按钮,目前基本能使用。接下来就把我完成的东西分享出来给你们。
    


    package javadatabase;
    css

    一、DBConnection类
    主要是用来加载驱动,登陆数据库啥的。html

    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();
    }

    二、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;
    }

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

    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;

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

    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;
    
    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); }

    二、AddServlet.java
    执行点击添加按钮以后的功能web

    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);
    }

    三、DelServlet.java
    执行点击删除功能后的一系列操做。sql

    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 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); }

    四、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文件
    一、添加数据的界面app

     %@ 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 
    

    二、开始界面jsp

     %@ 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 
    
    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
    /html
    最后提醒下你们,因为每一个人使用的数据库不同,我这是按照我写的数据库来写的,若是移植到你们的电脑上的话可能须要改jsp文件和java文件里的东西,有不懂的欢迎私信讨论。