八、项目开发实训步骤
(七)创建数据访问接口实现类
在net.hw.student.dao
包里创建impl
子包
1、创建学校数据访问接口实体类
在net.hw.student.dao
包里创建CollegeDaoImpl
类 实现CollegeDao
接口
(1)编写按标识符查询学校记录方法和更新学校记录方法
package net. huawei. student. dao. impl ;
import net. huawei. student. bean. College ;
import net. huawei. student. dao. CollegeDao ;
import net. huawei. student. dbutil. ConnectionManager ;
import java. sql. * ;
public class CollegeDaoImpl implements CollegeDao {
@Override
public College findById ( int id) {
College college = null ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "SELECT * FROM t_college WHERE id = ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
pstmt. setInt ( 1 , id) ;
ResultSet rs = pstmt. executeQuery ( ) ;
if ( rs. next ( ) ) {
college = new College ( ) ;
college. setId ( rs. getInt ( "id" ) ) ;
college. setName ( rs. getString ( "name" ) ) ;
college. setPresident ( rs. getString ( "president" ) ) ;
college. setStartTime ( rs. getTimestamp ( "stsrt_time" ) ) ;
college. setEmail ( rs. getString ( "email" ) ) ;
college. setAddress ( rs. getString ( "address" ) ) ;
college. setProfile ( rs. getString ( "profile" ) ) ;
}
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
} finally {
}
ConnectionManager . closeConnection ( conn) ;
return college;
}
@Override
public int update ( College college) {
int count = 0 ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "UPDATE t_college SET name = ?, president = ?,start_time = ?, email = ?, address = ?, profile = ? WHERE id = ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
pstmt. setString ( 1 , college. getName ( ) ) ;
pstmt. setString ( 2 , college. getPresident ( ) ) ;
pstmt. setTimestamp ( 3 , new Timestamp ( college. getStartTime ( ) . getTime ( ) ) ) ;
pstmt. setString ( 4 , college. getEmail ( ) ) ;
pstmt. setString ( 5 , college. getAddress ( ) ) ;
pstmt. setString ( 6 , college. getProfile ( ) ) ;
pstmt. setInt ( 7 , college. getId ( ) ) ;
count = pstmt. executeUpdate ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return count;
}
}
1_、测试学校数据访问接口实现类
在test
创建net.huawei.student.dao.imp
包 net.huawei.student.dao.imp
包创建TestCollegeDaoImpl
(1)编写测试按标识符查询学校记录方法
package net. huawei. student. dao. impl ;
import net. huawei. student. bean. College ;
import net. huawei. student. dao. CollegeDao ;
import org. junit. Test ;
public class TestCollegeDaoImpl {
@Test
public void testFindById ( ) {
int id = 9 ;
CollegeDao collegeDao = new CollegeDaoImpl ( ) ;
College college = collegeDao. findById ( id) ;
if ( college != null ) {
System . out. println ( "标识符:" + college. getId ( ) ) ;
System . out. println ( "学校名称:" + college. getName ( ) ) ;
System . out. println ( "校长:" + college. getPresident ( ) ) ;
System . out. println ( "建校时间:" + college. getStartTime ( ) ) ;
System . out. println ( "电子邮箱:" + college. getEmail ( ) ) ;
System . out. println ( "通信地址:" + college. getAddress ( ) ) ;
System . out. println ( "学校概况:" + college. getProfile ( ) ) ;
} else {
System . out. println ( "标识符[" + id + "]的学校记录不存在~" ) ;
}
}
}
运行,查看结果 修改标识符变量值
(2)编写测试更新学校记录方法
@Test
private void testUpdate ( ) {
CollegeDao collegeDao = new CollegeDaoImpl ( ) ;
College college = collegeDao. findById ( 1 ) ;
System . out. println ( "更新前:" + college) ;
college. setName ( "泸职院" ) ;
college. setPresident ( "么么哒" ) ;
college. setProfile ( "" ) ;
int count = collegeDao. update ( college) ;
if ( count > 0 ) {
System . out. println ( "恭喜,学习记录更新成功" ) ;
System . out. println ( "更新后:" + collegeDao. findById ( 1 ) ) ;
} else {
System . out. println ( "遗憾,学校记录更新失败" ) ;
}
}
运行结果
2、创建状态数据访问接口实体类
在net.hw.student.dao
包里创建StatusDaoImpl
类 -
(1)编写按标识符查询状态记录方法
package net. huawei. student. dao. impl ;
import net. huawei. student. bean. Status ;
import net. huawei. student. dao. StatusDao ;
import net. huawei. student. dbutil. ConnectionManager ;
import java. sql. Connection ;
import java. sql. PreparedStatement ;
import java. sql. ResultSet ;
import java. sql. SQLException ;
public class StatusDaoImpl implements StatusDao {
@Override
public Status findById ( int id) {
Status status = null ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "SELECT * FROM t_status WHERE id = ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
pstmt. setInt ( 1 , id) ;
ResultSet rs = pstmt. executeQuery ( ) ;
if ( rs. next ( ) ) {
status = new Status ( ) ;
status. setId ( rs. getInt ( "id" ) ) ;
status. setCollege ( rs. getString ( "college" ) ) ;
status. setVersion ( rs. getString ( "version" ) ) ;
status. setAuthor ( rs. getString ( "author" ) ) ;
status. setTelephone ( rs. getString ( "telephone" ) ) ;
status. setAddress ( rs. getString ( "address" ) ) ;
status. setEmail ( rs. getString ( "email" ) ) ;
}
pstmt. close ( ) ;
rs. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return status;
}
(2)编写更新状态记录方法
@Override
public int update ( Status status) {
int count = 1 ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strsQL = "update t_status set college - ?, version - ?, author = ?,+telephone = ?, address = ?, email = ? where id = ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strsQL) ;
pstmt. setString ( 1 , status. getCollege ( ) ) ;
pstmt. setString ( 2 , status. getVersion ( ) ) ;
pstmt. setString ( 3 , status. getAuthor ( ) ) ;
pstmt. setString ( 4 , status. getTelephone ( ) ) ;
pstmt. setString ( 5 , status. getAddress ( ) ) ;
pstmt . setString ( 6 , status. getEmail ( ) ) ;
pstmt. setInt ( 7 , status. getId ( ) ) ;
count = pstmt. executeUpdate ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . out. println ( e. getMessage ( ) ) ;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return count;
}
}
2_,测试状态数据访问接口实现类
net.huawei.student.dao.imp
包创建TestStatusDaoImpl
(1)编写测试按标识符查询状态记录方法
package net. huawei. student. dao. impl ;
import net. huawei. student. bean. Status ;
import net. huawei. student. dao. StatusDao ;
import org. junit. Test ;
public class TestStatusDaoImpl {
StatusDao dao = new StatusDaoImpl ( ) ;
@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 ( ) ) ;
}
(2)编写测试更新状态记录方法
@Test
public void testUpdate ( ) {
Status status = dao. findById ( 1 ) ;
status. setAuthor ( "无剑" ) ;
status. setTelephone ( "13845456780" ) ;
status. setEmail ( "ghf1213" ) ;
int count = dao. update ( status) ;
if ( count > 0 ) {
System . out. println ( "状态记录更新成功!" ) ;
System . out. println ( dao. findById ( 1 ) ) ;
} else {
System . out. println ( "状态记录更新失败!" ) ;
}
}
}
查看运行结果
3,创建学生数据访问接口实现类
在net.hw.student.dao
包里创建StudentDaoImpl
类
(1)编写插入学生记录的方法
package net. huawei. student. dao. impl ;
import net. huawei. student. bean. Student ;
import net. huawei. student. dao. StudentDao ;
import net. huawei. 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 ( ) ;
String strSQL = "insert into t_student (id,name,sex, age, department, class, telephone)"
+ "values (?, ?,?,?,?, ?,?)" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
pstmt. setString ( 1 , student. getId ( ) ) ;
pstmt. setString ( 2 , student. getName ( ) ) ;
pstmt. setString ( 3 , student. getSex ( ) ) ;
pstmt. setInt ( 4 , student. getAge ( ) ) ;
pstmt. setString ( 5 , student. getDepartment ( ) ) ;
pstmt. setString ( 6 , student. getClazz ( ) ) ;
pstmt. setString ( 7 , student. getTelephone ( ) ) ;
count = pstmt. executeUpdate ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return count;
}
(2)编写按id删除学生记录的方法
@Override
public int deleteById ( String id) {
int count = 0 ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strsQL = "delete from t_student where id = ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strsQL) ;
pstmt. setString ( 1 , id) ;
count = pstmt. executeUpdate ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return count;
}
(3)编写按班级删除学生记录的方法
@Override
public int deleteByClass ( String clazz) {
int count = 0 ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "delete from t_student where class = ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
pstmt. setString ( 1 , clazz) ;
count = pstmt. executeUpdate ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return count;
}
(4)编写按系部删除学生记录的方法
@Override
public int deleteByDepartment ( String department) {
int count = 6 ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "delete from t_student where department - ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return count;
}
(5)定义更新学生记录的方法
@Override
public int update ( Student student) {
int count = 0 ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "update t_student set name = ?, sex = ?, age = ?,"
+ " department = ?, class = ?, telephone = ? where id = ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
pstmt. setString ( 1 , student. getName ( ) ) ;
pstmt. setString ( 2 , student. getSex ( ) ) ;
pstmt. setInt ( 3 , student. getAge ( ) ) ;
pstmt. setString ( 4 , student. getDepartment ( ) ) ;
pstmt. setString ( 5 , student. getClazz ( ) ) ;
pstmt. setString ( 6 , student. getTelephone ( ) ) ;
pstmt. setString ( 7 , student. getId ( ) ) ;
count = pstmt. executeUpdate ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return count;
}
(6)编写按id查询学生记录的方法
@Override
public Student findById ( String id) {
Student student = null ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "select * from t_student where id =?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
pstmt. setString ( 1 , id) ;
ResultSet rs = pstmt. executeQuery ( ) ;
if ( rs. next ( ) ) {
student = new Student ( ) ;
student. setId ( rs. getString ( "id" ) ) ;
student. setName ( rs. getString ( "name" ) ) ;
student. setSex ( rs. getString ( "sex" ) ) ;
student. setAge ( rs. getInt ( "age" ) ) ;
student. setDepartment ( rs. getString ( "department" ) ) ;
student. setClazz ( rs. getString ( "class" ) ) ;
student. setTelephone ( rs. getString ( "telephone" ) ) ;
}
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return student;
}
(7)编写按姓名查询学生记录的方法
@Override
public List < Student > findByName ( String name) {
List < Student > students = new ArrayList < > ( ) ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "select * from t_student where name like ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
pstmt. setString ( 1 , name + "%" ) ;
ResultSet rs = pstmt. executeQuery ( ) ;
while ( rs. next ( ) ) {
Student student = new Student ( ) ;
student. setId ( rs. getString ( "id" ) ) ;
student. setName ( rs. getString ( "name" ) ) ;
student. setSex ( rs. getString ( "sex" ) ) ;
student. setAge ( rs. getInt ( "age" ) ) ;
student. setDepartment ( rs. getString ( "department" ) ) ;
student. setClazz ( rs. getString ( "class" ) ) ;
student. setTelephone ( rs. getString ( "telephone" ) ) ;
students. add ( student) ;
}
rs. close ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . out. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return students;
}
(8)编写按班级查询学生记录的方法
@Override
public List < Student > findByClass ( String clazz) {
List < Student > students = new ArrayList < > ( ) ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strsQL = "select * from t_student where class like ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strsQL) ;
pstmt. setString ( 1 , clazz + "%" ) ;
ResultSet rs = pstmt. executeQuery ( ) ;
while ( rs. next ( ) ) {
Student student = new Student ( ) ;
student. setId ( rs. getString ( "id" ) ) ;
student. setName ( rs. getString ( "name" ) ) ;
student. setSex ( rs. getString ( "sex" ) ) ;
student. setAge ( rs. getInt ( "age" ) ) ;
student. setDepartment ( rs. getString ( "department" ) ) ;
student. setClazz ( rs. getString ( "class" ) ) ;
student. setTelephone ( rs. getString ( "telephone" ) ) ;
students. add ( student) ;
}
rs. close ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return students;
}
(9)编写按系部查询学生记录的方法
@Override
public List < Student > findByDepartment ( String department) {
List < Student > students = new ArrayList < > ( ) ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "select * from t_student where department like ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
pstmt. setString ( 1 , department + "%" ) ;
ResultSet rs = pstmt. executeQuery ( ) ;
while ( rs. next ( ) ) {
Student student = new Student ( ) ;
student. setId ( rs. getString ( "id" ) ) ;
student. setName ( rs. getString ( "name" ) ) ;
student. setSex ( rs. getString ( "sex" ) ) ;
student. setAge ( rs. getInt ( "age" ) ) ;
student. setDepartment ( rs. getString ( "department" ) ) ;
student. setClazz ( rs. getString ( "class" ) ) ;
student. setTelephone ( rs. getString ( "telephone" ) ) ;
students. add ( student) ;
}
rs. close ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return students;
}
(10)编写查询全部学生记录的方法
@Override
public List < Student > findAll ( ) {
List < Student > students = new ArrayList < > ( ) ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strsQL = "select * from t_student" ;
try {
Statement stmt = conn. createStatement ( ) ;
ResultSet rs = stmt. executeQuery ( strsQL) ;
while ( rs. next ( ) ) {
Student student = new Student ( ) ;
student. setId ( rs. getString ( "id" ) ) ;
student. setName ( rs. getString ( "name" ) ) ;
student. setSex ( rs. getString ( "sex" ) ) ;
student. setAge ( rs. getInt ( "age" ) ) ;
student. setDepartment ( rs. getString ( "department" ) ) ;
student. setClazz ( rs. getString ( "class" ) ) ;
student. setTelephone ( rs. getString ( "telephone" ) ) ;
students. add ( student) ;
}
rs. close ( ) ;
stmt. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return students;
}
(11)编写按性别统计学生人数
@Override
public Vector findRowsBySex ( ) {
Vector rows = new Vector ( ) ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strsQL = "select sex as '性别',count(*) as '人数'"
+ "from t_student group by sex order by sex desc" ;
try {
Statement stmt = conn. createStatement ( ) ;
ResultSet rs = stmt. executeQuery ( strsQL) ;
while ( rs. next ( ) ) {
Vector < String > currentRow = new Vector ( ) ;
currentRow. addElement ( rs. getString ( "性别" ) ) ;
currentRow. addElement ( rs. getInt ( "人数" ) + "" ) ;
rows. addElement ( currentRow) ;
}
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return rows;
}
(12)编写按班级统计学生人数
@Override
public Vector findRowsByClass ( ) {
Vector rows = new Vector ( ) ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strsQL = "select class as'班级',count(*) as '人数'"
+ " from t_student group by class order by class desc" ;
try {
Statement stmt = conn. createStatement ( ) ;
ResultSet rs = stmt. executeQuery ( strsQL) ;
while ( rs. next ( ) ) {
Vector < String > currentRow = new Vector ( ) ;
currentRow. addElement ( rs. getString ( "班级" ) ) ;
currentRow. addElement ( rs. getInt ( "人数" ) + "" ) ;
rows. addElement ( currentRow) ;
}
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return rows;
}
(13)编写按系部统计学生人数
@Override
public Vector findRowsByDepartment ( ) {
Vector rows = new Vector ( ) ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strsQL = "select department as '系部', count(*) as'人数'"
+ " from t_student group by department order by department desc" ;
try {
Statement stmt = conn. createStatement ( ) ;
ResultSet rs = stmt. executeQuery ( strsQL) ;
while ( rs. next ( ) ) {
Vector < String > currentRow = new Vector ( ) ;
currentRow. addElement ( rs. getString ( "系部" ) ) ;
currentRow. addElement ( rs. getInt ( "人数" ) + "" ) ;
rows. addElement ( currentRow) ;
}
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return rows;
}
}
3_、测试学生数据访问接口实现类
net.huawei.student.dao.imp
包创建TestStudentDaoImpl
类
(1)编写测试插入学生记录的方法
package net. huawei. student. dao. impl ;
import net. huawei. student. bean. Student ;
import net. huawei. student. dao. StudentDao ;
import org. junit. Test ;
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 ( "192042099" ) ;
student. setName ( "张晓慧" ) ;
student. setSex ( "女" ) ;
student. setAge ( 19 ) ;
student. setDepartment ( "艺术传媒学院" ) ;
student. setClazz ( "2019数媒3班" ) ;
student. setTelephone ( "15890674568" ) ;
int count = dao. insert ( student) ;
if ( count > 0 ) {
System . out. println ( "恭喜,学生记录插入成功! " ) ;
System . out. println ( dao. findById ( student. getId ( ) ) ) ;
} else {
System . out. println ( "遗憾,学生记录插入失败! " ) ;
}
}
(2)编写测试按id删除学生记录的方法
@Test
public void testDeleteById ( ) {
String id = "19101001" ;
int count = dao. deleteById ( id) ;
if ( count > 0 ) {
System . out. println ( "恭喜,学生记录除成功! " ) ;
} else {
System . out. println ( "遗憾,学生记录删除失败! " ) ;
}
}
(3)编写测试按班级删除学生记录的方法
@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 + "]学生记录除失败! " ) ;
}
}
(4)编写测试按系部删除学生记录的方法
@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 ( "温馨提示:无此人! " ) ;
}
}
(5)编写测试更新学生记录的方法
@Test
public void testFindA1l ( ) {
List < Student > students = dao. findAll ( ) ;
for ( Student student : students) {
System . out. println ( student) ;
}
}
(6)编写测试按id查询学生记录的方法
@Test
public void testFindRowsBySex ( ) {
Vector rows = dao. findRowsBySex ( ) ;
Iterator iterator = rows. iterator ( ) ;
while ( iterator. hasNext ( ) ) {
System . out. println ( iterator. next ( ) ) ;
}
}
(7)编写测试按姓名查询学生记录的方法
@Test
public void testDeleteByDepartment ( ) {
String department = "电子工程学院" ;
int count = dao. deleteByDepartment ( department) ;
if ( count > 0 ) {
System . out. println ( "恭喜," + department + "的学生记录删除成功!!!" ) ;
} else {
System . out. println ( "What a pity!学生记录删除失败!!!" ) ;
}
}
(8)编写测试按班级查询学生记录的方法
@Test
public void testUpdate ( ) {
StudentDao studentDao = new StudentDaoImpl ( ) ;
Student student = studentDao. findById ( "19204196" ) ;
System . out. println ( "更新前:" + student) ;
student. setAge ( 20 ) ;
student. setTelephone ( "11111000001" ) ;
student. setDepartment ( "怨种学院" ) ;
int count = studentDao. update ( student) ;
if ( count > 0 ) {
System . out. println ( "更新成功!!!" ) ;
System . out. println ( "更新后:" + studentDao. findById ( "19204196" ) ) ;
} else {
System . out. println ( "What a pity!更新失败!!!" ) ;
}
}
(9)编写测试按系部查询学生记录的方法
@Test
public void testFindById ( ) {
String id = "19204196" ;
Student student = dao. findById ( id) ;
if ( student != null ) {
System . out. println ( "查询到了:" + student) ;
} else {
System . out. println ( "What a pity!查询失败!!!" ) ;
}
}
(10)编写测试查询全部学生记录的方法
@Test
public void testFindByClass ( ) {
String clazz = "2022机电3班" ;
List < Student > students = dao. findByClass ( clazz) ;
if ( students. size ( ) > 0 ) {
for ( Student student : students) {
System . out. println ( student) ;
}
} else {
System . out. println ( "查询失败!!!" ) ;
}
}
(11)编写测试按性别统计学生人数
@Test
public void testFindByClass ( ) {
String clazz = "2022机电3班" ;
List < Student > students = dao. findByClass ( clazz) ;
if ( students. size ( ) > 0 ) {
for ( Student student : students) {
System . out. println ( student) ;
}
} else {
System . out. println ( "查询失败!!!" ) ;
}
}
(12)编写测试按班级统计学生人数
@Test
public void testFindRowsByClass ( ) {
Vector rows = dao. findRowsByClass ( ) ;
Iterator iterator = rows. iterator ( ) ;
while ( iterator. hasNext ( ) ) {
System . out. println ( iterator. next ( ) ) ;
}
}
(13)编写测试按系部统计学生人数
@Test
public void testFindRowsByDepartment ( ) {
Vector rows = dao. findRowsByDepartment ( ) ;
Iterator iterator = rows. iterator ( ) ;
while ( iterator. hasNext ( ) ) {
System . out. println ( iterator. next ( ) ) ;
}
}
}
4、创建用户数据访问接口实现类
在net.hw.student.dao
包里创建UserDaoImpl
(1)编写插入用户记录
package net. huawei. student. dao. impl ;
import net. huawei. student. bean. User ;
import net. huawei. student. dao. UserDao ;
import net. huawei. student. dbutil. ConnectionManager ;
import java. sql. * ;
import java. util. ArrayList ;
import java. util. List ;
public class UserDaoImpl implements UserDao {
@Override
public int insert ( User user) {
int count = 0 ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "insert into t_user (username,password,telephone,register_time)"
+ "values( ?, ?, ?,?)" ;
if ( ! isUsernameExisted ( user. getUsername ( ) ) ) {
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
pstmt. setString ( 1 , user. getUsername ( ) ) ;
pstmt. setString ( 2 , user. getPassword ( ) ) ;
pstmt. setString ( 3 , user. getTelephone ( ) ) ;
pstmt. setTimestamp ( 4 , new Timestamp ( user. getRegisterTime ( ) . getTime ( ) ) ) ;
count = pstmt. executeUpdate ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
}
return count;
}
(2)编写删除用户记录
public int deleteById ( int id) {
int count = 0 ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "delete from t_user where id = ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
pstmt. setInt ( 1 , id) ;
count = pstmt. executeUpdate ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return count;
}
(3)编写更新用户记录
@Override
public int update ( User user) {
int count = 0 ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strsQL = "update t_user set username = ?, password = ?, telephone = ?,"
+ "register_time = ? where id = ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strsQL) ;
pstmt. setString ( 1 , user. getUsername ( ) ) ;
pstmt. setString ( 2 , user. getPassword ( ) ) ;
pstmt. setString ( 3 , user. getTelephone ( ) ) ;
pstmt. setTimestamp ( 4 , new Timestamp ( user. getRegisterTime ( ) . getTime ( ) ) ) ;
pstmt. setInt ( 5 , user. getId ( ) ) ;
count = pstmt. executeUpdate ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return count;
}
(4)编写查询用户记录
@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) {
System . err. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return user;
}
(5)编写查询所有用户记录
@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) ;
}
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return users;
}
(6)编写用户登录记录
@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) {
System . out. println ( e. getMessage ( ) ) ;
;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return user;
}
(7)编写查看是否有记录
@Override
public boolean isUsernameExisted ( String username) {
boolean existed = false ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "SELECT * FROM t_user WHERE username = ?" ;
try {
PreparedStatement pstmt = conn. prepareStatement ( strSQL) ;
pstmt. setString ( 1 , username) ;
ResultSet rs = pstmt. executeQuery ( ) ;
if ( rs. next ( ) ) {
existed = true ;
}
pstmt. close ( ) ;
rs. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return existed;
}
}
4_、测试用户数据访问接口实现类
net.huawei.student.dao.imp
包创建TestUserDaoImpl
(1)编写测试插入用户记录
package net. huawei. student. dao. impl ;
import net. huawei. student. bean. User ;
import net. huawei. student. dao. UserDao ;
import org. junit. Test ;
import java. util. Date ;
import java. util. List ;
public class TestUserDaoImpl {
UserDao dao = new UserDaoImpl ( ) ;
@Test
public void testInsert ( ) {
User user = new User ( ) ;
user. setUsername ( "坤坤" ) ;
user. setPassword ( "123654" ) ;
user. setTelephone ( "963874521" ) ;
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 ( "插入失败!!!" ) ;
}
}
(2)编写测试删除用户记录
@Test
public void testDeleteById ( ) {
int id = 9 ;
int count = dao. deleteById ( id) ;
if ( count != 0 ) {
System . out. println ( "删除用户记录成功!!!" ) ;
} else {
System . out. println ( "删除记录失败!!!" ) ;
}
}
(3)编写测试更新用户记录
@Test
public void testUpdate ( ) {
UserDao userdao = new UserDaoImpl ( ) ;
User user = userdao. findById ( 1 ) ;
System . out. println ( "更新前:" + user) ;
user. setTelephone ( "11111000111" ) ;
user. setUsername ( "GGboard" ) ;
int count = userdao. update ( user) ;
if ( count > 0 ) {
System . out. println ( "更新成功!!!" ) ;
System . out. println ( "更新后:" + userdao. findById ( 1 ) ) ;
} else {
System . out. println ( "更新失败!!!" ) ;
}
}
(4)编写测试查询用户记录
@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 ( ) ) ;
}
(5)编写测试查询所有用户记录
@Test
public void testFindAll ( ) {
List < User > users = dao. findAll ( ) ;
for ( User user : users) {
System . out. println ( user) ;
}
}
(6)编写测试用户登录记录
@Test
public void testLogin ( ) {
String username, password;
username = "hhp" ;
password = "kkk" ;
User user = dao. login ( username, password) ;
if ( user != null ) {
System . out. println ( "用户名与密码正确,登录成功!!!" ) ;
} else {
System . out. println ( "登录失败!用户名或密码输入错误!!!" ) ;
}
}
(7)编写测试查看是否有记录
@Test
public void testLogin ( ) {
String username, password;
username = "hhp" ;
password = "kkk" ;
User user = dao. login ( username, password) ;
if ( user != null ) {
System . out. println ( "用户名与密码正确,登录成功!!!" ) ;
} else {
System . out. println ( "登录失败!用户名或密码输入错误!!!" ) ;
}
}
}