java【tomcat+servlet+jsp+jstl+mysql】实现用户表全查询案例

需求：java【tomcat+servlet+jsp+jstl+mysql】实现用户表全查询案例

---

需求页面如下：

思路：

1.环境准备
    mysql : jar包, 数据库表和数据.
    c3p0  : jar , c3p0-config.xml
    jstl  : 导入两个jar包
    包结构:  com.itheima.web   com.itheima.service   com.itheima.dao   com.itheima.utils    com.itheima.pojo
    工具类: C3P0Utils工具类
    静态资源: jsp   css  font  js文件

初始项目工程目录如下：

数据库语句：

CREATE DATABASE jstl_query DEFAULT CHARSET 'utf8';
USE jstl_query;
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) ,
	sex VARCHAR(10),
	age INT,
	address VARCHAR(100),
	qq VARCHAR(100),
	email VARCHAR(50)
);
INSERT INTO USER VALUE(NULL,'洪七公','男',65,'北京',110110110,'hongqigong@itcast.cn');
INSERT INTO USER VALUE(NULL,'欧阳锋','男',55,'上海',210110110,'ouyangfeng@itcast.cn');
INSERT INTO USER VALUE(NULL,'梅超风','女',35,'南京',310110110,'meichaofeng@itcast.cn');
INSERT INTO USER VALUE(NULL,'黄药师','男',68,'广州',410110110,'huangyaoshi@itcast.cn');
SELECT * FROM USER;

数据库表结构如下:

2.编写pojo包下User类 + C3P0Utils工具类

pojo包下User类代码如下：

package com.it.pojo;

public class User {
    private int uid;
    private String name;
    private String sex;
    private int age;
    private String address;
    private String qq;
    private String email;

    public User() {
    }

    public User(int uid, String name, String sex, int age, String address, String qq, String email) {
        this.uid = uid;
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.address = address;
        this.qq = qq;
        this.email = email;
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getQq() {
        return qq;
    }

    public void setQq(String qq) {
        this.qq = qq;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                ", address='" + address + '\'' +
                ", qq='" + qq + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

C3P0Utils工具类代码如下:

package com.it.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 1.定义一个成员对象,作为静态连接池对象
 * 2.提供一个静态的方法获取连接池对象
 * 3.提供一个静态的方法获取Connection连接对象
 * 4.提供一个静态的方法关闭资源
 */
public class C3P0Utils {

    //1.定义一个成员对象,作为静态连接池对象
    private static final DataSource dataSource = new ComboPooledDataSource();

    //2.提供一个静态的方法获取连接池对象
    public static DataSource getDataSource(){
        return dataSource;
    }

    //3.提供一个静态的方法获取Connection连接对象
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 4.提供一个静态的方法关闭资源
    public static void closeResource(Connection connection, Statement st , ResultSet resultSet){
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(st != null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

3.编写Dao层

UserDao接口代码如下：

package com.it.dao;


import com.it.pojo.User;

import java.sql.SQLException;
import java.util.List;

public interface UserDao {
    public List<User> queryUser() throws SQLException;
}

UserDaoImpl类代码如下：

package com.it.dao;
import com.it.pojo.User;
import com.it.utils.C3P0Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {

    @Override
    public List<User> queryUser() throws SQLException {
        //1.获取连接
        Connection connection = C3P0Utils.getConnection();
        //2.获取预处理语句执行对象,编译sql
        PreparedStatement pst = connection.prepareStatement("SELECT * FROM USER");
        //3.执行查询方法  executeQuery()  === > ResultSet
        ResultSet resultSet = pst.executeQuery();
        //4.创建List<User> 目的: 在循环中,添加user对象
        //5.循环结果集
        List list = new ArrayList();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String sex = resultSet.getString("sex");
            int age = resultSet.getInt("age");
            String address = resultSet.getString("address");
            String qq = resultSet.getString("qq");
            String email = resultSet.getString("email");
            User user = new User(id,name,sex,age,address,qq,email);
            list.add(user);
        }
        //6.释放资源
        C3P0Utils.closeResource(connection,pst,resultSet);
        //7.返回list结果
        return list;
    }

   /* @Test
    public void test() throws SQLException {
        List list = queryUser();
        System.out.println(list);
    }*/
}

@Test 单元测试，结果如下：

测试通过！

4.编写Service

UserService接口代码如下：

package com.it.service;

import com.it.pojo.User;

import java.sql.SQLException;
import java.util.List;

public interface UserService {
    public List<User> findUser() throws SQLException;
}

UserServiceImpl类代码如下：

package com.it.service;

import com.it.dao.UserDao;
import com.it.dao.UserDaoImpl;
import com.it.pojo.User;

import java.sql.SQLException;
import java.util.List;

public class UserServiceImpl implements UserService {
    @Override
    public List<User> findUser() throws SQLException {
        UserDao userDao = new UserDaoImpl();
        List<User> list = userDao.queryUser();
        return list;
    }
}

5.编写Web

QueryAllServlet类代码如下：

package com.it.web;

import com.it.pojo.User;
import com.it.service.UserService;
import com.it.service.UserServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

@WebServlet(name = "QueryAllServlet")
public class QueryAllServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            //1.设置编码
            request.setCharacterEncoding("utf-8");
            response.setContentType("text/html;charset=utf-8");
            //2.创建UserService
            UserService userService = new UserServiceImpl();
            //3.调用findUsers 得到一个list
            List<User> list = userService.findUser();
            //4.把list放到request域中
            request.setAttribute("list",list);
            //5.转发到show.jsp
            request.getRequestDispatcher("show.jsp").forward(request,response);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

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

    }
}

6.编写jsp页面(show.jsp)

<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <link href="css/bootstrap.css" rel="stylesheet">
        <script src="js/jquery-1.11.3.js"></script>
        <script src="js/bootstrap.js"></script>
        <style>
            tr th{
                text-align: center;
                background-color: #e3e3e3;
            }
        </style>
    </head>
<body>

    <table class="table table-bordered table-hover">
        <caption><h1><center>用户信息</center></h1></caption>
        <tr class="text-center">
            <th>编号</th>
            <th>姓名</th>
            <th>性别</th>
            <th>年龄</th>
            <th>籍贯</th>
            <th>qq</th>
            <th>邮箱</th>
            <th>操作</th>
        </tr>
        <c:forEach items="${list}" var="user">
            <tr class="text-center">
                <td>${user.uid}</td>
                <td>${user.name}</td>
                <td>${user.sex}</td>
                <td>${user.age}</td>
                <td>${user.address}</td>
                <td>${user.qq}</td>
                <td>${user.email}</td>
            </tr>
        </c:forEach>
    </table>

</body>
</html>

7.访问页面：

    http://localhost:8080/jstl_query/QueryAllServlet

---

信息如下：

目标完成！

总结

最终项目工程目录如下：