JDBC在Servlet中的使用

Servlet

Servlet是使用应用程序设计接口(API)相关类和方法的Java程序,是位于web服务器的服务器端的Java应用程序。

HTML通过Servlet操作数据库

1.先熟悉一下Servlet的使用

首先IDEA建立一个工程…
结合下面几条注意事项,按照https://www.cnblogs.com/liusong-funtime/p/9144275.html操作即可

  1. 如果在选择Tomcat-> Local右边没有出现配置信息,问题可能是你的IDEA有汉化包,需要将汉化包去掉,汉化包文件在IDEA安装路径下/lib/resource-cn.jar,删除重启IDEA即可。
  2. 如果需要编译Servlet的java类前包含包,如com.cn.userServlet,则web.xml配置文件的< servlet-class >里需要加上包com.cn.userServlet.AServlet
  3. 运行如果出现端口被占用,则可以更改Tomcat的配置,Edit configuration–>将HTTP port改为任意端口,如8078,再运行
  4. 在浏览器打开localhost:8080/web/Hello,其中8080为HTTP port设置的端口,/web为参考博客的第四步中的Application context里的内容,/Hello是web.xml中< url-pattern>里的内容,都可以自己设定。
  5. 如果在浏览器打开localhost:8080/web,得到的是index.jsp文件的内容。
  6. 记得tomcat/lib/里要有mysql-connector.jar那个文件

可以添加一个Servlet例子:

package com.cn.userServlet;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class UserServlet extends HttpServlet {
    public void destroy(){
        super.destroy();
    }
    public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException{
        String username = request.getParameter("usename");
        if(username == null || username == ""){
            username="No User";
        }
        response.setContentType("text/html");
        response.setCharacterEncoding("utf-8");
        PrintWriter out = response.getWriter();
        out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
        out.println("<HTML>");
        out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");
        out.println(" <BODY>");
        out.print("  这里是Servlet所在的包和类: ");
        out.print(this.getClass());
        out.println(this.getClass());
        out.println("<br>");
        out.println("使用了doPost方法");
        out.println("<br>");
        out.println("用户名为:"+username);
        out.println(" </BODY>");
        out.println("</HTML>");
        out.flush();
        out.close();
    }
    public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException{
        response.setContentType("text/html,charset=utf-8");
        PrintWriter out = response.getWriter();
        out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
        out.println("<HTML>");
        out.println(" <HEAD><TITLE>a Servlet</TITLE></HEAD>");
        out.println(" <BODY>");
        out.println("    <h1>This is my first Servlet</h1>");
        out.println("    <br>");
        out.print("    This is");
        out.print(this.getClass());
        out.println(",using the POST method");
        out.println(" </BODY>");
        out.println("<HTML>");
        out.flush();
        out.close();
    }
    public void init()
        throws ServletException{

    }
}

因为前面有包“com.cn.userServlet”,所以对于web.xml的配置文件进行修改,需在< servlet-class >添加com.cn.userServlet。

2.向数据库插入数据

在web(记住是web目录)下新建一个hello.html文件,在这里插入图片描述
添加以下内容:


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <title>
        add.html
    </title>
    <meta http-equiv="content-type" content="text/html;charset=gb2312">
</head>
<body>
<form action="AddServlet"method="post">
    <lable>请输入部门信息:</lable><br><br>
    <lable>部门号:</lable><br>
    <input type="text" name="id"><br>
    <label>部门名:</label><br>
    <input type="text" name="name"><br>
    <label>地址:</label><br>
    <input type="text" name="address"><br>
    <label>部门人数:</label><br>
    <input type="text" name="number"><br><br>
    <input type="submit" value="提交">
</form>
</body>
</html>



效果图展示(webproject_war_exploded是我Application context里设定的):
在这里插入图片描述

添加数据

在src添加新文件AddServlet.java

package com.cn.add;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;

