Dao模式封装JDBC实操

步骤如下图

代码如下

在resources里写的database.properties文件内容

//驱动路径
drive = com.mysql.jdbc.drive
//url信息
url = jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf-8&useSSL=false
//用户名
username = root
//密码
password = root
//连接池初始容量
initialSize = 8
//连接池最大活跃数
maxActive = 16
//在连接池到达最大连接数之后等待空闲连接的等待时间
maxWait = 3000

实体类 

实体父类

public class BaseEntity {
    private Integer id;
    private Integer idDelete;
    private String createDate;
    private String modifyDate;

    @Override
    public String toString() {
        return
//                "BaseEntity{" +
                "id=" + id +
                ", idDelete=" + idDelete +
                ", createDate='" + createDate + '\'' +
                ", modifyDate='" + modifyDate + '\''
//                        + '}'
                ;
    }

    public Integer getId() {
        return id;
    }

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

    public Integer getIdDelete() {
        return idDelete;
    }

    public void setIdDelete(Integer idDelete) {
        this.idDelete = idDelete;
    }

    public String getCreateDate() {
        return createDate;
    }

    public void setCreateDate(String createDate) {
        this.createDate = createDate;
    }

    public String getModifyDate() {
        return modifyDate;
    }

    public void setModifyDate(String modifyDate) {
        this.modifyDate = modifyDate;
    }
}

实体子类

public class User extends BaseEntity{
    private String username;
    private String password;
    private String avatar;
    private String sex;
    private String mobile;
    private String idCode;

    @Override
    public String toString() {
        return "User{" +
                super.toString()+
                "username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", avatar='" + avatar + '\'' +
                ", sex='" + sex + '\'' +
                ", mobile='" + mobile + '\'' +
                ", idCode='" + idCode + '\'' +
                '}';
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getAvatar() {
        return avatar;
    }

    public void setAvatar(String avatar) {
        this.avatar = avatar;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getIdCode() {
        return idCode;
    }

    public void setIdCode(String idCode) {
        this.idCode = idCode;
    }
}

创建连接池

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class ConnectionUtil {
    private static DataSource dataSource;

    static {
        init();
    }
    /*
    *
    * 连接池dataSource的private私有化，连接池初始化的过程对外不可见
    * 暴露一个公有的方法给外部获取连接对象
    * 外界*/
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    private static void init(){
        //传的是配置文件，方法参数是配置文件
        //database.properties
        Properties properties = new Properties();   //用于读properties文件的对象
        try {
            //properties.load()加载属性文件，参数是属性文件的输入流
            properties.load(ConnectionUtil.class.getClassLoader().getResourceAsStream("database.properties"));
            //DruidDataSourceFactory.createDataSource()使用三方库创建连接对象，参数是properties
            dataSource = DruidDataSourceFactory.createDataSource(properties);//调用工厂方法，返回连接池对象
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

父类

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class BaseDao {
    protected Connection conn;
    public BaseDao(Connection conn){
        this.conn = conn;
    }
    /*
    * 封装增删改操作，该方法可以被子类直接调用，返回受影响行数
    * */
    protected int update(String sql, List<Object> params){
        try {
            //预编译SQL语句
            PreparedStatement ps = conn.prepareStatement(sql);
            //为SQL中的？填入参数
            if (params != null && params.size() > 0 ){
                for (int i = 0; i < params.size(); i++) {
                    ps.setObject((i+1),params.get(i));
                }
            }
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }
}

接口

import com.iweb.shop.dao.impl.UserDaoImpl;
import com.iweb.shop.entity.User;

import java.sql.Connection;

public interface UserDao {
    /*
    * 暴露给上层调用插入一个用户到数据库的方法
    * 参数是一个user对象
    * 返回值int代表受影响的行数*/

    int insert(User user);

    /*
    * 静态工厂方法，提供给上层调用*/
    static UserDao getInstance(Connection conn){
        return new UserDaoImpl(conn);
    }
}

子类重写接口的抽象方法

import com.iweb.shop.dao.BaseDao;
import com.iweb.shop.dao.UserDao;
import com.iweb.shop.entity.User;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class UserDaoImpl extends BaseDao implements UserDao {
    //父类手动了一个有参构造方法，子类有一个无参构造方法中有一个隐式super调用父类
    //无参，但父类不存在无参构造方法，所以子类要有一个匹配的有参构造方法
    public UserDaoImpl(Connection conn) {
        super(conn);
    }

    public int insert(User user) {
        String sql = "insert into user(username,password,sex,mobile,id_code) values(?,?,?,?,?)";
//        List<Object> params = new ArrayList<>();
//        params.add(user.getUsername());
//        params.add(user.getPassword());
//        params.add(user.getSex());
//        params.add(user.getMobile());
//        params.add(user.getIdCode());
//        return super.update(sql,params);
        return super.update(sql, Arrays.asList(new Object[] {user.getUsername(),user.getPassword()
                ,user.getSex(),user.getMobile(),user.getIdCode()}));
    }
}