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

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