public class AddServlet extends HttpServlet {
    public void destroy(){
        super.destroy();
    }
    public void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException{
        response.setContentType("text/html;charset=gb2312");
        PrintWriter out = response.getWriter();
        this.doPost(request,response);
        out.flush();
        out.close();
    }
    public void doPost(HttpServletRequest request,HttpServletResponse response)
    throws ServletException,IOException{
        System.out.println("到了Servlet!!!");
        response.setContentType("text/html;charset=gb2312");
        request.setCharacterEncoding("gb2312");
        PrintWriter out=response.getWriter();
        String id=request.getParameter("id");
        String name=request.getParameter("name");
        String address = request.getParameter("address");
        int num = Integer.parseInt(request.getParameter("number"));
        Connection conn = null;
        PreparedStatement pstmt = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("创建驱动成功!");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC","root","********");
            System.out.println("创建数据库成功!");
            String sql = "INSERT INTO bank(id,name,address,num)VALUES(?,?,?,?)";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,id);
            pstmt.setString(2,name);
            pstmt.setString(3,address);
            pstmt.setInt(4,num);
            int result = pstmt.executeUpdate();
            if(result==1){
                out.print("插入数据成功!");
            }else
            {
                out.print("插入数据失败!请重新插入");
            }

    }catch(Exception e){
            out.println("无法连接数据库!请检查数据库连接是否正确!");
        }
        out.flush();
        out.close();
    }
    public void init()throws ServletException{

    }

}

配置依赖:https://www.cnblogs.com/Ran-Chen/p/9646187.html(到使用JDBC操作数据库前为止)

同时更改web.xml配置文件改为与AddServlet相关的,因为你需要运行的是这个文件,运行,在浏览器输入localhost:8080/web/hello.html,填完信息后提交,会转到localhost:8080/web/AddServlet,出现成功就说明插入数据成功了。

删除数据

package com.cn.delete;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class DeleteByIdServlet extends HttpServlet {

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

        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        this.doPost(request, response);
        out.flush();
        out.close();
    }
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        response.setContentType("text/html;charset=gb2312") ;
        request.setCharacterEncoding("gb2312");
        PrintWriter out = response.getWriter ();
        String id = request.getParameter("id");
        // 连接数据库
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        try {
            //连接到MySQL数据库中的bank数据库模式
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("创建驱动成功!");
            //连接数据库
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC", "root", "********");
            System.out.println("连接数据库成功!");
            String sql = "delete from bank where id = ?";//删除数据的SQL语句
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            pstmt.executeUpdate();//执行修改
            System.out.println("删除成功!");
            // 显示结果信息
            out.println("<html><head><title>"
                    + "删除部门表数据</title></head>" + "<body>");
            out.println("<h1>删除部门表数据成功!</h1>");
            out.print ("</body></html>");
        } catch (Exception e) {}
        out.flush();
        out.close();
    }

}

查询数据

