java(十三) —— JDBC(三) DAO接口 DAO接口实现类 DAO接口实现类测试

1.DAO接口

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

/**
 * @author ym
 * @create 2022-02-01 14:38
 * @description 针对于Customer表的dao操作
 */
public interface CustomerDAO {
    void insert(Connection connection, Customer customer);

    void deleteById(Connection connection, int id);

    void update(Connection connection, Customer customer);

    Customer selectById(Connection connection, int id);

    List<Customer> getAll(Connection connection);

    long getCount(Connection connection);

    Date getMaxBirth(Connection connection);
}

2.DAO接口实现类

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

/**
 * @author ym
 * @create 2022-02-01 14:46
 * @description
 */
public class CustomerDAOImpl extends BaseDAO implements CustomerDAO {
    @Override
    public void insert(Connection connection, Customer customer) {
        String insertSql = "insert into customers(name,email,birth) values(?,?,?)";
        update(connection, insertSql, customer.getName(), customer.getEmail(), customer.getBirth());
    }


    @Override
    public void deleteById(Connection connection, int id) {
        String insertSql = "delete from customers where id = ?";
        update(connection, insertSql, id);
    }

    @Override
    public void update(Connection connection, Customer customer) {
        String updateSql = "update customers set name=? ,email=? ,birth=? where id=?";
        update(connection, updateSql, customer.getName(), customer.getEmail(), customer.getBirth(), customer.getId());

    }

    @Override
    public Customer selectById(Connection connection, int id) {
        String selectByIdSql = "select * from customers where id =?";
        Customer customer1 = selectAllTable(connection, Customer.class, selectByIdSql, id);
        return customer1;
    }

    @Override
    public List<Customer> getAll(Connection connection) {
        String getAllSql = "select id,name,email,birth from customers";
        List<Customer> customers = selectAllTableList(connection, Customer.class, getAllSql);
        return customers;
    }

    @Override
    public long getCount(Connection connection) {
        String getCountSql = "select COUNT(*) from customers";
        Long value = getValue(connection, Long.class, getCountSql);
        return value;
    }

    @Override
    public Date getMaxBirth(Connection connection) {
        String getMaxValue = "select max(birth) from customers";
        Date value = getValue(connection, Date.class, getMaxValue);
        return value;
    }
}

3.DAO接口实现类测试

import org.junit.Test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * @author ym
 * @create 2022-02-01 15:08
 * @description
 */
public class TestImpl {

    CustomerDAOImpl customerDAO = new CustomerDAOImpl();

    @Test
    public void testInsert() {
        //1.建立连接
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            //2.要添加的customer
            Customer customer = new Customer();
            customer.setName("陈胜");
            customer.setEmail("chensheng@163.com");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            java.util.Date parse = simpleDateFormat.parse("1887-02-01");
            customer.setBirth(new Date(parse.getTime()));
            customerDAO.insert(connection, customer);
            System.out.println("添加成功");
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, null);
        }
    }

    @Test
    public void testDelete() {
        //1.建立连接
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            //2.要删除的customerId

            customerDAO.deleteById(connection, 4);
            System.out.println("删除成功");
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, null);
        }
    }

    @Test
    public void testUpdate() {
        //1.建立连接
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            //2.要修改的customer
            Customer customer = new Customer();
            customer.setName("吴广");
            customer.setEmail("wuguang@163.com");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            java.util.Date parse = simpleDateFormat.parse("1889-01-02");
            customer.setBirth(new Date(parse.getTime()));
            customer.setId(19);
            customerDAO.update(connection, customer);
            System.out.println("修改成功");
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, null);
        }
    }


    @Test
    public void testSelectOne() {
        //1.建立连接
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();
            //2.要查找的customerId

            Customer customer = customerDAO.selectById(connection, 20);
            System.out.println(customer.toString());
            System.out.println("查找成功");
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, null);
        }
    }

    @Test
    public void testSelectAll() {
        //1.建立连接
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();


            List<Customer> customer = customerDAO.getAll(connection);
            customer.forEach(System.out::println);
            System.out.println("查找成功");
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, null);
        }
    }

    @Test
    public void testSelectCount() {
        //1.建立连接
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();


            long customer = customerDAO.getCount(connection);
            System.out.println(customer);
            System.out.println("查找成功");
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, null);
        }
    }

    @Test
    public void testSelectMaxBirth() {
        //1.建立连接
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();


            Date customer = customerDAO.getMaxBirth(connection);
            System.out.println(customer);
            System.out.println("查找成功");
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, null);
        }
    }
}