DbUtils库是一套小巧的用来简化 JDBC 调用的库。JDBC源代码库单调且易出错，所以DBUtils 类抽象出所有简单的任务，让你更专注于使用JDBC做Query和Update的工作。

引入DBUtils

jar包下载：链接：http://pan.baidu.com/s/1i53UkgX 密码：l9v8

利用DBUtils实现数据库的增删改查

添加一个用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// 1.创建核心类 利用dbcp获取到datasource对象
QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
// 2.编写sql语句
String sql = "insert into user values(null,?,?)";
// 3.为占位符设置值
Object[] params = { "李白", "李太白" };
// 4. 执行添加操作
try {
    int rows = qr.update(sql, params);
    if (rows > 0) {
        System.out.println("添加成功");
    } else {
        System.out.println("添加失败");
    }
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

修改一个用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// 1.创建核心类
QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
// 2.编写sql语句
String sql = "update user set password=? where userid=?";
// 3.为占位符设置值
Object[] params = { "吕子乔", 1 };
// 4. 执行添加操作
try {
    int rows = qr.update(sql, params);
    if (rows > 0) {
        System.out.println("修改成功");
    } else {
        System.out.println("修改失败");
    }
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

删除一个用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// 1.创建核心类
QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
// 2.编写sql语句
String sql = "delete from user where userid=?";
// 3.为占位符设置值
Object[] params = { 3 };
// 4. 执行添加操作
try {
    int rows = qr.update(sql, params);
    if (rows > 0) {
        System.out.println("删除成功");
    } else {
        System.out.println("删除失败");
    }
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

查询所有

1
2
3
4
5
6
7
8
9
10
11
QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
String sql = "select * from user;";
try {
    List<User> users = qr.query(sql, new BeanListHandler<User>(User.class));
    for (User user : users) {
        System.out.println(user);
    }
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

查询某个用户

1
2
3
4
5
6
7
8
9
10
QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
String sql = "select * from user where userid=?";
Object [] params = {9};
try {
    User user = qr.query(sql, new BeanHandler<User>(User.class),params);
    System.out.println(user);
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

查询所有用户总数

1
2
3
4
5
6
7
8
9
10
QueryRunner qRunner = new QueryRunner(DBCPUtils.getDataSource());
String sql = "select count(*) from user;";
try {
    //此处注意返回对象是long类型的对象
    long num = (long) qRunner.query(sql, new ScalarHandler());
    System.out.println("您查询的用户总数是:"+num);
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

查询列结果的集合

1
2
3
4
5
6
7
8
9
10
11
QueryRunner qRunner = new QueryRunner(DBCPUtils.getDataSource());
String sql = "select * from user;";
try {
    List<Object> list = qRunner.query(sql, new ColumnListHandler("username"));
    for (Object object : list) {
        System.out.println(object);
    }
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

后台     

本博客所有文章除特别声明外，均采用 CC BY-SA 3.0协议 。转载请注明出处！