Java实训 学生信息管理系统 第二天

一、创建接口实现类并测试

1.

/*
功能：学校数据访问接口实现类
作者：宋云鹏
日期：2019.6.18
 */
package net.syp.student.dao.impl;
import net.syp.student.bean.College;
import net.syp.student.dao.CollegeDao;
import net.syp.student.dbutil.ConnectionManager;

import java.sql.*;

public class CollegeDaoImpl implements CollegeDao {
    //按ID查询学校
    @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;
    }
 }

================================================================
下载junit
利用@Test测试

/*
功能：测试学校数据访问接口实现类
作者：宋云鹏
时间：2019年6月18日
 */
package net.syp.student.test;

import net.syp.student.bean.College;
import net.syp.student.dao.CollegeDao;
import net.syp.student.dao.impl.CollegeDaoImpl;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;


public class TestCollegeDaoImpl {
    @Before
    public void beforTest(){
        System.out.println("单元测试开始！");
    }


    @Test
    public void testFindByID(){
        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);
    }
    @After
    public void afterTest(){
        System.out.println("单元测试结束！");
    }
}

/*
功能：状态数据访问接口
作者：宋云鹏
时间：2019年6月18日16:39:07
 */
package net.syp.student.dao.impl;

import net.syp.student.bean.Status;
import net.syp.student.dao.StatusDao;
import net.syp.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;
        //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;

        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) {
            e.printStackTrace();
        } finally {
            ConnectionManager.closeConnection(conn);
        }

        return count;
    }
}

================================================================

/*
功能：测试状态数据访问接口实现类
作者：宋云鹏
时间：2019年6月18日16:59:56
 */
package net.syp.student.test;

import net.syp.student.bean.Status;
import net.syp.student.dao.StatusDao;
import net.syp.student.dao.impl.StatusDaoImpl;
import org.junit.Test;


public class TestStatusDaoImpl {
    @Test
    public void testFindById(){
        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("11548484846");
        status.setEmail("960192824@qq.com");
        dao.update(status);
        status = dao.findById(1);
        System.out.println(status);
    }
}

在今天天的项目中完全熟悉了数据库连接和使用SQL语句对数据库进行增删改查，在今天测试遇到了较多错误，例如表名不同找不到表、SQL语句错误、单词打错，但都有效的解决了。