DBUtil, DAO & POJO

文件结构：

DBUtil:

package utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * 工具类：
 * 静态代码块：连接登入数据库
 * getConnection():加载并注册数据库驱动，返回数据库连接
 * closeConnection():关闭连接
 * set and get 方法，设置或返回各属性值
 * @author ZZG_干干
 *
 */

public class DBUtil {
    // 成员变量
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;
    
    // 静态代码块-配置文件
    static {
        // DBUtil 和 config.properties 同在 utils 包下
        InputStream is = DBUtil.class.getClassLoader().getResourceAsStream("utils/config.properties");
        Properties pro = new Properties();
        try {
            pro.load(is);
            driverClass = pro.getProperty("driverClass");
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
        } catch (IOException e1) {
            
            e1.printStackTrace();
            System.out.println("配置文件读取失败！");
        }
        
        // 静态代码块-非配置文件
//        static {
//            driverClass = "com.mysql.jdbc.Driver";
//            url = "jdbc:mysql://127.0.0.1:3306/test";
//            user = "root";
//            password = "zzgzzg";
//            try {
//                Class.forName(driverClass);
//            } catch (ClassNotFoundException e) {
//                
//                e.printStackTrace();
//            }
//        }
        
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            
            e.printStackTrace();
        }
    }
    
    // 成员方法-获取连接
    public static Connection getConnection() {
        
        Connection conn = null;
        
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            
            e.printStackTrace();
        }
        return conn;
        
    }
    
    // 关闭连接资源
    public static void closeConnection(Connection conn){
        if(conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}

config.properties:（DBUtil配置文件）

# 数据库相关参数：账号、密码
# 不能有任何多余的空格
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
user=root
password=zzgzzg

DAO:

package DAO;

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

import POJO.Student;
import utils.DBUtil;

public class StudentDao {
    /**
     * 访问数据库
     * 
     * @return 返回一个 Student类数组 Student类，属性：id,sequence,name,sex,birthday
     *         Student类，方法：属性的 set和get方法 StudentDao类： 
     *         1) list():查询 ，返回 ArrayList<Student> 
     *         2) delete(int id):删除 , 返回 boolean型 
     *         3) insert(Student student):插入，返回 boolean型
     * @throws SQLException
     */

    // 增
    public Boolean insert(Student student) {
        Connection conn = DBUtil.getConnection();
        String sql = "insert into student(sequence,name,sex)values(?,?,?)";
        PreparedStatement prep;
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1, student.getSequence());
            prep.setString(2, student.getName());
            prep.setString(3, student.getSex());
            prep.executeUpdate();
            return true;
        } catch (SQLException e) {

            e.printStackTrace();
            return false;
        }

    }

    // 删
    public boolean delete(int id) {
        Connection conn = DBUtil.getConnection();
        String sql = "delete from student where id=?";

        PreparedStatement prep;
        try {
            prep = conn.prepareStatement(sql);
            prep.setInt(1, id);
            prep.executeUpdate();
            return true;
        } catch (SQLException e) {

            e.printStackTrace();
            return false;
        }

    }
    
    // 改
    public boolean update(Student student) {
        Connection conn = DBUtil.getConnection();
        String sql = "update student set sequence=?,name=?,sex=? where id=?";
        PreparedStatement prep;
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1, student.getSequence());
            prep.setString(2, student.getName());
            prep.setString(3, student.getSex());
            prep.setInt(4, student.getId());
            prep.executeUpdate();
            return true;
        } catch (SQLException e) {
            
            e.printStackTrace();
            return false;
        }
        
    }
    

    // 查
    public ArrayList<Student> list() throws SQLException {

        ArrayList<Student> students = new ArrayList<Student>();
        Connection conn = DBUtil.getConnection(); // 创建连接

        // 数据库访问并获取结果
        Statement state = conn.createStatement();
        String sql = "select id,sequence,name,sex,birthday from student";
        ResultSet rs = state.executeQuery(sql);

        // ResultSet.next():指针向后滚动
        while (rs.next()) {
            Student student = new Student();
            student.setId(rs.getInt("id"));
            student.setSequence(rs.getString("sequence"));
            student.setName(rs.getString("name"));
            student.setSex(rs.getString("sex"));
            students.add(student);
        }
        return students;
    }
    
    public Student listOne(int id) {
        
        Student student = new Student();
        Connection conn = DBUtil.getConnection();
        String sql = "select id,sequence,name,sex,birthday from student where id = ?";
        PreparedStatement prep;
        try {
            prep = conn.prepareStatement(sql);
            prep.setInt(1, id);
            ResultSet rs = prep.executeQuery();
            if(rs.next()) {
                student.setId(id);
                student.setSequence(rs.getString("sequence"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
            }
        } catch (SQLException e) {
            
            e.printStackTrace();
            System.out.println("查询执行失败！");
        }
        
        return student;
        
    }

}

POJO:

package POJO;

import java.util.Date;

// 实体类
public class Student {
    
    // 成员变量
    private int id;
    private String sequence;
    private String name;
    private String sex;
    private Date birthday;
    
    // 构造方法
    public Student(){
        
    }
    
    // 重写 toString
    @Override
    public String toString() {
        
        return ("id:"+id+",sequence:"+sequence+",name:"+name+",sex:"+sex+",birthday:"+birthday);
    }

    // get and set
    /**
     * @return the id
     */
    public int getId() {
        return id;
    }
    /**
     * @param id the id to set
     */
    public void setId(int id) {
        this.id = id;
    }
    /**
     * @return the sequence
     */
    public String getSequence() {
        return sequence;
    }
    /**
     * @param sequence the sequence to set
     */
    public void setSequence(String sequence) {
        this.sequence = sequence;
    }
    /**
     * @return the name
     */
    public String getName() {
        return name;
    }
    /**
     * @param name the name to set
     */
    public void setName(String name) {
        this.name = name;
    }
    /**
     * @return the sex
     */
    public String getSex() {
        return sex;
    }
    /**
     * @param sex the sex to set
     */
    public void setSex(String sex) {
        this.sex = sex;
    }
    /**
     * @return the birthday
     */
    public Date getBirthday() {
        return birthday;
    }
    /**
     * @param birthday the birthday to set
     */
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    
    
    
    
}

ListStu:（查）

package controllers;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.ArrayList;


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 DAO.StudentDao;
import POJO.Student;


@WebServlet("/ListStu")
public class ListStu extends HttpServlet {
    private static final long serialVersionUID = 1L;

    public ListStu() {
        super();

    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset = utf-8");
        PrintWriter out = response.getWriter();

        // 调用Dao 的相关方法获取对象
        StudentDao stuDao = new StudentDao();
        ArrayList<Student> students;
        try {
            students = stuDao.list();
            // 画表
            out.print("<table border=1>");
            out.print("<tr>" + 
                    "<td>id</td>" + 
                    "<td>sequence</td>" + 
                    "<td>name</td>" + 
                    "<td>sex</td>" + 
                    "<td>操作</td>"+ 
                    "</tr>");     // 表头

            // 循环获取字段数据并输出
            for (int index = 0; index < students.size(); index++) {
                
                // ArrayList.get(int index):获取元素
                out.print("<tr>" + 
                        "<td>" + students.get(index).getId() + "</td>" + 
                        "<td>"+ students.get(index).getSequence() + "</td>" +
                        "<td>" + students.get(index).getName()+ "</td>" + 
                        "<td>" + students.get(index).getSex() + "</td>" + 
                        "<td>" + 
                        "<a href='/MVC01/DropStu?id="+ students.get(index).getId() + "'>删除</a>&nbsp;" + 
                        "<a href='/MVC01/ListOne?id="+ students.get(index).getId() + "'>修改 </a>"+ 
                        "</td>" + 
                        "</tr>");
            }

            out.print("</table>");
            out.print("<br>" + "数据库访问成功！");

        } catch (SQLException e) {
            out.print("<br>" + "数据库访问失败！");
            e.printStackTrace();
        }

        
    }

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

        doGet(request, response);
    }

}

