jdbc的简单封装2-增删改查

jdbc的简单封装2-增删改查

一、封装前的代码

1.1表结构

1.2 java对应实体类

/**
 * 一个数据库表对应的实体类如下
 */
public class Account {
   /*
   FieldTypeComment
    id smallint(6)
    account varchar(20)
    money double
    time time*/
    private Integer id;
    private String account;
    private double money;
    private Date date;

    public Account() {
    }

    public Account(Integer id, String account, double money, Date date) {
        this.id = id;
        this.account = account;
        this.money = money;
        this.date = date;
    }

   /**
   ==================
   此处省略get和set方法
   =================
   **/
    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", account='" + account + '\'' +
                ", money=" + money +
                ", date=" + date +
                '}';
    }
}

1.3 数据访问接口和类Dao

接口：

/**
 * 对应的增删改查接口
 */
public interface AccountDao {
    public int insert(Account account);
    public int delete(Integer id);
    public int update(Account account);
    public Account select(Integer id);
    public List<Account> selectAll();
}

实现类：

public class AccountDaoImpl implements AccountDao {
     private Connection connection=DaoUtils.getConnection();
    @Override
    public int insert(Account account) {
        int insert = 0;
        PreparedStatement preparedStatement = null;
        String sql = "insert into account(account,money,time)values(?,?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, account.getAccount());
            preparedStatement.setObject(2, account.getMoney());
            preparedStatement.setObject(3, account.getDate());
            insert = preparedStatement.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return insert;
    }
	//==========删除===========
    @Override
    public int delete(Integer id) {
        int delete = 0;
        String sql = "delete from account where id=?";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, id);
            delete = preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return delete;
    }
	//==========修改===========
    @Override
    public int update(Account account) {
        int update = 0;
        String sql = "update account set money=? where id=?";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, account.getMoney());
            preparedStatement.setObject(2, account.getId());
            update = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return update;
    }
	//==========根据id查询===========
    @Override
    public Account select(Integer id) {
        Account account = null;
        String sql = "select * from account where id =?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                Integer accountId = (Integer) resultSet.getObject(1);
                String accountName = (String) resultSet.getObject(2);
                double accoutMoney = (double) resultSet.getObject(3);
                Date accountDate = (Date) resultSet.getObject(4);
                account = new Account(accountId, accountName, accoutMoney, accountDate);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return account;
    }
    //==========查询所有===========
    @Override
    public List<Account> selectAll() {
        List<Account> list = new ArrayList();
        String sql = "select * from account";
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Integer accountId = (Integer) resultSet.getObject(1);
                String accountName = (String) resultSet.getObject(2);
                double accountMoney = (double) resultSet.getObject(3);
                Date accountDate = (Date) resultSet.getObject(4);
                Account account = new Account(accountId, accountName, accountMoney, accountDate);
                list.add(account);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}

二、分析

除了sql语句和参数的不同，其它都是一样的做法。即：通过连接获取PreparedStatement对象，执行sql语句获取结果ResultSet。所以对增、删、改为一组进行分组复用，查询单独为一组进行复用。其实可以看出增删改返回是int值。而查询则要对返回的数据集进行封装。

2.1 增、删、改封装

//公共的增、删，改
    public static int commonUpdate(String sql,Object ... args){
        int resultRow=0;
        try {
            PreparedStatement ps = getConnection().prepareStatement(sql);
            //设置参数
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1,args[i]);
            }
            //执行
             resultRow= ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultRow;

    }

2.2 查询的封装

无论查询多个还是多个都存储到集合里面。但是由于返回的resultSet需要给对象设置属性，而在工具 类中，不能固定某个对象，所以使用泛型，对于某个具体的对象，单独写一个接口RowMapper去封装查询的结果集。
RowMapper的实现类代码如下：

package com.itheima.entity.impl;

import com.itheima.entity.Account;
//用户自己对resultset查询的数据封装成一个对象的包
import com.itheima.entity.RowMapper;

import java.sql.ResultSet;
import java.util.Date;

/**
 * 使用泛型返回具体的内容
 * =========以后如果对另外的类进行查询，把Account换成对应的实体类名即可==
 * ===============================================================
 */
public class RowMapperImpl2 implements RowMapper<Account> {
    @Override
    public Account getRow(ResultSet resultSet) {
        //调用者自己处理某个类的查询数据
        Account a = null;
        try {
            Integer id = resultSet.getInt(1);
            String account = resultSet.getString(2);
            double money = resultSet.getDouble(3);
            Date time = resultSet.getDate(4);
            a = new Account(id, account, money, time);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return a;
    }
}

查询通用封装如下：

、//===============================
  //设置泛型 
 //这个是用于方法上面的泛型
 //此处需要能够了解泛型的基本使用
     public static <T>List<T> commonQuery(String sql, RowMapper<T> rowMapper, Object ... args){
        List<T> list=new ArrayList();
        if(args!=null){
            try {
                PreparedStatement ps = getConnection().prepareStatement(sql);
                //设置注入参数
                for (int i = 0; i < args.length; i++) {
                    ps.setObject(i+1,args[i]);
                }
                ResultSet resultSet = ps.executeQuery();
                // 查询的结果  应该通过一个接口来让调用者自己实现
                while (resultSet.next()){
                    T t = rowMapper.getRow(resultSet);
                    list.add(t);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

三、改进后的数据访问类Dao

public class AccountDaoImpl2 implements AccountDao {
    //============如果把连接作为成员变量，应该考虑如何关闭==============
    private Connection connection=DaoUtils.getConnection();
    @Override
    public int insert(Account account) {
        String sql = "insert into account(account,money,time)values(?,?,?)";
        Object args[]={account.getAccount(),account.getMoney(),account.getDate()};
        int insert = DaoUtils.commonUpdate(sql, args);
        return insert;
    }

    @Override
    public int delete(Integer id) {
        int delete = 0;
        String sql = "delete from account where id=?";
        delete= DaoUtils.commonUpdate(sql, id);
        return delete;
    }

    @Override
    public int update(Account account) {
        int update = 0;
        String sql = "update account set money=? where id=?";
        update= DaoUtils.commonUpdate(sql,account.getId(),account.getMoney());
        return update;
    }

    @Override
    public Account select(Integer id) {
        // System.out.println("select连接对象："+connection);
        String sql = "select * from account where id =?";
        RowMapper<Account> rowMapper=new RowMapperImpl2();
        List<Account> list = DaoUtils.commonQuery(sql, rowMapper, id);
        //需要转换一下
        if (!list.isEmpty()){ //不为空，取第一个
            return list.get(0);
        }
        //list为空，直接返回null
        return null;
    }

    @Override
    public List<Account> selectAll() {
        String sql = "select * from account";
        RowMapper<Account> rowMapper=new RowMapperImpl2();
        List<Account> list = DaoUtils.commonQuery(sql, rowMapper);
        return list;
    }
}

四、测试效果

写一个测试类，导入junit和相关依赖，方法如下：

 @Test
    public void testSelectById(){
        Account account = accountDao.select(8);
        System.out.println(account);
    }

结果：