servlet查询数据库信息并输出在前端页面

BaseDao层封装的一些功能方法的实现

package com.cqy.dao;

import com.cqy.util.JdbcUtil;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 基础数据操作
 */
public class BaseDao {
    //查询方法
    public <T> List<T> query(Class<T> tClass, String sql, Object... params) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList();
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.prepareStatement(sql);
            setParams(statement, params);
            //执行查询
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            while (resultSet.next()) {
                //获取构造方法对象
                Constructor<T> constructor = tClass.getConstructor();
                //创建实体类对象
                T t = constructor.newInstance();
                //获取查询结果列的数量  行数据装换成JAVA对象
                int len = metaData.getColumnCount();

                for (int i = 0; i < len; i++) {
                    //获取列名称  索引从1开始
                    String colName = metaData.getColumnName(i + 1);
                    //获取属性
                    Field field = tClass.getDeclaredField(colName);
                    //修改属性访问权限
                    field.setAccessible(true);
                    //从查询结果集取数据  设置给filed对象的数据
                    field.set(t, resultSet.getObject(i + 1));
                }
                //添加到list集合
                list.add(t);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.release(null, statement, connection);
        }
        return list;
    }


    //增删改的方法
    public boolean execute(String sql, Object... params) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.prepareStatement(sql);
            setParams(statement, params);
            return statement.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.release(null, statement, connection);
        }
        return false;
    }


    //绑定参数
    private void setParams(PreparedStatement statement, Object[] params) throws SQLException {
        if (params != null && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                statement.setObject(i + 1, params[i]);
            }
        }
    }

}

EmployeeDao 向BaseDao传入参数

package com.cqy.dao;

import com.cqy.entity.Employee;

import java.util.List;

public class EmployeeDao extends BaseDao{

    public boolean saveEmployee(Employee employee){
        String sql="insert into emps(name,sex,birthday,phone,salary,job) values(?,?,?,?,?,?)";
        return execute(sql,employee.getname(),employee.getSex(),employee.getBirthday(),employee.getPhone(),employee.getSalary(),employee.getJob());
    }

    public List<Employee> findAll(){
        String sql = "select id,name,sex,birthday,phone,salary,job from emps";
        return query(Employee.class,sql);
    }

}

servlet

package com.cqy.servlet;

import com.cqy.dao.EmployeeDao;
import com.cqy.entity.Employee;

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.io.PrintWriter;
import java.util.List;

// url映射  http请求调用的服务名称
@WebServlet("/hello")
public class Servlet extends HttpServlet {
    //依赖dao对象
    EmployeeDao employeeDao=new EmployeeDao();

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("text/html;charset=UTF-8");
        //获取dao的返回集合
        List<Employee> list=employeeDao.findAll();

        PrintWriter out= resp.getWriter();
        out.append("<ul>");
        for (int i = 0; i < list.size(); i++) {
            out.append("<li>" + list.get(i) + "</li>");
        }
        out.append("</ul>");
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}

实体类

package com.cqy.entity;

import java.sql.Date;

/**
 * 实体类    关系模型 到 对象模型 转换
 */
public class Employee {
    private Integer id;
    private String name;
    private String sex;
    private Date birthday;
    private String phone;
    private Integer salary;
    private String job;

    public Employee() {
    }

    public Employee(Integer id, String name, String sex, Date birthday, String phone, Integer salary, String job) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.birthday = birthday;
        this.phone = phone;
        this.salary = salary;
        this.job = job;
    }

    public Integer getid() {
        return id;
    }

    public void setid(Integer id) {
        this.id = id;
    }

    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 Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public Integer getSalary() {
        return salary;
    }

    public void setSalary(Integer salary) {
        this.salary = salary;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                ", phone='" + phone + '\'' +
                ", salary=" + salary +
                ", job='" + job + '\'' +
                '}';
    }
}

工具类JdbcUtil

package com.cqy.util;

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;
import java.io.FileInputStream;
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 JdbcUtil {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    private static DataSource source;

    //静态代码块 初始化数据库连接池对象  只加载一次
    static {
        Properties prop = new Properties();
        try {
            prop.load(new FileInputStream("D:\\IdeaProjects\\web2112\\day1206\\src\\com\\cqy\\jdbc.properties"));
            driver = prop.getProperty("jdbc.driver");
            url = prop.getProperty("jdbc.url");
            username = prop.getProperty("jdbc.username");
            password = prop.getProperty("jdbc.password");
            System.out.println("driver+"+driver);
            //创建数据库连接池对象
            DruidDataSource ds=new DruidDataSource();
            ds.setDriverClassName(driver);
            ds.setUrl(url);
            ds.setUsername(username);
            ds.setPassword(password);
            source=ds;
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //获取数据库连接对象
    public static Connection getConnection(){
        try {
            return source.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public static void release(ResultSet resultSet, Statement statement,Connection connection){
        try {
            if(resultSet!=null){
                resultSet.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if(statement != null){
                statement.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if(connection != null){
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void main(String[] args) {
        new JdbcUtil();
    }

}

输出效果