学生管理系统(JDBC)

我们利用JDBC来实现学生管理系统
我们先建两个实体类User Student用于临时存数据库中的记录

package com.qf.entity;

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

package com.qf.entity;

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

下面是测试类

package com.qf.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import com.qf.entity.Student;
import com.qf.entity.User;
import com.qf.utils.DBUtils;

public class Test {
	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(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(add(new Student(0, name, age))){   //添加
					System.out.println("添加成功~~~！");
				}
				break;
			case 2:
				System.out.println("请输入要删除的id:");
				int id = scanner.nextInt();
				if(deleteById(id)){ //删除
					System.out.println("删除成功~~~！");
				}
				break;
			case 3:
				queryAll();   //查询	
				break;
			case 4:
				System.out.println("请输入要查找的id:");
				id = scanner.nextInt();
				queryById(id);  //根据id查询单个对象
				break;
			case 5:
				System.out.println("请输入要修改的id:");
				id = scanner.nextInt();
				System.out.println("修改后的用户名:");
				name = scanner.next();
				System.out.println("修改后的年龄：");
				age  = scanner.nextInt();
				if(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();
				queryByPage(currentPage,pageSize);  //分页查询
				break;
			case 7:
				System.out.println("退出成功~~！");
				return;
			default:
				System.out.println("输入有误~~！");
				break;
			}
		}
		
	}

	private static void queryByPage(int currentPage, int pageSize) {
		//起始下标 = (当前页-1)*条数  
		int startIndex = (currentPage-1)*pageSize;
		
		Connection conn = null;
		PreparedStatement prst = null;
		ResultSet rs    = null;
		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");
				 System.out.println(id+"=="+name+"=="+age);
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(rs,prst,conn);
		}
		
	}

	private static boolean 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());
			 int result = prst.executeUpdate();
			 return result>0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(rs,prst,conn);
		}
		return false;
	}

	private static void 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");
				 System.out.println(id+"=="+name+"=="+age);
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(rs,prst,conn);
		}
		
	}

	private static boolean 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);
			 int result = prst.executeUpdate();
			 return result>0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(rs,prst,conn);
		}
		return false;
	}

	private static void queryAll() {
		Connection conn = null;
		PreparedStatement prst = null;
		ResultSet rs    = null;
		try {
			 conn = DBUtils.getConnection();
			 String sql = "select * from t_student";
			 prst = conn.prepareStatement(sql);
			 rs = prst.executeQuery();
			 while(rs.next()){
				 int id = rs.getInt("id");
				 String name = rs.getString("name");
				 int age = rs.getInt("age");
				 System.out.println(id+"=="+name+"=="+age);
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(rs,prst,conn);
		}
	}

	private static boolean 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());
			 int result = prst.executeUpdate();
			 return result>0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(rs,prst,conn);
		}
		return false;
	}

	private static boolean 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()){
				 int result = rs.getInt(1);
				 return result>0;
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(rs,prst,conn);
		}
		return false;
	}
}

如果代码都这样放入测试类里面太过混乱,结构不够清晰
就像我们常说的是懒人在推动这个社会的便捷
这个时候我们引入分层设计与优化