MySQL数据库版的信息管理系统

Test.java
public class Test {

//用户登录的业务助理
private static UserBiz userBiz = new UserBiz();

//实例化Stdent的业务助理
private static StudentBiz studentBiz = new StudentBiz();

public static void main(String[] args) {
	System.out.println("===========学员管理系统============");
	System.out.print("请输入用户名:");
	Scanner scanner = new Scanner(System.in);
	String name = scanner.next();
	System.out.print("请输入密码:");
	String password = scanner.next();
	if(userBiz.login(new User(name, password))){
		System.out.println("登录成功~~！");
	}else{
		return;
	}
	
	//--学员管理系统，增删改查--
	while(true){
		System.out.println("请操作学员管理系统：\n1.添加\n2.删除\n3.查询\n4.匹配id查询\n5.修改\n6.分页\n7.退出");
		System.out.print("请输入编号：");
		int num = scanner.nextInt();
		switch (num) {
		case 1:
			System.out.println("请输入学员姓名:");
			name = scanner.next();
			System.out.println("请输入学员年龄:");
			int age = scanner.nextInt();
			if(studentBiz.add(new Student(0, name, age))){   //添加
				System.out.println("添加成功~~~！");
			}
			break;
		case 2:
			System.out.println("请输入要删除的id:");
			int id = scanner.nextInt();
			if(studentBiz.deleteById(id)){ //删除
				System.out.println("删除成功~~~！");
			}
			break;
		case 3:
			List<Student> list = studentBiz.getList();   //查询
			for(Student student : list){
				System.out.println(student);
			}
			break;
		case 4:
			System.out.println("请输入要查找的id:");
			id = scanner.nextInt();
			Student student = studentBiz.queryById(id);  //根据id查询单个对象
			System.out.println(student);  //查询出单个对象
			break;
		case 5:
			System.out.println("请输入要修改的id:");
			id = scanner.nextInt();
			System.out.println("修改后的用户名:");
			name = scanner.next();
			System.out.println("修改后的年龄：");
			age  = scanner.nextInt();
			if(studentBiz.updateStudent(new Student(id, name, age))){//修改
				System.out.println("修改成功~~！");
			}
			break;
		case 6:
			System.out.println("请输入当前页：");
			int currentPage = scanner.nextInt();
			System.out.println("请输入页大小:");
			int pageSize = scanner.nextInt();
			list = studentBiz.queryByPage(currentPage,pageSize);  //分页查询
			for(Student st : list){
				System.out.println(st);
			}
			break;
		case 7:
			System.out.println("退出成功~~！");
			return;
		default:
			System.out.println("输入有误~~！");
			break;
		}
	}
	
}

}

DAO层
public class UserDao {
public int login(User user) {
	Connection conn = null;
	PreparedStatement prst = null;
	ResultSet rs    = null;
	try {
		 conn = DBUtils.getConnection();
		 String sql = "select count(1) from t_user where name=? and password=?";
		 prst = conn.prepareStatement(sql);
		 prst.setString(1, user.getName());
		 prst.setString(2, user.getPassword());
		 rs = prst.executeQuery();
		 if(rs.next()){
			 return rs.getInt(1);
		 }
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeAll(rs,prst,conn);
	}
	return 0;
}

}

