自定义View系列教程00–推翻自己和过往，重学自定义View
自定义View系列教程01–常用工具介绍
自定义View系列教程02–onMeasure源码详尽分析
自定义View系列教程03–onLayout源码详尽分析
自定义View系列教程04–Draw源码分析及其实践
自定义View系列教程05–示例分析
自定义View系列教程06–详解View的Touch事件处理
自定义View系列教程07–详解ViewGroup分发Touch事件
自定义View系列教程08–滑动冲突的产生及其处理
探索Android软键盘的疑难杂症
深入探讨Android异步精髓Handler
详解Android主流框架不可或缺的基石
站在源码的肩膀上全解Scroller工作机制
Android多分辨率适配框架（1）— 核心基础
Android多分辨率适配框架（2）— 原理剖析
Android多分辨率适配框架（3）— 使用指南
Android程序员C语言自学完备手册
讲给Android程序员看的前端系列教程(图文版)
讲给Android程序员看的前端系列教程(视频版)
DBUtils框架概述
为更加简单且高效地使用JDBC，Apache组织提供了数据库操作工具类commons-dbutils。该工具对JDBC进行了封装，可极大地简化JDBC的编码工作量。例如：DBUtils在查询数据时可把结果转换成List，Array，Set等集合，非常便于开发人员操作。若需使用该工具，请在官方网站免费下载即可。
DBUtils操作简单，功能强大；其中，它的大部分功能都由以下三大核心实现：
DbUtils工具类
该类主要用于关闭连接、装载JDBC驱动程序等等ResultSetHandler接口
该接口及其实现类主要用于处理结果集QueryRunner类
该类主要用于增，删，改，查
接下来，我们详细介绍DBUtils常用功能及其使用。
DbUtils工具类
该工具类常用方法如下：
public static boolean loadDriver(java.lang.String driverClassName)
该方法用于方装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，不需要捕捉ClassNotFoundException
public static void close(Connection conn,Statement stmt, ResultSet rs) throws java.sql.SQLException
该类方法用于关闭Connection、Statement和ResultSet
public static void closeQuietly(Connection conn,Statement stmt, ResultSet rs):
该类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLEeception
public static void commitAndCloseQuietly(Connection conn)
该方法用于提交事务并关闭连接，而且在关闭连接时不抛出SQL异常。
public static void rollbackAndCloseQuietly(Connection conn)
该方法用于回滚事务并关闭连接，而且在关闭连接时不抛出SQL异常。
ResultSetHandler接口
ResultSetHandler接口常用的实现类如下：
BeanHandler
将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler
将结果集中的每一行数据都封装到一个对应的JavaBean实例中，再存放到List里
ArrayHandler
把结果集中的第一行数据转成对象数组
ArrayListHandler
把结果集中的每一行数据都转成一个数组存放到List中
ColumnListHandler
将结果集中某一列的数据存放到List中
KeyedHandler(name)
将结果集中的每一行数据都封装到一个Map<列名,列值>里，再把这些map再存到一个map里，其key为指定的key。
MapHandler
将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值
MapListHandler
将结果集中的每一行数据都封装到一个Map里，然后再存放到List
ScalarHandler
将结果集中某一条记录的其中某一列的数据存储成Object对象
QueryRunner类
QueryRunner类提供了两个构造函数：
QueryRunner()
采用该方法创建QueryRunner时数据库的事务可由我们自己手动控制。正因为该构造方法无参，所以在调用该对象的query、update、等方法时需要传入参数Connection。
QueryRunner(DataSource ds)
采用该方法创建QueryRunner时数据库的事务由DBUtils自动控制。正因为该构造方法传入了参数DataSource，所以在调用该对象的query、update、等方法时无需传入参数Connection。
QueryRunner的常用方法如下：
public Object query(Connection conn, String sql, Object[] params, ResultSetHandler rsh) throws SQLException
该方法用于执行查询，在该查询中Object数组里的每个值被用来作为查询语句的置换参数。该方法会自行处理PreparedStatement和ResultSet的创建和关闭。
public Object query(String sql, Object[] params, ResultSetHandler rsh) throws SQLException
该方法与上面的这个方法基本一样；不同的是它不用传入参数Connection
public Object query(Connection conn, String sql, ResultSetHandler rsh) throws SQLException
该方法与上面的两个方法基本一样，它用于执行一个不需要置换参数的查询操作。
public int update(Connection conn, String sql, Object[] params) throws SQLException
该方法用于执行更新操作(例如：增加、删除、更新)，在该查询中Object数组里的每个元素值被用来作为更新语句的置换参数。
public int update(Connection conn, String sql) throws SQLException
该方法与上面的方法基本一样，用来执行一个不需要置换参数的更新操作。
public int update(String sql) throws SQLException
该方法与上面的方法基本一样，用来执行一个不需要置换参数和不指定Connection的更新操作。
DBUtils使用示例1
(1)、在数据库中建立会员卡表并插入数据
代码如下：
-- 创建表
DROP TABLE IF EXISTS membershipcard;
CREATE TABLE membershipcard (
id int primary key auto_increment,
username varchar(40),
password varchar(40),
email varchar(40),
birthday date
);
-- 插入数据
INSERT INTO membershipcard (username,password,email,birthday) VALUES ("lili","abc123","[email protected]","1999-08-14");
INSERT INTO membershipcard (username,password,email,birthday) VALUES ("koko","efg456","[email protected]","1998-07-15");
INSERT INTO membershipcard (username,password,email,birthday) VALUES ("mbmb","mnb333","[email protected]","1997-06-16");
INSERT INTO membershipcard (username,password,email,birthday) VALUES ("zxzx","poi666","[email protected]","1996-05-17");
图示如下：
(2)、编写JavaBean即MembershipCard
package cn.com.demo9;
import java.util.Date;
/**
* 原创作者：谷哥的小弟
* 博客地址：http://blog.csdn.net/lfdfhl
*/
public class MembershipCard {
private int id;
private String username;
private String password;
private String email;
private Date birthday;
public MembershipCard() {
}
public MembershipCard(int id, String username, String password, String email, Date birthday) {
this.id = id;
this.username = username;
this.password = password;
this.email = email;
this.birthday = birthday;
}
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;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "MembershipCard [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email
+ ", birthday=" + birthday + "]";
}
}
(3)、利用DBUtils实现增删改查
package cn.com.demo9;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
/**
* 原创作者：谷哥的小弟
* 博客地址：http://blog.csdn.net/lfdfhl
*/
public class TestDBUtils1 {
public static void main(String[] args) {
try {
TestDBUtils1 demo = new TestDBUtils1();
demo.testSelectAll();
} catch (Exception e) {
}
}
// 插入数据
public void testInsert() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
Date birthday = Date.valueOf("1997-07-01");
String sql = "insert into membershipcard(username,password,email,birthday) values(?,?,?,?)";
Object params[] = { "huhu", "asd777", "[email protected]", birthday };
qr.update(sql, params);
}
// 更新数据
public void testUpdate() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "update membershipcard set password=? where username=?";
Object params[] = { "www888", "huhu" };
qr.update(sql, params);
}
// 删除数据
public void testDelete() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "delete from membershipcard where username=?";
Object params[] = { "huhu" };
qr.update(sql, params);
}
// 查询单个数据
public void testSelect() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from membershipcard where username=?";
Object params[] = { "lili" };
BeanHandler<MembershipCard> beanHandler=new BeanHandler<>(MembershipCard.class);
MembershipCard membershipCard=qr.query(sql, beanHandler, params);
System.out.println(membershipCard);
}
// 查询所有数据
public void testSelectAll() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql="select * from membershipcard";
BeanListHandler<MembershipCard> beanListHandler = new BeanListHandler<MembershipCard>(MembershipCard.class);
List<MembershipCard> list = qr.query(sql, beanListHandler);
for (MembershipCard membershipCard : list) {
System.out.println(membershipCard);
}
}
}
图示如下：
示例总结
在该示例中利用DBUtils实现了增删改查操作。其中，我们可利用BeanHandler将查询到的一条数据转换成与其对应的JavaBean；类似地，我们可利用BeanListHandler将查询到的多条数据转换为JavaBean再存放至List集合。
DBUtils使用示例2
(1)、在数据库中建立会员卡表并插入数据
代码如下：
-- 创建表
DROP TABLE IF EXISTS membershipcard;
CREATE TABLE membershipcard (
id int primary key auto_increment,
username varchar(40),
password varchar(40),
email varchar(40),
birthday date
);
-- 插入数据
INSERT INTO membershipcard (username,password,email,birthday) VALUES ("lili","abc123","[email protected]","1999-08-14");
INSERT INTO membershipcard (username,password,email,birthday) VALUES ("koko","efg456","[email protected]","1998-07-15");
INSERT INTO membershipcard (username,password,email,birthday) VALUES ("mbmb","mnb333","[email protected]","1997-06-16");
INSERT INTO membershipcard (username,password,email,birthday) VALUES ("zxzx","poi666","[email protected]","1996-05-17");
图示如下：
(2)、编写JavaBean即MembershipCard
package cn.com.demo9;
import java.util.Date;
/**
* 原创作者：谷哥的小弟
* 博客地址：http://blog.csdn.net/lfdfhl
*/
public class MembershipCard {
private int id;
private String username;
private String password;
private String email;
private Date birthday;
public MembershipCard() {
}
public MembershipCard(int id, String username, String password, String email, Date birthday) {
this.id = id;
this.username = username;
this.password = password;
this.email = email;
this.birthday = birthday;
}
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;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "MembershipCard [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email
+ ", birthday=" + birthday + "]";
}
}
(3)、利用DBUtils实现查询
package cn.com.demo9;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
/**
* 原创作者：谷哥的小弟
* 博客地址：http://blog.csdn.net/lfdfhl
*/
public class TestDBUtils2 {
public static void main(String[] args) {
try {
TestDBUtils2 demo = new TestDBUtils2();
demo.testBeanListHandler();
} catch (Exception e) {
// TODO: handle exception
}
}
// 测试BeanHandler
public void testBeanHandler() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from membershipcard where username=?";
Object params[] = { "lili" };
BeanHandler<MembershipCard> beanHandler = new BeanHandler<>(MembershipCard.class);
MembershipCard membershipCard = qr.query(sql, beanHandler, params);
System.out.println(membershipCard);
}
// 测试BeanListHandler
public void testBeanListHandler() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from membershipcard";
BeanListHandler<MembershipCard> beanListHandler = new BeanListHandler<MembershipCard>(MembershipCard.class);
List<MembershipCard> list = qr.query(sql, beanListHandler);
for (MembershipCard membershipCard : list) {
System.out.println(membershipCard);
}
}
//测试ArrayHandler
public void testArrayHandler() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from membershipcard";
ArrayHandler arrayHandler=new ArrayHandler();
Object[] resultArray = qr.query(sql, arrayHandler);
for(Object o:resultArray) {
System.out.println(o);
}
System.out.println(Arrays.asList(resultArray));
}
//测试ArrayListHandler
public void testArrayListHandler() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from membershipcard";
ArrayListHandler arrayListHandler=new ArrayListHandler();
List<Object[]> list=qr.query(sql, arrayListHandler);
for(Object[] obj:list) {
System.out.println(Arrays.asList(obj));
}
}
//测试ColumnListHandler
public void testColumnListHandler() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from membershipcard";
ColumnListHandler columnListHandler=new ColumnListHandler("username");
List<Object> list = qr.query(sql, columnListHandler);
for(Object obj:list) {
System.out.println(obj);
}
}
// 测试KeyedHandler
public void testKeyedHandler() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from membershipcard";
KeyedHandler keyedHandler = new KeyedHandler("username");
Map<Object, Map<String, Object>> resultMap = qr.query(sql, keyedHandler);
Set<Object> resultMapKeySet = resultMap.keySet();
Iterator<Object> resultMapIterator = resultMapKeySet.iterator();
while(resultMapIterator.hasNext()) {
Object resultMapKey = resultMapIterator.next();
System.out.println("key="+resultMapKey);
Map<String, Object> innerMap = resultMap.get(resultMapKey);
Set<String> innerMapKeySet = innerMap.keySet();
Iterator<String> innerMapIterator = innerMapKeySet.iterator();
while(innerMapIterator.hasNext()) {
String innerMapKey = innerMapIterator.next();
Object innerMapValue = innerMap.get(innerMapKey);
System.out.println(innerMapKey+"="+innerMapValue);
}
System.out.println();
}
}
//测试MapHandler
public void testMapHandler() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from membershipcard";
MapHandler mapHandler=new MapHandler();
Map<String, Object> map = qr.query(sql, mapHandler);
Set<String> keySet = map.keySet();
Iterator<String> iterator = keySet.iterator();
while(iterator.hasNext()) {
String key = iterator.next();
Object value = map.get(key);
System.out.println(key+"="+value);
}
}
//测试MapListHandler
public void testMapListHandler() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from membershipcard";
MapListHandler mapListHandler=new MapListHandler();
List<Map<String,Object>> list = qr.query(sql, mapListHandler);
for(int i=0;i<list.size();i++) {
Map<String, Object> map = list.get(i);
Set<String> keySet = map.keySet();
Iterator<String> iterator = keySet.iterator();
while(iterator.hasNext()) {
String key = iterator.next();
Object value = map.get(key);
System.out.println(key+"="+value);
}
System.out.println();
}
}
//测试ScalarHandler
public void testScalarHandler() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from membershipcard where id=?";
ScalarHandler scalarHandler=new ScalarHandler("username");
int id=1;
Object object = qr.query(sql,scalarHandler,id);
System.out.println(object);
}
}
图示如下：
示例总结
在该示例中学习并使用了ResultSetHandler接口的常用实现类：BeanHandler、BeanListHandler、ArrayHandler、ArrayListHandler、ColumnListHandler、KeyedHandler()、MapHandler、MapListHandler、ScalarHandler
DBUtils处理事务
DBUtils常在Service层获得连接Connection得以保证涉及事务操作的Connection对象为同一个。接下来，通过示例的方式介绍DBUtils对于事务的处理
(1)、在数据库中创建表并插入数据，代码如下：
DROP TABLE IF EXISTS account;
-- 创建account表
CREATE TABLE account(
id INT primary key auto_increment,
name VARCHAR(40),
money FLOAT
);
-- 向表中插入数据
INSERT INTO account(name, money) VALUES ('A',1000);
INSERT INTO account(name, money) VALUES ('B',1000);
(2)、编写AccountDao
package cn.com.demo10;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
/**
* 本文作者：谷哥的小弟
* 博客地址：http://blog.csdn.net/lfdfhl
*/
public class AccountDao {
//转出
public void outMoney(Connection connection, String from, float money) {
QueryRunner qr = new QueryRunner();
try {
String sql = "update account set money = money - ? where name = ?";
Object params[] = {money,from};
qr.update(connection, sql, params);
} catch (SQLException e) {
e.printStackTrace();
}
}
//转入
public void inMoney(Connection connection, String to, float money) {
QueryRunner qr = new QueryRunner();
try {
String sql = "update account set money = money + ? where name = ?";
Object params[] = {money,to};
qr.update(connection, sql,params);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
(3)、编写AccountService
package cn.com.demo10;
import java.sql.Connection;
import org.apache.commons.dbutils.DbUtils;
/**
* 本文作者：谷哥的小弟
* 博客地址：http://blog.csdn.net/lfdfhl
*/
public class AccountService {
public void transfer(String from, String to, float money) {
AccountDao accountDao = new AccountDao();
Connection connection = null;
try {
//获得连接
connection = C3P0Util.getConnection();
//设置事务不自动提交
connection.setAutoCommit(false);
accountDao.outMoney(connection,from,money);
//模拟异常
//int error = 9527/0 ;
accountDao.inMoney(connection,to,money);
//提交事务并安静地关闭连接
DbUtils.commitAndCloseQuietly(connection);
} catch (Exception e) {
//异常出现时，回滚事务并安静地关闭连接
DbUtils.rollbackAndCloseQuietly(connection);
//e.printStackTrace();
}
}
}
(4)、测试DBUtils事务处理
package cn.com.demo10;
/**
* 本文作者：谷哥的小弟
* 博客地址：http://blog.csdn.net/lfdfhl
*/
public class TestDBUtils {
public static void main(String[] args) {
AccountService accountService=new AccountService();
accountService.transfer("A", "B", 200);
System.out.println("transfer over");
}
}