jsp+servlet+jdbc原生实现增删改查两张表

package dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import pojo.Employee; public class EmployeeDao { public List<Employee> getAllEmployee(){ //查询所有信息 List<Employee> list = new ArrayList<Employee>(); //创建集合 Connection conn = DbHelper.getConnection(); String sql = "select * from dev.Employee"; //SQL查询语句 try { PreparedStatement pst = conn.prepareStatement(sql); ResultSet rst = pst.executeQuery(); while (rst.next()) { Employee Employee = new Employee(); Employee.setId(rst.getString("id")); Employee.setName(rst.getString("name")); //得到成绩信息 Employee.setJob(rst.getString("job")); Employee.setEducation(rst.getString("education")); Employee.setSex(rst.getString("sex")); Employee.setAge(rst.getString("age")); list.add(Employee); } rst.close(); //关闭 pst.close(); //关闭 } catch (SQLException e) { e.printStackTrace(); //抛出异常 } return list; //返回一个集合 } public boolean addEmployee(Employee Employee){ //添加信息 String sql = "INSERT INTO `Employee`(`id`,`name`,`job`,`education`,`sex`,`age`) VALUES (?,?,?,?,?,?)"; //添加的SQL语句 Connection conn = DbHelper.getConnection(); try { PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, Employee.getId()); pst.setString(2, Employee.getName()); pst.setString(3, Employee.getJob()); pst.setString(4, Employee.getEducation()); pst.setString(5, Employee.getSex()); pst.setString(6, Employee.getAge()); int count = pst.executeUpdate(); pst.close(); return count>0?true:false; //是否添加的判断 } catch (SQLException e) { e.printStackTrace(); } return false; } public boolean updateEmployee(Employee Employee){ //修改 String sql = "UPDATE `Employee` SET `name`=?,`job`=?,`education`=?,`sex`=?,`age`=? WHERE `id` = ?"; //修改的SQL语句，根据ID修改 Connection conn = DbHelper.getConnection(); try { PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, Employee.getName()); pst.setString(2, Employee.getJob()); pst.setString(3, Employee.getEducation()); pst.setString(4, Employee.getSex()); pst.setString(5,Employee.getAge()); pst.setString(6, Employee.getId()); //根据的ID int count = pst.executeUpdate(); pst.close(); //关闭 return count>0?true:false; //是否修改的判断 } catch (SQLException e) { e.printStackTrace(); } return false; } public boolean deleteEmployee(String id){ //删除 String sql = "delete from dev.Employee where id = ?"; //删除的SQL语句，根据ID删除 Connection conn = DbHelper.getConnection(); try { PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, id); int count = pst.executeUpdate(); pst.close(); return count>0?true:false; //是否删除的判断 } catch (SQLException e) { e.printStackTrace(); } return false; } public Employee selectEmployeeById(String id){ //根据ID进行查询 Connection conn = DbHelper.getConnection(); String sql = "select * from dev.Employee where id = ?"; Employee Employee = null; try { PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, id); ResultSet rst = pst.executeQuery(); while (rst.next()) { Employee= new Employee(); Employee.setId(rst.getString("id")); Employee.setName(rst.getString("name")); Employee.setJob(rst.getString("job")); Employee.setEducation(rst.getString("education")); Employee.setSex(rst.getString("sex")); Employee.setAge(rst.getString("age")); } rst.close(); pst.close(); } catch (SQLException e) { e.printStackTrace(); } return Employee; //返回 } }