所实现的功能差不多。这是第三方开发的，要导入相应的jar包(就一个jar包)，比自己写的强大。强大之处在于结果集的处理。主要涉及的类是 org.apache.commons.dbutils Class QueryRunner API如下： Constructor Summary QueryRunner () Constructor for QueryR
所实现的功能差不多。这是第三方开发的，要导入相应的jar包(就一个jar包)，比自己写的强大。强大之处在于结果集的处理。主要涉及的类是
org.apache.commons.dbutils
Class QueryRunner
API如下：
| Constructor Summary | |
|---|---|
|
QueryRunner() Constructor for QueryRunner. |
|
|
QueryRunner(boolean pmdKnownBroken) Constructor for QueryRunner, allows workaround for Oracle drivers |
|
|
QueryRunner(DataSource ds) Constructor for QueryRunner which takes a DataSource. |
|
|
QueryRunner(DataSource ds, boolean pmdKnownBroken) Constructor for QueryRunner, allows workaround for Oracle drivers. |
|
| Method Summary | ||
|---|---|---|
| int[] |
batch(Connection conn, String sql, Object[][] params) Execute a batch of SQL INSERT, UPDATE, or DELETE queries. |
|
| int[] |
batch(String sql, Object[][] params) Execute a batch of SQL INSERT, UPDATE, or DELETE queries. |
|
|
|
query(Connection conn, String sql, Object[] params, ResultSetHandler Deprecated. Use query(Connection,String,ResultSetHandler,Object...) instead |
|
|
|
query(Connection conn, String sql, Object param, ResultSetHandler Deprecated. Use query(Connection, String, ResultSetHandler, Object...) |
|
|
|
query(Connection conn, String sql, ResultSetHandler Execute an SQL SELECT query without any replacement parameters. |
|
|
|
query(Connection conn, String sql, ResultSetHandler Execute an SQL SELECT query with replacement parameters. |
|
|
|
query(String sql, Object[] params, ResultSetHandler Deprecated. Use query(String, ResultSetHandler, Object...) |
|
|
|
query(String sql, Object param, ResultSetHandler Deprecated. Use query(String, ResultSetHandler, Object...) |
|
|
|
query(String sql, ResultSetHandler Executes the given SELECT SQL without any replacement parameters. |
|
|
|
query(String sql, ResultSetHandler Executes the given SELECT SQL query and returns a result object. |
|
| int |
update(Connection conn, String sql) Execute an SQL INSERT, UPDATE, or DELETE query without replacement parameters. |
|
| int |
update(Connection conn, String sql, Object... params) Execute an SQL INSERT, UPDATE, or DELETE query. |
|
| int |
update(Connection conn, String sql, Object param) Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement parameter. |
|
| int |
update(String sql) Executes the given INSERT, UPDATE, or DELETE SQL statement without any replacement parameters. |
|
| int |
update(String sql, Object... params) Executes the given INSERT, UPDATE, or DELETE SQL statement. |
|
| int |
update(String sql, Object param) Executes the given INSERT, UPDATE, or DELETE SQL statement with a single replacement parameter. |
|
一、框架编写准备：数据库元数据的获取
1、元数据：数据库、表、列的定义信息
二、编写自己的框架简化JDBC开发
CUD：语句不同和参数不同。
三、ORM：
O：Object
R：Relation
M：Mapping
对象关系映射
JavaBean 关系数据库表结构 ：对应的映射关系
Hibernate：ORM映射框架 -----》规范化：JPA（Java Persistent API)
IBatis(Apache):ORM映射框架----------->2010(google) MyBatis
Commons DbUtils(Apache):只是对JDBC编码进行了简单的封装。
Spring JDBC Template:只是对JDBC编码进行了简单的封装。
例子：不用自己关闭资源，底层源代码已经关了，而且是结合dbcp使用的，关闭不是真的关闭，而是加入到连接池中，以后就放心使用，一句话解决crud操作。
//账户维护
public class DBUtilDemo {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
@Test
public void testAdd() throws Exception{
qr.update("insert into account(name,money) values(?,?)", "fff",1000);
}
@Test
public void testFindOne() throws Exception{
Account a = qr.query("select * from account where id=?", new BeanHandler<Account>(Account.class),1);
System.out.println(a);
}
@Test
public void testFindAll() throws Exception{
List<Account> list = qr.query("select * from account", new BeanListHandler<Account>(Account.class));
for(Account a:list)
System.out.println(a);
}
//插入大文本行不行：知道clob对应的类型是什么
/*
* create table t1(id int,content text);
*/
@Test
public void testText() throws Exception{
File file = new File("src/jpm.txt");
Reader reader = new FileReader(file);//流并没有对应的数据库类型
char ch[] = new char[(int)file.length()];
reader.read(ch);//不好，开发中不用
reader.close();
Clob clob = new SerialClob(ch);
qr.update("insert into t1(id,content) values(?,?)", 1,clob);
}
//插入大二进制行不行：知道blob对应的类型是什么
/*
* create table t2(id int,content longblob);
*/
@Test
public void testBlob() throws Exception{
InputStream in = new FileInputStream("src/1.jpg");
byte b[] = new byte[in.available()];
in.read(b);
in.close();
Blob blob = new SerialBlob(b);
qr.update("insert into t2(id,content) values(?,?)", 1,blob);
}
//批处理
/*
* create table t3(id int,name varchar(200));
*/
@Test
public void testBatch()throws Exception{
Object params[][] = new Object[10][];//第1维，插入的条数。第2维，每条需要的参数
for(int i=0;i<params.length;i++){
params[i] = new Object[]{i+1,"aaa"+(i+1)};
}
qr.batch("insert into t3 values(?,?)", params);
}
}部分源代码如下：说明已经关闭资源了。
public int update(String sql, Object... params) throws SQLException {
Connection conn = this.prepareConnection();
return this.update(conn, true, sql, params);
}
/**
* Calls update after checking the parameters to ensure nothing is null.
* @param conn The connection to use for the update call.
* @param closeConn True if the connection should be closed, false otherwise.
* @param sql The SQL statement to execute.
* @param params An array of update replacement parameters. Each row in
* this array is one set of update replacement values.
* @return The number of rows updated.
* @throws SQLException If there are database or parameter errors.
*/
private int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {
if (conn == null) {
throw new SQLException("Null connection");
}
if (sql == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null SQL statement");
}
PreparedStatement stmt = null;
int rows = 0;
try {
stmt = this.prepareStatement(conn, sql);
this.fillStatement(stmt, params);
rows = stmt.executeUpdate();
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
return rows;
}
} DBUtils框架提供的结果处理器例子:
public class ResultSetHandlerDemo {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
//ArrayHandler：把结果集中的第一行数据转成对象数组。
@Test
public void test1() throws SQLException{
//数组中的元素就是记录的每列的值
Object[] objs = qr.query("select * from account where id=?", new ArrayHandler(), 1);
for(Object obj:objs)
System.out.println(obj);
}
//ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
@Test
public void test2() throws SQLException{
//数组中的元素就是记录的每列的值
List<Object[]> list = qr.query("select * from account", new ArrayListHandler());
for(Object[] objs:list){
System.out.println("------------------");
for(Object obj:objs){
System.out.println(obj);
}
}
}
//ColumnListHandler：将结果集中某一列的数据存放到List中。 投影
@Test
public void test3() throws SQLException{
//数组中的元素就是记录的每列的值
List<Object> list = qr.query("select * from account", new ColumnListHandler("name"));
for(Object objs:list){
System.out.println(objs);
}
}
//KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map<列名,列值>里，再把这些map再存到一个map里，其key为指定的key。
@Test
public void test4() throws SQLException{
Map<Object,Map<String,Object>> bmap= qr.query("select * from account", new KeyedHandler("id"));
for(Map.Entry<Object,Map<String,Object>> bme:bmap.entrySet()){
System.out.println("-----------------");
for(Map.Entry<String,Object> lme:bme.getValue().entrySet()){
System.out.println(lme.getKey()+"="+lme.getValue());
}
}
}
//MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值
@Test
public void test5() throws SQLException{
Map<String,Object> map = qr.query("select * from account where id=?", new MapHandler(),1);
for(Map.Entry<String,Object> lme:map.entrySet()){
System.out.println(lme.getKey()+"="+lme.getValue());
}
}
//MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List
@Test
public void test6() throws SQLException{
List<Map<String,Object>> list= qr.query("select * from account", new MapListHandler());
for(Map<String,Object> map:list){
System.out.println("-----------------");
for(Map.Entry<String,Object> lme:map.entrySet()){
System.out.println(lme.getKey()+"="+lme.getValue());
}
}
}
//ScalarHandler :只有一条记录的投影查询 select count(*) from account;
@Test
public void test7() throws SQLException{
// Object obj = qr.query("select * from account where id=?", new ScalarHandler("name"),1);
// System.out.println(obj);
Object obj = qr.query("select count(*) from account", new ScalarHandler(1));
int num = ((Long)obj).intValue();
System.out.println(num);
}
}
