今天完成了三个访问接口实现类`
package net.dyc.student.dao.impl;
import net.dyc.student.bean.College;
import net.dyc.student.dao.CollegeDao;
import net.dyc.student.dbutil.ConnectionManager;
import java.sql.*;
/**
-
功能:学校数据访问接口实现类
-
作者:邓益春
-
日期:2019年6月18日
*/
public class CollegeDaoImpl implements CollegeDao {
@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.setStartTime(rs.getDate("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;
}
@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 = ?, 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.getStartTime().getTime())); pstmt.setString(4, college.getTelephone()); pstmt.setString(5, college.getEmail()); pstmt.setString(6, college.getProfile()); pstmt.setInt(7, college.getId()); // 5. 执行SQL,返回更新记录数 count = pstmt.executeUpdate(); // 6. 关闭预备语句对象 pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭数据库连接 ConnectionManager.closeConnection(conn); } // 返回更新记录数 return count;
}
}
`
package net.dyc.student.dao.impl;
import net.dyc.student.bean.Status;
import net.dyc.student.dao.StatusDao;
import net.dyc.student.dbutil.ConnectionManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 功能:状态数据访问接口实现类
* 作者:邓益春
* 日期:2019年6月18日
*/
public class StatusDaoImpl 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;
}
}
package net.dyc.student.dao.impl;
import net.dyc.student.bean.User;
import net.dyc.student.dao.UserDao;
import net.dyc.student.dbutil.ConnectionManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
/**
-
包名:net.hw.student.dao.impl
-
类名:UserDaoImpl
-
描述:用户数据访问接口实现类
-
作者:邓益春
-
日期:2019年6月18日
*/
public class UserDaoImpl implements UserDao{@Override
public int insert(User user) {
// 定义插入记录数
int count = 0;// 获得数据库连接 Connection conn = ConnectionManager.getConnection(); // 定义SQL字符串 String strSQL = "INSERT INTO t_user (username, password, telephone, register_time)" + " VALUES (?, ?, ?, ?)"; 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) { e.printStackTrace(); } finally { ConnectionManager.closeConnection(conn); } // 返回插入记录数 return count;
}
@Override
public int deleteById(int id) {
return 0;
}@Override
public int update(User user) {
// 定义更新记录数
int count = 0;// 获得数据库连接 Connection conn = ConnectionManager.getConnection(); // 定义SQL字符串 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) { e.printStackTrace(); } finally { ConnectionManager.closeConnection(conn); } // 返回更新记录数 return count;
}
@Override
public User findById(int id) {
// 声明用户
User user = null;// 获取数据库连接对象 Connection conn = ConnectionManager.getConnection(); // 定义SQL字符串 String strSQL = "SELECT * FROM t_user WHERE id = ?"; try { // 创建预备语句对象 PreparedStatement pstmt = conn.prepareStatement(strSQL); // 设置占位符的值 pstmt.setInt(1, id); // 执行SQL查询,返回结果集 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 findAll() {
return null;
}@Override
public User login(String username, String password) {
// 声明用户
User user = null;
// 获取数据库连接
Connection conn = ConnectionManager.getConnection();
// 定义SQL字符串
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;
}
}
也完成了三个测试数据访问接口实现类
`import net.dyc.student.dao.StudentDao;
import net.dyc.student.dao.impl.StudentDaoImpl;
import org.junit.Test;
/**
-
功能:测试学生数据访问接口实现类
-
作者:邓益春
-
时间:2019.6.18
*/
public class TestStudentDaoImpl {
@Test
public void testInsert(){
Student student = new Student();
student.setId(“18205106”);
student.setName(“邓益春”);
student.setSex(“男”);
student.setAge(19);
student.setDepartment(“信息工程学院”);
student.setClazz(“18软件技术三班”);
student.setTelephone(“158906745”);StudentDao dao = new StudentDaoImpl(); int count = dao.insert(student); if (count >0){ System.out.println("恭喜,学生记录插入成功!"); }else { System.out.println("遗憾,学生记录插入失败!"); }
}
}
package net.dyc.student.test;
import net.dyc.student.bean.Status;
import net.dyc.student.dao.StatusDao;
import net.dyc.student.dao.impl.StatusDaoImpl;
import org.junit.Test;
/**
* 功能:测试状态数据访问接口实现类
* 作者:邓益春
* 时间:2019.6.18
*/
public class TestStatusDaoImpl {
@Test
public void testFinById(){
StatusDao dao =new StatusDaoImpl();
Status status = dao.findById(1);
System.out.println(status);
}
@Test
public void testUpdate(){
StatusDao dao = new StatusDaoImpl();
Status status = dao.findById(1);
status.setAuthor("花剑");
status.setTelephone("1395678345");
status.setEmail("huajian@163.com");
dao.update(status);
status = dao.findById(1);
System.out.println(status);
}
}
package net.dyc.student.test;
import net.dyc.student.bean.College;
import net.dyc.student.dao.CollegeDao;
import net.dyc.student.dao.impl.CollegeDaoImpl;
import org.junit.Test;
/**
* 功能:测试学校数据访问接口实现类
* 作者:邓益春
* 时间:2019.6.18
*/
public class TestCollegeDaoImpl {
@Test
public void testFinByID(){
CollegeDao dao = new CollegeDaoImpl();
College college = dao.findById(1);
System.out.println(college);
}
@Test
public void testUpdate(){
CollegeDao dao = new CollegeDaoImpl();
College college = dao.findById(1);
college.setPresident("王洪礼");
dao.update(college);
college = dao.findById(1);
System.out.println(college);
}
}
实训时出现的问题:今天重新创建了数据库,把第一天做的全部重新做了一遍,在实现接口类中出现了很多问题。