学生管理项目使用JdbcUitl,BaseDao完成数据持久化操作和DbUtils
一,学生管理项目使用JdbcUitl,BaseDao完成数据持久化操作
1.1 需求
使用数据库作为数据持久化操作是一个非常非常常见。剥离原本的数据保存方式,之前数据保存使用的是Json个数文件,并且使用到Dao层
项目Dao层需要继承BaseDao完成对于数据的操作CRUD。并且数据库和当前项目中的实体类是对应关系:
数据表 ==> 实体类名一致
字段名 ==> 成员变量名
数据类型 ==> 成员变量数据类型
完成一个简版Student管理系统
dao
interface StudentDao
impl(package)
StudentDaoImpl
service
interface StudentService
impl(package)
StudentServiceImpl
view
interface ProjectView
impl(package)
ProjectViewImpl
mainproject
main方法
1.2 数据库设计
1.3 Student实体类
package com.qfedu.b_studentsys.entity;
/**
* @author Anonymous 2020/3/25 10:59
*/
public class Student {
private Integer id;
private String name;
private Integer age;
private Boolean gender;
private Float score;
private String address;
public Student() {
}
public Student(String name, Integer age, Boolean gender, Float score, String address) {
this.name = name;
this.age = age;
this.gender = gender;
this.score = score;
this.address = address;
}
public Student(Integer id, String name, Integer age, Boolean gender, Float score, String address) {
this.id = id;
this.name = name;
this.age = age;
this.gender = gender;
this.score = score;
this.address = address;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Boolean getGender() {
return gender;
}
public void setGender(Boolean gender) {
this.gender = gender;
}
public Float getScore() {
return score;
}
public void setScore(Float score) {
this.score = score;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", gender=" + gender +
", score=" + score +
", address='" + address + '\'' +
'}';
}
}
1.4 StudentDao规范
package com.qfedu.b_studentsys.dao;
import com.qfedu.b_studentsys.entity.Student;
import java.util.List;
/**
* StudentDao接口,规定Dao层需要完成的方法
*
* @author Anonymous 2020/3/25 11:01
*/
public interface StudentDao {
/**
* 添加学生方法要求,参数为Student类对象, 返回值为int类型,操作成功返回1, 失败
* 返回0
*
* @param student Student类对象
* @return 添加成功返回1,否则返回0
*/
int addStudent(Student student);
/**
* 根据指定ID删除对应的学生
*
* @param id 指定的ID
* @return 删除成功返回1,否则返回0
*/
int deleteStudent(int id);
/**
* 修改学生信息,传入的参数是一个Student类对象
*
* @param student Student类对象
* @return 更新成功返回1,否则返回0
*/
int updateStudent(Student student);
/**
* 查询指定ID的学生,没有找到返回null
*
* @param id 指定的ID号
* @return 查询成功返回Student类对象,失败返回null
*/
Student findStudentById(int id);
/**
* 查询当前数据库中所有学生信息
*
* @return 返回值List集合,如果没有数据返回null
*/
List<Student> findAllStudent();
}
1.5 StudentDaoImpl实现
package com.qfedu.b_studentsys.dao.impl;
import com.qfedu.b_studentsys.dao.StudentDao;
import com.qfedu.b_studentsys.entity.Student;
import util.BaseDao;
import java.util.List;
/**
* StudentDaoImpl StudentDao接口实现类
* 这里需要继承BaseDao使用BaseDao对于数据库操作的Update,Query方法
* 同时遵从StudentDao接口,完成方法实现,当前类使用一个符合StudentDao规范的实现类
*
* @author Anonymous 2020/3/25 11:09
*/
public class StudentDaoImpl extends BaseDao implements StudentDao {
@Override
public int addStudent(Student student) {
String sql = "insert into student(name, age, gender, score, address) value (?, ?, ?, ?, ?)";
Object[] parameters = {student.getName(), student.getAge(), student.getGender()
, student.getScore(), student.getAddress()};
return super.update(sql, parameters);
}
@Override
public int deleteStudent(int id) {
String sql = "delete from student where id = " + id;
return super.update(sql, null);
}
@Override
public int updateStudent(Student student) {
String sql = "update student set name = ?, age = ?, gender = ?, score = ?, address = ? where id = ?";
Object[] parameters = {student.getName(), student.getAge(), student.getGender()
, student.getScore(), student.getAddress(), student.getId()};
return super.update(sql, parameters);
}
@Override
public Student findStudentById(int id) {
String sql = "select * from student where id = " + id;
Student student = super.queryBean(sql, null, Student.class);
return student;
}
@Override
public List<Student> findAllStudent() {
String sql = "select * from student";
return super.queryBeanList(sql, null, Student.class);
}
}
1.6 StudentService规范
package com.qfedu.b_studentsys.service;
import com.qfedu.b_studentsys.entity.Student;
import java.util.Comparator;
import java.util.List;
/**
* @author Anonymous 2020/3/25 11:21
*/
public interface StudentService {
/*
增删改查排序
*/
/**
* Service层规定的添加学生方法,返回值类型是boolean
*
* @param student Student类对象
* @return 添加成功返回true,失败返回false
*/
boolean addStudent(Student student);
/**
* 删除指定ID学生
*
* @param id 指定学生的ID
* @return 删除成功返回true,失败返回false
*/
boolean deleteStudentById(Integer id);
/**
* 更新学生信息
*
* @param student 需要更新信息的一个Student类对象
* @return 更新成功返回true,失败返回false
*/
boolean updateStudent(Student student);
/**
* 查询指定ID的学生
*
* @param id 指定的学生的ID号
* @return Student类对象,没有找到返回null
*/
Student findStudentById(Integer id);
/**
* 查询所有的学生类对象,存储于List集合中
*
* @return List集合,没有数据返回null
*/
List<Student> findAllStudents();
/**
* 按照提供的方法,排序学生信息保存到List集合中
*
* @param comparator Comparator函数式接口要求规范
* @return List集合,排序之后的学生数据,没有数据返回null
*/
List<Student> sortUsingCompare(Comparator<Student> comparator);
}
1.7 StudentServiceImpl实现
package com.qfedu.b_studentsys.service.impl;
import com.qfedu.b_studentsys.dao.StudentDao;
import com.qfedu.b_studentsys.dao.impl.StudentDaoImpl;
import com.qfedu.b_studentsys.entity.Student;
import com.qfedu.b_studentsys.service.StudentService;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
/**
* @author Anonymous 2020/3/25 11:29
*/
public class StudentServiceImpl implements StudentService {
/**
* 准备一个StudentDao层的实现类对象,帮助操作数据库
*/
StudentDao studentDao = new StudentDaoImpl();
@Override
public boolean addStudent(Student student) {
return studentDao.addStudent(student) != 0;
}
@Override
public boolean deleteStudentById(Integer id) {
return studentDao.deleteStudent(id) != 0;
}
@Override
public boolean updateStudent(Student student) {
return studentDao.updateStudent(student) != 0;
}
@Override
public Student findStudentById(Integer id) {
return studentDao.findStudentById(id);
}
@Override
public List<Student> findAllStudents() {
return studentDao.findAllStudent();
}
@Override
public List<Student> sortUsingCompare(Comparator<Student> comparator) {
List<Student> list = studentDao.findAllStudent();
if (list != null) {
list.sort(comparator);
}
return list;
}
}
1.8 StudentView规范
package com.qfedu.b_studentsys.view;
import com.qfedu.b_studentsys.entity.Student;
import java.util.List;
/**
* @author Anonymous 2020/3/25 11:35
*/
public interface StudentView {
/**
* 提示用户输入ID
*/
void inputStudentId();
/**
* 提示用户输入醒目
*/
void inputStudentName();
/**
* 提示用户输入年龄
*/
void inputStudentAge();
/**
* 提示用户输入性别
*/
void inputStudentGender();
/**
* 提示用户输入成绩
*/
void inputStudentScore();
/**
* 提示用户输入住址
*/
void inputStudentAddress();
/**
* 展示一个学生信息
*
* @param student Student类对象
*/
void showStudentInfo(Student student);
/**
* 删除确认提示
*/
void deleteConfirm();
/**
* 操作取消
*/
void operationCancel();
/**
* Not Found!!! ==> 404
*/
void notFound();
/**
* 展示List集合的中的Student类对象
* @param students List集合
*/
void showStudentList(List<Student> students);
/**
* 排序选择提示
*/
void sortOperationChoose();
/**
* 修改学生菜单
* @param student Student
*/
void modifyStudentMenu(Student student);
/**
* 选择错误提示
*/
void chooseError();
/**
* 主菜单,主界面
*/
void mainMenu();
}
1.9 StudentViewImpl实现
package com.qfedu.b_studentsys.view.impl;
import com.qfedu.b_studentsys.entity.Student;
import com.qfedu.b_studentsys.view.StudentView;
import java.util.List;
/**
* @author Anonymous 2020/3/25 11:36
*/
public class StudentViewImpl implements StudentView {
@Override
public void inputStudentId() {
System.out.println("请输入学生的ID号:");
}
@Override
public void inputStudentName() {
System.out.println("请输入学生的姓名:");
}
@Override
public void inputStudentAge() {
System.out.println("请输入学生的年龄:");
}
@Override
public void inputStudentGender() {
System.out.println("请输入学生的性别 true => 男 false => 女:");
}
@Override
public void inputStudentScore() {
System.out.println("请输入学生的成绩:");
}
@Override
public void inputStudentAddress() {
System.out.println("请输入学生的地址:");
}
@Override
public void showStudentInfo(Student student) {
System.out.println(student);
}
@Override
public void deleteConfirm() {
System.out.println("确定删除吗? true or false");
}
@Override
public void operationCancel() {
System.out.println("操作取消");
}
@Override
public void notFound() {
System.out.println("查无此人");
}
@Override
public void showStudentList(List<Student> students) {
for (Student student : students) {
System.out.println(student);
}
}
@Override
public void sortOperationChoose() {
System.out.println("1. 年龄升序");
System.out.println("2. 成绩降序");
}
@Override
public void modifyStudentMenu(Student student) {
System.out.println("ID:" + student.getId() + " Name:" + student.getName());
System.out.println("Age:" + student.getAge() + " Gender:" + (student.getGender() ? "男" : "女"));
System.out.println("Score:" + student.getScore() + " Address:" + student.getAddress());
System.out.println("1. 修改学生的名字");
System.out.println("2. 修改学生的年龄");
System.out.println("3. 修改学生的性别");
System.out.println("4. 修改学生的成绩");
System.out.println("5. 修改学生的地址");
System.out.println("6. 退出保存");
}
@Override
public void chooseError() {
System.out.println("选择错误!!!");
}
@Override
public void mainMenu() {
System.out.println("1. 添加学生");
System.out.println("2. 删除指定ID学生");
System.out.println("3. 修改指定ID学生");
System.out.println("4. 查询指定ID学生");
System.out.println("5. 查询所有学生");
System.out.println("6. 按照条件排序学生信息");
System.out.println("7. 退出");
}
}
1.10 StudentController实现
package com.qfedu.b_studentsys.controller;
import com.qfedu.b_studentsys.entity.Student;
import com.qfedu.b_studentsys.service.StudentService;
import com.qfedu.b_studentsys.service.impl.StudentServiceImpl;
import com.qfedu.b_studentsys.view.StudentView;
import com.qfedu.b_studentsys.view.impl.StudentViewImpl;
import java.math.BigDecimal;
import java.util.Comparator;
import java.util.List;
import java.util.Scanner;
/**
* @author Anonymous 2020/3/25 11:34
*/
public class StudentController {
/**
* StudentService层实现类对象,提供服务
*/
private StudentService studentService = new StudentServiceImpl();
/**
* StudentView 界面层实现类对象,提供界面展示
*/
private StudentView studentView = new StudentViewImpl();
private Scanner scanner = new Scanner(System.in);
/**
* 添加学生
*/
public void addStudent() {
studentView.inputStudentName();
String name = scanner.next();
studentView.inputStudentAge();
int age = scanner.nextInt();
studentView.inputStudentGender();
boolean gender = scanner.nextBoolean();
studentView.inputStudentScore();
float score = scanner.nextFloat();
studentView.inputStudentAddress();
String address = scanner.next();
Student student = new Student(name, age, gender, score, address);
studentService.addStudent(student);
}
/**
* 删除学生
*/
public void deleteStudent() {
studentView.inputStudentId();
int id = scanner.nextInt();
Student student = studentService.findStudentById(id);
if (null == student) {
studentView.notFound();
return;
}
studentView.showStudentInfo(student);
studentView.deleteConfirm();
if (scanner.nextBoolean()) {
studentService.deleteStudentById(id);
} else {
studentView.operationCancel();
}
}
/**
* 修改指定ID学生
*/
public void modifyStudentById() {
studentView.inputStudentId();
int id = scanner.nextInt();
Student student = studentService.findStudentById(id);
if (null == student) {
studentView.notFound();
return;
}
int choose = 0;
boolean flag = false;
while (true) {
studentView.modifyStudentMenu(student);
choose = scanner.nextInt();
switch (choose) {
case 1:
studentView.inputStudentName();
String name = scanner.next();
student.setName(name);
break;
case 2:
studentView.inputStudentAge();
int age = scanner.nextInt();
student.setAge(age);
break;
case 3:
studentView.inputStudentGender();
boolean gender = scanner.nextBoolean();
student.setGender(gender);
break;
case 4:
studentView.inputStudentScore();
float score = scanner.nextFloat();
student.setScore(score);
break;
case 5:
studentView.inputStudentAddress();
String address = scanner.next();
student.setAddress(address);
break;
case 6:
flag = true;
break;
default:
studentView.chooseError();
break;
}
if (flag) {
studentService.updateStudent(student);
break;
}
}
}
/**
* 查询指定ID学生
*/
public void findStudentById() {
studentView.inputStudentId();
int id = scanner.nextInt();
Student student = studentService.findStudentById(id);
if (student != null) {
studentView.showStudentInfo(student);
} else {
studentView.notFound();
}
}
/**
* 查询所有学生
*/
public void findAllStudent() {
List<Student> list = studentService.findAllStudents();
if (list != null) {
studentView.showStudentList(list);
} else {
studentView.notFound();
}
}
/**
* 按照条件排序
*/
public void sort() {
studentView.sortOperationChoose();
int choose = scanner.nextInt();
List<Student> list = null;
switch (choose) {
case 1:
// 年龄升序
list = studentService.sortUsingCompare(Comparator.comparingInt(Student::getAge));
break;
case 2:
// 成绩降序
list = studentService.sortUsingCompare((stu1, stu2) -> {
BigDecimal bigDecimal1 = new BigDecimal(stu1.getScore() + "");
BigDecimal bigDecimal2 = new BigDecimal(stu2.getScore() + "");
return bigDecimal2.compareTo(bigDecimal1);
});
break;
default:
studentView.chooseError();
break;
}
if (list != null) {
studentView.showStudentList(list);
} else {
studentView.notFound();
}
}
public void mainMenu() {
studentView.mainMenu();
}
public void chooseError() {
studentView.chooseError();
}
}
1.11 StudentProject main方法
package com.qfedu.b_studentsys.main;
import com.qfedu.b_studentsys.controller.StudentController;
import java.util.Scanner;
/**
* 主方法
*
* @author Anonymous 2020/3/25 14:56
*/
public class MainProject {
private static Scanner scanner = new Scanner(System.in);
private static StudentController studentController = new StudentController();
public static void main(String[] args) {
int choose = 0;
boolean flag = true;
while (flag) {
studentController.mainMenu();
choose = scanner.nextInt();
switch (choose) {
case 1:
studentController.addStudent();
break;
case 2:
studentController.deleteStudent();
break;
case 3:
studentController.modifyStudentById();
break;
case 4:
studentController.findStudentById();
break;
case 5:
studentController.findAllStudent();
break;
case 6:
studentController.sort();
break;
case 7:
flag = false;
break;
default:
studentController.chooseError();
break;
}
}
}
}
结构演示
二,轻量级数据库ORM框架DbUtils
2.1 DbUtils介绍
Apache组织下的一个轻量级ORM框架
Commons DbUtils: JDBC Utility Component
两个核心方法
update方法 ==> insert,update,delete
query方法 ==> select
一个核心类
QueryRunner DbUtils的核心类
2.2 DbUtils ORM工具使用
package com.qfedu.c_dbutils;
import com.qfedu.b_studentsys.entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import util.JdbcUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
/**
* DbUtils ORM框架演示
*
* @author Anonymous 2020/3/25 16:03
*/
public class DbUtilsTest {
@Test
public void testInsert() throws SQLException {
// 1. DbUtils核心类 QueryRunner对象
QueryRunner runner = new QueryRunner();
// 2. 插入数据到Student数据表中
String sql = "insert into student(name, age, gender, score, address) value(?, ?, ?, ?, ?)";
Object[] parameters = {"老黑", 70, true, 59, "河南郑州"};
// 3. 获取数据库连接
Connection connection = JdbcUtil.getConnection();
// 4. 执行Update方法
runner.update(connection, sql, parameters);
JdbcUtil.close(connection);
}
/**
* 了解操作方式, ResultSetHandler
*/
@Test
public void testQuery1() throws SQLException {
// 1. DbUtils核心类 QueryRunner对象
QueryRunner runner = new QueryRunner();
// 2. SQL语句
String sql = "select * from student where id = 1";
Connection connection = JdbcUtil.getConnection();
/*
ResultSetHandler 核心接口
ResultSet结果集 Handler处理,
核心方法 handler(ResultSet rs)
*/
Student student = runner.query(connection, sql, rs -> {
Student stu = null;
if (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
boolean gender = rs.getBoolean("gender");
float score = rs.getFloat("score");
String address = rs.getString("address");
stu = new Student(id, name, age, gender, score, address);
}
return stu;
});
System.out.println(student);
JdbcUtil.close(connection);
}
/**
* BeanHandler
*/
@Test
public void queryBean() throws SQLException {
// 1. DbUtils核心类 QueryRunner对象
QueryRunner runner = new QueryRunner();
// 2. SQL语句
String sql = "select * from student where id = 1";
Connection connection = JdbcUtil.getConnection();
/*
BeanHandler: 处理符合JavaBean规范的类对象,传入参数是对应JavaBean规范 Class对象
*/
Student student = runner.query(connection, sql, new BeanHandler<>(Student.class));
System.out.println(student);
JdbcUtil.close(connection);
}
/**
* BeanListHandler
*/
@Test
public void queryBeanList() throws SQLException {
// 1. DbUtils核心类 QueryRunner对象
QueryRunner runner = new QueryRunner();
// 2. SQL语句
String sql = "select * from student where id > ?";
Connection connection = JdbcUtil.getConnection();
/*
BeanListHandler: 处理符合JavaBean规范的实体类,并且返回值是一个List集合
包含制定的JavaBean实体类
*/
List<Student> list = runner.query(connection, sql, new BeanListHandler<>(Student.class), 2);
for (Student student : list) {
System.out.println(student);
}
JdbcUtil.close(connection);
}
/**
* ArrayHandler
*/
@Test
public void queryArray() throws SQLException {
// 1. DbUtils核心类 QueryRunner对象
QueryRunner runner = new QueryRunner();
// 2. SQL语句
String sql = "select * from student where id = 1";
Connection connection = JdbcUtil.getConnection();
/*
ArrayHandler: 查询一个数据行,数据行中的所有数据整合成一个Object类型数组返回
*/
Object[] values = runner.query(connection, sql, new ArrayHandler());
System.out.println(Arrays.toString(values));
JdbcUtil.close(connection);
}
/**
* ArrayListHandler
*/
@Test
public void queryArrayList() throws SQLException {
// 1. DbUtils核心类 QueryRunner对象
QueryRunner runner = new QueryRunner();
// 2. SQL语句
String sql = "select * from student where id > ?";
Connection connection = JdbcUtil.getConnection();
/*
ArrayListHandler: 查询结果集中所有数据行,每一行数据对应一个Object类型数组,存储在List集合中
*/
List<Object[]> list = runner.query(connection, sql, new ArrayListHandler(), 2);
for (Object[] values : list) {
System.out.println(Arrays.toString(values));
}
JdbcUtil.close(connection);
}
/**
* MapHandler
*/
@Test
public void queryMap() throws SQLException {
// 1. DbUtils核心类 QueryRunner对象
QueryRunner runner = new QueryRunner();
// 2. SQL语句
String sql = "select * from student where id = 1";
Connection connection = JdbcUtil.getConnection();
/*
MapHandler: 处理一个数据行,数据行中字段是对应Key,字段对应数据是value,组成一个Map双边队列
*/
Map<String, Object> map = runner.query(connection, sql, new MapHandler());
System.out.println(map);
}
/**
* MapListHandler
*/
@Test
public void queryMapList() throws SQLException {
// 1. DbUtils核心类 QueryRunner对象
QueryRunner runner = new QueryRunner();
// 2. SQL语句
String sql = "select * from student where id > ?";
Connection connection = JdbcUtil.getConnection();
/*
MapListHandler: 结果集中所有的数据行,每一行对应一个Map对象,字段名为Key,字段对应的数据为value,所有数据行存储
在List中
*/
List<Map<String, Object>> mapList = runner.query(connection, sql, new MapListHandler(), 2);
for (Map<String, Object> map : mapList) {
System.out.println(map);
}
}
}
2.3 ResultHandler以及其子类
ResultSetHandler 核心接口
ResultSet结果集 Handler处理,
核心方法 handler(ResultSet rs)
BeanHandler:
处理符合JavaBean规范的类对象,传入参数是对应JavaBean规范 Class对象
BeanListHandler:
处理符合JavaBean规范的实体类,并且返回值是一个List集合包含制定的JavaBean实体类
ArrayHandler:
查询一个数据行,数据行中的所有数据整合成一个Object类型数组返回
ArrayListHandler:
查询结果集中所有数据行,每一行数据对应一个Object类型数组,存储在List集合中
MapHandler:
处理一个数据行,数据行中字段是对应Key,字段对应数据是value,组成一个Map双边队列
MapListHandler:
结果集中所有的数据行,每一行对应一个Map对象,字段名为Key,字段对应的数据为value,所有数据行存储在List中