package net.zd.student.dao.impl;
import net.zd.student.bean.Student;
import net.zd.student.dao.StudentDao;
import net.zd.student.dbutil.ConnectionManager;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
/**
- 功能：学生数据访问接口实现类
- 作者：赵丹
- 日期：2019年6月28日
*/
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, class1, 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.getClass1());
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 class1
* @return 删除记录数
*/
@Override
public int deleteByClass(String class1) {
//定义删除记录数
int count = 0;
//1、获取数据库连接
Connection conn = ConnectionManager.getConnection();
//2、定义SQL字符串
String strSQL = "delete from t_student where class1 = ?";
try {
//3、创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
//4、设置占位符的值
pstmt.setString(1, class1);
//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 = ?, class1 = ?, 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.getClass1());
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.setClass1(rs.getString("class1"));
student.setTelephone(rs.getString("telephone"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭预备语句对象
ConnectionManager.closeConnection(conn);
}
//返回学生对象
return student;
}
/**
* 按姓名查询学生记录
*
* @param name
* @return 学生列表
*/
@Override
public List<Student> findByName(String name) {
//声明学生列表
List<Student> students = new ArrayList<Student>();
//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.setClass1(rs.getString("class1"));
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 class1
* @return 学生列表
*/
@Override
public List<Student> findByClass(String class1) {
//声明学生列表
List<Student> students = new ArrayList<Student>();
//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, class1 + "%");
//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.setClass1(rs.getString("class1"));
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<Student> findByDepartment(String department) {
//声明学生列表
List<Student> students = new ArrayList<Student>();
//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.setClass1(rs.getString("class1"));
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<Student> findAll() {
//声明学生列表
List<Student> students = new ArrayList<Student>();
//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.setClass1(rs.getString("class1"));
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 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<String> 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 sreSQL = "selecr 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(sreSQL);
//5、遍历结果集
while (rs.next()) {
//定义当前行向量
Vector<String> 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;
}
@Override
public Vector findRowsByClass() {
return null;
}
}
在test包内创建一个TestStudentDaoImpl 类
则运行结果为
再测试能不能删除student表的这条记录
数据库则没有该学号学生的记录
测试用户数据访问接口实现类UserDaoImpl
package net.zd.student.dao.impl;
import net.zd.student.bean.User;
import net.zd.student.dao.UserDao;
import net.zd.student.dbutil.ConnectionManager;
import org.junit.Test;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
- 功能：用户数据访问接口实现类
- 作者：赵丹
- 日期:2019年6月29日
*/
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.getUsename());
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.getUsename());
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.setUsename(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.setUsename(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setTelephone(rs.getString("telephone"));
user.setRegisterTime(rs.getTimestamp("register_time"));
//将实体添加到用户列表
users.add(user);
}
} 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.setUsename(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;
}
}
在test包内创建一个类TestUserDaoImpl
运行结果为
测试用户是否登陆成功
测试结果
插入一条用户数据
插入的结果为
在UserDao添加一个判断用户名是否存在的方法，如果存在，返回真，否则返回假
测试类TestDaoImpl里添加测试方法testIsUsernameExisted()：
在src目录下添加net.zd.student.service
在包内添加以下四个类
为四个接口添加所所需要的业务逻辑
Sercice内新建一个impl包
实现服务接口
第一页个接口Collegeserciceimpl
在net.hw.student.test包里创建测试类TestCollegeServiceImpl
第二个接口statusserviceimpl
在net.hw.student.test包里创建测试类TestStatusServiceImpl：
运行结果
第三个接口StudentServiceImpl
package net.zd.student.service.impl;
import net.zd.student.bean.Student;
import net.zd.student.dao.StudentDao;
import net.zd.student.dao.impl.StudentDaoImpl;
import net.zd.student.service.StudentService;
import java.util.List;
import java.util.Vector;
/**
-
功能：学生服务接口实现类
-
作者：赵丹
-
日期：2019年6月29日
/
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 class1) {
return studentDao.deleteByClass(class1);
}@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 class1) {
return studentDao.findByClass(class1);
}@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);
}
} - 声明学生数据访问对象
在net.hw.student.test包里创建测试类TestStudentServiceImpl
第四个接口UserServiceImpl
package net.zd.student.service.impl;
import net.zd.student.bean.User;
import net.zd.student.dao.UserDao;
import net.zd.student.dao.impl.UserDaoImpl;
import net.zd.student.service.UserService;
import java.util.List;
/**
-
功能：用户服务接口实现类
-
作者：赵丹
-
日期：2019年6月29日
/
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);
}@Override
public boolean isUsernameExisted(String username) {
return userDao.isUsernameExisted(username);
}
}
在net.hw.student.test包里创建测试类TestUserServiceImpl：
package net.zd.student.test; - 声明用户数据访问对象
import net.zd.student.bean.User;
import net.zd.student.service.UserService;
import net.zd.student.service.impl.UserServiceImpl;
import org.junit.Test;
/**
- 功能：测试用户服务接口实现类
- 作者：赵丹
- 日期：2019年6月29日
*/
public class TestUserServiceImpl {
@Test
public void testLogin() {
UserService service = new UserServiceImpl();
String username, password;
username = “王平”;
password = “33333”;
User user = service.login(username, password);
if (user != null){
System.out.println(“恭喜，用户名与密码正确，登陆成功！”);
}else {
System.out.println(“遗憾，用户名或密码错误，登录失败！”);
}
}
}
测试结果为
3260
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
