文章目录
- 八、项目开发实现步骤
- (七)创建数据访问实现接口类
- 1、创建学校数据访问接口实现类
- 2、创建状态数据访问接口实现类
- 3、创建学生数据访问接口实现类
- (1)编写插入学生记录的方法
- (2)编写测试插入学生记录的方法
- (3)编写通过标识符删除学生记录的方法
- (4)编写测试通过标识符删除学生记录的方法
- (5)编写通过班级删除学生记录的方法
- (6)编写测试通过班级删除学生记录的方法
- (7)编写通过系部删除学生记录的方法
- (8)编写测试通过系部删除学生记录的方法
- (9)编写更新学生记录的方法
- (10)编写测试更新学生记录的方法
- (11)编写通过标识符查找学生记录的方法
- (12)编写测试通过标识符查找学生记录的方法
- (13)编写根据姓名查询学生记录的方法
- (14)编写测试根据姓名查询学生记录的方法
- (15)编写按班级查询学生记录的方法
- (16)编写测试按班级查询学生记录的方法
- (17)编写按系部查询学生记录的方法
- (18)编写测试按系部查询学生记录的方法
- (19)编写查询所有学生记录的方法
- (20)编写测试查询所有学生记录的方法
- (21)编写按性别统计学生人数的方法
- (22)编写测试按性别统计学生人数的方法
- (23)编写按班级统计学生人数的方法
- (24)编写测试按班级统计学生人数的方法
- (25)编写按系部统计学生人数的方法
- (26)编写测试按系部统计学生人数的方法
- 4、创建用户数据访问接口实现类
八、项目开发实现步骤
(七)创建数据访问实现接口类
在net.lzm.student.dao包里面创建impl子包
1、创建学校数据访问接口实现类
在在net.lzm.student.dao.imql包里创建CollegeDaoimpl类
实现CollegeDao接口
(1)编写按标识符查询学校记录和更新学校记录的方法
package net.lzm.student.dao.impl;
import net.lzm.student.bean.College;
import net.lzm.student.dao.CollegeDao;
import net.lzm.student.dbutil.ConnectionManager;
import java.sql.*;
/**
* 功能:学校数据访问接口实现类
* 作者: lzm
* 日期:2023年06月14日
*/
public class CollegeDaoImpl implements CollegeDao {
// override重载
@Override // 按标识符查询学校记录
public College findById(int id) {
// 定义学校对象
College college = null;
// 获取数据库连接
Connection conn = ConnectionManager.getConnection();
// 定义SQL字符串
String strSQL = "SELECT * FROM t_college WHERE id = ?";
try {
// 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 设置占位符的值
pstmt.setInt(1, id);
// 执行查询操作,返回结果集
ResultSet rs = pstmt.executeQuery();
// 使用Id寻找,主键不可能重复,直接判断是否为空
if (rs.next()) {
// 创建学校对象
college = new College();
// 利用当前记录字段值设置学校对象属性值
college.setId(rs.getInt(id));
college.setName(rs.getString("name"));
college.setPresident(rs.getString("president"));
college.setStartTime(rs.getTimestamp("start_time"));
college.setEmail(rs.getString("email"));
college.setAddress(rs.getString("address"));
college.setProfile(rs.getString("profile"));
}
// 关闭结果集
rs.close();
// 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn); // 关闭数据库
}
// 返回学校对象
return college;
}
@Override // 更新学校记录
public int update(College college) {
// 定义更新记录数
int count = 0;
// 获取数据库连接
Connection conn = ConnectionManager.getConnection();
// 定义SQL语句对象
String strSQL = "UPDATE t_college SET name = ?, president = ?, start_time = ?, email = ?, address = ?, profile = ? WHERE id = ?";
try {
// 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 设置占位符的值
pstmt.setString(1, college.getName());
pstmt.setString(2, college.getPresident());
pstmt.setTimestamp(3, new Timestamp(college.getStartTime().getTime()));
pstmt.setString(4, college.getEmail());
pstmt.setString(5, college.getAddress());
pstmt.setString(6, college.getProfile());
pstmt.setInt(7, college.getId());
// 执行更新操作,返回更新记录数
count = pstmt.executeUpdate();
// 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}
// 返回更新记录数
return count;
}
}
package net.lzm.student.impl;
import net.lzm.student.bean.College;
import net.lzm.student.dao.CollegeDao;
import net.lzm.student.dao.impl.CollegeDaoImpl;
import org.junit.Test;
/**
* 功能:测试学校数据访问接口实现类
* 作者: lzm
* 日期:2023年06月14日
*/
public class TestCollegeDaoImpl {
@Test // 测试按标识符查询学校记录
public void testFindById() {
// 定义标识符变量
int id = 1;
// 创建学校数据访问接口对象
CollegeDao collegeDao = new CollegeDaoImpl();
// 调用按标识符查询学校记录的方法
College college = collegeDao.findById(id);
// 判断查询是否成功
if (college != null) {
System.out.println("标识符:" + college.getId());
System.out.println("学校名称:" + college.getName());
System.out.println("校长:" + college.getPresident());
System.out.println("建校时间:" + college.getStartTime());
System.out.println("电子邮箱:" + college.getEmail());
System.out.println("通信地址:" + college.getAddress());
System.out.println("学校概况:" + college.getProfile());
} else {
System.out.println("what a pity !查询失败!!!");
}
}
}
修改标识符变量值,再运行
package net.lzm.student.impl;
import net.lzm.student.bean.College;
import net.lzm.student.dao.CollegeDao;
import net.lzm.student.dao.impl.CollegeDaoImpl;
import org.junit.Test;
/**
* 功能:测试学校数据访问接口实现类
* 作者: lzm
* 日期:2023年06月14日
*/
public class TestCollegeDaoImpl {
@Test // 测试按标识符查询学校记录
public void testFindById() {
// 定义标识符变量
int id = 1;
// 创建学校数据访问接口对象
CollegeDao collegeDao = new CollegeDaoImpl();
// 调用按标识符查询学校记录的方法
College college = collegeDao.findById(id);
// 判断查询是否成功
if (college != null) {
System.out.println("标识符:" + college.getId());
System.out.println("学校名称:" + college.getName());
System.out.println("校长:" + college.getPresident());
System.out.println("建校时间:" + college.getStartTime());
System.out.println("电子邮箱:" + college.getEmail());
System.out.println("通信地址:" + college.getAddress());
System.out.println("学校概况:" + college.getProfile());
} else {
System.out.println("what a pity !查询失败!!!");
}
}
@Test // 测试学校更新记录
public void testUpdate() {
// 创建学校数据访问接口对象
CollegeDao collegeDao = new CollegeDaoImpl();
// 获取标识符为1的学校记录
College college = collegeDao.findById(1);
// 输出更新前的学校信息
System.out.println("更新前:" + college);
// 更新学校对象属性
college.setName("日龙包大学");
college.setPresident("阿洛小刚");
college.setProfile("一座以阿洛小刚为主的宝批龙构成的学校");
// 调用更新学校记录方法
int count = collegeDao.update(college);
// 判断更新是否成功
if (count > 0) {
System.out.println("更新成功!!!");
System.out.println("更新后:" + collegeDao.findById(1));
} else {
System.out.println("What a pity!更新失败!!!");
}
}
}
(2)编写测试按标识符查询学校记录和测试学校更新记录的方法
更新记录成功
2、创建状态数据访问接口实现类
在在net.lzm.student.dao.imql包里创建StatusDaoImpl类
(1)编写按标识符查询状态记录和更新状态记录的方法
package net.lzm.student.dao.impl;
import net.lzm.student.bean.Status;
import net.lzm.student.dao.StatusDao;
import net.lzm.student.dbutil.ConnectionManager;
import java.sql.*;
/**
* 功能:状态数据访问接口实现类
* 作者: lzm
* 日期:2023年06月14日
*/
public class StatusDaoImpl implements StatusDao {
@Override
public Status findById(int id) {
Status status = null;
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT * FROM t_status WHERE id = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setInt(1,id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
status = new Status();
status.setId(rs.getInt(id));
status.setCollege(rs.getString("college"));
status.setAuthor(rs.getString("author"));
status.setTelephone(rs.getString("telephone"));
status.setAddress(rs.getString("address"));
status.setEmail(rs.getString("email"));
}
rs.close();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn); // 关闭数据库
}
return status;
}
@Override
public int update(Status status) {
// 定义更新记录数
int count = 0;
// 获取数据库连接
Connection conn = ConnectionManager.getConnection();
// 定义SQL字符串
String strSQL = "UPDATE t_status SET college = ?, version = ?, author = ?, telephone = ?, address = ?, email = ? WHERE id = ?";
try {
// 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 设位置占位符的值
pstmt.setString(1, status.getCollege());
pstmt.setString(2, status.getVersion());
pstmt.setString(3, status.getAuthor());
pstmt.setString(4, status.getTelephone());
pstmt.setString(5, status.getAddress());
pstmt.setString(6, status.getEmail());
pstmt.setInt(7, status.getId());
// 执行更新操作,返回更新记录数
count = pstmt.executeUpdate();
// 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
// 返回更新记录数
return count;
}
}
(2)编写测试按标识符查询状态记录和测试更新状态记录的方法
创建状态数据访问接口实现类的测试类
写入如下代码
package net.lzm.student.dao.impl;
import net.lzm.student.bean.Status;
import net.lzm.student.dao.StatusDao;
import org.junit.Test;
/**
* 功能:
* 作者: lzm
* 日期:2023年06月15日
*/
public class TestStatusDaoImpl {
@Test
public void testFindById(){
int id = 1;
StatusDao statusDao = new StatusDaoImpl();
Status status = statusDao.findById(id);
if (status != null){
System.out.println("标识符:" + status.getId());
System.out.println("学校名称:" + status.getCollege());
System.out.println("版本:" + status.getVersion());
System.out.println("作者:" + status.getAuthor());
System.out.println("联系电话:" + status.getTelephone());
System.out.println("通讯地址:" + status.getAddress());
System.out.println("邮箱:" + status.getEmail());
} else {
System.out.println("what a pity !查询失败!!!");
}
}
@Test
public void testUpdate() {
StatusDao statusDao = new StatusDaoImpl();
Status status = statusDao.findById(1);
System.out.println("更新前:" + status);
status.setAuthor("阿洛小刚");
status.setTelephone("11000110001");
status.setCollege("爱看美女大学");
int count = statusDao.update(status);
if (count > 0) {
System.out.println("更新成功!!!");
System.out.println("更新后:" + statusDao.findById(1));
} else {
System.out.println("What a pity!更新失败!!!");
}
}
}
运行效果如下
3、创建学生数据访问接口实现类
(1)编写插入学生记录的方法
@Override
public int insert(Student student) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = "INSERT INTO t_student (id, name, sex, age, department, class, telephone)"
+ "values (?, ?, ?, ?, ?, ?, ?)";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1,student.getId());
pstmt.setString(2,student.getName());
pstmt.setString(3,student.getSex());
pstmt.setInt(4,student.getAge());
pstmt.setString(5,student.getDepartment());
pstmt.setString(6,student.getClazz());
pstmt.setString(7,student.getTelephone());
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
(2)编写测试插入学生记录的方法
// 定义学生数据访问对象
StudentDao dao = new StudentDaoImpl();
@Test
public void testInsert() {
Student student = new Student();
student.setId("22212163");
student.setName("杨云");
student.setSex("男");
student.setAge(19);
student.setDepartment("人工智能与大数据学院");
student.setClazz("2022级软件3班");
student.setTelephone("17713226655");
int count = dao.insert(student);
if (count > 0) {
System.out.println("恭喜,学生记录插入成功!!!");
System.out.println(dao.findById(student.getId()));
} else {
System.out.println("What a pity!学生记录插入失败!!!");
}
}
运行效果如下
(3)编写通过标识符删除学生记录的方法
@Override
public int deleteById(String id) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = "DELETE FROM t_student WHERE id = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, id);
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
(4)编写测试通过标识符删除学生记录的方法
@Test
public void testDeleteById() {
String id = "19204188";
int count = dao.deleteById(id);
if (count > 0) {
System.out.println("恭喜,学生记录删除成功!!!");
} else {
System.out.println("What a pity!学生记录删除失败!!!");
}
}
运行效果如下
(5)编写通过班级删除学生记录的方法
@Override
public int deleteByClass(String clazz) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = "DELETE FROM t_student WHERE class = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, clazz);
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
(6)编写测试通过班级删除学生记录的方法
@Test
public void testDeleteByClass() {
String clazz = "2022计应3班";
int count = dao.deleteByClass(clazz);
if (count > 0) {
System.out.println("恭喜," + clazz + "的记录删除成功!!!");
} else {
System.out.println("What a pity!" + clazz + "的学生记录删除失败!!!");
}
}
运行效果如下
(7)编写通过系部删除学生记录的方法
@Override
public int deleteByDepartment(String department) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = "DELETE FROM t_student WHERE department = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, department);
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
(8)编写测试通过系部删除学生记录的方法
@Test
public void testDeleteByDepartment() {
String department = "大数据学院";
int count = dao.deleteByDepartment(department);
if (count > 0) {
System.out.println("恭喜," + department + "的学生记录删除成功!!!");
} else {
System.out.println("What a pity!学生记录删除失败!!!");
}
}
运行效果如下
(9)编写更新学生记录的方法
@Override
public int update(Student student) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = "UPDATE t_student SET name = ?, sex = ?, age = ?, department = ?,"
+ " class = ?, telephone = ? WHERE id = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getSex());
pstmt.setInt(3, student.getAge());
pstmt.setString(4, student.getDepartment());
pstmt.setString(5, student.getClazz());
pstmt.setString(6, student.getTelephone());
pstmt.setString(7, student.getId());
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
(10)编写测试更新学生记录的方法
@Test
public void testUpdate() {
StudentDao studentDao = new StudentDaoImpl();
Student student = studentDao.findById("22212160");
System.out.println("更新前:" + student);
student.setAge(20);
student.setTelephone("11111000001");
student.setDepartment("大数据学院");
int count = studentDao.update(student);
if (count > 0) {
System.out.println("更新成功!!!");
System.out.println("更新后:" + studentDao.findById("22212160"));
} else {
System.out.println("What a pity!更新失败!!!");
}
}
运行效果如下
(11)编写通过标识符查找学生记录的方法
@Override
public Student findById(String id) {
Student student = null;
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT * FROM t_student WHERE id = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
student = new Student();
student.setId(rs.getString("id"));
student.setName(rs.getString("name"));
student.setSex(rs.getString("sex"));
student.setAge(rs.getInt("age"));
student.setDepartment(rs.getString("department"));
student.setClazz(rs.getString("class"));
student.setTelephone(rs.getString("telephone"));
}
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return student;
}
(12)编写测试通过标识符查找学生记录的方法
@Test
public void testFindById() {
String id = "22212160";
Student student = dao.findById(id);
if (student != null) {
System.out.println("查询到了:" + student);
} else {
System.out.println("What a pity!查询失败!!!");
}
}
运行效果如下
(13)编写根据姓名查询学生记录的方法
@Override
public List<Student> findByName(String name) {
List<Student> students = new ArrayList<>();
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT * FROM t_student WHERE name LIKE ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, name + "%");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Student student = new Student();
student.setId(rs.getString("id"));
student.setName(rs.getString("name"));
student.setSex(rs.getString("sex"));
student.setAge(rs.getInt("age"));
student.setDepartment(rs.getString("department"));
student.setClazz(rs.getString("class"));
student.setTelephone(rs.getString("telephone"));
// 将实体添加到学生列表
students.add(student);
}
rs.close();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return students;
}
(14)编写测试根据姓名查询学生记录的方法
@Test
public void testFindByName() {
String name = "李";
List<Student> students = dao.findByName(name);
;
if (students.size() > 0) {
for (Student student : students) {
System.out.println(student);
}
} else {
System.out.println("What a pity!查无此人!!!");
}
}
运行效果如下
(15)编写按班级查询学生记录的方法
@Override
public List<Student> findByClass(String clazz) {
List<Student> students = new ArrayList<>();
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT * FROM t_student WHERE class LIKE ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, clazz + "%");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Student student = new Student();
student.setId(rs.getString("id"));
student.setName(rs.getString("name"));
student.setSex(rs.getString("sex"));
student.setAge(rs.getInt("age"));
student.setDepartment(rs.getString("department"));
student.setClazz(rs.getString("class"));
student.setTelephone(rs.getString("telephone"));
// 将实体添加到学生列表
students.add(student);
}
rs.close();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return students;
}
(16)编写测试按班级查询学生记录的方法
@Test
public void testFindByClass() {
String clazz = "2022机电3班";
List<Student> students = dao.findByClass(clazz);
if (students.size() > 0) {
for (Student student : students) {
System.out.println(student);
}
} else {
System.out.println("What a pity!查询失败!!!");
}
}
运行效果如下
(17)编写按系部查询学生记录的方法
@Override
public List<Student> findByDepartment(String department) {
List<Student> students = new ArrayList<>();
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT * FROM t_student WHERE department LIKE ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, department + "%");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Student student = new Student();
student.setId(rs.getString("id"));
student.setName(rs.getString("name"));
student.setSex(rs.getString("sex"));
student.setAge(rs.getInt("age"));
student.setDepartment(rs.getString("department"));
student.setClazz(rs.getString("class"));
student.setTelephone(rs.getString("telephone"));
// 将实体添加到学生列表
students.add(student);
}
rs.close();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return students;
}
(18)编写测试按系部查询学生记录的方法
@Test
public void testFindByDepartment() {
String department = "国际学院";
List<Student> students = dao.findByDepartment(department);
if (students.size() > 0) {
for (Student student : students) {
System.out.println(student);
}
} else {
System.out.println("What a pity!查询失败!!!");
}
}
运行效果如下
(19)编写查询所有学生记录的方法
@Override
public List<Student> findAll() {
List<Student> students = new ArrayList<>();
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT * FROM t_student";
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
Student student = new Student();
student.setId(rs.getString("id"));
student.setName(rs.getString("name"));
student.setSex(rs.getString("sex"));
student.setAge(rs.getInt("age"));
student.setDepartment(rs.getString("department"));
student.setClazz(rs.getString("class"));
student.setTelephone(rs.getString("telephone"));
// 将实体添加到学生列表
students.add(student);
}
rs.close();
stmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return students;
}
(20)编写测试查询所有学生记录的方法
@Test
public void testFindAll() {
List<Student> students = dao.findAll();
for (Student student : students) {
System.out.println(student);
}
}
运行效果如下
(21)编写按性别统计学生人数的方法
@Override
public Vector findRowsBySex() {
Vector rows = new Vector();
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT sex AS '性别', count(*) as '人数'"
+ "FROM t_student GROUP BY sex ORDER BY sex DESC";
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
Vector<String> currentRow = new Vector();
currentRow.addElement(rs.getString("性别"));
currentRow.addElement(rs.getInt("人数") + "");
// 当前行向量添加到行集向量
rows.addElement(currentRow);
}
rs.close();
stmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return rows;
}
(22)编写测试按性别统计学生人数的方法
@Test
public void testFindRowsBySex() {
Vector rows = dao.findRowsBySex();
Iterator iterator = rows.iterator();
while (iterator.hasNext()) {
System.out.println(iterator.next());
}
}
运行效果如下
(23)编写按班级统计学生人数的方法
@Override
public Vector findRowsByClass() {
Vector rows = new Vector();
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT class AS '班级', count(*) as '人数'"
+ "FROM t_student GROUP BY class ORDER BY class DESC";
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
Vector<String> currentRow = new Vector();
currentRow.addElement(rs.getString("班级"));
currentRow.addElement(rs.getInt("人数") + "");
// 当前行向量添加到行集向量
rows.addElement(currentRow);
}
rs.close();
stmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return rows;
}
(24)编写测试按班级统计学生人数的方法
@Test
public void testFindRowsByClass() {
Vector rows = dao.findRowsByClass();
Iterator iterator = rows.iterator();
while (iterator.hasNext()) {
System.out.println(iterator.next());
}
}
运行效果如下
(25)编写按系部统计学生人数的方法
@Override
public Vector findRowsByDepartment() {
Vector rows = new Vector();
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT department AS '系部', count(*) as '人数'"
+ "FROM t_student GROUP BY department ORDER BY department DESC";
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
Vector<String> currentRow = new Vector();
currentRow.addElement(rs.getString("系部"));
currentRow.addElement(rs.getInt("人数") + "");
// 当前行向量添加到行集向量
rows.addElement(currentRow);
}
rs.close();
stmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return rows;
}
(26)编写测试按系部统计学生人数的方法
@Test
public void testFindRowsByDepartment() {
Vector rows = dao.findRowsByDepartment();
Iterator iterator = rows.iterator();
while (iterator.hasNext()) {
System.out.println(iterator.next());
}
}
运行效果如下
4、创建用户数据访问接口实现类
(1)编写插入用户记录的方法
@Override
public int insert(User user) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = "INSERT INTO t_user (username, password, telephone, register_time)"
+ " value (?, ?, ?, ?)";
if (!isUsernameExisted(user.getUsername())) {
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getPassword());
pstmt.setString(3, user.getTelephone());
pstmt.setTimestamp(4, new Timestamp(user.getRegisterTime().getTime()));
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
}
return count;
}
(2)编写测试用户记录的方法
@Test
public void testInsert() {
User user = new User();
user.setUsername("秦牛正威");
user.setPassword("555555");
user.setTelephone("17652115666");
user.setRegisterTime(new Date());
int count = dao.insert(user);
if (count > 0) {
System.out.println("恭喜!!!用户记录插入成功!");
System.out.println(dao.findById(dao.findAll().size()));
} else {
System.out.println("What a pity,插入失败!!!");
}
}
运行效果如下
(3)编写按id删除用户记录的方法
@Override
public int deleteById(int id) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = "DELETE FROM t_user WHERE id = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setInt(1, id);
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
(4)编写测试按id删除用户记录的方法
@Test
public void testDeleteById() {
int id = 9;
int count = dao.deleteById(id);
if (count != 0) {
System.out.println("删除用户记录成功!!!");
} else {
System.out.println("What a pity,删除记录失败!!!");
}
}
运行效果如下
(5)编写更新用户记录的方法
@Override
public int update(User user) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = "UPDATE t_user SET username = ?, password = ?, telephone = ?,"
+ "register_time = ? WHERE id = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getPassword());
pstmt.setString(3, user.getTelephone());
pstmt.setTimestamp(4, new Timestamp(user.getRegisterTime().getTime()));
pstmt.setInt(5, user.getId());
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
(6)编写测试更新用户记录的方法
@Test
public void testUpdate() {
UserDao userdao = new UserDaoImpl();
User user = userdao.findById(1);
System.out.println("更新前:" + user);
user.setTelephone("11111000111");
user.setUsername("芜湖");
int count = userdao.update(user);
if (count > 0) {
System.out.println("更新成功!!!");
System.out.println("更新后:" + userdao.findById(1));
} else {
System.out.println("What a pity!更新失败!!!");
}
}
运行效果如下
(7)编写按ID查询用户记录的方法
@Override
public User findById(int id) {
User user = null;
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT * FROM t_user WHERE id = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setTelephone(rs.getString("telephone"));
user.setRegisterTime(rs.getTimestamp("register_time"));
}
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return user;
}
(8)编写测试按ID查询用户记录的方法
@Test
public void testFindById() {
User user = dao.findById(1);
System.out.println("用户名:" + user.getUsername());
System.out.println("密码:" + user.getPassword());
System.out.println("电话:" + user.getTelephone());
System.out.println("注册时间:" + user.getRegisterTime());
}
运行效果如下
(9)编写查询所有用户记录的方法
@Override
public List<User> findAll() {
List<User> users = new ArrayList<User>();
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT * FROM t_user";
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setTelephone(rs.getString("telephone"));
user.setRegisterTime(rs.getTimestamp("register_time"));
users.add(user);
}
rs.close();
stmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return users;
}
(10)编写测试查询所有用户记录的方法
@Test
public void testFindAll() {
List<User> users = dao.findAll();
for (User user : users) {
System.out.println(user);
}
}
运行效果如下
(11)编写用户登录的方法
@Override
public User login(String username, String password) {
User user = null;
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT * FROM t_user WHERE username = ? and password = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setTelephone(rs.getString("telephone"));
user.setRegisterTime(rs.getTimestamp("register_time"));
}
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return user;
}
(12)编写测试用户登录的方法
@Test
public void testLogin() {
String username, password;
username = "admin";
password = "admin";
User user = dao.login(username, password);
if (user != null) {
System.out.println("用户名与密码正确,登录成功!!!");
} else {
System.out.println("What a pity,登录失败!用户名或密码输入错误!!!");
}
}
运行效果如下
(13)编写判断用户名是否存在的方法
@Override
public boolean isUsernameExisted(String username) {
boolean existed = false;
Connection conn = ConnectionManager.getConnection();
String strSQL = "SELECT * FROM t_user WHERE username = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, username);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
existed = true;
}
pstmt.close();
rs.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
} finally {
ConnectionManager.closeConnection(conn);
}
return existed;
}
(14)编写测试判断用户名是否存在的方法
@Test
public void testIsUsernameExisted() {
String username = "张三丰";
boolean result = dao.isUsernameExisted(username);
if (result) {
System.out.println("温馨提示:用户名[" + username + "]已存在,不可用此名注册!!!");
} else {
System.out.println("温馨提示:用户名[" + username + "]不存在,可用此名注册");
}
}
运行效果如下