package com.cn.query;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class QueryByIdServlet extends HttpServlet {


    public void destroy() {
        super.destroy(); // Just puts "destroy" string in log
        // Put your code here
    }

    public void doGet(HttpServletRequest request,HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=gb2312") ;
        request.setCharacterEncoding("gb2312");
        PrintWriter out = response.getWriter ();
        String id = request.getParameter("id");	// 获取部门编号
        Connection conn = null;//声明一个Connection对象,用来连接数据库
        PreparedStatement pstmt = null;//声明PreparedStatement对象
        ResultSet rs = null;//声明一个结果集
        try{
            //连接到MySQL数据库中的bank模式
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("创建驱动成功");
            //连接数据库
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC","root","********");
            System.out.println("连接数据库成功");
            String sql = "select * from bank where id=?";//查询的SQL语句
            pstmt = conn. prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
        }catch(Exception e){}
        try {
            while(rs.next()){//在页面中打印出查询消息
                out.print("部门编号: "+rs.getString(1)+"\n");
                out.print("部门名称: "+rs.getString(2)+"\n");
                out.print("部门地址 : "+rs.getString(3)+"\n");
                out.print("部门人数: "+rs.getString(4)+"\n");

            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        out.flush();
        out.close();
    }
    public void doPost(HttpServletRequest request,HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=gb2312") ;
        request.setCharacterEncoding("gb2312");
        PrintWriter out = response.getWriter ();
        this.doGet(request, response);//调用doGet方法
        out.flush();
        out.close();
    }
    public void init() throws ServletException {
        // Put your code here
    }

}


修改数据

package com.cn.update;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class QueryToUpdateServlet extends HttpServlet {
    public void doGet(HttpServletRequest request,HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=gb2312") ;
        request.setCharacterEncoding("gb2312");
        PrintWriter out = response.getWriter ();
        String id = request.getParameter("id");	// 获取部门编号
        Connection conn = null;//声明一个Connection对象,用来连接数据库
        PreparedStatement pstmt = null;//声明PreparedStatement
        ResultSet rs = null;//声明一个结果集
        try{
            //连接到MySQL数据库中的bank的数据库模式
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("创建驱动成功!");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC", "root", "********");
            System.out.println("连接数据库成功!");
            String sql = "select * from bank where id=?";//
            pstmt = conn. prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
        }catch(Exception e){}
        // 显示单个部门信息
        out.println("<html>"
                + "<head><title>显示单个部门信息</title></head>"
                + "<body>");
        out.println("<h1>显示单个部门信息</h1><br><br>");
        out.print("<form action='UpdateDeptServlet' method='post'>");
        try {
            while(rs.next()){//在页面中打印出查询消息
                out.println("部门编号: ");
                out.print("<br>");
                // 在文本框中显示部门编号,设置成只读
                out.println("<input type='text' name='id' readonly='true' value=");
                out.println(rs.getString(1).toString());
                out.print(">");
                out.print("<br>");
                out.println("部门名称 ");
                out.print("<br>");
                // 在文本框中显示部门名称
                out.println("<input type='text' name='name' value=");
                out.println(rs.getString(2).toString());
                out.println(">");
                out.print("<br>");
                out.println("部门地址:");
                out.print("<br>");
                // 在文本框中显示部门地址
                out.println("<input type='text' name='address' value=");
                out.println(rs.getString(3).toString());
                out.print(">");
                out.print("<br>");
                out.println("部门人数 ");
                out.print("<br>");
                // 在文本框中显示部门人数
                out.println("<input type='text' name='num' value=");
                out.println(rs.getString(4).toString());
                out.println(">");
                out.print("<br>");
                //
                out.print("<input type='submit' value='Submit'>");
                out.print("</form>");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        out.flush();
        out.close();
    }
    public void doPost(HttpServletRequest request,HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=gb2312") ;
        request.setCharacterEncoding("gb2312");
        PrintWriter out = response.getWriter ();
        this.doGet(request, response);//����doGet����
        out.flush();
        out.close();
    }
}

package com.cn.update;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class UpdateDeptServlet extends HttpServlet {

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

        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        this.doPost(request, response);
        out.flush();
        out.close();
    }

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

        response.setContentType("text/html;charset=gb2312") ;
        request.setCharacterEncoding("gb2312");
        PrintWriter out = response.getWriter ();
        Connection conn = null;//声明一个Connection对象,原用来连接数据库。
        PreparedStatement pstmt = null;//声明PreparedStatement对象,用来向数据库插入数据条数据
        ResultSet rs = null;//声明一个结果集
        Statement stmt = null;
        String id = request.getParameter("id");
        String name = request.getParameter("name");
        String address = request.getParameter("address");
        int num = Integer.parseInt(request.getParameter("num"));

        try{
            //	连接到MySQL数据库中的bank数据库模式
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("创建驱动成功!");
            //连接数据库
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC","root","********");
            System.out.println("连接数据库成功!");
            //修改的SQL语句
            String sql = "update bank set id=?,name=?,address=?,num=? where id=?";
            pstmt = conn. prepareStatement(sql);
            //下面是设置修改的数据值
            pstmt.setString(1, id);
            pstmt.setString(2, name);
            pstmt.setString(3, address);
            pstmt.setInt(4, num);
            pstmt.setString(5, id);
            pstmt.executeUpdate();//执行修改
            System.out.println("修改成功!");
            /*
             * 添加成功以后,显示出全部信息
             */
            stmt = conn.createStatement() ;
            rs = stmt.executeQuery("SELECT * FROM bank");//查询数据
            //在页面中显示表中的所有信息
            out.println(
                    "<html>" +
                            "<head><title>部门表信息</title></head>" +
                            "<body>");
            out.println("<h1>部门表信息:</h1><br><br>");
            //循环遍历输出查询结果
            while(rs.next()){
                out.print("部门编号: ");
                out.print(rs.getString(1)+"\t");
                out.print("部门名称: ");
                out.print(rs.getString(2)+"\t");
                out.print("部门地址 : ");
                out.print(rs.getString(3)+"\t");
                out.print("部门人数: ");
                out.print(rs.getInt(4)+"\t");


                out.println("<br>");
            }
            out.print ("</body></html>");
            out.close();
        }catch(Exception e){}
        out.flush();
        out.close();
    }

}

  • 5
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值