1:导入lib包
2:引入连接池配置文件
3:工具类
3.1: DataSourceUtils
/**
* 加载properties配置文件 + oracle驱动
*/
public class DataSourceUtils{
public static Properties properties = new Properties();
private static DataSource dataSource;
static {
try {
// 加载配置文件
InputStream is = Thread.currentThread().getContextClassLoader()
.getResourceAsStream("properties.properties");
properties.load(is);
// 加载oracle驱动
Class.forName(properties.getProperty("driverClass"));
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/* 获取连接对象 */
public static Connection getConnection() {
String oracleUrl = properties.getProperty("oracleUrl");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
try {
return DriverManager.getConnection(oracleUrl, user, password);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/*获取c3p0数据源*/
public static DataSource getC3P0DataSource() {
if(dataSource==null){
try
{
dataSource = new ComboPooledDataSource();
}catch(Exception e){
throw new RuntimeException(e + "\tclasspath下找不到 [c3p0-config.xml] 配置文件 ");
}
}
return dataSource;
}
}
3.2:DBUtils
/**
* 对DBUtils的sql操作简单封装
*
*/
@SuppressWarnings({"rawtypes","unchecked"})
public class DBUtils {
/**
* 查询操作
* params 1: runner :QueryRunner操作对象 2: appEndSql: where条件sql语句 3: params:占位符参参数 4: 对数据库字段一一对应的bean类
* return list包装:符合条件的对象
*/
public static List<Object> executionQuery(QueryRunner runner,String appEndSql,List params,Class clazz){
List<Object> query = null;
try {
String sql = "SELECT * FROM "+clazz.getSimpleName()+" WHERE 1=1 " + appEndSql;
System.out.println(sql);
query = runner.query(sql,new BeanListHandler<>(clazz),params.toArray());
} catch (SQLException e) {
throw new RuntimeException(e);
}
return query;
}
/**
* 更新操作:增、删、改
* params 1:runner=QueryRunner操作对象 2: sql:sql语句 3: params:占位符参参数
* return 更新的记录数
*/
public static int executionUpdate(QueryRunner runner,String sql,List params){
Connection con = null;
int updateNumb;
try {
//从数据库中获取连接对象
con = runner.getDataSource().getConnection();
con.setAutoCommit(false);//取消自动提交事务
updateNumb = runner.update(sql,params.toArray());
con.commit();//提交
} catch (Exception e) {
try {
if(con!=null)
con.rollback(); //回滚
} catch (SQLException e1) {
throw new RuntimeException(e1);
}
throw new RuntimeException(e);
}
return updateNumb;
}
/**
* 查询操作
* params 1: con连接对象 2: appEndSql: where条件sql语句 3: params:占位符参参数 4: 对数据库字段一一对应的bean类
* return list包装:符合条件的对象
*/
public static List<Object> executionQuery(Connection con,String appEndSql,List params,Class clazz){
QueryRunner runner = new QueryRunner();
List<Object> query = null;
try {
String sql = "SELECT * FROM "+clazz.getSimpleName()+" WHERE 1=1 " + appEndSql;
System.out.println(sql);
query = runner.query(con,sql,new BeanListHandler<>(clazz),params.toArray());
} catch (SQLException e) {
throw new RuntimeException(e);
}
return query;
}
/**
* 更新操作:增、删、改
* params 1: con连接对象 2: sql:sql语句 3: params:占位符参参数
* return 更新的记录数
*/
public static int executionUpdate(Connection con,String sql,List params){
QueryRunner runner = new QueryRunner();
int updateNumb;
try {
con.setAutoCommit(false);//取消自动提交事务
updateNumb = runner.update(con,sql,params.toArray());
con.commit();//提交
} catch (Exception e) {
try {
con.rollback(); //回滚
} catch (SQLException e1) {
throw new RuntimeException(e1);
}
throw new RuntimeException(e);
}
return updateNumb;
}
}
3.3 JDBCUtils
/**
* @author hubiao
* JDBC工具类
*/
@SuppressWarnings({"rawtypes","unchecked"})
public class JDBCUtils {
/** 设置日期格式*/
public static String dateForma = "yyyy-MM-DD HH:mm:ss.SS";
/**
* executeUpdate:执行:add、update、delete
* params
* con:Connection连接对象
* sql:操作数据库的sql语句
* params:参数对象
* return
* int 更新记录数
*/
public static int executionUpdate(Connection con,String sql,List<Object> params) {
int update;
try {
con.setAutoCommit(false);
PreparedStatement statement = con.prepareStatement(sql);
for(int x = 1; x <=params.size() ;x ++){
statement.setObject(x, params.get(x-1));
}
update = statement.executeUpdate();
con.commit();
} catch (Exception e) {
try {
con.rollback();
} catch (SQLException e1) {
throw new RuntimeException(e);
}
throw new RuntimeException(e);
}
return update;
}
/**
* executeQuery:执行:add、update、delete
* params
* con:Connection连接对象
* sql:操作数据库的sql语句
* params:参数对象
* return
* int 更新记录数
*/
public static List executionQuery(Connection con,String appEndSql,List<Object> params,Class clazz) {
List arrayList = new ArrayList();
ResultSet executeQuery = null;
try {
String sql = String.format("SELECT * FROM %s WHERE 1 = 1 %S",clazz.getSimpleName() ,appEndSql);
System.out.println(sql);
PreparedStatement statement = con.prepareStatement(sql);
for(int x = 1; x <=params.size() ;x ++){
statement.setObject(x, params.get(x-1));
}
executeQuery = statement.executeQuery();
} catch (Exception e) {
throw new RuntimeException(e);
}
try {
while(executeQuery.next()){
Object instance = clazz.newInstance();
Field[] fields = clazz.getDeclaredFields();
for(Field f : fields){
String fieldName = f.getName();
Method method = null;
String methodName = null;
try{
//组织参数,调用Set方法
methodName = String.format("set%s%s", fieldName.substring(0, 1).toUpperCase(),fieldName.substring(1));
method = clazz.getMethod(methodName, f.getType());
}catch(Exception e){
System.out.println("在"+clazz+" 中找不到 "+methodName+" 方法,异常为:"+e);
continue;
}
Object object = null;
try{
object = executeQuery.getObject(fieldName);
}catch(Exception e){
System.out.println("没有找不到 "+methodName+" 字段,异常为:"+e);
continue;
}
if(object==null)
continue;
//把值类型转为String,再转给参数的相应类型。
String objStr = object.toString();
//日期转换
if(f.getType().getName().contains("Date")){
Date date = DateConverter(objStr);
method.invoke(instance, date);
continue;
}
Constructor constructor = f.getType().getConstructor(String.class);
object = constructor.newInstance(objStr);
method.invoke(instance, object);
}
arrayList.add(instance);
}
} catch (Exception e) {
throw new RuntimeException(e);
}
return arrayList;
}
/*日期类型转换器*/
public static Date DateConverter(String source){
SimpleDateFormat sdf = new SimpleDateFormat(dateForma);
Date parse;
try {
parse = sdf.parse(source);
} catch (ParseException e) {
throw new RuntimeException(e);
}
return parse;
}
}
源代码下载:点击打开链接