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

来源：互联网发布：安卓5.0 源码编辑：程序博客网时间：2024/06/05 23:44

(一)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>

输出：
这里写图片描述

阅读全文

0 0