DropStu:（删）

package controllers;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

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 case01.StudentDao;
import utils.DBUtil;
import DAO.StudentDao;

@WebServlet("/DropStu")
public class DropStu extends HttpServlet {
    private static final long serialVersionUID = 1L;
       

    public DropStu() {
        super();
        
    }


    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset = utf-8");
        PrintWriter out = response.getWriter();
        
        int id = Integer.parseInt(request.getParameter("id"));
        StudentDao stuDao = new StudentDao();
        if(stuDao.delete(id)) {
            response.sendRedirect("ListStu"); // 重定向
            out.print("<br>" + "删除学生信息成功！");
        }else {
            out.print("<br>" + "删除学生信息失败！");
        }
    }


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

}

InsertStu:（增）

package controllers;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

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 DAO.StudentDao;
import POJO.Student;
import utils.DBUtil;


@WebServlet("/InsertStu")
public class InsertStu extends HttpServlet {
    private static final long serialVersionUID = 1L;
       

    public InsertStu() {
        super();
        
    }


    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset = utf-8");
        PrintWriter out = response.getWriter();
        
            
            // 获得来自前端的参数
        
            //id自增，可以不用获取
            String sequence = request.getParameter("sequence");
            String name = request.getParameter("name");
            String sex = request.getParameter("sex");

