commons-dbutils-1.6.jar
commons-dbutils-.jar
操作 JDBC 连接数据库,并且执行 sql 语句,处理返回的结果集
三个核心类:
DbUtils 关闭资源方法(安静的关闭,不用异常处理)
QueryRunner 执行 sql 语句,并且返回想要的结果集,参数 ResultSetHandler 是接口
(使用实现类,想要什么结果集,就传入该接口的实现类)
public static void insert() throws SQLException {
// 插入一条数据使用 QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 获取链接
Connection connection = JDBCUtil.getConnection();
String sql = "insert into sort values(null, '显示器', 5, '这是一台播放动画片显示器')";
// 插入数据
int row = queryRunner.update(connection, sql);
if (row > ) {
System.out.println("插入成功");
}
// 安静的关闭资源
DbUtils.closeQuietly(connection);
}
// 更新
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
// 使用占位符传入 sql 语句
String sql = "update sort set sname=?,sprice=?,sdesc=? where sid=5";
// 多参数参数传参方式一:利用数组
Object[] params = {"咖啡", , "Java 里的咖啡"};
int row = queryRunner.update(connection, sql, params);
// 多参数参数传参方式二:直接传参数
// 注意:要和占位符号的顺序对应
// int row = queryRunner.update(connection, sql, "咖啡", 2000, "Java 里的咖啡");
if (row > ) {
System.out.println("修改成功");
}
DbUtils.closeQuietly(connection);
查询操作
/*
* ArrayHandler
* 把查询出来的数据第一行,放进对象数据中,并返回
*/
@Test
public void select() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
// 调用查询方法
// 传入什么容器,该类就把数据放到什么容器中,给你返回
Object[] rel = queryRunner.query(connection, sql, new ArrayHandler());
for (Object object : rel) {
System.out.println(object);
}
DbUtils.closeQuietly(connection);
}
/*
* ArrayListHandler
* 查询每一条记录,放到 List 集合中,每一条记录是一个对象数组
*/
@Test
public void select() throws SQLException{
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Object[]> query = queryRunner.query(connection, sql, new ArrayListHandler());
for (Object[] objects : query) {
for (Object object : objects) {
System.out.println(object);
}
}
DbUtils.closeQuietly(connection);
}
/*
* BeanHandler
* 把结果集的第一行封装成一个 JavaBean 对象
* JavaBean 对象规则
* 1.成员变量私有化
* 2.提供空参的构造方法
* 3.提供 set/get 方法
* 符合以上规则的实体类创建出来的对象
* 就是一个 JavaBean 对象
*/
@Test
public void select() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
// 要把使用哪个 JavaBean 实体类传进去
// 类名.class
Sort query = queryRunner.query(connection, sql, new BeanHandler<Sort>(Sort.class));
if (query == null) {
System.out.println("没有这条数据");
}
System.out.println(query);
DbUtils.closeQuietly(connection);
}
/*
* BeanListHandler(常用)
* 把 JavaBean 对象,放入 list 集合中返回
*/
@Test
public void select() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Sort> query = queryRunner.query(connection, sql, new BeanListHandler<>(Sort.class));
for (Sort sort : query) {
System.out.println(sort);
}
DbUtils.closeQuietly(connection);
}
/*
* ColumnListHandler
* 返回指定列的数据,存储到 list 集合中
*/
@Test
public void select() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Object> query = queryRunner.query(connection, sql, new ColumnListHandler<>("sname"));
for (Object object : query) {
System.out.println(object);
}
DbUtils.closeQuietly(connection);
}
/*
* ScalarHandler
* 查询结果是一个的时候使用
*/
@Test
public void select() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select count(*) from sort";
Long query = queryRunner.query(connection, sql, new ScalarHandler<Long>());
// String sql = "select max(sprice) from sort";
// Double query = queryRunner.query(connection, sql, new ScalarHandler<Double>());
// String sql = "select avg(sprice) from sort";
// Double query = queryRunner.query(connection, sql, new ScalarHandler<Double>());
// String sql = "select sum(sprice) from sort";
// Double query = queryRunner.query(connection, sql, new ScalarHandler<Double>());
System.out.println(query);
DbUtils.closeQuietly(connection);
}
/*
* MapHandler
* 将结果集第一行数据,封装到 Map 集合中
* Map<键,值> 键:列名 值:数据
*/
@Test
public void select7() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
Map<String, Object> query = queryRunner.query(connection, sql, new MapHandler());
for (String key : query.keySet()) {
System.out.println(key + "\t" + query.get(key));
}
DbUtils.closeQuietly(connection);
}
@Test
public void select8() throws SQLException {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Map<String, Object>> query = queryRunner.query(connection, sql, new MapListHandler());
for (Map<String, Object> map : query) {
for (String key : map.keySet()) {
System.out.println(key + "\t" + map.get(key));
}
}
DbUtils.closeQuietly(connection);
}
数据库连接池
dbinfo.properties配置文件:
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myjdbc
user=root
password=
/*
* 与表对应的 JavaBean 实体类
*/
public class Sort {
private int sid;
private String sname;
private Double sprice;
private String sdesc;
public Sort() {
super();
}
public Sort(int sid, String sname, Double sprice, String sdesc) {
super();
this.sid = sid;
this.sname = sname;
this.sprice = sprice;
this.sdesc = sdesc;
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public Double getSprice() {
return sprice;
}
public void setSprice(Double sprice) {
this.sprice = sprice;
}
public String getSdesc() {
return sdesc;
}
public void setSdesc(String sdesc) {
this.sdesc = sdesc;
}
@Override
public String toString() {
return "[sid=" + sid + ", sname=" + sname + ", sprice=" + sprice + ", sdesc=" + sdesc + "]";
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//import java.util.Properties;
import java.util.ResourceBundle;
public class JDBCUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
// 使用静态代码块加载驱动,读取配置文件
static {
// 使用系统类来读取配置文件
ResourceBundle resourceBundle = ResourceBundle.getBundle("dbinfo");
// 获取文件中的数据
driverClass = resourceBundle.getString("driverClass");
url = resourceBundle.getString("url");
user = resourceBundle.getString("user");
password = resourceBundle.getString("password");
// 让驱动类只加载一次
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 获取数据库连接的方法
public static Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("连接失败");
}
return connection;
}
// 关闭数据库的方法 如果没有结果集需要关闭,直接传空就行
public static void closeAll(ResultSet resultSet, Statement statement, Connection connection) {
// 关闭资源前,要做非空判断,防止空指针异常
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException("资源关闭失败");
}
// 加快系统回收的速度
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException("资源关闭失败");
}
// 加快系统回收的速度
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException("资源关闭失败");
}
// 加快系统回收的速度
connection = null;
}
}
}
/*
* Java 为了解决频繁创建数据库连接和释放数据库连接的问题
* 使用了数据库连接池来解决
* 并且出了一套规范数据库连接池规范(接口)
* javax.sql.DataSource Java 扩展包
* 由数据库厂商来提供的
* 常用数据库连接池 DBCP C3P0
* 需要导入的 jar 包(dbcp 中依赖 pool jar 包中的类)
* commons-dbcp-1.4.jar
* commons-pool-1.5.6.jar
*
* BasicDataSource 是 DataSource (规范类)的实现类
* 基础设置(要使用数据库连接池,必须要设置的内容)
* mysql 驱动类 com.mysql.jdbc.Driver
* 访问数据库连接地址
* 数据库访问用户名和密码
*/
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
public class DataSourceUtil {
private static BasicDataSource basicDataSource = new BasicDataSource();
static {
// 设置basicDataSource
// mysql 的驱动类的全限定类名
basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
basicDataSource.setUrl("jdbc:mysql://localhost:3306/myjdbc");
basicDataSource.setUsername("root");
basicDataSource.setPassword("123456");
// 扩展设置
basicDataSource.setInitialSize();// 初始化连接数
basicDataSource.setMaxActive();// 最大活动数
basicDataSource.setMaxIdle();// 最大空闲连接数
basicDataSource.setMinIdle();// 最小空闲连接数
}
// 获取数据库连接池
public static DataSource getDataSource() {
return basicDataSource;
}
}
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
/*
* 测试 DataSourceUtil 类
*/
public class Demo03 {
public static void main(String[] args) throws SQLException {
QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
// 插入一条数据
String sql = "insert into sort values(null, ?, ?, ?)";
Object[] param = {"试卷", , "一份万分试卷"};
int row = queryRunner.update(sql, param);
if (row > ) {
System.out.println("插入成功");
}
}
}
http://blog.csdn.net/huzongnan/article/list
![Java String.format方法的简单使用[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)