模拟使用QueryRunner查询数据库:
首先创建一张表:
create table `user` (
`id` int (11),
`name` varchar (60),
`birthday` date ,
`sex` varchar (30),
`home_address` varchar (150)
);
insert into `user` (`id`, `name`, `birthday`, `sex`, `home_address`) values('1','张三','1990-02-21','男','北京');
insert into `user` (`id`, `name`, `birthday`, `sex`, `home_address`) values('2','李四','1995-07-14','女','天津');
insert into `user` (`id`, `name`, `birthday`, `sex`, `home_address`) values('3','王五','1996-03-08','男','河南');
创建一个封装对象User:
package rk.news.entity;
import java.util.Date;
/*
create table `user` (
`id` int (11),
`name` varchar (60),
`birthday` date ,
`sex` varchar (30),
`home_address` varchar (150)
);
*/
public class User {
private int id;
private String name;
private Date birthday;
private String sex;
private String home_address; //homeAddress
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getHome_address() {
return home_address;
}
public void setHome_address(String home_address) {
this.home_address = home_address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", home_address='" + home_address + '\'' +
'}';
}
}
连接数据库的配置dbcp-config.properties:
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/news_db
username=root
password=root
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;]
#注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=utf8
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
使用DBCPUtils去连接:
package rk.news.utils;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @Author rk
* @Date 2018/12/1 14:59
* @Description:
* dbcp和p0都是常用的数据库的连接池
**/
public class DBCPUtils {
private static DataSource ds;
private DBCPUtils(){}
public static String url;
public static String username;
public static String password;
static {
try {
Properties properties = new Properties();
String path = "dbcp-config.properties";
InputStream in = DBCPUtils.class.getClassLoader().getResourceAsStream(path);
properties.load(in);
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
ds = BasicDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static DataSource getDataSource(){
return ds;
}
public static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void release(Connection con, Statement st, ResultSet rs){
try {
if(rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (st != null) {
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
使用QueryRunner去测试查询:
package rk.utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import rk.news.utils.DBCPUtils;
import java.sql.SQLException;
/**
* @Author rk
* @Date 2018/12/1 14:28
* @Description:
**/
public class TestDB {
public static void main(String[] args) throws SQLException {
QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
String sql = "select count(1) from book";
Long count = qr.query(sql, new ScalarHandler<Long>());
System.out.println(count);
}
}
下面模拟QueryRunner去实现:
首先使用传统的jdbc去查询:
package rk.utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import rk.news.entity.User;
import rk.news.utils.DBCPUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @Author rk
* @Date 2018/12/4 16:56
* @Description:
* 模拟dbutil框架
* 使用传统的jdbc的方式,将数据库表user中的数据查询出来,还可以按照条件查询
**/
public class TestDBUtil1 {
private Connection connection;
private PreparedStatement ps;
private ResultSet rs;
@Before
public void setUp() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(DBCPUtils.url, DBCPUtils.username, DBCPUtils.password);
}
//条件查询
@Test
public void testQuery1() throws SQLException {
String sql = "select id, name, birthday, sex, home_address from user where id = ?";
ps = connection.prepareStatement(sql);
ps.setObject(1,1);
rs = ps.executeQuery();
User user = new User();
while(rs.next()){
int id = rs.getInt(1);
String name = rs.getString("name");
Date birthday = rs.getDate("birthday");
String sex = rs.getString("sex");
String home_address = rs.getString("home_address");
user.setId(id);
user.setHome_address(home_address);
user.setName(name);
user.setSex(sex);
user.setBirthday(birthday);
}
System.out.println(user);
}
//查询全部
@Test
public void testQuery2() throws SQLException {
String sql = "select id, name, birthday, sex, home_address from user ";
ps = connection.prepareStatement(sql);
rs = ps.executeQuery();
List<User> list = new ArrayList<>();
while(rs.next()){
User user = new User();
int id = rs.getInt(1);
String name = rs.getString("name");
Date birthday = rs.getDate("birthday");
String sex = rs.getString("sex");
String home_address = rs.getString("home_address");
user.setId(id);
user.setHome_address(home_address);
user.setName(name);
user.setSex(sex);
user.setBirthday(birthday);
list.add(user);
}
for(User user : list){
System.out.println(user);
}
}
//使用QueryRunner查询
@Test
public void testQuery3() throws SQLException {
QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
List<User> list = qr.query("select id, name, birthday, sex, home_address from user", new BeanListHandler<User>(User.class));
for(User user : list){
System.out.println(user);
}
String sql = "select id, name, birthday, sex, home_address from user where id = ?";
User user = qr.query(sql, new BeanHandler<User>(User.class), 2);
System.out.println(user);
}
@After
public void cleanUp() throws SQLException {
if (rs != null){
rs.close();
}
if (ps != null){
ps.close();
}
if(connection != null){
connection.close();
}
}
}
中级封装(通过反射):
package rk.utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import rk.news.entity.User;
import rk.news.utils.DBCPUtils;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @Author rk
* @Date 2018/12/4 16:53
* @Description:
**/
public class TestDBUtil2 {
private Connection connection;
private PreparedStatement ps;
private ResultSet rs;
@Before
public void setUp() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(DBCPUtils.url, DBCPUtils.username, DBCPUtils.password);
}
//条件查询
@Test
public void testQuery1() throws SQLException, IllegalAccessException, InstantiationException, IntrospectionException, InvocationTargetException {
String sql = "select id, name, birthday, sex, home_address from user where id = ?";
ps = connection.prepareStatement(sql);
ps.setObject(1,1);
rs = ps.executeQuery();
Class clazz = User.class;
Object obj = null;
while(rs.next()){//要想自动封装结果集,只能用反射
obj = clazz.newInstance();
//通过反射的方式拿到obj中的字段或者setter
// clazz.getField() 没有办法直接获取对应的字段,因为不知道对象的具体内容,只有一个clazz
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++){
String colName = rsmd.getColumnName(i);
Object colValue = rs.getObject(colName);
PropertyDescriptor pd = new PropertyDescriptor(colName, clazz);
// Method rM = pd.getReadMethod();
Method wM = pd.getWriteMethod();
// System.out.println(rM);
// System.out.println(wM);
wM.invoke(obj,colValue);
}
}
System.out.println(obj);
}
//查询全部
@Test
public void testQuery2() throws SQLException, IllegalAccessException, InstantiationException, InvocationTargetException, IntrospectionException {
String sql = "select id, name, birthday, sex, home_address from user";
ps = connection.prepareStatement(sql);
rs = ps.executeQuery();
Class clazz = User.class;
List list = new ArrayList();
while(rs.next()){//要想自动封装结果集,只能用反射
Object obj = clazz.newInstance();
//通过反射的方式拿到obj中的字段或者setter
// clazz.getField() 没有办法直接获取对应的字段,因为不知道对象的具体内容,只有一个clazz
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++){
String colName = rsmd.getColumnName(i);
Object colValue = rs.getObject(colName);
PropertyDescriptor pd = new PropertyDescriptor(colName, clazz);
// Method rM = pd.getReadMethod();
Method wM = pd.getWriteMethod();
// System.out.println(rM);
// System.out.println(wM);
wM.invoke(obj,colValue);
}
list.add(obj);
}
for (Object obj : list){
System.out.println(obj);
}
}
//使用QueryRunner查询
@Test
public void testQuery3() throws SQLException {
QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
List<User> list = qr.query("select id, name, birthday, sex, home_address from user", new BeanListHandler<User>(User.class));
for(User user : list){
System.out.println(user);
}
String sql = "select id, name, birthday, sex, home_address from user where id = ?";
User user = qr.query(sql, new BeanHandler<User>(User.class), 2);
System.out.println(user);
}
@After
public void cleanUp() throws SQLException {
if (rs != null){
rs.close();
}
if (ps != null){
ps.close();
}
if(connection != null){
connection.close();
}
}
}
终极封装(通过自定义的MyQueryRunner):
package rk.utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import rk.news.db.MyQueryRunner;
import rk.news.db.handler.impl.MyBeanHandler;
import rk.news.db.handler.impl.MyBeanListHandler;
import rk.news.entity.User;
import rk.news.utils.DBCPUtils;
import java.beans.IntrospectionException;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.List;
/**
* @Author rk
* @Date 2018/12/4 16:53
* @Description:
**/
public class TestDBUtil3 {
private Connection connection;
private PreparedStatement ps;
private ResultSet rs;
@Before
public void setUp() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(DBCPUtils.url, DBCPUtils.username, DBCPUtils.password);
}
//初级版:要封装的对象,和结果集
@Test
public void testQuery1() throws SQLException, IllegalAccessException, InstantiationException, IntrospectionException, InvocationTargetException {
String sql = "select id, name, birthday, sex, home_address from user where id = ?";
ps = connection.prepareStatement(sql);
ps.setObject(1,1);
rs = ps.executeQuery();
MyQueryRunner mqr = new MyQueryRunner();
User user = mqr.query( User.class, rs);
System.out.println(user);
}
//使用QueryRunner查询
@Test
public void testQuery2() throws SQLException {
QueryRunner qr = new QueryRunner(DBCPUtils.getDataSource());
List<User> list = qr.query("select id, name, birthday, sex, home_address from user", new BeanListHandler<User>(User.class));
for(User user : list){
System.out.println(user);
}
String sql = "select id, name, birthday, sex, home_address from user where id = ?";
User user = qr.query(sql, new BeanHandler<User>(User.class), 2);
System.out.println(user);
}
//中级版:查询语句和封装对象和参数
@Test
public void testQuery3() {
MyQueryRunner mqr = new MyQueryRunner(DBCPUtils.getDataSource());
String sql = "select id, name, birthday, sex, home_address from user where id = ?";
User user = mqr.query( sql, User.class,1);
System.out.println(user);
User user1 = mqr.query( sql, new MyBeanHandler<User> (User.class) ,1);
System.out.println(user1);
}
//条件查询最终版:查询语句和自定义类的泛型(封装对象)参数,返回一个对象
@Test
public void testQuery4() {
MyQueryRunner mqr = new MyQueryRunner(DBCPUtils.getDataSource());
String sql = "select id, name, birthday, sex, home_address from user where id = ?";
User user = mqr.query( sql, User.class,1);
System.out.println(user);
User user1 = mqr.query( sql, new MyBeanHandler<User> (User.class) ,1);
System.out.println(user1);
}
//全部查询最终版:查询语句和自定义类的泛型(封装对象)参数,返回是一个集合
@Test
public void testQuery5() {
MyQueryRunner mqr = new MyQueryRunner(DBCPUtils.getDataSource());
String sql = "select id, name, birthday, sex, home_address from user ";
//不能指定接收的类型,最终封装
List<User> list = mqr.query(sql, new MyBeanListHandler<User>(User.class));
for (User user : list) {
System.out.println(user);
}
}
@After
public void cleanUp() throws SQLException {
if (rs != null){
rs.close();
}
if (ps != null){
ps.close();
}
if(connection != null){
connection.close();
}
}
}
MyQueryRunner类:
package rk.news.db;
import rk.news.db.handler.MyResultHandler;
import javax.sql.DataSource;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.sql.*;
/**
* @Author rk
* @Date 2018/12/4 21:10
* @Description:
**/
public class MyQueryRunner {
private Connection connection;
public MyQueryRunner(){
}
public MyQueryRunner(DataSource dataSource) {
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
//最终版:查询语句和自定义类的泛型(封装对象)参数
public <T> T query(String sql, MyResultHandler<T> mrh, Object ... params){
try {
PreparedStatement ps = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++){
ps.setObject(i+1,params[i]);
}
ResultSet rs = ps.executeQuery();
return mrh.handle(rs);
}catch (Exception e){
e.printStackTrace();
}
return null;
}
//中级版:查询语句和封装对象和参数
public <T> T query(String sql, Class<T> t, Object ... params){
try {
PreparedStatement ps = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++){
ps.setObject(i+1,params[i]);
}
ResultSet rs = ps.executeQuery();
Class clazz = t;
Object obj = null;
while(rs.next()){//要想自动封装结果集,只能用反射
obj = clazz.newInstance();
//通过反射的方式拿到obj中的字段或者setter
// clazz.getField() 没有办法直接获取对应的字段,因为不知道对象的具体内容,只有一个clazz
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++){
String colName = rsmd.getColumnName(i);
Object colValue = rs.getObject(colName);
PropertyDescriptor pd = new PropertyDescriptor(colName, clazz);
// Method rM = pd.getReadMethod();
Method wM = pd.getWriteMethod();
// System.out.println(rM);
// System.out.println(wM);
wM.invoke(obj,colValue);
}
}
return (T) obj;
}catch (Exception e){
e.printStackTrace();
}
return null;
}
//初级版:要封装的对象,和结果集
public <T> T query(Class<T> t, ResultSet rs){
try {
Class clazz = t;
Object obj = null;
while(rs.next()){//要想自动封装结果集,只能用反射
obj = clazz.newInstance();
//通过反射的方式拿到obj中的字段或者setter
// clazz.getField() 没有办法直接获取对应的字段,因为不知道对象的具体内容,只有一个clazz
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++){
String colName = rsmd.getColumnName(i);
Object colValue = rs.getObject(colName);
PropertyDescriptor pd = new PropertyDescriptor(colName, clazz);
// Method rM = pd.getReadMethod();
Method wM = pd.getWriteMethod();
// System.out.println(rM);
// System.out.println(wM);
wM.invoke(obj,colValue);
}
}
return (T) obj;
}catch (Exception e){
e.printStackTrace();
}
return null;
}
}
用来保存查询出来的结果:ResultHandler接口
package rk.news.db.handler;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @Author rk
* @Date 2018/12/5 11:39
* @Description:
**/
public interface MyResultHandler<T> {
T handle(ResultSet rs) throws SQLException;
}
用来保存查询出来一个对象的结果:MyBeanHandler
package rk.news.db.handler.impl;
import rk.news.db.handler.MyResultHandler;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
/**
* @Author rk
* @Date 2018/12/5 11:41
* @Description:
**/
public class MyBeanHandler<T> implements MyResultHandler<T> {
private Class<T> clazz;
public MyBeanHandler(Class<T> clazz){
this.clazz = clazz;
}
@Override
public T handle(ResultSet rs) {
try {
T obj = null;
while (rs.next()) {//要想自动封装结果集,只能用反射
obj = clazz.newInstance();
//通过反射的方式拿到obj中的字段或者setter
// clazz.getField() 没有办法直接获取对应的字段,因为不知道对象的具体内容,只有一个clazz
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
String colName = rsmd.getColumnName(i);
Object colValue = rs.getObject(colName);
PropertyDescriptor pd = new PropertyDescriptor(colName, clazz);
// Method rM = pd.getReadMethod();
Method wM = pd.getWriteMethod();
// System.out.println(rM);
// System.out.println(wM);
wM.invoke(obj, colValue);
}
}
return obj;
} catch (InstantiationException e ) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (IntrospectionException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return null;
}
}
用来保存查询出来一个对象集合的结果:MyBeanListHandler
package rk.news.db.handler.impl;
import rk.news.db.handler.MyResultHandler;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* @Author rk
* @Date 2018/12/5 11:54
* @Description:
**/
public class MyBeanListHandler<T> implements MyResultHandler<List<T>> {
private Class<T> clazz;
public MyBeanListHandler(Class<T> clazz) {
this.clazz = clazz;
}
@Override
public List<T> handle(ResultSet rs) {
try{
List<T> list = new ArrayList<>();
while(rs.next()){//要想自动封装结果集,只能用反射
T obj = clazz.newInstance();
//通过反射的方式拿到obj中的字段或者setter
// clazz.getField() 没有办法直接获取对应的字段,因为不知道对象的具体内容,只有一个clazz
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++){
String colName = rsmd.getColumnName(i);
Object colValue = rs.getObject(colName);
PropertyDescriptor pd = new PropertyDescriptor(colName, clazz);
Method wM = pd.getWriteMethod();
wM.invoke(obj,colValue);
}
list.add(obj);
}
return list;
} catch (InstantiationException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (IntrospectionException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return null;
}
}
注意:在进行封装时,泛型的使用不是太熟悉的,还是要多了解了解!!!