            Student student = new Student();
            student.setSequence(sequence);
            student.setName(name);
            student.setSex(sex);

            StudentDao stuDao = new StudentDao(); // stuDao:一个集成增删改查的功能类
            
            // 执行删除操作
            if(stuDao.insert(student)) {
                response.sendRedirect("ListStu");
                out.print("<br>"+"学生信息添加成功！");
            }else {
                out.print("<br>"+"学生信息添加失败！");
            }
            
            
            

    }


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

}

UpdateStu:（改）

package controllers;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

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 DAO.StudentDao;
import POJO.Student;
import utils.DBUtil;


@WebServlet("/UpdateStu")
public class UpdateStu extends HttpServlet {
    private static final long serialVersionUID = 1L;
       

    public UpdateStu() {
        super();
        
    }


    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset = utf-8");
        PrintWriter out = response.getWriter();
        
        // 获得来自前端的参数
        int id = Integer.parseInt(request.getParameter("id"));
        String sequence = request.getParameter("sequence");
        String name = request.getParameter("name");
        String sex = request.getParameter("sex");
        
        Student student = new Student();
        student.setSequence(sequence);
        student.setName(name);
        student.setSex(sex);
        student.setId(id);
        
        StudentDao stuDao = new StudentDao();
        if(stuDao.update(student)) {
            response.sendRedirect("ListStu"); // 重定向
            out.print("更新学生信息失败！");
        }else {
            out.print("更新学生信息失败！");
        }
    }


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

}

ListOne:（查询单个）

package controllers;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

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 DAO.StudentDao;
import POJO.Student;
import utils.DBUtil;


@WebServlet("/ListOne")
public class ListOne extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    
    public ListOne() {
        super();
        
    }

    
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset = utf-8");
        PrintWriter out = response.getWriter();
            
        int id = Integer.parseInt(request.getParameter("id"));
        StudentDao stuDao = new StudentDao();
        Student stu = stuDao.listOne(id);
            
        out.print("<form action='/MVC01/UpdateStu' method='post'>");
        out.print("<p>"
                    +"<label>学号：</label>"
                    + "<input type='text' name='sequence' value="+stu.getSequence()+">"
                    + "</p>");
                
        out.print("<p>"
                    +"<label>姓名：</label>"
                    + "<input type='text' name='name' value="+stu.getName()+">"
                    + "</p>");
                
        out.print("<p>"
                    +"<label>性别：</label>"
                    + "<input type='text' name='sex' value="+stu.getSex()+">"
                    + "</p>");

        out.print("<input type='hidden' name='id' value="+id+"></input>");
        out.print("<button type='submit'>提交</button>");
        out.print("</form>");
    }
    



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

}

posted @ 2020-11-24 12:53 ZengZG Views(134) Comments(0) Edit 收藏举报

刷新页面返回顶部

ZengZG

DBUtil, DAO & POJO

公告