实训6.18：连接数据库及单元测试

1.创建表的实体
例：
package net.lcn.student.bean.bean;
import java.util.Date;
public class College {
private int id;
private String name;
private String president;
private Date startTime;
private String telephone;
private String email;
private String address;
private String profile;

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getPresident() {
    return president;
}

public void setPresident(String president) {
    this.president = president;
}

public Date getStartTime() {
    return startTime;
}

public void setStartTime(Date startTime) {
    this.startTime = startTime;
}

public String getTelephone() {
    return telephone;
}

public void setTelephone(String telephone) {
    this.telephone = telephone;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

public String getAddress() {
    return address;
}

public void setAddress(String address) {
    this.address = address;
}

public String getProfile() {
    return profile;
}

public void setProfile(String profile) {
    this.profile = profile;
}

@Override
public String toString() {
    return "College{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", president='" + president + '\'' +
            ", startTime=" + startTime +
            ", telephone='" + telephone + '\'' +
            ", email='" + email + '\'' +
            ", address='" + address + '\'' +
            ", profile='" + profile + '\'' +
            '}';
}

}

2.连接数据库实现类，完成数据库的连接，感觉很复杂但是最终还是成功的弹出了“恭喜连接成功”窗口。
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190618163449574.png?xoss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpc2FtZWFs,size_16,color_FFFFFF,t_70连接数据库实现类，完成数据库的连接，感觉很复杂但是最终还是成功的弹出了“恭喜连接成功”窗口。
3.创建数据访问接口

例：
package net.lcn.student.bean.dao;

import net.lcn.student.bean.bean.Student;

import java.util.List;
import java.util.Vector;

public interface StudentDao {
int insert(Student student);
int deleteById(Student id);
int deleteByClass(Student clazz);
int deleteByDepartment(Student department);
int update(Student student);
Student finById(String id);
List findByname(String name);
List findByClass(String clazz);
List findByDepartment(String department);
List findAll();
Vector findRowsBySex();
Vector findRowsByclass();
Vector findRowsByDepartment();

}
4.测试学校数据库访问接口实现类，打印输出college表中第一条数据，并更新一条数据，完成单元测试开始与结束功能
跟着老师的脚步一步一步学习及修改，最终效果达到理想效果！

如下：

package net.lcn.student.bean.test;

import com.sun.tracing.dtrace.ArgsAttributes;
import net.lcn.student.bean.bean.College;
import net.lcn.student.bean.dao.CollegeDao;
import net.lcn.student.bean.dao.impl.CollegeDaoImpl;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

/**

• 功能：测试学校数据库访问接口实现类
• 2019.6.18
  */

public class TestCollegeDaoImpl {
@Before
public void beforeTest() {
System.out.println(“单元测试开始了！”);
}

@After
public void afterTest() {
    System.out.println("单元测试结束了！");
}

@Test
public void testFindById() {
    CollegeDao dao = new CollegeDaoImpl();
    College college = dao.finfById(1);//第一条记录
    System.out.println(college);
}

@Test
public void testUpdate() {
    CollegeDao dao = new CollegeDaoImpl();
    College college = dao.finfById(1);
    college.setPresident("王狗屎");
    dao.update(college);
    college = dao.finfById(1);
    int count = dao.update(college);
    System.out.println(college);
    if (count > 0) {
        System.out.println("恭喜，学校记录更新");
    }else {
        System.out.println("遗憾,学校记录更新失败");
    }
}

}

运行结果：

5.状态数据访问接口实现类StatusDaoImpl

代码如下：
package net.lcn.student.bean.dao.impl;

import net.lcn.student.bean.bean.Status;
import net.lcn.student.bean.dao.StatusDao;
import net.lcn.student.bean.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 finfById(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.setEmail(rs.getString("email"));
        }
        //7.关闭预备语句对象
        pstmt.close();
        //8.关闭结果集对象
        rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        //关闭结果集对象
        ConnectionManager.closeConnection(conn);
    }
    //返回状态对象
    return status;
}

@Override
public int updatr(Status status) {
    //定义更新记录数
    int count = 0;
    //1.获取数据库连接
    Connection conn = ConnectionManager.getConnection();
    //2.定义SQL字符串
    String strSQL = "update t_status set college = ?, version = ?, author = ?,"
            + "telephone = ?, address = ?, emaile = ? 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;
}

}

6.加粗样式在net.lcn.student.test包里创建测试类TestStatusDaoImpl：

运行结果如下：

更新数据：

运行更新结果如下：