JDBC小工具
使用jdbc小工具有利于我们减少大量的重复代码的工作量,当然也会加深理解难度,所以最好对Java泛型这方面的知识有足够的把握。
1、工具类
package com.Server.dao;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import com.Server.utils.jdbcUtils;
public class BaseDao<T> {
private Class<T> clazz;
private Logger logger=Logger.getLogger(this.getClass());//日志
@SuppressWarnings("unchecked")
public BaseDao(){
//用BaseDao的构造方法初始化Clazz属性
Type superType=this.getClass().getGenericSuperclass();//拿到调用着发父类的属性
if(superType instanceof ParameterizedType){
ParameterizedType pType=(ParameterizedType) superType;
Type[] tarry=pType.getActualTypeArguments();//User.Class
if(tarry[0] instanceof Class){
clazz=(Class<T>) tarry[0];
System.out.println(tarry);
}
}
}
QueryRunner queryRunner =new QueryRunner();
/**
* 查询数据表,取出sql语句的结果集的第一条数据,封装成一个类的对象返回,不支持事物
* @param sql
* @param args
* @return
*/
/**
* null的位置应该传入BaseDao<T>里面的T是真正用的时候的类型的Class
* @param sql
* @param args
* @return
* @throws LoginSQLException
*/
public T get(String sql,Object... args) throws LoginSQLException {
Connection conn= null;
T entity=null;
try {
conn=jdbcUtils.getConnection();
entity= queryRunner.query(conn, sql,new BeanHandler<T>(clazz), args);
} catch (Exception e) {
System.out.println("数据库异常");
logger.info("数据库get异常:"+e.getMessage()+"\n");
throw new LoginSQLException(e);
}finally {
jdbcUtils.CloseConn(conn);
}
return entity;
}
public T get(Connection conn,String sql,Object... args){
T entity=null;
try {
entity= queryRunner.query(conn, sql,new BeanHandler<T>(clazz), args);
} catch (Exception e) {
logger.info("数据库 get异常:"+e.getMessage());
}
return entity;
}
/**
* 获取多条记录的通用方法
* @return
*/
public List<T> getList(String sql,Object... args){
Connection conn= null;
List<T>list=null;
try {
conn=jdbcUtils.getConnection();
list= queryRunner.query(conn,sql,new BeanListHandler<T>(clazz),args);
} catch (Exception e) {
System.out.println("数据库异常:"+e.getMessage()+"\n");
logger.info("数据库异常getList:"+e.getMessage());
}finally {
jdbcUtils.CloseConn(conn);
}
return list;
}
/**
* 通用实现 insert,update,delete的更新方法
* @param sql
* @param args
* @return
*/
public int update(String sql,Object... args){
Connection conn= null;
int rows=0;
try {
conn=jdbcUtils.getConnection();
rows= queryRunner.update(conn,sql,args);
System.out.println(sql);
} catch (Exception e) {
logger.info("数据库update异常"+e.getMessage()+"\n");
e.printStackTrace();
}finally {
jdbcUtils.CloseConn(conn);
}
return rows;
}
/**
* 通用的返回sql语句结果只有一个数值的类型的查询,例如count(id)
* @param sql
* @param args
* @return
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public Object getValue(String sql,Object... args){
Connection conn= null;
Object obj=null;
try {
conn=jdbcUtils.getConnection();
obj= queryRunner.query(conn,sql, new ScalarHandler(),args);
} catch (Exception e) {
logger.info("数据库Object getValue异常"+e.getMessage()+"\n");
e.printStackTrace();
}finally {
jdbcUtils.CloseConn(conn);
}
return obj;
}
}
连接jdbc的小工具在上一篇文章里,这里直接调用获得connection。
2、用法案例
创建一个Javabean即模型类,可作为返回的封装对象,这里创建一个Monitors的对象。
package com.Server.model;
import java.util.Date;
/**
* @author ZP
*2018年11月1日
*
*/
public class Monitors {
private int ID;
private String UserName;
private int States;
private int Count;
private String Monitor;
private String Share;
private Date Time;
public int getID() {
return ID;
}
public void setID(int iD) {
ID = iD;
}
public String getUserName() {
return UserName;
}
public void setUserName(String userName) {
UserName = userName;
}
public int getStates() {
return States;
}
public void setStates(int states) {
States = states;
}
public int getCount() {
return Count;
}
public void setCount(int count) {
Count = count;
}
public String getMonitor() {
return Monitor;
}
public void setMonitor(String monitor) {
Monitor = monitor;
}
public String getShare() {
return Share;
}
public void setShare(String share) {
Share = share;
}
public Date getTime() {
return Time;
}
public void setTime(Date time) {
Time = time;
}
public Monitors() {
super();
}
public Monitors(int iD, String userName, int states, int count, String monitor, String share, Date time) {
super();
ID = iD;
UserName = userName;
States = states;
Count = count;
Monitor = monitor;
Share = share;
Time = time;
}
@Override
public String toString() {
return "Monitors [ID=" + ID + ", UserName=" + UserName + ", States=" + States + ", Count=" + Count
+ ", Monitor=" + Monitor + ", Share=" + Share + ", Time=" + Time + "]";
}
}
创建ZPTEST测试类
package com.Server.Test;
import com.Server.model.Monitors;
import com.Server.dao.BaseDao;
public class ZPTEST extends BaseDao<Monitors>{
@Test
public void getListName() {
// TODO Auto-generated method stub
String sql="select UserName from monitors where States=1";
List<Monitors> monitors = super.getList(sql);
System.out.println(monitors.toString());
}
}
3、测试结果:
这个工具确实是好用,给我在用mybatis的感觉,当然,小工具和框架还是有很大区别的。