八、项目开发实现步骤
(七)创建数据访问接口实现类
在net.yangyunfneg.studeng.dao
包里创建impl
子包(impl:implementation)
1.创建学校数据访问接口实现类
在net.yangyunfneg.studeng.dao.impl
包里创建CollegeDaoImpl
类 实现CollegeDao
接口
方法的空实现
(1)编写按标识符查询学校记录方法
@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 ( "start_time" ) ) ;
college. setEmail ( rs. getString ( "email" ) ) ;
college. setAddress ( rs. getString ( "address" ) ) ;
college. setProfile ( rs. getString ( "profile" ) ) ;
}
rs. close ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return college;
(2)编写更新学校记录方法
@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.impl
包,在包里创建TestCollegeDaoImpl
类
(1)编写测试按标识符查询学校记录方法
@Test
public void testFindById ( ) {
int id = 1 ;
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 + "]的学校记录不存在~" ) ;
}
}
运行testFindById()
方法,查看结果
(2)编写测试更新学校记录方法
@Test
public 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.huawei.student.dao.impl
包里创建StatusDaoImpl
类
(1)编写按标识符查询状态记录方法
@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" ) ) ;
}
rs. close ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
System . err. println ( e. getMessage ( ) ) ;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return status;
}
(2)编写更新状态记录方法
@Override
public int update ( Status status) {
int count = 0 ;
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 . err. println ( e. getMessage ( ) ) ;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return count;
}
2_、测试状态数据访问接口实现类
(1)编写测试按标识符查询状态记录方法
@Test
public void testFindById ( ) {
int id = 1 ;
StatusDao statusDao = new StatusDaoImpl ( ) ;
Status status = statusDao. findById ( id) ;
if ( status != null ) {
System . out. println ( status) ;
} else {
System . out. println ( "标识符[" + id + "]的状态记录不存在~" ) ;
}
}
运行testFindById()
方法,查看结果
(2)编写测试更新状态记录方法
@Test
public void testUpdate ( ) {
StatusDao statusDao = new StatusDaoImpl ( ) ;
Status status = statusDao. findById ( 1 ) ;
System . out. println ( "更新前:" + status) ;
status. setCollege ( "泸州职业技术学院" ) ;
status. setVersion ( "2.0" ) ;
status. setVersion ( "无心剑" ) ;
status. setTelephone ( "15834345670" ) ;
status. setEmail ( "375912360@qq.com" ) ;
status. setAddress ( "泸州江阳区上平远路10号" ) ;
int count = statusDao. update ( status) ;
if ( count > 0 ) {
System . out. println ( "恭喜,状态记录更新成功~" ) ;
System . out. println ( "更新后:" + statusDao. findById ( 1 ) ) ;
} else {
System . out. println ( "遗憾,状态记录更新失败~" ) ;
}
}
运行testUpdate()
方法,查看结果
3.创建学生数据访问接口实现类
(1)编写插入学生记录的方法
@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 ( ) ;
pstmt. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
} 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) {
e. printStackTrace ( ) ;
} 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) {
e. printStackTrace ( ) ;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return count;
}
(4)编写按系部删除学生记录的方法
@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;
}
(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) {
e. printStackTrace ( ) ;
} 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) {
e. printStackTrace ( ) ;
} 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) {
e. printStackTrace ( ) ;
} 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) {
e. printStackTrace ( ) ;
} 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 departemt 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) {
e. printStackTrace ( ) ;
} 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) {
e. printStackTrace ( ) ;
} 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) {
e. printStackTrace ( ) ;
} 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) {
e. printStackTrace ( ) ;
} 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) {
e. printStackTrace ( ) ;
} finally {
ConnectionManager . closeConnection ( conn) ;
}
return rows;
}
3_、测试学生数据访问接口实现类
(1)编写测试方法testInsert()
@Test
public void testInsert ( ) {
Student student = new Student ( ) ;
student. setId ( "22262140" ) ;
student. setName ( "栗子妙" ) ;
student. setSex ( "男" ) ;
student. setAge ( 19 ) ;
student. setDepartment ( "人工智能与大数据学院" ) ;
student. setClazz ( "2022级软件3班" ) ;
student. setTelephone ( "15623555423" ) ;
int count = dao. insert ( student) ;
if ( count > 0 ) {
System . out. println ( "恭喜,学生记录插入成功!" ) ;
System . out. println ( dao. findById ( student. getId ( ) ) ) ;
} else {
System . out. println ( "遗憾,学生记录插入失败!" ) ;
}
}
运行结果
(2)编写测试方法testDeleteById()
@Test
public void testDeleById ( ) {
String id = "19204091" ;
int count = dao. deleteById ( id) ;
if ( count > 0 ) {
System . out. println ( "恭喜,学生记录删除成功!" ) ;
} else {
System . out. println ( "遗憾,学生记录删除失败!" ) ;
}
}
运行结果
(3)编写测试方法testDeleteByClass()
@Test
public void testDeleByClass ( ) {
String clazz = "2022营销1班" ;
int count = dao. deleteByClass ( clazz) ;
if ( count > 0 ) {
System . out. println ( "恭喜,[" + clazz + "]学生记录删除成功!" ) ;
} else {
System . out. println ( "遗憾,[\" + clazz + \"]学生记录删除失败!" ) ;
}
}
运行结果
(4)编写测试方法testFindByName()
@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)编写测试方法testFindAll()
@Test
public void testFindAll ( ) {
List < Student > students = dao. findAll ( ) ;
for ( Student student : students) {
System . out. println ( student) ;
}
}
运行结果
(6)编写测试方法testFindRowsBySex()
@Test
public void testFindRowsBySex ( ) {
Vector rows = dao. findRowsBySex ( ) ;
Iterator iterator = rows. iterator ( ) ;
while ( iterator. hasNext ( ) ) {
System . out. println ( iterator. next ( ) ) ;
}
}
运行结果
(7)编写测试方法testDeleteByDepartment()
@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)编写测试方法testUpdate()
@Test
public void testUpdate ( ) {
StudentDao studentDao = new StudentDaoImpl ( ) ;
Student student = studentDao. findById ( "19205177" ) ;
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 ( "19205177" ) ) ;
} else {
System . out. println ( "What a pity!更新失败!!!" ) ;
}
}
运行结果
(9)编写测试方法testFindById()
@Test
public void testFindById ( ) {
String id = "19205177" ;
Student student = dao. findById ( id) ;
if ( student != null ) {
System . out. println ( "查询到了:" + student) ;
} else {
System . out. println ( "What a pity!查询失败!!!" ) ;
}
}
运行结果
(10)编写测试方法testFindByClass()
@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 ( "What a pity!查询失败!!!" ) ;
}
}
运行结果
(11)编写测试方法testFindByDepartment()
@Test
public void testFindByDepartment ( ) {
String department = "人文学院" ;
List < Student > students = dao. findByDepartment ( department) ;
if ( students. size ( ) > 0 ) {
for ( Student student : students) {
System . out. println ( student) ;
}
} else {
System . out. println ( "What a pity!查询失败!!!" ) ;
}
}
运行结果
(12)编写测试方法testFindRowsByClass()
@Test
public void testFindRowsByClass ( ) {
Vector rows = dao. findRowsByClass ( ) ;
Iterator iterator = rows. iterator ( ) ;
while ( iterator. hasNext ( ) ) {
System . out. println ( iterator. next ( ) ) ;
}
}
运行结果
(13)编写测试方法testFindRowsByDepartment()
@Test
public void testFindRowsByDepartment ( ) {
Vector rows = dao. findRowsByDepartment ( ) ;
Iterator iterator = rows. iterator ( ) ;
while ( iterator. hasNext ( ) ) {
System . out. println ( iterator. next ( ) ) ;
}
}
运行结果
4、创建用户数据访问接口实现类
(1)编写插入用户记录的方法
@Override
public int insert ( User user) {
int count = 0 ;
Connection conn = ConnectionManager . getConnection ( ) ;
String strSQL = "INSERT INTO t_user (username, password, telephone, register_time)"
+ " value (?, ?, ?, ?)" ;
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)编写按标识符删除用户记录
@Override
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" ) ) ;
}
pstmt. close ( ) ;
} 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) ;
}
rs. close ( ) ;
stmt. close ( ) ;
} 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" ) ) ;
}
pstmt. close ( ) ;
} catch ( SQLException e) {
System . err. 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_、测试用户数据访问接口实现类
(1)编写测试方法testFindById()
@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 ( ) ) ;
}
运行结果
(2)编写测试方法testLogin()
@Test
public void testLogin ( ) {
String username, password;
username = "admin" ;
password = "admin" ;
User user = dao. login ( username, password) ;
if ( user != null ) {
System . out. println ( "用户名与密码正确,登录成功!!!" ) ;
} else {
System . out. println ( "What a pity,登录失败!用户名或密码输入错误!!!" ) ;
}
}
运行结果
(3)编写测试方法testIsUsernameExisted()
@Test
public void testIsUsernameExisted ( ) {
String username = "张三丰" ;
boolean result = dao. isUsernameExisted ( username) ;
if ( result) {
System . out. println ( "温馨提示:用户名[" + username + "]已存在,不可用此名注册!!!" ) ;
} else {
System . out. println ( "温馨提示:用户名[" + username + "]不存在,可用此名注册" ) ;
}
}
运行结果
(4)编写测试方法testInsert()
@Test
public void testInsert ( ) {
User user = new User ( ) ;
user. setUsername ( "蔡徐坤" ) ;
user. setPassword ( "111111" ) ;
user. setTelephone ( "17652136666" ) ;
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 ( "What a pity,插入失败!!!" ) ;
}
}
运行结果
(5)编写测试方法testDeleteById()
@Test
public void testDeleteById ( ) {
int id = 9 ;
int count = dao. deleteById ( id) ;
if ( count != 0 ) {
System . out. println ( "删除用户记录成功!!!" ) ;
} else {
System . out. println ( "What a pity,删除记录失败!!!" ) ;
}
}
运行结果
(6)编写测试方法testUpdate()
@Test
public void testUpdate ( ) {
UserDao userdao = new UserDaoImpl ( ) ;
User user = userdao. findById ( 1 ) ;
System . out. println ( "更新前:" + user) ;
user. setTelephone ( "11111000111" ) ;
user. setUsername ( "只因" ) ;
int count = userdao. update ( user) ;
if ( count > 0 ) {
System . out. println ( "更新成功!!!" ) ;
System . out. println ( "更新后:" + userdao. findById ( 1 ) ) ;
} else {
System . out. println ( "What a pity!更新失败!!!" ) ;
}
}
运行结果
(7)编写测试方法testFindAll()
@Test
public void testFindAll ( ) {
List < User > users = dao. findAll ( ) ;
for ( User user : users) {
System . out. println ( user) ;
}
}
运行结果