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=)