JDBC总结

1.apache-dbutilsSQL语句是固定，不能通过参数传入；对于select操作，如果有返回值，返回类型不能固定，需要使用泛型。DAO：data access object 数据访问对象。BasicDao是专门和数据库交互的，即完成对数据库表的crud操作。

BasicDAO应用示例需创建存放工具类(dao_.utils)的包；把javabean都存放到dao_.damain包下；XxxDAO和BasicDAO存放到dao_.dao包下；测试类都存放到dao_.test包下。

工具类：

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();
    }
}

//编写getConnection方法
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 e) {
        throw new RuntimeException(e);
    }
}

创建存放的Actor对象存放到dao_.damain包下：

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 + '\'' +
            '}';
}

创建BasicDAO(是XxxDAO类的父类)存放到dao_.dao包下：

public class BasicDAO<T> {
    private QueryRunner qr = new QueryRunner();

    //dml方法
    public int update(String sql, Object... parameters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            int update = qr.update(connection, sql, parameters);
            return update;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } 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 e) {
            throw new RuntimeException(e);
        } 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 e) {
            throw new RuntimeException(e);
        } 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 e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }
}

ActorDAO需继承自BasicDAO<Actor>(Actor是具体类型，ActorDAO是对Actor表的操作)存放到dao_.dao包下：

public class ActorDAO extends BasicDAO<Actor> {
}

创建测试类存放到dao_.test包下：

 public static void main(String[] args) {
        TestDAO testDAO = new TestDAO();
        testDAO.testActorDAO();
    }
    public void testActorDAO() {
        ActorDAO actorDAO = new ActorDAO();
        //查询全部
        List<Actor> actors = actorDAO.queryMulti("select * from actor where id >= ?", Actor.class, 1);
        System.out.print("===查询结果===");
        for (Actor actor : actors) {
            System.out.println(actor);
        }
        //查询单行
        Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 5);
        System.out.println("===查询单行结果===");
        System.out.print(actor);
        //查询单行单列
        Object o = actorDAO.queryScalar("select name from actor where id = ?", 5);
        System.out.println("===查询单行单列值===");
        System.out.println(o);
        //4.dml操作 增加
//        int insert = actorDAO.update("insert into actor values(null,?,?,?,?)", "小芳", "女", "2005-5-5", "99999");
//        System.out.println(insert > 0 ? "执行成功" : "执行没有影响表");

        //dml操作 修改
//        int update = actorDAO.update("update actor set name = ? where id = ?", "小明", 4);
//        System.out.println(update > 0 ? "执行成功" : "执行没有影响表");
        //dml操作 删除
        int delete = actorDAO.update("delete from actor where id = ?", 7);
        System.out.println(delete > 0 ? "执行成功" : "执行没有影响表");
    }