JDBC连接数据库（Servlet+JSP）

　　JDBC（Java Database connectivity）,是连接数据库的一种方式。后面的框架Mybatis和Hibernate等都封装的是JDBC。在JDBC中常用的API有4个：DriverManager、Connection、Statement、ResultSet。

代码演示：

　　首先要导入jar包: jstl_el、jstl-1.2_1、mysql-5.1.10

User.java

 1 public class User {
 2     private int id;
 3     private String age;
 4     private String name;
 5 
 6     public int getId() {
 7         return id;
 8     }
 9 
10     public void setId(int id) {
11         this.id = id;
12     }
13 
14     public String getAge() {
15         return age;
16     }
17 
18     public void setAge(String age) {
19         this.age = age;
20     }
21 
22     public String getName() {
23         return name;
24     }
25 
26     public void setName(String name) {
27         this.name = name;
28     }
29 }

QueryServlet.java

  1 package cn.woo.servlet;
  2 
  3 import java.io.IOException;
  4 import java.sql.ResultSet;
  5 import java.sql.SQLException;
  6 import java.util.ArrayList;
  7 import java.util.List;
  8 
  9 import javax.servlet.ServletException;
 10 import javax.servlet.http.HttpServlet;
 11 import javax.servlet.http.HttpServletRequest;
 12 import javax.servlet.http.HttpServletResponse;
 13 
 14 import cn.woo.entity.User;
 15 import cn.woo.util.DBUtil;
 16 
 17 /**
 18  * 类说明：接收请求的Servlet
 19  * 
 20  * @author wyh
 21  * @version 创建时间：2018年8月23日 下午2:46:29
 22  */
 23 public class QueryServlet extends HttpServlet {
 24 
 25     /**
 26      * 
 27      */
 28     private static final long serialVersionUID = 1L;
 29 
 30     @Override
 31     protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
 32         doPost(req, resp);
 33     }
 34 
 35     @Override
 36     protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
 37         
 38         req.setCharacterEncoding("utf-8");
 39         resp.setCharacterEncoding("utf-8");
 40         
 41         String action = req.getParameter("action");
 42         List<User> userList = new ArrayList<>();
 43         
 44         switch (action) {
 45         case "getUserList":
 46             try {
 47                 ResultSet resultSet = DBUtil.query("select * from user");
 48                 // 遍历结果集【一行行数据读取】，将结果集放置到实体对象中
 49                 while (resultSet.next()) {
 50                     User user = new User();
 51                     user.setId(resultSet.getInt("id"));
 52                     user.setAge(String.valueOf(resultSet.getInt("age")));
 53                     user.setName(resultSet.getString("name"));
 54                     userList.add(user);
 55                 }
 56                 req.setAttribute("userList",userList);
 57                 // 跳转至展示页面
 58                 req.getRequestDispatcher("../UserList.jsp").forward(req, resp);
 59             } catch (SQLException e) {
 60                 e.printStackTrace();
 61             }finally {
 62                 // 释放资源
 63                 DBUtil.closeResource();
 64             }
 65             break;
 66         case "deleteUserInfo":
 67             try {
 68                 String id = req.getParameter("id");
 69                 int delResult = DBUtil.insertOrDeleteOrUpdate("delete from user where id='"+id+"'");
 70                 if(delResult>0) {
 71                     System.out.println("删除成功");
 72                 }
 73             } catch (SQLException e) {
 74                 e.printStackTrace();
 75             }
 76             break;
 77         case "updateUserName":
 78             try {
 79                 String id = req.getParameter("id");
 80                 String name = req.getParameter("name");
 81                 int updateResult = DBUtil.insertOrDeleteOrUpdate("update user set name='"+name+"' where id='"+id+"'");
 82                 if(updateResult>0) {
 83                     System.out.println("更新用户名成功");
 84                 }
 85             } catch (SQLException e) {
 86                 e.printStackTrace();
 87             }
 88             break;
 89         case "insertUserInfo":
 90             try {
 91                 String addAge = req.getParameter("age");
 92                 String addName = req.getParameter("name");
 93                 int addResult = DBUtil.insertOrDeleteOrUpdate("insert into user(age,name) value('"+addAge+"','"+addName+"')");
 94                 if(addResult>0) {
 95                     System.out.println("新增用户成功");
 96                 }
 97             } catch (SQLException e) {
 98                 e.printStackTrace();
 99             }
100             break;
101         default:
102             break;
103         }
104     }
105 }

UserList.jsp

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
 4 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 5 <html>
 6 <head>
 7 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 8 <title>用户信息列表</title>
 9 <style type="text/css">
10     *{
11         padding: 0;
12         margin: 0;
13     }
14     
15     table{
16         margin: 300px auto;
17     }
18     
19     td{
20         100px;
21         text-align: center;
22     }
23 </style>
24 </head>
25 <body>
26     <table border="1" cellpadding="0" cellspacing="0">
27         <tr>
28             <td>序号</td>
29             <td>工号</td>
30             <td>年龄</td>
31             <td>姓名</td>
32         </tr>
33         <c:forEach items="${ userList }" var="user" varStatus="status">
34             <tr>
35                 <td>${ status.index+1 }</td>
36                 <td>${ user.id }</td>
37                 <td>${ user.age }</td>
38                 <td>${ user.name }</td>
39             </tr>
40         </c:forEach>
41     </table>
42 </body>
43 </html>

DBUtil.java

 1 package cn.woo.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 import java.sql.Statement;
 8 
 9 /**
10 * 类说明：数据库连接工具类
11 * @author wyh
12 * @version 创建时间：2018年8月23日 下午3:58:55
13 */
14 public class DBUtil {
15     
16     private static Connection connection;
17     private static Statement statement;
18     
19     static {
20         try {
21             DriverManager.registerDriver(new com.mysql.jdbc.Driver());
22             // 获取连接 【并设置编码方式】
23             connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/woo?unicode=true&characterEncoding=UTF-8","root","root");
24         } catch (SQLException e) {
25             e.printStackTrace();
26         }
27     }
28     
29     public static Statement getStatement() throws SQLException {
30         // 获取statement 【用于执行SQL语句】
31         statement = connection.createStatement();
32         return statement;
33     }
34     
35     /**
36      * 查
37      * @param sql 执行查询的sql语句
38       * @return resultSet 返回的结果集
39      * @throws SQLException 
40      */
41     public static ResultSet query(String sql) throws SQLException {
42         return getStatement().executeQuery(sql);
43     }
44     
45     /**
46      * 增/删/改 通用方法
47      * @param sql 要执行的SQL语句
48       * @return 执行结果 >0表明执行成功
49      * @throws SQLException 
50      */
51     public static int insertOrDeleteOrUpdate(String sql) throws SQLException {
52         return getStatement().executeUpdate(sql);
53     }
54     
55     
56     /**
57      * 释放资源
58      */
59     public static void closeResource() {
60         try {
61             if(statement!=null) {
62                 statement.close();
63             }
64             if(connection!=null) {
65                 connection.close();
66             }
67         }catch(Exception e) {
68             e.printStackTrace();
69         }
70     }
71 }