Dbutils使用
一、DbUtils简介二、DbUtils核心类或接口三、使用步骤四、实现代码JdbcDemoDbUtils.javaJdbcUtils.javaUsers.javajdbc.properties数据库表结构
一、DbUtils简介
commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，创建连接、结果集封装、释放资源，同时也不会影响程序的性能。创建连接、结果集封装、释放资源因此dbutils成为很多不喜欢hibernate的公司的首选。
二、DbUtils核心类或接口
QueryRunner：类名 作用：操作sql语句 构造器：new QueryRunner(Datasource ds); 常用方法：
query(…);update(…); DbUtils：释放资源，控制事务
closeQuietly(conn)：内部处理了异常的释放资源commitAndClose(Connection conn)：提交事务并释放连接 … ResultSetHandler：封装结果集 （接口）查询的时候要用到结果集，ResultSetHandler接口就是将ResultSet结果集映射为Bean、List、Map等Java中的对象或者集合。 1.处理单行数据的类：ScalarHandler、ArrayHandler、MapHandler、BeanHandler 2. 处理多行数据的类：BeanListHandler、AbstractListHandler（ArrayListHandler、MapListHandler、ColumnListHandler）、AbstractKeyedHandler（KeyedHandler BeanMapHandler） 3.可扩展的类：BaseResultSetHandler
三、使用步骤
1.导入jar包（commons-dbutils-1.4.jar 2.创建一个 queryrunner类：操作sql
方法：new QueryRunner(Datasource ds)
3.编写sql 4.执行sql
query(…)：执行操作update（…)：执行cud操作
四、实现代码
JdbcDemoDbUtils.java
package jdbc
;
import org
.apache
.commons
.dbutils
.DbUtils
;
import org
.apache
.commons
.dbutils
.QueryRunner
;
import org
.apache
.commons
.dbutils
.handlers
.BeanListHandler
;
import org
.apache
.commons
.dbutils
.handlers
.ScalarHandler
;
import java
.sql
.Connection
;
import java
.sql
.DriverManager
;
import java
.util
.List
;
public class JdbcDemoDbUtils {
public static void main(String
[] args
) throws Exception
{
QueryRunner qr
= new QueryRunner();
Class
.forName("com.mysql.jdbc.Driver");
Connection cn
= DriverManager
.getConnection("jdbc:mysql:///dbuser", "root", "root");
String sql0
= "insert into users(username, password) values(?, ?)";
qr
.update(cn
, sql0
, new Object[]{"lisi", "4444"});
String sql
= "update users set password = ? where username = ? ";
qr
.update(cn
, sql
, new Object[]{"0000", "lisi"});
String sql1
= "delete from users where username = ?";
qr
.update(cn
, sql1
, new Object[]{"lisi"});
String sql2
= "select count(id) from users ";
Object obj
= qr
.query(cn
, sql2
, new ScalarHandler());
System
.out
.println("用户数：" + obj
);
String sql3
= "select * from users";
List
<Users> lists
= qr
.query(cn
, sql3
, new BeanListHandler<>(Users
.class));
for (Users us
: lists
) {
System
.out
.println(us
);
}
DbUtils
.closeQuietly(cn
);
}
}
JdbcUtils.java
package jdbcutils
;
import java
.io
.FileReader
;
import java
.io
.IOException
;
import java
.net
.URL
;
import java
.sql
.*
;
import java
.util
.Properties
;
public class JdbcUtils {
private static String url
;
private static String user
;
private static String password
;
private static String driver
;
static {
try {
Properties pro
= new Properties();
ClassLoader classLoader
= JdbcUtils
.class.getClassLoader();
URL res
= classLoader
.getResource("jdbc.properties");
String path
= res
.getPath();
pro
.load(new FileReader(path
));
url
= pro
.getProperty("url");
user
= pro
.getProperty("user");
password
= pro
.getProperty("password");
driver
= pro
.getProperty("driver");
Class
.forName(driver
);
} catch (IOException e
) {
e
.printStackTrace();
} catch (ClassNotFoundException e
) {
e
.printStackTrace();
}
}
public static Connection
getConnection() throws SQLException
{
return DriverManager
.getConnection(url
, user
, password
);
}
public static void close(Connection conn
, Statement stat
) {
if (conn
!= null
) {
try {
conn
.close();
} catch (SQLException e
) {
e
.printStackTrace();
}
}
if (stat
!= null
) {
try {
stat
.close();
} catch (SQLException e
) {
e
.printStackTrace();
}
}
}
public static void close(Connection conn
, Statement stat
, ResultSet rs
) {
if (rs
!= null
) {
try {
rs
.close();
} catch (SQLException e
) {
e
.printStackTrace();
}
}
if (conn
!= null
) {
try {
conn
.close();
} catch (SQLException e
) {
e
.printStackTrace();
}
}
if (stat
!= null
) {
try {
stat
.close();
} catch (SQLException e
) {
e
.printStackTrace();
}
}
}
}
Users.java
package jdbc
;
public class Users {
private int id
;
private String username
;
private String password
;
public int getId() {
return id
;
}
public void setId(int id
) {
this.id
= id
;
}
public String
getUsername() {
return username
;
}
public void setUsername(String username
) {
this.username
= username
;
}
public String
getPassword() {
return password
;
}
public void setPassword(String password
) {
this.password
= password
;
}
@Override
public String
toString() {
return "Users{" +
"id=" + id
+
", username='" + username
+ '\'' +
", password='" + password
+ '\'' +
'}';
}
}
jdbc.properties
driver = com.mysql.jdbc.Driver
url = jdbc:mysql:///dbuser
user = root
password = root
数据库表结构
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(32),
PASSWORD VARCHAR(32)
)
