cat.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
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.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DBUtil {
private static DataSource dataSource =
null;
// 把构造函数私有化,可以防止别人new本类的实例
private DBUtil() {
}
// 创建数据源
static {
dataSource =
new ComboPooledDataSource("mysql");
//mysql是在配置文件中配置的
}
// 从C3P0数据源获取Connection对象(数据库连接对象)
public static Connection getConn() {
Connection conn =
null;
try {
conn = dataSource.getConnection();
// 返回的是经过代理的对象,其close方法已经被重写
}
catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
return conn;
}
// 关闭资源
public static void close(ResultSet rs, Statement stm, Connection conn) {
if (rs !=
null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stm !=
null) {
try {
stm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn !=
null) {
try {
conn.close(); // 关连接的方法和过去一样,由于数据源返回的Connection对象是代理对象,所以close方法被处理
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
// 关闭资源
public static void close(Connection conn) {
if (conn !=
null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 万能更新(可以进行添加,更新,删除三种操作)
public static int update(String sql, Object... params) {
int result = 0
;
QueryRunner qr =
new QueryRunner();
// 是一个线程不安全的类
Connection conn=
getConn();
try {
result =
qr.update(conn, sql, params);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
close(conn);
}
return result;
}
// 添加数据,并将生成的自增ID返回
public static int addWithId(String sql, Object... params) {
int autoId = 0
;
Connection conn =
null;
PreparedStatement stm =
null;
ResultSet rs =
null;
try {
conn =
getConn();
stm =
conn.prepareStatement(sql,
PreparedStatement.RETURN_GENERATED_KEYS);
for (
int i = 0; i < params.length; i++
) {
stm.setObject(i + 1
, params[i]);
}
// 执行添加操作
stm.executeUpdate();
// 取出生成的自增ID
ResultSet rsKey =
stm.getGeneratedKeys();
rsKey.next();
autoId = rsKey.getInt(1
);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
close(rs, stm, conn);
}
return autoId;
}
// 查询出一个单个的对象
public static <T> T getSingleObj(String sql, Class<T>
clazz,
Object... params) {
QueryRunner qr =
new QueryRunner();
T result =
null;
Connection conn=
getConn();
try {
result = qr.query(conn, sql,
new BeanHandler<T>
(clazz),
params);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
close(conn);
}
return result;
}
// 查询出对象列表(以ArrayList的方式返回),注意,如果没有查询到数据,该方法返回一个空列表,而不是null
public static <T> List<T> getList(String sql, Class<T>
clazz,
Object... params) {
List<T> list =
new ArrayList<T>
();
QueryRunner qr =
new QueryRunner();
Connection conn =
getConn();
try {
list = qr.query(conn, sql,
new BeanListHandler<T>
(clazz), params);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally{
close(conn);
}
return list;
}
// 返回Map集合(该方法只将一条数据返回为Map集合,key为字段名称,value为字段值)
public static Map<String, Object>
getMap(String sql, Object... params) {
Map<String, Object> m =
null;
QueryRunner qr =
new QueryRunner();
Connection conn =
getConn();
try {
m = qr.query(conn, sql,
new MapHandler(), params);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
close(conn);
}
return m;
}
// 返回一个List集合,其中每条数据都被封装成了一个Map集合,
public static List<Map<String, Object>>
getMapList(String sql,
Object... params) {
List<Map<String, Object>> mapList =
new ArrayList<Map<String, Object>>
();
QueryRunner qr =
new QueryRunner();
Connection conn =
getConn();
try {
mapList = qr.query(conn, sql,
new MapListHandler(),
params);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
close(conn);
}
return mapList;
}
// 返回单行单个数据,该方法可以用来查询记录数(这时请使用Long型进行接收),单个字段值等数据
public static <T>
T getScalar(String sql, Object... obj) {
T result =
null;
QueryRunner qr =
new QueryRunner();
Connection conn =
getConn();
try {
result = qr.query(conn, sql,
new ScalarHandler<T>(1
), obj);
} catch (Exception ex) {
ex.printStackTrace();
throw new RuntimeException(ex);
} finally {
close(conn);
}
return result;
}
}
2 AdminDao操作示例
package cat.dao;
import java.util.List;
import java.util.Map;
import cat.beans.AdminInfo;
import cat.jdbc.DBUtil;
public class AdminDao {
//测试添加
//2016-06-20 11:54 增加状态字段state
public int addAdmin(AdminInfo admin){
String sql="insert into adminInfo (adminName,password,note,state) values (?,?,?,?)";
Object [] params={
admin.getAdminName(),
admin.getPassword(),
admin.getNote(),
admin.getState()
};
return DBUtil.update(sql, params);
}
//测试查询单个对象
public AdminInfo getAdminById(int id){
String sql="select * from adminInfo where id=?";
return DBUtil.getSingleObj(sql, AdminInfo.class, id);
}
//测试查询一组对象
//2016-06-20 14:35 更改sql语句 添加 按照时间降序排序
public List<AdminInfo> getAllAdmin(){
String sql="select * from adminInfo order by editDate desc";
return DBUtil.getList(sql, AdminInfo.class);
}
//测试返回id
public int addAdminWidthId(AdminInfo admin){
String sql="insert into adminInfo (adminName,password,note) values (?,?,?)";
Object [] params={
admin.getAdminName(),
admin.getPassword(),
admin.getNote()
};
int id=DBUtil.addWithId(sql, params);
System.out.println("生成的id是"+id);
return id;
}
//测试返回Map集合
public Map<String,Object> getAdminMap(int id){
String sql="select * from adminInfo where id=?";
return DBUtil.getMap(sql, id);
}
//测试返回一组Map集合
public List<Map<String,Object>> getAdminListMap(){
String sql="select * from adminInfo";
return DBUtil.getMapList(sql);
}
//测试返回行数(注意,对于查询行数,返回的默认是long型,所以要转成int型)
public int getAdmminCount(){
String sql="select count(*) from adminInfo";
long count=DBUtil.getScalar(sql);
return new Integer(count+"");
}
//测试删除
public int delAdminById(int id){
String sql="delete from adminInfo where id=?";
return DBUtil.update(sql, id);
}
public static void main(String[] args) {
AdminDao dao=new AdminDao();
AdminInfo admin=new AdminInfo();
admin.setAdminName("张三");
admin.setPassword("123");
admin.setNote("这是备注信息");
int result=dao.addAdmin(admin);
System.out.println(1);
/*
AdminInfo admin=dao.getAdminById(22);
System.out.println(admin); */
/*
List<AdminInfo> adminList=dao.getAllAdmin();
for(AdminInfo admin:adminList){
System.out.println(admin);
}
*/
/*
AdminInfo admin=new AdminInfo();
admin.setAdminName("张三");
admin.setPassword("123");
admin.setNote("这是备注信息");
int id=dao.addAdminWidthId(admin); */
/*
Map<String ,Object> adminMap=dao.getAdminMap(23);
for(Map.Entry<String, Object> item: adminMap.entrySet()){
System.out.println(item.getKey() +"-"+item.getValue());
}
*/
/*List<Map<String,Object>> mapList=dao.getAdminListMap();
for(Map<String,Object> m:mapList){
for(Map.Entry<String, Object> item: m.entrySet()){
System.out.print(item.getKey() +"-"+item.getValue()+"\t");
}
System.out.println();
}*/
/* int count=dao.getAdmminCount();
System.out.println(count);*/
// int result=dao.delAdminById(25);
System.out.println(result);
}
//根据用户名和密码,验证用户信息(用于登录页)
public AdminInfo getLoginAdmin(String adminName, String password) {
String sql="select * from adminInfo where adminName=? and password=? and state =?";
return DBUtil.getSingleObj(sql,AdminInfo.class, adminName,password,1);
}
}
骗字数的分割线-===
博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园
博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园
博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园
博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园
博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园博客园
数据库工具类（我老师写的 感觉很好用）
标签：
