开启辅助访问 注册|登录
 找回密码
 立即注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

知蛛网

查看: 101|回复: 0

一个java最精简实用的jdbc工具类

[复制链接]

2万

主题

2万

帖子

7万

积分

超级版主

Rank: 8Rank: 8

积分
78631
发表于 2019-8-6 17:56:43 | 显示全部楼层 |阅读模式

java类代码如下:

package util.db;

import java.io.ioexception;

import java.lang.reflect.constructor;

import java.lang.reflect.field;

import java.sql.callablestatement;

import java.sql.connection;

import java.sql.drivermanager;

import java.sql.preparedstatement;

import java.sql.resultset;

import java.sql.resultsetmetadata;

import java.sql.sqlexception;

import java.sql.statement;

import java.sql.time;

import java.util.arraylist;

import java.util.date;

import java.util.hashmap;

import java.util.list;

import java.util.map;

import java.util.properties;

import oracle.jdbc.oracletypes;

import util.db.test.dbutiltest;

/**

* @author yinbin

* dbutil,数据库访问工具类

* 对应测试类: {@link dbutiltest}

* @preserve all

*/

public class dbutil {

private static connection con = null;

public static connection openconnection() throws sqlexception, classnotfoundexception, ioexception {

if (null == con || con.isclosed()) {

properties p = new properties();

p.load(dbutil.class.getresourceasstream("/config-db.properties"));

class.forname(p.getproperty("db_driver"));

con = drivermanager.getconnection(p.getproperty("db_url"), p.getproperty("db_username"),

p.getproperty("db_password"));

}

return con;

}

public static void closeconnection() throws sqlexception {

try {

if (null != con)

con.close();

} finally {

con = null;

system.gc();

}

}

public static list> querymaplist(connection con, string sql) throws sqlexception,

instantiationexception, illegalaccessexception {

list> lists = new arraylist>();

statement prestmt = null;

resultset rs = null;

try {

prestmt = con.createstatement();

rs = prestmt.executequery(sql);

resultsetmetadata rsmd = rs.getmetadata();

int columncount = rsmd.getcolumncount();

while (null != rs && rs.next()) {

mapmap = new hashmap();

for (int i = 0; i < columncount; i++) {

string name = rsmd.getcolumnname(i + 1);

object value = rs.getobject(name);

map.put(name, value);

}

lists.add(map);

}

} finally {

if (null != rs)

rs.close();

if (null != prestmt)

prestmt.close();

}

return lists;

}

public static list> querymaplist(connection con, string sql, object... params)

throws sqlexception, instantiationexception, illegalaccessexception {

list> lists = new arraylist>();

preparedstatement prestmt = null;

resultset rs = null;

try {

prestmt = con.preparestatement(sql);

for (int i = 0; i < params.length; i++)

prestmt.setobject(i + 1, params[i]);// 下标从1开始

rs = prestmt.executequery();

resultsetmetadata rsmd = rs.getmetadata();

int columncount = rsmd.getcolumncount();

while (null != rs && rs.next()) {

mapmap = new hashmap();

for (int i = 0; i < columncount; i++) {

string name = rsmd.getcolumnname(i + 1);

object value = rs.getobject(name);

map.put(name, value);

}

lists.add(map);

}

} finally {

if (null != rs)

rs.close();

if (null != prestmt)

prestmt.close();

}

return lists;

}

public staticlistquerybeanlist(connection con, string sql, classbeanclass) throws sqlexception,

instantiationexception, illegalaccessexception {

listlists = new arraylist();

statement stmt = null;

resultset rs = null;

field[] fields = null;

try {

stmt = con.createstatement();

rs = stmt.executequery(sql);

fields = beanclass.getdeclaredfields();

for (field f : fields)

f.setaccessible(true);

while (null != rs && rs.next()) {

t t = beanclass.newinstance();

for (field f : fields) {

string name = f.getname();

try {

object value = rs.getobject(name);

setvalue(t, f, value);

} catch (exception e) {

}

}

lists.add(t);

}

} finally {

if (null != rs)

rs.close();

if (null != stmt)

stmt.close();

}

return lists;

}

public staticlistquerybeanlist(connection con, string sql, classbeanclass, object... params)

throws sqlexception, instantiationexception, illegalaccessexception {

listlists = new arraylist();

preparedstatement prestmt = null;

resultset rs = null;

field[] fields = null;

try {

prestmt = con.preparestatement(sql);

for (int i = 0; i < params.length; i++)

prestmt.setobject(i + 1, params[i]);// 下标从1开始

rs = prestmt.executequery();

fields = beanclass.getdeclaredfields();

for (field f : fields)

f.setaccessible(true);

while (null != rs && rs.next()) {

t t = beanclass.newinstance();

for (field f : fields) {

string name = f.getname();

try {

object value = rs.getobject(name);

setvalue(t, f, value);

} catch (exception e) {

}

}

lists.add(t);

}

} finally {

if (null != rs)

rs.close();

if (null != prestmt)

prestmt.close();

}

return lists;

}

public staticlistquerybeanlist(connection con, string sql, iresultsetcallqdi) throws sqlexception {

listlists = new arraylist();

statement stmt = null;

resultset rs = null;

try {

stmt = con.createstatement();

rs = stmt.executequery(sql);

while (null != rs && rs.next())

lists.add(qdi.invoke(rs));

} finally {

if (null != rs)

rs.close();

if (null != stmt)

stmt.close();

}

return lists;

}

public staticlistquerybeanlist(connection con, string sql, iresultsetcallqdi, object... params)

throws sqlexception {

listlists = new arraylist();

preparedstatement prestmt = null;

resultset rs = null;

try {

prestmt = con.preparestatement(sql);

for (int i = 0; i < params.length; i++)

prestmt.setobject(i + 1, params[i]);

rs = prestmt.executequery();

while (null != rs && rs.next())

lists.add(qdi.invoke(rs));

} finally {

if (null != rs)

rs.close();

if (null != prestmt)

prestmt.close();

}

return lists;

}

public statict querybean(connection con, string sql, classbeanclass) throws sqlexception,

instantiationexception, illegalaccessexception {

listlists = querybeanlist(con, sql, beanclass);

if (lists.size() != 1)

throw new sqlexception("sqlerror:期待一行返回值,却返回了太多行!");

return lists.get(0);

}

public statict querybean(connection con, string sql, classbeanclass, object... params)

throws sqlexception, instantiationexception, illegalaccessexception {

listlists = querybeanlist(con, sql, beanclass, params);

if (lists.size() != 1)

throw new sqlexception("sqlerror:期待一行返回值,却返回了太多行!");

return lists.get(0);

}

public staticlistqueryobjectlist(connection con, string sql, classobjclass) throws sqlexception,

instantiationexception, illegalaccessexception {

listlists = new arraylist();

statement stmt = null;

resultset rs = null;

try {

stmt = con.createstatement();

rs = stmt.executequery(sql);

label: while (null != rs && rs.next()) {

constructor[] constor = objclass.getconstructors();

for (constructorc : constor) {

object value = rs.getobject(1);

try {

lists.add((t) c.newinstance(value));

continue label;

} catch (exception e) {

}

}

}

} finally {

if (null != rs)

rs.close();

if (null != stmt)

stmt.close();

}

return lists;

}

public staticlistqueryobjectlist(connection con, string sql, classobjclass, object... params)

throws sqlexception, instantiationexception, illegalaccessexception {

listlists = new arraylist();

preparedstatement prestmt = null;

resultset rs = null;

try {

prestmt = con.preparestatement(sql);

for (int i = 0; i < params.length; i++)

prestmt.setobject(i + 1, params[i]);

rs = prestmt.executequery();

label: while (null != rs && rs.next()) {

constructor[] constor = objclass.getconstructors();

for (constructorc : constor) {

string value = rs.getobject(1).tostring();

try {

t t = (t) c.newinstance(value);

lists.add(t);

continue label;

} catch (exception e) {

}

}

}

} finally {

if (null != rs)

rs.close();

if (null != prestmt)

prestmt.close();

}

return lists;

}

public statict queryobject(connection con, string sql, classobjclass) throws sqlexception,

instantiationexception, illegalaccessexception {

listlists = queryobjectlist(con, sql, objclass);

if (lists.size() != 1)

throw new sqlexception("sqlerror:期待一行返回值,却返回了太多行!");

return lists.get(0);

}

public statict queryobject(connection con, string sql, classobjclass, object... params)

throws sqlexception, instantiationexception, illegalaccessexception {

listlists = queryobjectlist(con, sql, objclass, params);

if (lists.size() != 1)

throw new sqlexception("sqlerror:期待一行返回值,却返回了太多行!");

return lists.get(0);

}

public static int execute(connection con, string sql) throws sqlexception {

statement stmt = null;

try {

stmt = con.createstatement();

return stmt.executeupdate(sql);

} finally {

if (null != stmt)

stmt.close();

}

}

public static int execute(connection con, string sql, object... params) throws sqlexception {

preparedstatement prestmt = null;

try {

prestmt = con.preparestatement(sql);

for (int i = 0; i < params.length; i++)

prestmt.setobject(i + 1, params[i]);// 下标从1开始

return prestmt.executeupdate();

} finally {

if (null != prestmt)

prestmt.close();

}

}

public static int[] executeasbatch(connection con, listsqllist) throws sqlexception {

return executeasbatch(con, sqllist.toarray(new string[] {}));

}

public static int[] executeasbatch(connection con, string[] sqlarray) throws sqlexception {

statement stmt = null;

try {

stmt = con.createstatement();

for (string sql : sqlarray) {

stmt.addbatch(sql);

}

return stmt.executebatch();

} finally {

if (null != stmt) {

stmt.close();

}

}

}

public static int[] executeasbatch(connection con, string sql, object[][] params) throws sqlexception {

preparedstatement prestmt = null;

try {

prestmt = con.preparestatement(sql);

for (int i = 0; i < params.length; i++) {

object[] rowparams = params[i];

for (int k = 0; k < rowparams.length; k++) {

object obj = rowparams[k];

prestmt.setobject(k + 1, obj);

}

prestmt.addbatch();

}

return prestmt.executebatch();

} finally {

if (null != prestmt) {

prestmt.close();

}

}

}

private staticvoid setvalue(t t, field f, object value) throws illegalaccessexception {

// todo 以数据库类型为准绳,还是以java数据类型为准绳?还是混合两种方式?

if (null == value)

return;

string v = value.tostring();

string n = f.gettype().getname();

if ("java.lang.byte".equals(n) || "byte".equals(n)) {

f.set(t, byte.parsebyte(v));

} else if ("java.lang.short".equals(n) || "short".equals(n)) {

f.set(t, short.parseshort(v));

} else if ("java.lang.integer".equals(n) || "int".equals(n)) {

f.set(t, integer.parseint(v));

} else if ("java.lang.long".equals(n) || "long".equals(n)) {

f.set(t, long.parselong(v));

} else if ("java.lang.float".equals(n) || "float".equals(n)) {

f.set(t, float.parsefloat(v));

} else if ("java.lang.double".equals(n) || "double".equals(n)) {

f.set(t, double.parsedouble(v));

} else if ("java.lang.string".equals(n)) {

f.set(t, value.tostring());

} else if ("java.lang.character".equals(n) || "char".equals(n)) {

f.set(t, (character) value);

} else if ("java.lang.date".equals(n)) {

f.set(t, new date(((java.sql.date) value).gettime()));

} else if ("java.lang.timer".equals(n)) {

f.set(t, new time(((java.sql.time) value).gettime()));

} else if ("java.sql.timestamp".equals(n)) {

f.set(t, (java.sql.timestamp) value);

} else {

system.out.println("sqlerror:暂时不支持此数据类型,请使用其他类型代替此类型!");

}

}

public static void executeprocedure(connection con, string procedurename, object... params) throws sqlexception {

callablestatement proc = null;

try {

proc = con.preparecall(procedurename);

for (int i = 0; i < params.length; i++) {

proc.setobject(i + 1, params[i]);

}

proc.execute();

} finally {

if (null != proc)

proc.close();

}

}

public static boolean executeprocedurereturnerrormsg(connection con, string procedurename, stringbuffer errormsg,

object... params) throws sqlexception {

callablestatement proc = null;

try {

proc = con.preparecall(procedurename);

proc.registeroutparameter(1, oracletypes.varchar);

for (int i = 0; i < params.length; i++) {

proc.setobject(i + 2, params[i]);

}

boolean b = proc.execute();

errormsg.append(proc.getstring(1));

return b;

} finally {

if (null != proc)

proc.close();

}

}

public staticlistexecuteprocedurereturncursor(connection con, string procedurename, classbeanclass,

object... params) throws sqlexception, instantiationexception, illegalaccessexception {

listlists = new arraylist();

callablestatement proc = null;

resultset rs = null;

try {

proc = con.preparecall(procedurename);

proc.registeroutparameter(1, oracletypes.cursor);

for (int i = 0; i < params.length; i++) {

proc.setobject(i + 2, params[i]);

}

boolean b = proc.execute();

if (b) {

rs = (resultset) proc.getobject(1);

while (null != rs && rs.next()) {

t t = beanclass.newinstance();

field[] fields = beanclass.getdeclaredfields();

for (field f : fields) {

f.setaccessible(true);

string name = f.getname();

try {

object value = rs.getobject(name);

setvalue(t, f, value);

} catch (exception e) {

}

}

lists.add(t);

}

}

} finally {

if (null != rs)

rs.close();

if (null != proc)

proc.close();

}

return lists;

}

public staticlist> listlimit(listlists, int pagesize) {

list> llists = new arraylist>();

for (int i = 0; i < lists.size(); i = i + pagesize) {

try {

listlist = lists.sublist(i, i + pagesize);

llists.add(list);

} catch (indexoutofboundsexception e) {

listlist = lists.sublist(i, i + (lists.size() % pagesize));

llists.add(list);

}

}

return llists;

}

/* public static void executeprocedure(connection con, string sql) {

callablestatement callstmt = null;

try {

callstmt = con.preparecall(sql);

callstmt.exe

} catch (exception e) {

e.printstacktrace();

}

}*/

}

来源:https://github.com/felixyin/dbutil/

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|广告投放|项目策划|小黑屋|知蛛网 ( 蜀ICP备18034777号 )

GMT+8, 2019-11-22 13:16 , 知蛛网 分享每一链接 都有她的价值

© Powered by 知蛛

快速回复 返回顶部 返回列表