/*
新增学生信息
*/@Testpublicvoidinsert(){Student stu =newStudent(5,"周七",27,newDate());int result = service.insert(stu);if(result !=0){System.out.println("新增成功");}else{System.out.println("新增失败");}}
5.需求四:修改数据
持久层
/*
修改学生信息
*/@Overridepublicintupdate(Student stu){Connection con =null;Statement stat =null;int result =0;try{//1.注册驱动Class.forName("com.mysql.jdbc.Driver");//2.获取数据库连接
con =DriverManager.getConnection("jdbc:mysql://192.168.59.129:3306/db14","root","itheima");//3.获取执行者对象
stat = con.createStatement();//4.执行sql语句,并且接收返回的结果集Date d = stu.getBirthday();SimpleDateFormat sdf =newSimpleDateFormat("yyyy-MM-dd");String birthday = sdf.format(d);String sql ="UPDATE student SET sid='"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE sid='"+stu.getSid()+"'";
result = stat.executeUpdate(sql);}catch(Exception e){
e.printStackTrace();}finally{//6.释放资源if(con !=null){try{
con.close();}catch(SQLException e){
e.printStackTrace();}}if(stat !=null){try{
stat.close();}catch(SQLException e){
e.printStackTrace();}}}//将结果返回return result;}
/*
修改学生信息
*/@Testpublicvoidupdate(){Student stu = service.findById(5);
stu.setName("周七七");int result = service.update(stu);if(result !=0){System.out.println("修改成功");}else{System.out.println("修改失败");}}
6.需求五:删除数据
持久层
/*
删除学生信息
*/@Overridepublicintdelete(Integer id){Connection con =null;Statement stat =null;int result =0;try{//1.注册驱动Class.forName("com.mysql.jdbc.Driver");//2.获取数据库连接
con =DriverManager.getConnection("jdbc:mysql://192.168.59.129:3306/db14","root","itheima");//3.获取执行者对象
stat = con.createStatement();//4.执行sql语句,并且接收返回的结果集String sql ="DELETE FROM student WHERE sid='"+id+"'";
result = stat.executeUpdate(sql);}catch(Exception e){
e.printStackTrace();}finally{//6.释放资源if(con !=null){try{
con.close();}catch(SQLException e){
e.printStackTrace();}}if(stat !=null){try{
stat.close();}catch(SQLException e){
e.printStackTrace();}}}//将结果返回return result;}
/*
查询所有学生信息
*/@OverridepublicArrayList<Student>findAll(){ArrayList<Student> list =newArrayList<>();Connection con =null;Statement stat =null;ResultSet rs =null;try{
con =JDBCUtils.getConnection();//3.获取执行者对象
stat = con.createStatement();//4.执行sql语句,并且接收返回的结果集String sql ="SELECT * FROM student";
rs = stat.executeQuery(sql);//5.处理结果集while(rs.next()){Integer sid = rs.getInt("sid");String name = rs.getString("name");Integer age = rs.getInt("age");Date birthday = rs.getDate("birthday");//封装Student对象Student stu =newStudent(sid,name,age,birthday);//将student对象保存到集合中
list.add(stu);}}catch(Exception e){
e.printStackTrace();}finally{//6.释放资源JDBCUtils.close(con,stat,rs);}//将集合对象返回return list;}
条件查询
/*
条件查询,根据id查询学生信息
*/@OverridepublicStudentfindById(Integer id){Student stu =newStudent();Connection con =null;Statement stat =null;ResultSet rs =null;try{
con =JDBCUtils.getConnection();//3.获取执行者对象
stat = con.createStatement();//4.执行sql语句,并且接收返回的结果集String sql ="SELECT * FROM student WHERE sid='"+id+"'";
rs = stat.executeQuery(sql);//5.处理结果集while(rs.next()){Integer sid = rs.getInt("sid");String name = rs.getString("name");Integer age = rs.getInt("age");Date birthday = rs.getDate("birthday");//封装Student对象
stu.setSid(sid);
stu.setName(name);
stu.setAge(age);
stu.setBirthday(birthday);}}catch(Exception e){
e.printStackTrace();}finally{//6.释放资源JDBCUtils.close(con,stat,rs);}//将对象返回return stu;}
新增数据
/*
添加学生信息
*/@Overridepublicintinsert(Student stu){Connection con =null;Statement stat =null;int result =0;try{
con =JDBCUtils.getConnection();//3.获取执行者对象
stat = con.createStatement();//4.执行sql语句,并且接收返回的结果集Date d = stu.getBirthday();SimpleDateFormat sdf =newSimpleDateFormat("yyyy-MM-dd");String birthday = sdf.format(d);String sql ="INSERT INTO student VALUES ('"+stu.getSid()+"','"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
result = stat.executeUpdate(sql);}catch(Exception e){
e.printStackTrace();}finally{//6.释放资源JDBCUtils.close(con,stat);}//将结果返回return result;}
修改数据
/*
修改学生信息
*/@Overridepublicintupdate(Student stu){Connection con =null;Statement stat =null;int result =0;try{
con =JDBCUtils.getConnection();//3.获取执行者对象
stat = con.createStatement();//4.执行sql语句,并且接收返回的结果集Date d = stu.getBirthday();SimpleDateFormat sdf =newSimpleDateFormat("yyyy-MM-dd");String birthday = sdf.format(d);String sql ="UPDATE student SET sid='"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE sid='"+stu.getSid()+"'";
result = stat.executeUpdate(sql);}catch(Exception e){
e.printStackTrace();}finally{//6.释放资源JDBCUtils.close(con,stat);}//将结果返回return result;}
删除数据
/*
删除学生信息
*/@Overridepublicintdelete(Integer id){Connection con =null;Statement stat =null;int result =0;try{
con =JDBCUtils.getConnection();//3.获取执行者对象
stat = con.createStatement();//4.执行sql语句,并且接收返回的结果集String sql ="DELETE FROM student WHERE sid='"+id+"'";
result = stat.executeUpdate(sql);}catch(Exception e){
e.printStackTrace();}finally{//6.释放资源JDBCUtils.close(con,stat);}//将结果返回return result;}
publicclassStudentDaoImplimplementsStudentDao{/*
查询所有学生信息
*/@OverridepublicArrayList<Student>findAll(){Connection con =null;Statement stat =null;ResultSet rs =null;ArrayList<Student> list =newArrayList<>();try{//1.获取连接
con =JDBCUtils.getConnection();//2.获取执行者对象
stat = con.createStatement();//3.执行sql语句,并接收结果String sql ="SELECT * FROM student";
rs = stat.executeQuery(sql);//4.处理结果,将每条记录封装成一个Student对象。将多个Student对象保存到集合中while(rs.next()){Integer sid = rs.getInt("sid");String name = rs.getString("name");Integer age = rs.getInt("age");Date birthday = rs.getDate("birthday");Student stu =newStudent(sid,name,age,birthday);
list.add(stu);}}catch(SQLException e){
e.printStackTrace();}finally{//5.释放资源JDBCUtils.close(con,stat,rs);}return list;}/*
条件查询,根据id查询学生信息
*/@OverridepublicStudentfindById(Integer id){Connection con =null;Statement stat =null;ResultSet rs =null;Student stu =newStudent();try{//1.获取连接
con =JDBCUtils.getConnection();//2.获取执行者对象
stat = con.createStatement();//3.执行sql语句,并接收结果String sql ="SELECT * FROM student WHERE sid='"+id+"'";
rs = stat.executeQuery(sql);//4.处理结果,将记录封装成一个Student对象。if(rs.next()){Integer sid = rs.getInt("sid");String name = rs.getString("name");Integer age = rs.getInt("age");Date birthday = rs.getDate("birthday");
stu.setSid(sid);
stu.setName(name);
stu.setAge(age);
stu.setBirthday(birthday);}}catch(SQLException e){
e.printStackTrace();}finally{//5.释放资源JDBCUtils.close(con,stat,rs);}return stu;}/*
新增学生信息
*/@Overridepublicintinsert(Student stu){Connection con =null;Statement stat =null;int result =0;try{//1.获取连接
con =JDBCUtils.getConnection();//2.获取执行者对象
stat = con.createStatement();//3.执行sql语句,并接收结果Date date = stu.getBirthday();SimpleDateFormat sdf =newSimpleDateFormat("yyyy-MM-dd");String birthday = sdf.format(date);String sql ="INSERT INTO student VALUES (null,'"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
result = stat.executeUpdate(sql);}catch(SQLException e){
e.printStackTrace();}finally{//4.释放资源JDBCUtils.close(con,stat);}return result;}/*
修改学生信息
*/@Overridepublicintupdate(Student stu){Connection con =null;Statement stat =null;int result =0;try{//1.获取连接
con =JDBCUtils.getConnection();//2.获取执行者对象
stat = con.createStatement();//3.执行sql语句,并接收结果Date date = stu.getBirthday();SimpleDateFormat sdf =newSimpleDateFormat("yyyy-MM-dd");String birthday = sdf.format(date);String sql ="UPDATE student SET sid='"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE sid='"+stu.getSid()+"'";
result = stat.executeUpdate(sql);}catch(SQLException e){
e.printStackTrace();}finally{//4.释放资源JDBCUtils.close(con,stat);}return result;}/*
删除学生信息
*/@Overridepublicintdelete(Integer id){Connection con =null;Statement stat =null;int result =0;try{//1.获取连接
con =JDBCUtils.getConnection();//2.获取执行者对象
stat = con.createStatement();//3.执行sql语句,并接收结果String sql ="DELETE FROM student WHERE sid='"+id+"'";
result = stat.executeUpdate(sql);}catch(SQLException e){
e.printStackTrace();}finally{//4.释放资源JDBCUtils.close(con,stat);}return result;}}