第三方jar包的使用

第三方jar包使用

所谓第三方jar包,就是由第三方编写的工具类,里面有很多已经写好的方法,
我们可以通过加载jar包,直接调用里面的方法,省去了大量的代码编写,比如int row = qr.update(connection,sql, params);
这一方法,只需要数据库连接,sql语句,与数组,便可以直接完成更新,而我们自己实现更新功能时,
获取到连接后,需要用connection.createStatement,建立statement对象,然后再用statement对象执行sql语句,
statement.executeUpdate()执行sql语句,用result接受得到的结果,
可见第三方jar包里面提供的封装好的方法,可以方便我们实现功能

代码示例

使用占位符传入语句
直接传递参数 注意要和占位符问号的顺序 对应

更新数据

关闭资源

插入数据

// 实现所有数据的更新
public static void main(String[] args) throws SQLException {
    Connection connection = JDBCUtil.getConnection();
    QueryRunner qr = new QueryRunner();
    String sql = "update sort set sname=?,sprice=?,sdesc=?";
    String[]  params = {"张飞","2301","桃园三结义"};
    int row = qr.update(connection,sql, params);
    if(row>0) {
        System.out.println("更新成功");
    }
    DbUtils.closeQuietly(connection);
}

public static void main(String[] args) throws SQLException {
    Connection connection = JDBCUtil.getConnection();
    QueryRunner qr = new QueryRunner();
    String sql = "update sort set sname=?,sprice=?,sdesc=?";
    int row = qr.update(connection,sql, "关羽","2206","过五关斩六将");
    if(row>0) {
        System.out.println("更新成功");
    }
    DbUtils.closeQuietly(connection);
}

public static void main(String[] args) throws SQLException {
    Connection connection = JDBCUtil.getConnection();
    QueryRunner qr = new QueryRunner();
    String sql = "insert into sort values (null,'刘备','2207','汉中王')";
    int row = qr.update(connection, sql);
    if(row > 0) {
        System.out.println("插入成功");
    }
    DbUtils.close(connection);
}

查询操作八个结果集

ArrayHander

把查询出来的数据的第一行 放进对象数据中 并返回

private static void fun1() throws SQLException {
        // ArrayHandler
        Connection connection = JDBCUtil.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "select * from sort";
        Object[] query = qr.query(connection, sql, new ArrayHandler());
        for (Object object : query) {
            System.out.println(object);
        }
        DbUtils.closeQuietly(connection);
    }

ArrayListHandler

查询每一条记录,放到List集合中
每一条记录是一个对象数组 

private static void fun2() throws SQLException {
    Connection connection = JDBCUtil.getConnection();
    QueryRunner qr = new QueryRunner();
    String sql = "select * from sort";
    List<Object[]> list = qr.query(connection, sql, new ArrayListHandler());
    for (Object[] key : list) {
        for (Object object : key) {
            System.out.println(object);
        }
    }
}

BeanHandler

 把结果集的第一行 封装成一个JavaBean对象
 JeanBean对象规则
 1.成员变量私有化
 2.提供空参的构造方法
 3.提供set/get方法
 符合以上规则的实体类创建处理的对象
 就是一个javabean对象

private static void fun3() throws SQLException {
    Connection connection = JDBCUtil.getConnection();
    QueryRunner qr = new QueryRunner();
    String sql = "select * from sort";
    Sort query = qr.query(connection, sql, new BeanHandler<Sort>(Sort.class));
    if(query != null) {
        System.out.println(query);
    }
    DbUtils.closeQuietly(connection);
}

BeanListHandler

private static void fun4() throws SQLException {
    //BeanListHandler
    Connection connection = JDBCUtil.getConnection();
    QueryRunner qr = new QueryRunner();
    String sql = "select * from sort";
    List<Sort> list = qr.query(connection, sql, new BeanListHandler<Sort>(Sort.class));
    for (Sort sort : list) {
        System.out.println(sort);
    }
}

ColumnlistHandler

返回表中的某一列数据

public static void main(String[] args) throws SQLException {
     //  ColumnlistHandler
    Connection connection = JDBCUtil.getConnection();
    QueryRunner qr = new QueryRunner();
    String sql = "select * from sort";
    List<Double> query = qr.query(connection, sql,new ColumnListHandler<Double>("sprice"));
    for (Double string : query) {
        System.out.println(string);
    }
}

ScalarHandler

查询结果是一个的时候使用的
比如count(*) MAX(sprice) MIN(sproce) AVG(sprice)

private static void fun6() throws SQLException {
    // ScalarHandler
    Connection connection = JDBCUtil.getConnection();
    String sql = "select AVG(sprice) from sort";
    QueryRunner qr = new QueryRunner();
    Double query = qr.query(connection, sql, new ScalarHandler<Double>());
    System.out.println(query);
}

MapHandler

以键值对的形式保存  字段名 与 值

private static void fun7() throws SQLException {
    // MapHandler
    Connection connection = JDBCUtil.getConnection();
    QueryRunner qr = new QueryRunner();
    String sql = "select * from sort";
    Map<String, Object> query = qr.query(connection, sql, new MapHandler());
    for (String key : query.keySet()) {
        System.out.println(key + " " + query.get(key));
    }
}

MapListHandler

list表中保存Map对象

private static void fun8() throws SQLException {
Connection connection = JDBCUtil.getConnection();
QueryRunner qr = new QueryRunner();
String sql = "select * from sort";
List<Map<String,Object>> list = qr.query(connection, sql, new MapListHandler());
for (Map<String, Object> map : list) {
    for (String key : map.keySet()) {
        System.out.println(key + " "+ map.get(key));
    }
}

数据库连接池

package com.lanou3g.jdbc;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

public class Demo05 {
    private static BasicDataSource dataSource = new BasicDataSource();
    static {
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/myjdbc");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        // 初始化连接数
        dataSource.setInitialSize(10);
        // 设置最大活动数
        dataSource.setMaxActive(7);
        // 设置最大空闲连接数
        dataSource.setMaxIdle(4);
        // 设置最小空闲连接数
        dataSource.setMinIdle(2);
    }
    // 获取数据库连接池
    public static DataSource getDataSource() {
        return dataSource;
    }
}

测试数据库连接池

public class Demo03 {
    public static void main(String[] args) throws SQLException {
        // 数据库连接池和QueryRunner的使用
        QueryRunner qr
        = new QueryRunner(DataSourceUtil.getDataSource());
        // 插入一条数据
        String sql = "insert into sort values(null,?,?,?)";
        Object[] params = {"试卷",10,"一份万分试卷"};
        int row = qr.update(sql,params);
        if(row > 0) {
            System.out.println("插入成功");
        }
    }
}