public class StudentDao {

public int add(Student student) {
	Connection conn = null;
	PreparedStatement prst = null;
	ResultSet rs    = null;
	try {
		 conn = DBUtils.getConnection();
		 String sql = "insert into t_student(name,age) values(?,?)";
		 prst = conn.prepareStatement(sql);
		 prst.setString(1, student.getName());
		 prst.setInt(2, student.getAge());
		 return prst.executeUpdate();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeAll(rs,prst,conn);
	}
	return 0;
}

public int deleteById(int id) {
	Connection conn = null;
	PreparedStatement prst = null;
	ResultSet rs    = null;
	try {
		 conn = DBUtils.getConnection();
		 String sql = "delete from t_student where id=?";
		 prst = conn.prepareStatement(sql);
		 prst.setInt(1, id);
		 return prst.executeUpdate();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeAll(rs,prst,conn);
	}
	return 0;
}

public  int updateStudent(Student student) {
	Connection conn = null;
	PreparedStatement prst = null;
	ResultSet rs    = null;
	try {
		 conn = DBUtils.getConnection();
		 String sql = "update t_student set name=?,age=? where id=?";
		 prst = conn.prepareStatement(sql);
		 prst.setString(1, student.getName());
		 prst.setInt(2, student.getAge());
		 prst.setInt(3, student.getId());
		 return prst.executeUpdate();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeAll(rs,prst,conn);
	}
	return 0;
}

public List<Student> getList() {
	Connection conn = null;
	PreparedStatement prst = null;
	ResultSet rs    = null;
	List<Student> list = new ArrayList<>();
	try {
		 conn = DBUtils.getConnection();
		 String sql = "select * from t_student";
		 prst = conn.prepareStatement(sql);
		 rs = prst.executeQuery();
		 //return rs; //因为结果集用完就关闭了，所以返回出去的为null
		 while(rs.next()){
			 int id = rs.getInt("id");
			 String name = rs.getString("name");
			 int age = rs.getInt("age");
			 list.add(new Student(id, name, age));  //存学生对象
		 }
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeAll(rs,prst,conn);
	}
	
	return list;  //返回集合
	
}

public Student queryById(int id) {
	Connection conn = null;
	PreparedStatement prst = null;
	ResultSet rs    = null;
	try {
		 conn = DBUtils.getConnection();
		 String sql = "select * from t_student where id=?";
		 prst = conn.prepareStatement(sql);
		 prst.setInt(1, id);
		 rs = prst.executeQuery();
		 if(rs.next()){
			 String name = rs.getString("name");
			 int age = rs.getInt("age");
			 return new Student(id, name, age);
		 }
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeAll(rs,prst,conn);
	}
	return null;
}

public List<Student> queryByPage(int startIndex, int pageSize) {
	Connection conn = null;
	PreparedStatement prst = null;
	ResultSet rs    = null;
	List<Student> list = new ArrayList<>();
	try {
		 conn = DBUtils.getConnection();
		 String sql = "select * from t_student limit ?,?";
		 prst = conn.prepareStatement(sql);
		 prst.setInt(1, startIndex); //偏移
		 prst.setInt(2, pageSize);   //页大小
		 rs = prst.executeQuery();
		 while(rs.next()){
			 int id = rs.getInt("id");
			 String name = rs.getString("name");
			 int age = rs.getInt("age");
			 list.add(new Student(id, name, age));
		 }
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeAll(rs,prst,conn);
	}
	return list;
}

}

Biz层

public class UserBiz {
//实例化dao层对象
private UserDao userDao = new UserDao();

public boolean login(User user) {
	int result = userDao.login(user);
	return result>0;
}

}

public class StudentBiz {
private StudentDao studentDao = new StudentDao();
public boolean add(Student student) {
	
	int result = studentDao.add(student);
	return result>0;
}
public boolean deleteById(int id) {
	int result = studentDao.deleteById(id);
	return result>0;
}
public boolean updateStudent(Student student) {
	int result = studentDao.updateStudent(student);
	return result>0;
}
public List<Student> getList() {
	List<Student> list = studentDao.getList();
	return list;
}
public Student queryById(int id) {
	Student student = studentDao.queryById(id);
	return student;
}
public List<Student> queryByPage(int currentPage, int pageSize) {
	//起始下标 = (当前页-1)*条数  
	int startIndex = (currentPage-1)*pageSize;
	List<Student> list = studentDao.queryByPage(startIndex,pageSize);
	return list;
	
}

}
实体类

    public class User {
    	private int id;
    	private String name;
    	private String password;
    	public int getId() {
    		return id;
    	}
    	public void setId(int id) {
    		this.id = id;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public String getPassword() {
    		return password;
    	}
    	public void setPassword(String password) {
    		this.password = password;
    	}
    	@Override
    	public String toString() {
    		return "User [id=" + id + ", name=" + name + ", password=" + password + "]";
    	}
    	public User(int id, String name, String password) {
    		super();
    		this.id = id;
    		this.name = name;
    		this.password = password;
    	}
    	public User() {
    		super();
    		// TODO Auto-generated constructor stub
    	}
    	public User(String name, String password) {
    		this.name = name;
    		this.password = password;
    	}
    	
    }
public class Student {
	private int id;
	private String name;
	private int age;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	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;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
	}
	public Student(int id, String name, int age) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
	}
	public Student() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	
}

工具类

public class DBUtils {
	private static String driver;
	private static String url;
	private static String username;
	private static String password;

static{
	try {
		//实例化配置属性的对象
		Properties properties = new Properties();
		//将配置文件加载进去
		properties.load(new FileInputStream("config.properties"));
		driver = properties.getProperty("driverName");  //根据key获取value
		url    = properties.getProperty("url");
		username = properties.getProperty("username");
		password = properties.getProperty("password");
		
		Class.forName(driver);
	} catch (ClassNotFoundException | IOException e) {
		e.printStackTrace();
	}
}

public static Connection getConnection(){
	Connection conn = null;
	try {
		conn = DriverManager.getConnection(url, username, password);
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return conn;
}

public static void closeAll(AutoCloseable...cs){
	for(AutoCloseable c : cs){
		if(c != null){
			try {
				c.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}

}