需求: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
信息如下:
目标完成!
总结
最终项目工程目录如下: