Java Web JDBC连接数据库基础总结(一)

最新推荐文章于 2023-02-17 23:55:31 发布

原创最新推荐文章于 2023-02-17 23:55:31 发布 · 1.1k 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #java web #jdbc

Java Web 专栏收录该内容

30 篇文章

订阅专栏

本文介绍如何使用JDBC连接MySQL数据库，并通过JSP页面展示数据表中的数据。包括数据库连接配置、执行SQL查询及结果展示等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

(一)JDBC连接数据库

1.导入mysql-connector-java-5.0.4-bin.jar

2.创建数据表

CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(12) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(12) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `description` text,
  `create_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2015419034 DEFAULT CHARSET=utf8;
INSERT INTO `person` VALUES ('20154990', '范冰冰', '35', '女', '2017-11-22', '演员', '2017-11-01 18:55:47');
INSERT INTO `person` VALUES ('201541908', '李晨', '40', '男', '2017-10-11', '演员', '2017-11-17 18:56:28');
INSERT INTO `person` VALUES ('2015419028', '赵忠祥', '29', '男', '2017-11-01', '研究员', '2017-10-31 18:58:07');
INSERT INTO `person` VALUES ('2015419033', '李四', '18', '男', '2017-11-09', '博士', '2017-11-13 18:57:09');

3.连接数据库

package com.eshore;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
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 PersonServlet extends HttpServlet {


    private static final long serialVersionUID = 1L;

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

        doPost(request,response);
    }
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");                            //注册数据库
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动程序加载错误");
        }
        try {
            con = DriverManager.
            getConnection("jdbc:mysql://localhost:3306/testWeb","root","root");//获取数据库连接
            st = con.createStatement();                                         //获取Statement
            rs = st.executeQuery("select * from person");                       //执行查询，返回结果集
            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>列出人员信息表</TITLE></HEAD>");
            out.println("  <BODY>");
            out.println("<center><h4>人员信息列表</h4>");
            out.println("  <table border=\"1\" width=\"100%\" cellpadding=\"2\" cellspacing=\"1\">");
            out.println("<tr>");
            out.println("<td>选择</td>");
            out.println("<td>姓名</td>");
            out.println("<td>年龄</td>");
            out.println("<td>性别</td>");
            out.println("<td>生日</td>");
            out.println("<td>备注</td>");
            out.println("</tr>");
            while(rs.next()){                                      //遍历结果集ResultSet
                int id = rs.getInt("id");                          //获取ID
                String name  = rs.getString("name");               //获取姓名
                int age = rs.getInt("age");                        //获取年龄
                String sex  = rs.getString("sex");                 //获取性别
                Date birthday = rs.getDate("birthday");            //获取生日
                String description = rs.getString("description");  //获取备注
                out.println("<tr>");
                out.println("<td><input type=\"checkbox\" name=\"id\" value=\""+id+"\"></td>");
                out.println("<td >"+name+"</td>");
                out.println("<td >"+age+"</td>");    
                out.println("<td >"+sex+"</td>");
                out.println("<td >"+birthday+"</td>");
                out.println("<td >"+description+"</td>");
                out.println("</tr>");
            }
            out.println("</table></center>"); 
            out.println("  </BODY>");
            out.println("</HTML>");
            out.flush();
            out.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try { //记住关闭连接
                rs.close();
                st.close();
                con.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    public void init() throws ServletException {
        // Put your code here
    }

}


----------
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5">
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
  <servlet>
    <description></description>
    <display-name>PersonServlet</display-name>
    <servlet-name>PersonServlet</servlet-name>
    <servlet-class>com.eshore.PersonServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>PersonServlet</servlet-name>
    <url-pattern>/PersonServlet</url-pattern>
  </servlet-mapping>
</web-app>

输出：

这里写图片描述

(二)JSP制作表格并添加数据

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<div id="content">


    <p>读取数据库数据贴到表格上: </p>

    <table border="1" cellpadding="10">

            <tr>
                <th>编号</th>
                <th>姓名</th>
                <th>年龄</th>
                <th>性别</th>
                <th>生日</th>
                <th>备注</th>
            </tr>




            <%
                Class.forName("com.mysql.jdbc.Driver");
                Connection conn = null;
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testWeb", "root", "root");
                Statement stmt = null;
                stmt = conn.createStatement();
                String query = "select * from person";
                ResultSet rs = null;
                rs = stmt.executeQuery(query);
                while(rs.next()){
            %>
            <tr>
                <%
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    int age = rs.getInt("age");
                    String sex = rs.getString("sex");
                    Date birthday = rs.getDate("birthday");
                    String description=rs.getString("description");
                %>
                <td><%=id %></td>
                <td><%=name %></td>
                <td><%=age %></td>
                <td><%=sex %></td>
                <td><%=birthday %></td>
                <td><%=description %></td>
            </tr>               

            <%      
                }
            %>


    </table>
</div>
</body>
</html>

输出：
这里写图片描述