Java连接数据库学生管理系统（二）对数据库信息进行增删查改操作

朴灿烈づ我的快乐病毒、 2022-05-21 10:58 135阅读 0赞

1.创建学生类包含属性：学号，姓名，年龄，性别，密码  
2.同时在数据库创建相应的表

package com.veryedu.b2c.boan;
    
    public class StudentBean {
    	private String sid;
    	private String name;
    	private int age;
    	private String sex;
    	private String pwd;
    	public String getPwd() {
    		return pwd;
    	}
    	public void setPwd(String pwd) {
    		this.pwd = pwd;
    	}
    	public String getSid() {
    		return sid;
    	}
    	public void setSid(String sid) {
    		this.sid = sid;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public int getAge() {
    		return age;
    	}
    	public void setAge(int age) {
    		this.age = age;
    	}
    	public String getSex() {
    		return sex;
    	}
    	public void setSex(String sex) {
    		this.sex = sex;
    	}
    	@Override
    	public String toString() {
    		return "学生信息： age=" + age + ", name=" + name + ", pwd=" + pwd
    				+ ", sex=" + sex + ", sid=" + sid;
    	}
    }

编写DAO类，创建的方法包括：  
1.查找所有学生信息  
2.根据学号查找学生信息  
3.删除学生信息

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);
    				}
    			}
    		}
    	}
    }