2018下期Java实训第二天（2019.06.18）

今天完成了三个访问接口实现类`

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);
    }
}

实训时出现的问题：今天重新创建了数据库，把第一天做的全部重新做了一遍，在实现接口类中出现了很多问题。