创建数据访问接口实现类
- 创建学校数据访问接口实现类
package net.xd.student.dao.impl;
import net.xd.student.bean.College;
import net.xd.student.dao.CollegeDao;
import net.xd.student.dbutil.ConnectionManager;
import java.sql.*;
/**
* 学校数据访问接口实现类
*/
public class CollegeDaoImpl implements CollegeDao {
/**
* 按id查找学校
* @param id
* @return 学校对象
*/
@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.setStarTime(rs.getTimestamp("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=?,address=?,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.getStarTime().getTime()));
pstmt.setString(4,college.getTelephone());
pstmt.setString(5,college.getEmail());
pstmt.setString(6,college.getAddress());
pstmt.setString(7,college.getProfile());
pstmt.setInt(8,college.getId());
//5.执行SQL，返回更新记录数
count=pstmt.executeUpdate();
//6.关闭预备语句对象
pstmt.close();
}catch (SQLException e){
e.printStackTrace();
}finally {
//关闭数据库连接
ConnectionManager.closeConnection(conn);
}
//返回更新记录数
return count;
}
}
- 对CollegeDaoImpl进行单元测试
package net.xd.student.test;
import net.xd.student.bean.College;
import net.xd.student.dao.CollegeDao;
import net.xd.student.dao.impl.CollegeDaoImpl;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
/**
* 测试学校数据访问接口实现类
*/
public class TestCollegeDaolmpl {
//创建学校数据访问接口对象
CollegeDao dao=new CollegeDaoImpl();
@Before
public void beforeTest(){
System.out.println("温馨提示：单元测绘开始咯~");
}
@After
public void afterTest(){
System.out.println("温馨提示：单元测绘结束咯~");
}
@Test
public void testFindById(){
//调用学校数据访问对象的查找方法，获取学校对象
College college=dao.findById(1);
//输出学校信息
System.out.println("校名："+college.getName());
System.out.println("校长："+college.getPresident());
System.out.println("地址："+college.getAddress());
System.out.println("邮箱："+college.getEmail());
System.out.println("电话："+college.getTelephone());
}
@Test
public void testUpdate(){
//调用学校数据访问对象的查询方法
College college=dao.findById(1);
//输出原校长
System.out.println("原校长："+college.getPresident());
//修改学校信息
college.setPresident("朴智旻");
//调用学校数据访问对象的更新方法
int count =dao.update(college);
//判断是否更新成功
if(count>0){
System.out.println("学校记录更新成功！");
System.out.println("新校长"+dao.findById(1).getPresident());
}else{
System.out.println("学校记录更新失败!");
}
}
}
注意：将单元测试JUnit4添加到类路径，才能使用测试注解符@Test
将鼠标移到@Test,按+组合键，弹出快捷菜单，选择第一项Add ‘JUnit4‘ to classpath
- 在每个单元测试之前都要执行的代码，我们可以将代码放在一个方法里面，但是要加上一个注解符@Before即可。
- 在每个单元测试之后都要执行的代码，我们可以将代码放在一个方法里面，但是加上一个注解符@After即可。
- 创建状态数据访问接口实现类
package net.xd.student.dao.impl;
import net.xd.student.bean.Status;
import net.xd.student.dao.StatusDao;
import net.xd.student.dbutil.ConnectionManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 状态数据访问接口实现类
*/
public class StatusDaolmpl 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;
}
}
- 对StatusDaoImpl进行单元测试
package net.xd.student.test;
import net.xd.student.bean.Status;
import net.xd.student.dao.StatusDao;
import net.xd.student.dao.impl.StatusDaolmpl;
import org.junit.Test;
/**
* 测试状态数据访问接口实现类
*/
public class TestStatusDaolmpl {
//声明状态数据访问对象
StatusDao dao =new StatusDaolmpl();
@Test
public void testFindById(){
//调用状态数据访问对象的程序方法
Status status=dao.findById(1);
//输出状态信息
System.out.println("作者："+status.getAuthor());
System.out.println("学校:"+status.getCollege());
System.out.println("版本:"+status.getVersion());
System.out.println("地址:"+status.getAddress());
System.out.println("电话:"+status.getTelephone());
System.out.println("邮箱:"+status.getEmail());
}
@Test
public void testUpdate(){
//调用状态数据访问对象的查询方法
Status status=dao.findById(1);
//修改状态对象的属性
status.setAuthor("金泰亨");
status.setTelephone("13782460380");
status.setEmail("jintaiheng@163.com");
//调用状态数据访问对象的更新方法
int count =dao.update(status);
//判断状态更新是否成功
if (count>0){
System.out.println("状态记录更新成功！");
System.out.println(dao.findById(1));
}else{
System.out.println("状态记录更新失败！");
}
}
}
- 学生数据访问接口实现类StudentDaoImpl
package net.xd.student.dao.impl;
import net.xd.student.bean.Student;
import net.xd.student.dao.StudentDao;
import net.xd.student.dbutil.ConnectionManager;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
/**
* 学生数据访问接口实现类
*/
public class StudentDaolmpl 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.
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) {
e.printStackTrace();
}finally{
ConnectionManager.closeConnection(conn);
}
//返回删除记录数
return count;
}
/**
* 按班级删除学生记录
* @param clazz
* @return 删除记录数
*/
@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) {
e.printStackTrace();
}finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
/**
* 按系部删除学生记录
* @param department
* @return 删除记录数
*/
@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) {
e.printStackTrace();
}finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
/**
* 更新学生记录
* @param student
* @return 更新记录数
*/
@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) {
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<Student> findByName(String name) {
//声明学生列表
List<Student> students=new ArrayList<Student>();
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);
}
rs.close();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}finally {
ConnectionManager.closeConnection(conn);
}
//返回学生列表
return students;
}
/**
* 按班级查询学生记录
* @param clazz
* @return 学生列表
*/
@Override
public List<Student> findByClass(String clazz) {
//声明学生列表
List<Student> students=new ArrayList<Student>();
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);
}
rs.close();
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>();
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);
}
rs.close();
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>();
Connection conn=ConnectionManager.getConnection();
//2,定义SQL字符串
String strSQL="select * from t_student";
try{
//3.创建预备语句对象
Statement stmt=conn.createStatement();
//5.执行SQL，返回结果集
ResultSet rs=stmt.executeQuery(strSQL);
//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);
}
rs.close();
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<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 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<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 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<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;
}
}
- 对StudentDaoImpl进行单元测试
package net.xd.student.test;
import net.xd.student.bean.Student;
import net.xd.student.dao.StudentDao;
import net.xd.student.dao.impl.StudentDaolmpl;
import org.junit.Test;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
public class TestStudentDaolmpl {
//定义学生数据访问对象
StudentDao dao=new StudentDaolmpl();
@Test
public void testInsert(){
//创建学生对象
Student student =new Student();
//设置学生对象属性
student.setId("19242075");
student.setName("邢巅");
student.setSex("女");
student.setAge(19);
student.setDepartment("信息工程学院");
student.setClazz("2019数据1班");
student.setTelephone("13782470380");
//调用学生数据访问对象的插入方法
int count=dao.insert(student);
//判断学生记录是否插入成功
if (count>0){
System.out.println("恭喜，学生记录插入成功！");
System.out.println(dao.findById(student.getId()));
}else{
System.out.println("遗憾，学生记录插入失败！");
}
}
@Test
public void testDeleteById(){
String id ="19242075";
//调用学生数据访问对象的按id删除方法
int count =dao.deleteById(id);
//判断学生记录是否删除成功
if(count>0){
System.out.println("恭喜，学生记录删除成功");
}else{
System.out.println("遗憾，学生记录删除失败！");
}
}
@Test
public void testDeleteByClass(){
String clazz="2019小教3班";
//调用学生数据访问对象的按班级删除方法
int count=dao.deleteByClass(clazz);
if(count>0){
System.out.println("恭喜，["+clazz+"]学生记录删除成功！");
}else{
System.out.println("遗憾,["+clazz+"]学生记录删除失败！");
}
}
@Test
public void testFindByName(){
String name ="李";
//调用学生数据访问对象的按姓名操作方法
List<Student> students=dao.findByName(name);
//判断列表里是否有元素
if(students.size()>0){
//通过增强for循环遍历学生列表
for(Student student:students){
System.out.println(student);
}
}else{
System.out.println("温馨提示：查无此人！");
}
}
@Test
public void testFindAll(){
//调用学生数据访问对象的查找全部方法
List<Student> students=dao.findAll();
for(Student student: students){
System.out.println(student);
}
}
@Test
public void testFindRowsBySex(){
//调用学生数据访问对象的按性别统计人数方法
Vector rows=dao.findRowsBySex();
//获取向量的迭代器
Iterator iterator=rows.iterator();
//遍历迭代器
while (iterator.hasNext()){
System.out.println(iterator.next());
}
}
@Test
public void testDeleteByDepartment(){
//调用学生数据访问对象的按系部删除方法
String department="国际学院";
//调用学生数据访问对象的按班级删除方法
int count=dao.deleteByDepartment(department);
if(count>0){
System.out.println("恭喜，["+department+"]学生记录删除成功！");
}else{
System.out.println("遗憾,["+department+"]学生记录删除失败！");
}
}
@Test
public void testUpdate() {
//更新学生记录
String id="19204091";
Student students=dao.findById(id);
students.setName("闵玧其");
students.setTelephone("19822228888");
int count =dao.update(students);
if (count>0){
System.out.println("学生记录更新成功！");
System.out.println(dao.findById(id));
}else{
System.out.println("学生记录更新失败！");
}
}
@Test
public void testFindById(){
String id ="19204188";
//调用学生数据访问对象的按id操作方法
Student student=dao.findById(id);
//输出学生信息
System.out.println("姓名:"+student.getName());
System.out.println("性别:"+student.getSex());
System.out.println("班级:"+student.getClazz());
System.out.println("系部:"+student.getDepartment());
System.out.println("电话:"+student.getTelephone());
}
@Test
public void testFindByClass(){
String clazz ="2019营销1班";
//调用学生数据访问对象的按姓名操作方法
List<Student> students=dao.findByClass(clazz);
//判断列表里是否有元素
if(students.size()>0){
//通过增强for循环遍历学生列表
for(Student student:students){
System.out.println(student);
}
}else{
System.out.println("温馨提示：查无此人！");
}
}
@Test
public void testByDepartment(){
String department ="商学院";
//调用学生数据访问对象的按姓名操作方法
List<Student> students=dao.findByDepartment(department);
//判断列表里是否有元素
if(students.size()>0){
//通过增强for循环遍历学生列表
for(Student student:students){
System.out.println(student);
}
}else{
System.out.println("温馨提示：查无此人！");
}
}
@Test
public void testFindRowsByClass(){
//调用学生数据访问对象的按班级统计人数方法
Vector rows=dao.findRowsByClass();
//获取向量的迭代器
Iterator iterator=rows.iterator();
//遍历迭代器
while (iterator.hasNext()){
System.out.println(iterator.next());
}
}
@Test
public void FindRowsByDepartment(){
//调用学生数据访问对象的按系部统计人数方法
Vector rows=dao.findRowsByDepartment();
//获取向量的迭代器
Iterator iterator=rows.iterator();
//遍历迭代器
while (iterator.hasNext()){
System.out.println(iterator.next());
}
}
}
- 创建用户数据访问接口实现类
package net.xd.student.dao.impl;
import net.xd.student.bean.User;
import net.xd.student.dao.UserDao;
import net.xd.student.dbutil.ConnectionManager;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* 用户数据访问接口实现类
*/
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(?, ?, ?, ?)";
//不允许用户表插入两条用户名相同的记录
if (!isUsernameExisted(user.getUsername())){
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;
}
@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{
PreparedStatement pstmt=conn.prepareStatement(strSQL);
pstmt.setInt(1,id);
count=pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
@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;
}
@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"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
ConnectionManager.closeConnection(conn);
}
return user;
}
@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) {
e.printStackTrace();
}finally {
ConnectionManager.closeConnection(conn);
}
return users;
}
@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"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
ConnectionManager.closeConnection(conn);
}
return user;
}
@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) {
e.printStackTrace();
}finally {
ConnectionManager.closeConnection(conn);
}
return existed;
}
}
- 创建用户数据访问接口实现类
package net.xd.student.test;
import net.xd.student.bean.User;
import net.xd.student.dao.UserDao;
import net.xd.student.dao.impl.UserDaoImpl;
import org.junit.Test;
import java.util.Date;
import java.util.List;
public class TestUserDaoImpl {
//定义用户数据访问对象
UserDao dao= new UserDaoImpl();
@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());
}
@Test
public void testLogin(){
String Username,password;
Username="xingdian";
password="111111";
//调用用户数据访问对象的登录方法
User user=dao.login(Username,password);
//判断用户登录是否成功
if (user!=null){
System.out.println("恭喜，用户名与密码正确，登录成功！");
}else{
System.out.println("遗憾，用户名与密码错误，登录失败");
}
}
@Test
public void testIsUsernameExistted(){
//定义用户名
String username="金硕珍";
boolean result=dao.isUsernameExisted(username);
if (result){
System.out.println("温馨提示：["+username+"]已存在，不可用此名注册！");
}else{
System.out.println("温馨提示：["+username+"]不存在，可用此名注册！");
}
}
@Test
public void testInsert(){
User user=new User();
user.setUsername("郑号锡");
user.setPassword("123456");
user.setTelephone("15256789999");
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("遗憾，遗憾记录插入失败！");
}
}
@Test
public void testDeleteById(){
int id =1;
//调用学生数据访问对象的按id删除方法
int count =dao.deleteById(id);
//判断学生记录是否删除成功
if(count>0){
System.out.println("恭喜，学生记录删除成功");
}else{
System.out.println("遗憾，学生记录删除失败！");
}
}
@Test
public void testUpdate(){
//更新学生记录
int id=2;
User user=dao.findById(id);
user.setUsername("田柾国");
user.setTelephone("19822428888");
int count =dao.update(user);
if (count>0){
System.out.println("学生记录更新成功！");
System.out.println(dao.findById(id));
}else{
System.out.println("学生记录更新失败！");
}
}
@Test
public void testFindAll(){
List<User> users=dao.findAll();
for(User user: users){
System.out.println(user);
}
}
}
- 对UserDaoImpl进行测试
package net.xd.student.test;
import net.xd.student.bean.User;
import net.xd.student.dao.UserDao;
import net.xd.student.dao.impl.UserDaoImpl;
import org.junit.Test;
import java.util.Date;
import java.util.List;
public class TestUserDaoImpl {
//定义用户数据访问对象
UserDao dao= new UserDaoImpl();
@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());
}
@Test
public void testLogin(){
String Username,password;
Username="xingdian";
password="111111";
//调用用户数据访问对象的登录方法
User user=dao.login(Username,password);
//判断用户登录是否成功
if (user!=null){
System.out.println("恭喜，用户名与密码正确，登录成功！");
}else{
System.out.println("遗憾，用户名与密码错误，登录失败");
}
}
@Test
public void testIsUsernameExistted(){
//定义用户名
String username="金硕珍";
boolean result=dao.isUsernameExisted(username);
if (result){
System.out.println("温馨提示：["+username+"]已存在，不可用此名注册！");
}else{
System.out.println("温馨提示：["+username+"]不存在，可用此名注册！");
}
}
@Test
public void testInsert(){
User user=new User();
user.setUsername("郑号锡");
user.setPassword("123456");
user.setTelephone("15256789999");
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("遗憾，遗憾记录插入失败！");
}
}
@Test
public void testDeleteById(){
int id =1;
//调用学生数据访问对象的按id删除方法
int count =dao.deleteById(id);
//判断学生记录是否删除成功
if(count>0){
System.out.println("恭喜，学生记录删除成功");
}else{
System.out.println("遗憾，学生记录删除失败！");
}
}
@Test
public void testUpdate(){
//更新学生记录
int id=2;
User user=dao.findById(id);
user.setUsername("田柾国");
user.setTelephone("19822428888");
int count =dao.update(user);
if (count>0){
System.out.println("学生记录更新成功！");
System.out.println(dao.findById(id));
}else{
System.out.println("学生记录更新失败！");
}
}
@Test
public void testFindAll(){
List<User> users=dao.findAll();
for(User user: users){
System.out.println(user);
}
}
}
269
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
