DAO 和增删改查通用方法

设计

准备工作

lib包

utils包下的JDBCUtilsByDruid类

package com.zlh.dao_.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtilsByDruid {
    private static DataSource ds;
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getconnection() throws SQLException {
        return ds.getConnection();
    }
    public static void close(ResultSet resultSet, Statement statement,Connection connection){
        try {
            if(resultSet!=null){
                resultSet.close();
            }
            if(statement!=null){
                statement.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }
}

druid properties文件

#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/zlh_db1?rewriteBatchedStatements=true
username=root
password=525715
#initial connection Size
initialSize=10
#min idle connecton size
minIdle=5
#max active connection size
maxActive=50
#max wait time (5000 mil seconds)
maxWait=5000

dao包

ActorDAO

package com.zlh.dao_.dao;

import com.zlh.jdbc.Actor;

public class ActorDAO extends BasicDAO<Actor>{
    //1. 就有 BasicDAO 的方法
    //2. 根据业务需求，可以编写特有的方法
}

BasicDAO

package com.zlh.dao_.dao;

import com.zlh.jdbc.Utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

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

public class BasicDAO<T> {
    QueryRunner qr=new QueryRunner();
    //开发通用dml方法,针对任意表
    public int upadate(String sql,Object... parameters ){
        Connection connection=null;
        try {
            connection=JDBCUtilsByDruid.getconnection();

            int update = qr.update(connection, sql, parameters);
            return update;
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);//编译异常--->运行异常
        }
        finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
    //返回多个对象（即查询结果多行）
    public List<T> queryMulti(String sql,Class<T>clazz ,Object... parameters){
        Connection connection=null;
        try {
            connection=JDBCUtilsByDruid.getconnection();
            return qr.query(connection,sql,new BeanListHandler<T>(clazz),parameters);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
    //查询单行结果
    public T querySingle(String sql,Class<T>clazz ,Object... parameters){
        Connection connection=null;
        try {
            connection=JDBCUtilsByDruid.getconnection();
            return qr.query(connection,sql,new BeanHandler<T>(clazz),parameters);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
    //查询单行单列的结果，返回单值
    public Object queryScalar(String sql,Object... parameters){
        Connection connection=null;
        try {
            connection=JDBCUtilsByDruid.getconnection();
            return qr.query(connection,sql,new ScalarHandler(),parameters);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
}

domain包

Actor类

package com.zlh.dao_.domain;

import java.util.Date;

public class Actor { //Javabean, POJO, Domain对象

    private Integer id;
    private String name;
    private String sex;
    private Date borndate;
    private String phone;

    public Actor() { //一定要给一个无参构造器[反射需要]
    }

    public Actor(Integer id, String name, String sex, Date borndate, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.borndate = borndate;
        this.phone = phone;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getSex() {
        return sex;
    }

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

    public Date getBorndate() {
        return borndate;
    }

    public void setBorndate(Date borndate) {
        this.borndate = borndate;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "\nActor{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", borndate=" + borndate +
                ", phone='" + phone + '\'' +
                '}';
    }
}

test包

package com.zlh.dao_.test;

import com.zlh.dao_.dao.ActorDAO;
import com.zlh.jdbc.Actor;

import java.util.List;

public class TestDAO {
    public void testActorDAO(){
        ActorDAO actorDAO = new ActorDAO();
        List<Actor> actors = actorDAO.queryMulti("select *from actor where id>=?", Actor.class, 1);
        for(Actor actor:actors){
            System.out.println(actor);
        }

    }

    public static void main(String[] args) {
        TestDAO testDAO = new TestDAO();
        testDAO.testActorDAO();

    }
}