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