java第三次实训
程序员文章站
2024-01-25 08:54:40
创建学校数据访问接口实现类创建访问接口类1.在CollegeDaolmpl中写代码1.1在test中创建测试类testCollegeDaoImpl1.1.2编写testUpdate方法2.在StausDaoImpl中插入一下代码2.1 测试TestStatusDaoImpl方法3. 在StudentDaoImpl中插入以下代码3.1 编写测试类TestStudentDaoImpl3.1.1. 先测试testInsert3.1.2.测试testDeleteById3.1.3.测试testDeleteByCla...
创建学校数据访问接口实现类
- 2.在StausDaoImpl中插入一下代码
- 3. 在StudentDaoImpl中插入以下代码
- 3.1 编写测试类TestStudentDaoImpl
- 3.1.1. 先测试testInsert
- 3.1.2.测试testDeleteById
- 3.1.3.测试testDeleteByClass
- 3.1.4.测试testFindByNam
- 3.1.5.测试 testFindAll
- 3.1.6.测试testFindRowsBySe
- 3.1.7.测试testDeleteByDepartment
- 3.1.8.测试testUpdate
- 3.1.9.测试testFindById
- 3.1.10测试testFindByClass
- 3.1.11.测试testFindByDepartment
- 3.1.12.测试testFindRowsByClass
- 3.1.13.测试testFindRowsByDepartment
- 4. 在UserDaolmpl 中插入以下代码
- 4.1 测试UserDaolmpl
创建下面几个类
19计应三班 贾杨庆
创建访问接口类
1.在CollegeDaolmpl中写代码
package net.jyq.student.dao.impl;
import net.jyq.student.bean.College;
import net.jyq.student.dao.CollegeDao;
import net.jyq.student.dbutil.ConnectionManager;
import java.sql.*;
public class CollegeDaolmpl implements CollegeDao {
@Override
public College findById(int id) {
//定义学校对象
College college=null;
//获取数据库连接
Connection conn= ConnectionManager.getConnection();
//定义sql字符串
String srtSQL="select * from t_college where id=?";
try {
PreparedStatement pstmt=conn.prepareStatement(srtSQL);
pstmt.setInt(1,id);
ResultSet rs=pstmt.executeQuery();
college =new College();
college.setId(rs.getInt("id"));
college.setName(rs.getString("president"));
college.setStartTime(rs.getTimestamp("start_time"));
college.setTelephone(rs.getString("telephone"));
college.setEmail(rs.getString("email"));
college.setAddress(rs.getString("address"));
college.setProfile(rs.getString("profile"));
pstmt.close();
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return college;
}
@Override
public int update(College college) {
int count=0;
Connection conn=ConnectionManager.getConnection();
String strSQL= "update t_college set name = ?, president = ?, start_time = ?,"
+ " telephone = ?, 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.getTelephone());
pstmt.setString(5,college.getEmail());
pstmt.setString(6,college.getAddress());
pstmt.setString(7,college.getProfile());
pstmt.setInt(8,college.getId());
count=pstmt.executeUpdate();
pstmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
}
1.1在test中创建测试类testCollegeDaoImpl
编写testFindById方法
package net.jyq.student.test;
import net.jyq.student.bean.College;
import net.jyq.student.bean.User;
import net.jyq.student.dao.CollegeDao;
import net.jyq.student.dao.impl.CollegeDaolmpl;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.Date;
public class TestCollegeDaoImpl {
//创建学校数据访问对象
CollegeDao dao=new CollegeDaolmpl();
@Before
public void beforeTest(){ System.out.println("温馨提示:单元测试开始了"); }
@After
public void afterTest(){ System.out.println("温馨提示:单元测试结束了"); }
@Test
public void testFindById(){
//调用学校数据访问接口对象的查询方法
College college=dao.findById(1);
if(college!=null) {
//输出学校信息
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());
}else {
System.out.println("没有信息");
}
}
运行结果
1.1.2编写testUpdate方法
@Test
public void testUpdate(){
//调用调用学校数据访问对象的查询方法
College college=dao.findById(1);
college.setPresident("王成新");
//调用学校数据访问为对象的更新方法
int count=dao.update(college);
//判断是否更新成功
if(count>0){
System.out.println("学校记录更新成功");
System.out.println("新校长:"+dao.findById(1).getPresident());
}else{
System.out.println("学校记录更新成功");
}
}
运行结果
2.在StausDaoImpl中插入一下代码
package net.jyq.student.dao.impl;
import net.jyq.student.bean.Status;
import net.jyq.student.dao.StatusDao;
import net.jyq.student.dbutil.ConnectionManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class StausDaoImpl 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("emali"));
}
// 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;
}
}
2.1 测试TestStatusDaoImpl方法
package net.jyq.student.test;
import net.jyq.student.bean.Status;
import net.jyq.student.dao.StatusDao;
import net.jyq.student.dao.impl.StausDaoImpl;
import org.junit.Test;
public class TestStatusDaoImpl {
//声明状态数据访问对象
StatusDao dao =new StausDaoImpl();
@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.getEmail());
}
}
运行结果
在StausDaolmpl中emali书写错误导致出错
出错的问题
运行结果
3. 在StudentDaoImpl中插入以下代码
package net.jyq.student.dao.impl;
import net.jyq.student.bean.Student;
import net.jyq.student.dao.StudentDao;
import net.jyq.student.dbutil.ConnectionManager;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
public class StudentDaoImpl implements StudentDao {
@Override
public int insert(Student student) {
//定义插入记录数
int count=0;
//获取数据库连接
Connection conn= ConnectionManager.getConnection();
//定义sql字符串
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());
//执行SQL返回插入的记录数
count=pstmt.executeUpdate();
pstmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
/**
* 按学号删除学生记录
* @param
* @return
*/
@Override
public int deleteById(String id) {
int count=0;
//获取数据库;链接
Connection conn=ConnectionManager.getConnection();
//定义sql字符串
String strSQL="delete from t_student where id=?";
//创建预备语句对象
try {
PreparedStatement pstmt =conn.prepareStatement(strSQL);
//设置占位符的值
pstmt.setString(1,id);
//执行SQL返回删除记录数
count=pstmt.executeUpdate();
//关闭预备语句对象
pstmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
//返回删除记录数
return count;
}
/**
* 按班级删除记录数
* @param clazz
* @return
*/
@Override
public int deleteByClass(String clazz) {
int count=0;
//获取数据库链接
Connection conn=ConnectionManager.getConnection();
//定义sql语句
String strSQL="delete from t_student where class=?";
try {
//创建预备语句
PreparedStatement pstmt=conn.prepareStatement(strSQL);
//设置占位符的值
pstmt.setString(1,clazz);
//执行SQL。返回删除语句
count=pstmt.executeUpdate();
//关闭数据库
pstmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}
return count;
}
/**
* 按系部删除学生记录
* @param department
* @return
*/
@Override
public int deleteByDepartment(String department) {
int count=0;
//获取数据库链接
Connection conn=ConnectionManager.getConnection();
//定义SQL语句
String strSQL="delete from t-student where department=?";
try {
//创建预备语句
PreparedStatement pstmt=conn.prepareStatement(strSQL);
//设置占位符的值
pstmt.setString(1,department);
//执行sql返回删除记录数
count=pstmt.executeUpdate();
//关闭预备语句对象
pstmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}
//返回删除记录
return count;
}
/**
* 更新学生记录
* @param student
* @return
*/
@Override
public int update(Student student) {
int count=0;
//创建数据库链接
Connection conn=ConnectionManager.getConnection();
//定义sql语句
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());
//执行SQL语句,返回更新数
count=pstmt.executeUpdate();
//关闭预备对象
pstmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//关闭数据库链接
ConnectionManager.closeConnection(conn);
}
//返回更新记录数
return count;
}
/**
* 按学号查询学生记录
* @param id
* @return
*/
@Override
public Student findById(String id) {
//声明学生对象
Student student=null;
//获取数据链接
Connection conn=ConnectionManager.getConnection();
//定义sql语句
String strSQL="select * from t_student where id=?";
//创建预备语句
try {
PreparedStatement pstmt=conn.prepareStatement(strSQL);
//设置占位符的值
pstmt.setString(1,id);
//执行SQL,返回结果集
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 throwables) {
throwables.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();
//定义SQL 语句
String strSQL="select * from t_student where name like ?";
try {
//创建预备语句
PreparedStatement pstmt=conn.prepareStatement(strSQL);
//设置占位符的值
pstmt.setString(1,name+"%");
//执行SQL语句返回结果值
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 throwables) {
throwables.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();
//定义SQL语句
String strSQL="select * from t_student where class like ?";
try {
//创建预备语句对象
PreparedStatement pstmt=conn.prepareStatement(strSQL);
//设置占位符的值
pstmt.setString(1,clazz+"%");
//执行sql返回结果值
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 throwables) {
throwables.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();
//定义SQL 语句
String strSQL="select * from t_student where department like ?";
try {
//创建预备语句对象
PreparedStatement pstmt=conn.prepareStatement(strSQL);
//设置占位符的值
pstmt.setString(1,department+"%");
//执行SQL语句返回结果值
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 throwables) {
throwables.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return students;
}
/**
* 查询全部学生记录
* @return
*/
@Override
public List<Student> findByAll() {
List<Student>students=new ArrayList<Student>();
//获取数据库链接
Connection conn= ConnectionManager.getConnection();
//定义SQL 语句
String strSQL="select * from t_student";
try {
//创建语句对象
Statement stmt=conn.createStatement();
//执行SQL返回结果值
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 throwables) {
throwables.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return students;
}
@Override
public Vector findRowsBySex() {
//定义行集向量
Vector rows=new Vector();
//获取数据库链接
Connection conn=ConnectionManager.getConnection();
//定义sql语句
String strSQL="select sex as '性别', count(*) as '人数'"
+ " from t_student group by sex order by sex desc";
try {
//创建语句对象
Statement stmt=conn.createStatement();
//执行sql返回结果集
ResultSet rs=stmt.executeQuery(strSQL);
while(rs.next()){
Vector<String> currentRow=new Vector();
currentRow.addElement(rs.getString("性别"));
currentRow.addElement(rs.getInt("人数")+"");
rows.addElement(currentRow);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//将当前行向量添加到行集向量
ConnectionManager.closeConnection(conn);
}
return rows;
}
/**
*按班级
* @return
*/
@Override
public Vector findRowsByClass() {
//定义行集向量
Vector rows=new Vector();
//获取数据库链接
Connection conn=ConnectionManager.getConnection();
//定义sql语句
String strSQL="select class as '班级', count(*) as '人数'"
+ " from t_student group by class order by class desc";
try {
//创建语句对象
Statement stmt=conn.createStatement();
//执行sql返回结果集
ResultSet rs=stmt.executeQuery(strSQL);
while(rs.next()){
//定义当前向量
Vector<String> currentRow=new Vector();
currentRow.addElement(rs.getString("班级"));
currentRow.addElement(rs.getInt("人数")+"");
rows.addElement(currentRow);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//将当前行向量添加到行集向量
ConnectionManager.closeConnection(conn);
}
return rows;
}
@Override
public Vector findRowsByDepartment() {
//定义行集向量
Vector rows=new Vector();
//获取数据库链接
Connection conn=ConnectionManager.getConnection();
//定义sql语句
String strSQL="select department as '系部', count(*) as '人数'"
+ " from t_student group by department order by department desc";
try {
//创建语句对象
Statement stmt=conn.createStatement();
//执行sql返回结果集
ResultSet rs=stmt.executeQuery(strSQL);
while(rs.next()){
//定义当前向量
Vector<String> currentRow=new Vector();
currentRow.addElement(rs.getString("系部"));
currentRow.addElement(rs.getInt("人数")+"");
//将当前行向量添加到行集向量
rows.addElement(currentRow);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//关闭数据库链接
ConnectionManager.closeConnection(conn);
}
return rows;
}
}
3.1 编写测试类TestStudentDaoImpl
3.1.1. 先测试testInsert
import net.jyq.student.dao.StudentDao;
import net.jyq.student.dao.impl.StudentDaoImpl;
import org.junit.Test;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
public class TestStudentDaoImpl {
//定义学生数据访问对象
StudentDao dao=new StudentDaoImpl();
@Test
public void testInsert(){
Student student=new Student();
student.setId("10060309");
student.setName("张晓松");
student.setSex("女");
student.setAge(20);
student.setDepartment("商学院");
student.setClazz("19级市场营销2班");
student.setTelephone("12345678910");
int count=dao.insert(student);
if(count>0){
System.out.println("恭喜,学生记录插入!");
System.out.println(dao.findById(student.getId()));
}else{
System.out.println("遗憾,学生记录插入失败");
}
}
运行结果
3.1.2.测试testDeleteById
@Test
public void testDeleteById(){
//按学号来删除
String id="10050303";
int count=dao.deleteById(id);
if(count>0){
System.out.println("恭喜,删除成功");
}else{
System.out.println("遗憾,删除失败");
}
}
运行结果
3.1.3.测试testDeleteByClass
@Test
public void testDeleteByClass(){
String clazz="19英教1班";
//调用学生数据访问对象的按班级删除方法
int count=dao.deleteByClass(clazz);
if(count>0){
System.out.println("恭喜,删除成功");
}else{
System.out.println("遗憾,删除失败");
}
}
运行结果在之前数据库被删除过所以删除失败
3.1.4.测试testFindByNam
@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("没有此人");
}
}
运行结果
3.1.5.测试 testFindAll
@Test
public void testFindAll(){
//调用学生数据访问对象的全部查找方法
List<Student> students=dao.findByAll();
for(Student student:students){
System.out.println(student);
}
}
运行结果
3.1.6.测试testFindRowsBySe
@Test
public void testFindRowsBySex(){
//调用学生数据访问对象的按性别统计人数方法
Vector rows=dao.findRowsBySex();
//获取向量的迭代器
Iterator iterator=rows.iterator();
while(iterator.hasNext()){
System.out.println(iterator.next());
}
}
运行结果
3.1.7.测试testDeleteByDepartment
@Test
public void testDeleteByDepartment(){
//按部门来删除
String department="信息工程学院";
int count=dao.deleteByDepartment(department);
if(count>0){
System.out.println("恭喜,删除成功");
}else{
System.out.println("遗憾,删除失败");
}
}
运行结果
3.1.8.测试testUpdate
@Test
public void testUpdate(){
Student student =new Student();
student.setId("10060301");
student.setName("黄忠文");
int count=dao.update(student);
if(count>0){
System.out.println("恭喜,更新成功");
}else{
System.out.println("遗憾,更新失败");
}
}
运行结果
3.1.9.测试testFindById
@Test
public void testFindById(){
String id="10080302";
List<Student> students = Collections.singletonList(dao.findById(id));
if(students.size()>0){
for(Student student:students){
System.out.println(student);
}
}else{
System.out.println("没有此人");
}
}
运行结果
3.1.10测试testFindByClass
@Test
public void testFindByClass(){
String clazz="19级英教1班";
List<Student> students = Collections.singletonList(dao.findById(clazz));
if(students.size()>0){
for(Student student:students){
System.out.println(student);
}
}else{
System.out.println("没有此人");
}
}
运行结果
3.1.11.测试testFindByDepartment
@Test
public void testFindByDepartment(){
String department="信息工程学院";
List<Student> students = Collections.singletonList(dao.findById(department));
if(students.size()>0){
for(Student student:students){
System.out.println(student);
}
}else{
System.out.println("没有此人");
}
}
运行结果
3.1.12.测试testFindRowsByClass
@Test
public void testFindRowsByClass(){
//调用学生数据访问对象的按班级统计人数方法
Vector rows=dao.findRowsByClass();
//获取向量的迭代器
Iterator iterator=rows.iterator();
while(iterator.hasNext()){
System.out.println(iterator.next());
}
}
运行结果
3.1.13.测试testFindRowsByDepartment
@Test
public void testFindRowsByDepartment(){
//调用学生数据访问对象的按部门统计人数方法
Vector rows=dao.findRowsByDepartment();
//获取向量的迭代器
Iterator iterator=rows.iterator();
while(iterator.hasNext()){
System.out.println(iterator.next());
}
}
运行结果
4. 在UserDaolmpl 中插入以下代码
package net.jyq.student.dao.impl;
import net.jyq.student.bean.User;
import net.jyq.student.dao.UserDao;
import net.jyq.student.dbutil.ConnectionManager;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserDaolmpl implements UserDao {
@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;
}
/**
* 按id删除用户记录
*
* @param id
* @return 删除记录数
*/
@Override
public int deleteById(String 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.setString(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;
}
@Override
public boolean isUsernameExisted(String username) {
// 定义存在与否变量
boolean existed = false;
// 1. 获取数据库连接
Connection conn = ConnectionManager.getConnection();
// 2. 定义SQL字符串
String strSQL = "select * from t_user where username = ?";
try {
// 3. 创建预备语句对象
PreparedStatement pstmt = conn.prepareStatement(strSQL);
// 4. 设置占位符的值
pstmt.setString(1, username);
// 5. 执行SQL,返回结果集
ResultSet rs = pstmt.executeQuery();
// 6. 判断结果集是否有记录
if (rs.next()) {
existed = true;
}
// 7. 关闭预备语句对象
pstmt.close();
// 8. 关闭结果集对象
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
ConnectionManager.closeConnection(conn);
}
// 返回存在与否变量
return existed;
}
}
4.1 测试UserDaolmpl
4.1.1.测试TestUserDaoImp
package net.jyq.student.test;
import net.jyq.student.bean.Student;
import net.jyq.student.bean.User;
import net.jyq.student.dao.UserDao;
import net.jyq.student.dao.impl.UserDaolmpl;
import org.junit.Test;
import java.util.Date;
import java.util.List;
public class TestUserDaoImpl {
//定义用户数据访问对象
UserDao dao=new UserDaolmpl();
@Test
public void testFindById(){
//调用用户数据访问对象的查找方法
User user=dao.findById(1);
//输出用户信息
System.out.println("用户名:"+user.getUsername());
System.out.println("密码"+user.getPassword());
System.out.println("电话"+user.getPassword());
System.out.println("注册时间"+user.getRegisterTime());
}
运行结果
4.1.2.测试testLogin
@Test
public void testLogin(){
String username;
String password;
username="admin";
password="admin";
//调用用户数据访问对象的登录方法
User user =dao.login(username,password);
//判断用户登录是否成功
if(user!=null){
System.out.println("恭喜,用户名与密码正确,登录成功");
}else{
System.out.println("遗憾,用户名或密码错误,登录失败");
}
}
运行结果
4.1.2.测试testInsert
@Test
public void testInsert(){
User user=new User();
user.setUsername("鸿俊");
user.setPassword("123456");
user.setTelephone("123456878910");
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("遗憾,用户记录插入失败");
}
}
运行结果
4.1.3.测试testDeleteById
@Test
public void testDeleteById(){
String id="2";
int count=dao.deleteById(id);
if(count>0){
System.out.println("恭喜,删除成功");
}else{
System.out.println("遗憾,删除失败");
}
}
运行结果
4.1.4.测试testUpdat
@Test
public void testUpdate(){
User user = new User();
user.setId(5);
user.setUsername("王丽");
user.setPassword("123456");
user.setTelephone("12343425678");
user.setRegisterTime(new Date());
int count = dao.update(user);
if (count > 0){
System.out.println("恭喜,更新学生记录成功");
System.out.println(user);
}else{
System.out.println("遗憾,更新数据失败");
}
}
运行结果
4.1.5.测试 testFindAll
@Test
public void testFindAll(){
List<User> users=dao.findAll();
for(User user:users){
System.out.println(user);
}
}
运行结果
本文地址:https://blog.csdn.net/Nflyingj/article/details/107162348