java连接mysql的学生管理系统

package com.veryedu.b2c.dao; import com.veryedu.b2c.boan.StudentBean; import com.veryedu.b2c.util.DBHelp; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import java.util.Scanner; public class StudentDAO { //根据id删除 public void deletebyid(String sno){ String sql="delete from student where sid='"+sno+"'"; Connection conn=DBHelp.getConn(); try{ Statement stmt=conn.createStatement(); stmt.executeUpdate(sql); }catch(Exception ex){ ex.printStackTrace(); } } //根据id查找 public StudentBean findbysno(String sno){ String sql="select * from student where sid='"+sno+"'"; StudentBean stb=null; Connection conn=DBHelp.getConn(); try{ Statement stmt=conn.createStatement(); ResultSet rs=stmt.executeQuery(sql); while(rs.next()){ String id=rs.getString("sid"); String sname=rs.getString("name"); String age=rs.getString("age"); String sex=rs.getString("sex"); //System.out.println(id+" "+sname+" "+age+" "+sex); stb=new StudentBean(); stb.setAge(Integer.parseInt(age)); stb.setSid(id); stb.setName(sname); stb.setSex(sex); } }catch(Exception ex){ ex.printStackTrace(); } return stb; } //根据用户名和密码 public StudentBean findbynameandpwd(String name,String pwd){ String sql="select * from student where name='"+name+"' and pwd='"+pwd+"';"; StudentBean stb=null; Connection conn=DBHelp.getConn(); try{ Statement stmt=conn.createStatement(); ResultSet rs=stmt.executeQuery(sql); while(rs.next()){ String id=rs.getString("sid"); String sname=rs.getString("name"); String age=rs.getString("age"); String sex=rs.getString("sex"); String spwd=rs.getString("pwd"); System.out.println(id+" "+sname+" "+age+" "+sex+" "+spwd); stb=new StudentBean(); stb.setAge(Integer.parseInt(age)); stb.setSid(id); stb.setName(name); stb.setSex(sex); stb.setPwd(pwd); } }catch(Exception ex){ ex.printStackTrace(); } return stb; } public List getAllStudentInfo(){ List studentList=new ArrayList(); //1.编写SQL String sql="select sid,name,age,sex,pwd from Student"; //2.获得数据库连接 Connection conn=DBHelp.getConn(); Statement stmt=null; ResultSet rs=null; try{ //3.获得Statement stmt=conn.createStatement(); //4.执行查询 rs=stmt.executeQuery(sql); while(rs.next()){ StudentBean sb=new StudentBean(); String id=rs.getString("sid"); String name=rs.getString("name"); String age=rs.getString("age"); String sex=rs.getString("sex"); String pwd=rs.getString("pwd"); System.out.println(id+" "+name+" "+age+" "+sex+" "+pwd); sb.setAge(Integer.parseInt(age)); sb.setSid(id); sb.setName(name); sb.setSex(sex); sb.setPwd(pwd); studentList.add(sb); } }catch(Exception ex){ ex.printStackTrace(); }finally{ try{ rs.close(); stmt.close(); conn.close(); }catch(Exception ex){ ex.printStackTrace(); } } return studentList; } public static void main(String[] args) { System.out.println("*****学生管理系统******"); System.out.println("**********************"); System.out.println("输入用户名和密码登陆系统"); Scanner scanner=new Scanner(System.in); System.out.println("请输入用户名："); String sname=scanner.nextLine(); System.out.println("输入密码："); String pwd=scanner.nextLine(); StudentDAO sdao=new StudentDAO(); StudentBean st=sdao.findbynameandpwd(sname, pwd); if(st==null){ int count =3; while(st==null){ System.out.println("验证失败,还有"+(count-1)+"次机会"); count--; if(count==0) { System.out.println("bye~"); System.exit(0); } System.out.println("输入用户名和密码登陆系统"); scanner=new Scanner(System.in); System.out.println("请输入用户名："); sname=scanner.nextLine(); System.out.println("输入密码："); pwd=scanner.nextLine(); st=sdao.findbynameandpwd(sname, pwd); } }else{ System.out.println("成功登陆"); System.out.println("功能如下"); while(true){ System.out.println("1.查询2.删除3.所有4.退出"); String p=scanner.nextLine(); if(p.equals("1")){ System.out.println("输入id:"); String sno=scanner.nextLine(); StudentBean st1=sdao.findbysno(sno); if(st1==null){ System.out.println("没找到"); }else{ System.out.println(st1); } } if(p.equals("2")){ System.out.println("输入要删除的id:"); String sno=scanner.nextLine(); sdao.deletebyid(sno); System.out.println("删除成功"); } if(p.equals("3")){ List slist=sdao.getAllStudentInfo(); System.out.println("共有"+slist.size()+"个学生信息"); } if(p.equals("4")){ System.out.println("bye~"); System.exit(0); } } } } }