JavaWeb用户信息查询

信息查询"/>

JavaWeb用户信息查询

创建数据库

创建数据库
CREATE DATABASE personinfo;使用数据库
USE personinfo;创建user表
CREATE TABLE USER(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) NOT NULL,gender VARCHAR(5),age INT,address VARCHAR(32),qq VARCHAR(20),email VARCHAR(50)
);

UserDao

import abc.domain.User;
import java.util.List;
/*** 用户操作的DAO*/
public interface UserDao {public List<User> findAll();
}

UserDao实现类

import abc.dao.UserDao;
import abc.domain.User;
import abc.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;import java.util.List;public class UserDaoImpl implements UserDao {private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDs());@Overridepublic List<User> findAll() {//使用JDBC操作数据库//1.定义sqlString sql = "select * from user";List<User> users = template.query(sql, new BeanPropertyRowMapper<User>(User.class));return null;}
}

domain下的User

public class User {private int id;private String name;private String gender;private int age;private  String address;private String qq;private String email;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}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;}@Overridepublic String toString() {return "User{" +"id=" + id +", name='" + name + '\'' +", gender='" + gender + '\'' +", age=" + age +", address='" + address + '\'' +", qq='" + qq + '\'' +", email='" + email + '\'' +'}';}
}

UserService

import abc.domain.User;
import java.util.List;/*** 用户管理的业务接口*/
public interface UserService {/*** 查询所有用户信息* @return*/public List<User> findAll();
}

UserService实现类

import abc.dao.UserDao;
import abc.dao.impl.UserDaoImpl;
import abc.domain.User;
import abc.service.UserService;import java.util.List;public class UserServieImpl implements UserService {private UserDao dao = new UserDaoImpl();@Overridepublic List<User> findAll() {//调用Dao完成查询return dao.findAll();}
}

JDBC工具类

import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;public class JDBCUtils {//1定义成员变量DataSourceprivate static DataSource ds;static {try {//1.加载配置文件Properties pro = new Properties();pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));//2.获DataSourceds = DruidDataSourceFactory.createDataSource(pro);} catch (IOException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();}}//获取连接public static Connection getConnetion() throws SQLException {return ds.getConnection();}//释放资源public static void close(Statement stmt, Connection conn) {if (stmt != null) {try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}if (conn != null) {try {conn.close();//归还连接} catch (SQLException e) {e.printStackTrace();}}//close(null,stmt,conn);}//释放资源重载public static void close(ResultSet rs, Statement stmt, Connection conn) {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (stmt != null) {try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}if (conn != null) {try {conn.close();//归还连接} catch (SQLException e) {e.printStackTrace();}}}//获取连接池方法public static DataSource getDs() {return ds;}
}

Servlet

import abc.domain.User;
import abc.service.UserService;
import abc.service.impl.UserServieImpl;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.util.List;@WebServlet("/UserListServlet")
public class UserListServlet extends HttpServlet {protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {//1.调用UserService完成查询UserService service = new UserServieImpl();List<User> users = service.findAll();//2.将list存入request域request.setAttribute("users",users);//3.转发到list.jsprequest.getRequestDispatcher("/list.jsp").forward(request,response);}protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {this.doPost(request, response);}
}

druid.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///personinfo
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000

JSP

index.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %><!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>Title</title>
</head><body><h1>test hello world</h1><a href="${pageContext.request.contextPath}/UserListServlet">查询所有用户</a>
</body>
</html>

list.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="" %><html>
<head><title>jstltest</title>
</head>
<body><h3>用户信息列表</h3><table border="1" class="table table-bordered table-hover"><tr><th>编号</th><th>姓名</th><th>性别</th><th>年龄</th><th>籍贯</th><th>QQ</th><th>邮箱</th><th>操作</th></tr><c:forEach items="${users}" var="user" varStatus="s"><tr><td>${s.count}</td><td>${user.name}</td><td>${user.gender}</td><td>${user.age}</td><td>${user.address}</td><td>${user.qq}</td><td>${user.email}</td></tr></c:forEach></table>
</body>
</html>