这节课的开始老师让我们用百度脑图输出了整个学生信息管理系统的框架,为制作学生信息管理系统建立一个大体的思路。
为这个信息管理系统创建一个数据库,随便的录入数据即可。
创建一个新的Java项目
加入文件夹
加入一些实体类
然后为这些类添加代码
package net.zhk.student.bean;
/**
- 包名:net.zhk.student.bean
- 类名:College
- 简述:学校实体
- 作者:张鸿科
- 日期:2019.6.17
*/
import java.util.Date;
public class College {
/**
* 学校标识符
/
private int id;
/*
* 学校名称
/
private String name;
/*
* 校长
/
private String president;
/*
* 建校时间
/
private Date startTime;
/*
* 联系电话
/
private String telephone;
/*
* 电子邮箱
/
private String email;
/*
* 通信地址
/
private String address;
/*
* 学校简介
*/
private String profile;
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 getPresident() {
return president;
}
public void setPresident(String president) {
this.president = president;
}
public Date getStartTime() {
return startTime;
}
public void setStartTime(Date startTime) {
this.startTime = startTime;
}
public String getTelephone() {
return telephone;
}
public void setTelephone(String telephone) {
this.telephone = telephone;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getProfile() {
return profile;
}
public void setProfile(String profile) {
this.profile = profile;
}
@Override
public String toString() {
return "College{" +
"id=" + id +
", name='" + name + '\'' +
", president='" + president + '\'' +
", startTime=" + startTime +
", telephone='" + telephone + '\'' +
", email='" + email + '\'' +
", address='" + address + '\'' +
", profile='" + profile + '\'' +
'}';
}
}
package net.zhk.student.bean;
/**
-
功能:status实体类
-
作者:张鸿科
-
日期:2019.6.18
/
public class Status {
/*- 标识符
/
private int id;
/* - 校名
/
private String college;
/* - 版本
/
private String version;
/* - 作者
/
private String author;
/* - 联系电话
/
private String telephone;
/* - 通讯地址
/
private String address;
/* - 电子邮箱
*/
private String email;
public int getId() {
return id;
}public void setId(int id) {
this.id = id;
}public String getCollege() {
return college;
}public void setCollege(String college) {
this.college = college;
}public String getVersion() {
return version;
}public void setVersion(String version) {
this.version = version;
}public String getAuthor() {
return author;
}public void setAuthor(String author) {
this.author = author;
}public String getTelephone() {
return telephone;
}public void setTelephone(String address) {
this.address = address;
}public String getAddress() {
return address;
}public void setAddress(String address) {
this.address = address;
}public String getEmail() {
return email;
}public void setEmail(String email) {
this.email = email;
}@Override
public String toString() {
return “status[” +
“id=” + id +
“, college=’” + college + ‘’’ +
“, version=’” + version + ‘’’ +
“, author=’” + author + ‘’’ +
“, telephone=’” + telephone + ‘’’ +
“, address=’” + address + ‘’’ +
“, email=’” + email +’’’ +
‘}’;
}
}
package net.zhk.student.bean; - 标识符
/**
- 功能:Student实体类
- 作者:张鸿科
- 日期:2019.6.18
*/
public class Student {
/**
* 学号
/
private String id;
/*
* 姓名
/
private String name;
/*
* 性别
/
private String sex;
/*
* 年龄
/
private int age;
/*
* 系部
/
private String departemnt;
/*
* 班级
/
private String clazz;
/*
* 联系电话
*/
private String telephone;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setNaem(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex() {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getDepartemnt() {
return departemnt;
}
public void setDepartemnt(String departemnt) {
this.departemnt = departemnt;
}
public String getClazz() {
return clazz;
}
public void setClazz(String clazz) {
this.clazz = clazz;
}
public String getTelephone() {
return telephone;
}
public void setTelephone(String telephone) {
this.telephone = telephone;
}
@Override
public String toString() {
return "student{" +
"id=" + id + '\'' +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", age='" + age +
", department='" + departemnt +'\'' +
", clazz='" + clazz + '\'' +
", telephone='" + telephone + '\'' +
'}';
}
}
package net.zhk.student.bean;
import java.util.Date;
public class User {
/**
* 用户标识符
/
private int id;
/*
* 用户名
/
private String username;
/*
* 密码
/
private String password;
/*
* 联系电话
/
private String telephone;
/*
* 注册时间
*/
private Date registerTime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getTelephone() {
return telephone;
}
public void setTelephone(String telephone) {
this.telephone = telephone;
}
public Date getRegisterTime() {
return registerTime;
}
public void setRegisterTime(Date registerTime) {
this.registerTime = registerTime;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", telephone='" + telephone + '\'' +
", registerTime='" + registerTime +
'}';
}
}
数据库连接管理
package net.zhk.student.dbutil;
import javax.swing.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionManager {
/**
* 数据库驱动程序
/
private static final String DRIVER = “com.mysql.jdbc.Driver”;
/*
* 数据库统一资源标识符
/
private static final String URL = “jdbc:mysql://localhost:3306/student”;
/*
* 数据库用户名
/
private static final String USERNAME = “root”;
/*
* 数据库密码
*/
private static final String PASSWORD = “1”;
/**
* 私有化构造方法,拒绝实例化
*/
private ConnectionManager() {
}
/**
* 获得数据库连接
*
* @return 数据库连接对象
*/
public static Connection getConnection() {
// 定义数据库连接
Connection conn = null;
try {
// 安装数据库驱动程序
Class.forName(DRIVER);
// 获得数据库连接
conn = DriverManager.getConnection(URL
+ "?useUnicode=true&characterEncoding=UTF8", USERNAME, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
// 返回数据库连接
return conn;
}
/**
* 关闭数据库连接
*
* @param conn
*/
public static void closeConnection(Connection conn) {
// 判断数据库连接是否为空
if (conn != null) {
// 判断数据库连接是否关闭
try {
if (!conn.isClosed()) {
// 关闭数据库连接
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 测试数据库连接是否成功
*
* @param args
*/
public static void main(String[] args) {
// 获得数据库连接
Connection conn = getConnection();
// 判断是否连接成功
if (conn != null) {
JOptionPane.showMessageDialog(null, "恭喜,数据库连接成功!");
} else {
JOptionPane.showMessageDialog(null, "遗憾,数据库连接失败!");
}
// 关闭数据库连接
closeConnection(conn);
}
}
测试连接成功
创建接口
数据访问接口实现类
package net.zhk.student.dao.impl;
import net.zhk.student.bean.College;
import net.zhk.student.dao.CollegeDao;
import net.zhk.student.dbutil.ConnectionManager;
import java.sql.*;
/**
- 功能:学校数据访问接口实现类
- 作者:张鸿科
- 日期:2019.6.18
*/
public class CollegeDaoImpl implements CollegeDao {
@Override
public College findById(int id) {
// 声明学校对象
College college = null;
//1.获取数据库连接
Connection conn = ConnectionManager.getConnection();
//2.定义SQL字符串
String strSQL = "select * from t_college where id = ?";
try {
//3.创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
//4.设置占位符
pstmt.setInt(1, id);
//5.执行SQL,返回结果集
ResultSet rs = pstmt.executeQuery();
//6.判断结果集是否有记录
if (rs.next()) {
//实例化学校对象
college = new College();
//利用当前记录字段值去设置学校对象的属性
college.setId(rs.getInt("id"));
college.setName(rs.getString("name"));
college.setPresident(rs.getString("president"));
college.setStartTime(rs.getDate("start_time"));
college.setTelephone(rs.getString("telephone"));
college.setEmail(rs.getString("email"));
college.setAddress(rs.getString("address"));
college.setProfile(rs.getString("profile"));
}
//7.关闭预备语句对象
pstmt.close();
//8.关闭结果集对象
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}
// 返回学校对象
return college;
}
/**
* 更新学校方法
*
* @param college
* @return
*/
@Override
public int update(College college) {
//更新记录数
int count = 0;
//1.获取数据库连接
Connection conn = ConnectionManager.getConnection();
//2.定义SQL字符串
String strSQL = "update t_college set name = ?, president = ?, start_time = ?,"
+ " telephone = ?, email = ?, profile = ? where id = ?";
try {
//3.创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
//4.设置占位符的值
pstmt.setString(1,college.getName());
pstmt.setString(2,college.getPresident());
pstmt.setTimestamp(3, new Timestamp(college.getStartTime().getTime()));
pstmt.setString(4, college.getTelephone());
pstmt.setString(5, college.getEmail());
pstmt.setString(6, college.getProfile());
pstmt.setInt(7, college.getId());
//5.执行SQL, 返回更新记录值
count = pstmt.executeUpdate();
//6.关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭数据库
ConnectionManager.closeConnection(conn);
}
//返回更新记录数
return count;
}
}
创建net.hw.student.test包,在里面创建测试类TestCollegeDaoImpl:
package net.zhk.student.test;
import net.zhk.student.bean.College;
import net.zhk.student.dao.impl.CollegeDaoImpl;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import net.zhk.student.dao.CollegeDao;
/**
- 功能:测试学校数据访问接口实现类
- 作者:张鸿科
- 日期:2019.6.18
*/
public class TestCollegeDaoImpl {
@Before
public void beforeTest() {
System.out.println(“单元测试开始了!”);
}
@Test
public void testFindById() {
CollegeDao dao = new CollegeDaoImpl();
College college = dao.findById(1);
System.out.println(college);
}
@Test
public void testUpdate() {
CollegeDao dao = new CollegeDaoImpl();
College college = dao.findById(1);
college.setPresident("王丽红");
dao.update(college);
college = dao.findById(1);
System.out.println(college);
}
@After
public void afterTest() {
System.out.println("单元测试结束!");
}
}
对他进行测试
状态数据访问接口实现类StatusDaoImpl
package net.hw.student.dao.impl;
import net.zhk.student.bean.Status;
import net.zhk.student.dao.StatusDao;
import net.zhk.student.dbutil.ConnectionManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
-
功能:状态数据访问接口实现类
-
作者:张鸿科
-
日期:2019年6月18日
*/
public class StatusDaoImpl implements StatusDao {@Override
public Status findById(int id) {
// 声明状态对象
Status status = null;// 1. 获取数据库连接对象 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "SELECT * FROM t_status WHERE id = ?"; try { // 3. 创建预备语句对象 PreparedStatement pstmt = conn.prepareStatement(strSQL); // 4. 设置占位符的值 pstmt.setInt(1, id); // 5. 执行SQL查询,返回结果集 ResultSet rs = pstmt.executeQuery(); // 6. 判断结果集是否有记录 if (rs.next()) { // 实例化状态 status = new Status(); // 利用当前记录字段值去设置状态对象的属性 status.setId(rs.getInt("id")); status.setCollege(rs.getString("college")); status.setVersion(rs.getString("version")); status.setAuthor(rs.getString("author")); status.setTelephone(rs.getString("telephone")); status.setAddress(rs.getString("address")); status.setEmail(rs.getString("email")); } // 7. 关闭预备语句对象 pstmt.close(); // 8. 关闭结果集对象 rs.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回状态对象 return status;
}
@Override
public int update(Status status) {
// 定义更新记录数
int count = 0;// 1. 获得数据库连接 Connection conn = ConnectionManager.getConnection(); // 2. 定义SQL字符串 String strSQL = "UPDATE t_status SET college = ?, version = ?, author = ?," + " telephone = ?, address = ?, email = ? WHERE id = ?"; try { // 3. 创建预备语句对象 PreparedStatement pstmt = conn.prepareStatement(strSQL); // 4. 设置占位符的值 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()); // 5. 执行更新操作,更新记录 count = pstmt.executeUpdate(); // 6. 关闭预备语句对象 pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回更新记录数 return count;
}
}
在net.zhk.student.test包里创建测试类TestStatusDaoImpl:
运行结果如下:
(3)学生数据访问接口实现类StudentDaoImpl
**package net.zhk.student.dao.impl;
import net.zhk.student.bean.Student;
import net.zhk.student.dao.StudentDao;
import net.zhk.student.dbutil.ConnectionManager;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
/**
-
功能:学生数据访问接口实现类
-
作者:张鸿科
-
日期:2019年6月18日
/
public class StudentDaoImpl implements StudentDao {
/*-
插入学生记录
-
@param student
-
@return 插入记录数
*/
@Override
public int insert(Student student) {
// 定义插入记录数
int count = 0;// 1. 获得数据库连接
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “insert into t_student (id, name, sex, age, department, class, telephone)”
+ " values (?, ?, ?, ?, ?, ?, ?)";
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
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());
// 5. 执行SQL,返回插入记录数
count = pstmt.executeUpdate();
// 6. 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回插入记录数
return count;
}
/**
-
按学号删除学生记录
-
@param id
-
@return 删除记录数
*/
@Override
public int deleteById(String id) {
// 定义删除记录数
int count = 0;// 1. 获取数据库连接、
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “delete from t_student where id = ?”;
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
pstmt.setString(1, id);
// 5. 执行SQL,返回删除记录数
count = pstmt.executeUpdate();
// 6. 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回删除记录数
return count;
}
/**
-
按班级删除学生记录
-
@param clazz
-
@return 删除记录数
*/
@Override
public int deleteByClass(String clazz) {
// 定义删除记录数
int count = 0;// 1. 获取数据库连接
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “delete from t_student where class = ?”;
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
pstmt.setString(1, clazz);
// 5. 执行SQL,返回删除记录数
count = pstmt.executeUpdate();
// 6. 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回删除记录数
return count;
}
/**
-
按系部删除学生记录
-
@param department
-
@return 删除记录数
*/
@Override
public int deleteByDepartment(String department) {
// 定义删除记录数
int count = 0;// 1. 获得数据库连接
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “delete from t_student where department = ?”;
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
pstmt.setString(1, department);
// 5. 执行SQL,返回删除记录数
count = pstmt.executeUpdate();
// 6. 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回删除记录数
return count;
}
/**
-
更新学生记录
-
@param student
-
@return 更新记录数
*/
@Override
public int update(Student student) {
// 定义更新记录数
int count = 0;// 1. 获得数据库连接
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “update t_student set name = ?, sex = ?, age = ?,”
+ " department = ?, class = ?, telephone = ? where id = ?";
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
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());
// 5. 执行SQL,返回更新记录数
count = pstmt.executeUpdate();
// 6. 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回更新记录数
return count;
}
/**
-
按学号查询学生记录
-
@param id
-
@return 学生实体
*/
@Override
public Student findById(String id) {
// 声明学生对象
Student student = null;// 1. 获取数据库连接对象
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “select * from t_student where id = ?”;
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
pstmt.setString(1, id);
// 5. 执行SQL,返回结果集
ResultSet rs = pstmt.executeQuery();
// 6. 判断结果集是否有记录
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) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回学生对象
return student;
}
/**
-
按姓名查询学生记录
-
@param name
-
@return 学生列表
*/
@Override
public List findByName(String name) {
// 声明学生列表
List students = new ArrayList();// 1. 获取数据库连接对象
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “select * from t_student where name like ?”;
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
pstmt.setString(1, name + “%”);
// 5. 执行SQL,返回结果集
ResultSet rs = pstmt.executeQuery();
// 6. 遍历结果集
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);
}
// 7. 关闭结果集
rs.close();
// 8. 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回学生列表
return students;
}
/**
-
按班级查询学生记录
-
@param clazz
-
@return 学生列表
*/
@Override
public List findByClass(String clazz) {
// 声明学生列表
List students = new ArrayList();// 1. 获取数据库连接对象
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “select * from t_student where class like ?”;
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
pstmt.setString(1, clazz + “%”);
// 5. 执行SQL,返回结果集
ResultSet rs = pstmt.executeQuery();
// 6. 遍历结果集
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);
}
// 7. 关闭结果集
rs.close();
// 8. 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回学生列表
return students;
}
/**
-
按系部查询学生记录
-
@param department
-
@return 学生列表
*/
@Override
public List findByDepartment(String department) {
// 声明学生列表
List students = new ArrayList();// 1. 获取数据库连接对象
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “select * from t_student where department like ?”;
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
pstmt.setString(1, department + “%”);
// 5. 执行SQL,返回结果集
ResultSet rs = pstmt.executeQuery();
// 6. 遍历结果集
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);
}
// 7. 关闭结果集
rs.close();
// 8. 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回学生列表
return students;
}
/**
-
查询全部学生记录
-
@return 学生列表
*/
@Override
public List findAll() {
// 声明学生列表
List students = new ArrayList();// 1. 获取数据库连接对象
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “select * from t_student”;
try {
// 3. 创建语句对象
Statement stmt = conn.createStatement();
// 4. 执行SQL,返回结果集
ResultSet rs = stmt.executeQuery(strSQL);
// 5. 遍历结果集
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);
}
// 6. 关闭结果集
rs.close();
// 7. 关闭语句对象
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回学生列表
return students;
}
/**
-
按性别统计学生人数
-
@return 统计结果向量
*/
@Override
public Vector findRowsBySex() {
// 定义行集向量
Vector rows = new Vector();// 1. 获取数据库连接对象
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “select sex as ‘性别’, count(*) as ‘人数’” + " from t_student group by sex order by sex desc";
try {
// 3. 创建语句对象
Statement stmt = conn.createStatement();
// 4. 执行SQL,返回结果集
ResultSet rs = stmt.executeQuery(strSQL);
// 5. 遍历结果集
while (rs.next()) {
// 定义当前行向量
Vector currentRow = new Vector();
// 利用当前记录字段值设置当前行向量的元素值
currentRow.addElement(rs.getString(“性别”));
currentRow.addElement(rs.getInt(“人数”) + “”);
// 将当前行向量添加到行集向量
rows.addElement(currentRow);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回行集向量
return rows;
}
/**
-
按班级统计学生人数
-
@return 统计结果向量
*/
@Override
public Vector findRowsByClass() {
// 定义行集向量
Vector rows = new Vector();// 1. 获取数据库连接对象
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “select class as ‘班级’, count(*) as ‘人数’” + " from t_student group by class order by class desc";
try {
// 3. 创建语句对象
Statement stmt = conn.createStatement();
// 4. 执行SQL,返回结果集
ResultSet rs = stmt.executeQuery(strSQL);
// 5. 遍历结果集
while (rs.next()) {
// 定义当前行向量
Vector currentRow = new Vector();
// 利用当前记录字段值设置当前行向量的元素值
currentRow.addElement(rs.getString(“班级”));
currentRow.addElement(rs.getInt(“人数”) + “”);
// 将当前行向量添加到行集向量
rows.addElement(currentRow);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回行集向量
return rows;
}
/**
-
按系部统计学生人数
-
@return 统计结果向量
*/
@Override
public Vector findRowsByDepartment() {
// 定义行集向量
Vector rows = new Vector();// 1. 获取数据库连接对象
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = “select department as ‘系部’, count(*) as ‘人数’”
+ " from t_student group by department order by department desc";
try {
// 3. 创建语句对象
Statement stmt = conn.createStatement();
// 4. 执行SQL,返回结果集
ResultSet rs = stmt.executeQuery(strSQL);
// 5. 遍历结果集
while (rs.next()) {
// 定义当前行向量
Vector currentRow = new Vector();
// 利用当前记录字段值设置当前行向量的元素值
currentRow.addElement(rs.getString(“系部”));
currentRow.addElement(rs.getInt(“人数”) + “”);
// 将当前行向量添加到行集向量
rows.addElement(currentRow);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}// 返回行集向量
return rows;
}
}
-
在net.zhk.student.test包里创建测试类TestStudentDaoImpl:
**
运行结果
编写测试方法testFindByName():
用户数据访问接口实现类UserDaoImplpackage
package net.zhk.student.dao.impl;
import net.zhk.student.bean.Status;
import net.zhk.student.bean.User;
import net.zhk.student.dao.UserDao;
import net.zhk.student.dbutil.ConnectionManager;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
- 功能:用户数据访问接口实现类
- 作者:张鸿科
- 日期:2019.6.19
*/
public class UserDaoImpl implements UserDao {
/**
* 插入用户记录
*
* @param user
* @return 插入记录数
*/
@Override
public int insert(User user) {
// 定义插入记录数
int count = 0;
// 1. 获得数据库连接
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = "insert into t_user (username, password, telephone, register_time)"
+ " values (?, ?, ?, ?)";
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getPassword());
pstmt.setString(3,user.getTelephone());
pstmt.setTimestamp(4, new Timestamp(user.getRegisterTime().getTime()));
// 5. 执行SQL,返回插入记录数
count = pstmt.executeUpdate();
// 6. 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}
// 返回插入记录数
return count;
}
/**
* 按id删除用户记录
*
* @param id
* @return 删除记录数
*/
@Override
public int deleteById(int id) {
// 定义删除记录数
int count = 0;
// 1. 获取数据库连接
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = "delete from t_user where id = ?";
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
pstmt.setInt(1, id);
// 5. 执行SQL,返回删除记录数
count = pstmt.executeUpdate();
// 6. 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}
// 返回删除记录数
return count;
}
/**
* 更新用户记录
*
* @param user
* @return 更新记录数
*/
@Override
public int update(User user) {
// 定义更新记录数
int count = 0;
// 1. 获得数据库连接
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = "update t_user set username = ?, password = ?, telephone = ?,"
+ " register_time = ? where id = ?";
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
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());
// 5. 执行SQL,返回更新记录数
count = pstmt.executeUpdate();
// 6. 关闭预备语句对象
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}
// 返回更新记录数
return count;
}
/**
* 按id查询用户
*
* @param id
* @return 用户实体
*/
@Override
public User findById(int id) {
// 声明用户对象
User user = null;
// 1. 获取数据库连接对象
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = "select * from t_user where id = ?";
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
pstmt.setInt(1, id);
// 5. 执行SQL,返回结果集
ResultSet rs = pstmt.executeQuery();
// 6. 判断结果集是否有记录
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"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}
// 返回用户对象
return user;
}
/**
* 查询所有用户
*
* @return 用户列表
*/
@Override
public List<User> findAll() {
// 声明用户列表
List<User> users = new ArrayList<User>();
// 1. 获取数据库连接对象
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = "select * from t_user";
try {
// 3. 创建语句对象
Statement stmt = conn.createStatement();
// 4. 执行SQL,返回结果集
ResultSet rs = stmt.executeQuery(strSQL);
// 5. 遍历结果集
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);
}
// 6. 关闭结果集
rs.close();
// 7. 关闭语句对象
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}
// 返回用户列表
return users;
}
/**
* 用户登录
*
* @param username
* @param password
* @return 登录用户实体
*/
@Override
public User login(String username, String password) {
// 声明用户对象
User user = null;
// 1. 获取数据库连接
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = "select * from t_user where username = ? and password = ?";
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
pstmt.setString(1, username);
pstmt.setString(2, password);
// 5. 执行SQL,返回结果集
ResultSet rs = pstmt.executeQuery();
// 6. 判断结果集是否有记录
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"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}
// 返回用户对象
return user;
}
}
在net.hw.student.test包里创建测试类TestUserDaoImpl:
编写测试方法testFindById():package net.zhk.student.test;
import net.zhk.student.bean.User;
import net.zhk.student.dao.UserDao;
import net.zhk.student.dao.impl.UserDaoImpl;
import org.junit.Test;
/**
-
功能:测试用户数据访问接口实现类
-
作者:张鸿科
-
日期:2019.6.19
*/
public class TestUserDaoImpl {
@Test
public void testFindById() {
UserDao dao = new UserDaoImpl();
User user = dao.findById(1);
System.out.println(user);}
@Test
public void testLogin() {
UserDao dao = new UserDaoImpl();
String username, password;username = "admin"; password = "12345"; User user = dao.login(username, password); if (user != null) { System.out.println("恭喜,登陆成功!"); } else { System.out.println("遗憾,用户名或密码错误,登陆失败!"); }
}
}
编写测试方法testLogin():运行该测试方法,结果如下
服务接口
package net.zhk.student.service;
import net.zhk.student.bean.College;
/**
- 功能:学校服务接口
- 作者:张鸿科
- 日期:2019.6.19
*/
public interface CollegeService {
College findCollegeById(int id);
int updateCollege(College college);
}
状态服务接口StatusService
package net.zhk.student.service;
import net.zhk.student.bean.Status;
/**
- 功能:状态服务接口
- 作者:张鸿科
- 日期:2019.6.19
*/
public interface StatusService {
Status findStatusById(int id);
int updateStatus(Status status);
}
学生服务接口Student
Servicepackage net.zhk.student.service;
import java.util.List;
import java.util.Vector;
import net.zhk.student.bean.Student;
/**
- 功能:学生服务接口
- 作者:张鸿科
- 日期:2019.6.19
*/
public interface StudentService {
int addStudent(Student student);
int deleteStudentById(String id);
int deleteStudentsByClass(String clazz);
int deleteStudentsByDepartment(String department);
int updateStudent(Student student);
Student findStudentById(String id);
List findStudentsByName(String name);
List findStudentsByClass(String clazz);
List findStudentsByDepartment(String department);
List findAllStudents();
Vector findRowsBySex();
Vector findRowsByClass();
Vector findRowsByDepartment();
}
用户服务接口UserService
package net.zhk.student.service;
import java.util.List;
import net.zhk.student.bean.User;
/**
- 功能:用户服务接口
- 作者:张鸿科
- 日期:2019.6.19
*/
public interface UserService {
int addUser(User user);
int deleteUserById(int id);
int updateUser(User user);
User findUserById(int id);
List findAllUsers();
User login(String username, String password);
}
服务接口实现类
学校服务接口实现类CollegeServiceImpl
package net.zhk.student.service.impl;
import net.zhk.student.bean.College;
import net.zhk.student.dao.CollegeDao;
import net.zhk.student.dao.impl.CollegeDaoImpl;
import net.zhk.student.service.CollegeService;
/**
-
功能:学校服务接口实现类
-
作者:张鸿科
-
日期:2019.6.19
/
public class CollegeServiceImpl implements CollegeService {
/*- 声明学校数据方位对象
*/
private CollegeDao collegeDao = new CollegeDaoImpl();
@Override
public College findCollegeById(int id) {
return collegeDao.findById(id);
}@Override
public int updateCollege(College college) {
return collegeDao.update(college);
}
}
状态服务接口实现类StatusServiceImpl
package net.zhk.student.service.impl; - 声明学校数据方位对象
import net.zhk.student.bean.Status;
import net.zhk.student.dao.StatusDao;
import net.zhk.student.dao.impl.StatusDaompl;
import net.zhk.student.service.StatusService;
/**
-
功能:状态服务接口实现类
-
作者:张鸿科
-
日期:2019.6.19
/
public class StatusServiceImpl implements StatusService{
/*- 声明状态数据访问对象
*/
private StatusDao statusDao = new StatusDaompl();
@Override
public Status findStatusById(int id) {
return statusDao.findById(id);
}@Override
public int updateStatus(Status status) {
return statusDao.update(status);
}
}
学生服务接口实现类StudentServiceImpl
package net.zhk.student.service.impl; - 声明状态数据访问对象
import net.zhk.student.bean.Student;
import net.zhk.student.dao.StudentDao;
import net.zhk.student.dao.impl.StudentDaoImpl;
import net.zhk.student.service.StudentService;
import java.io.Reader;
import java.util.List;
import java.util.Vector;
/**
-
功能:学生服务接口实现类
-
作者:张鸿科
-
日期:2019.6.19
/
public class StudentServiceImpl implements StudentService {
/*- 声明学生数据访问对象
*/
private StudentDao studentDao = new StudentDaoImpl();
@Override
public int addStudent(Student student) {
return studentDao.insert(student);
}@Override
public int deleteStudentById(String id) {
return studentDao.deleteById(id);
}@Override
public int deleteStudentsByClass(String clazz) {
return studentDao.deleteByClass(clazz);
}@Override
public int deleteStudentsByDepartment(String department) {
return studentDao.deleteByDepartment(department);
}@Override
public List findAllStudents() {
return studentDao.findAll();
}@Override
public Vector findRowsByClass() {
return studentDao.findRowsByClass();
}@Override
public Vector findRowsByDepartment() {
return studentDao.findRowsByDepartment();
}@Override
public Vector findRowsBySex() {
return studentDao.findRowsBySex();
}@Override
public Student findStudentById(String id) {
return studentDao.findById(id);
}@Override
public List findStudentsByClass(String clazz) {
return studentDao.findByClass(clazz);
}@Override
public List findStudentsByDepartment(String department) {
return studentDao.findByDepartment(department);
}@Override
public List findStudentsByName(String name) {
return studentDao.findByName(name);
}@Override
public int updateStudent(Student student) {
return studentDao.update(student);
}
}
用户服务接口实现类UserServiceImpl - 声明学生数据访问对象
package net.zhk.student.service.impl;
import java.util.List;
import net.zhk.student.bean.User;
import net.zhk.student.dao.UserDao;
import net.zhk.student.dao.impl.UserDaoImpl;
import net.zhk.student.service.UserService;
/**
-
功能:用户服务接口实现类
-
作者:张鸿科
-
日期:2019.6.19
/
public class UserServiceImpl implements UserService{
/*- 声明用户数据访问对象
*/
private UserDao userDao = new UserDaoImpl();
@Override
public int addUser(User user) {
return userDao.insert(user);
}@Override
public int deleteUserById(int id) {
return userDao.deleteById(id);
}@Override
public List findAllUsers() {
return userDao.findAll();
}@Override
public User findUserById(int id) {
return userDao.findById(id);
}@Override
public User login(String username, String password) {
return userDao.login(username, password);
}@Override
public int updateUser(User user) {
return userDao.update(user);
}
}
应用程序类
package net.zhk.student.app; - 声明用户数据访问对象
import net.zhk.student.gui.LoginFrame;
import net.zhk.student.gui.MainFrame;
import net.zhk.student.gui.RegisterFrame;
/**
- 功能:应用程序类
-
存放全局数据
- 作者:张鸿科
- 日期:2019.6.20
/
public class Application {
/*- 登陆用户标识符
/
public static int id;
/* - 登陆用户名
/
public static String username;
/* - 登陆用户密码
/
public static String password;
/* - 登陆窗口
/
public static LoginFrame loginFrame;
/* - 主窗口
/
public static MainFrame mainFrame;
/* - 注册窗口
*/
public static RegisterFrame registerFrame;
- 登陆用户标识符
}
窗口界面类(JFrame)
创建net.zhk.student.gui包,在里面先创建三个界面类:LoginFrame、MainFrame与RegisterFrame。