JDBC 操作数据库Util类
import java.io.IOException;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import oracle.jdbc.driver.OracleTypes;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import com.sgcc.uap.rest.support.QueryResultObject;
import com.sgcc.uap.rest.utils.RestUtils;
/***
*
* @desc DB工具类
* @author yujuan
* @todo TODO
*/
public class DBUtil {
public static final int BIT =OracleTypes.BIT;
public static final int TINYINT =OracleTypes.TINYINT;
public static final int SMALLINT = OracleTypes.SMALLINT;
public static final int INTEGER = OracleTypes.INTEGER;
public static final int BIGINT = OracleTypes.BIGINT;
public static final int FLOAT = OracleTypes.FLOAT;
public static final int REAL =OracleTypes.REAL;
public static final int DOUBLE =OracleTypes.DOUBLE;
public static final int NUMERIC = OracleTypes.NUMERIC;
public static final int DECIMAL = OracleTypes.DECIMAL;
public static final int CHAR = OracleTypes.CHAR;
public static final int VARCHAR = OracleTypes.VARCHAR;
public static final int LONGVARCHAR = OracleTypes.LONGVARCHAR;
public static final int DATE = OracleTypes.DATE;
public static final int TIME =OracleTypes.TIME;
public static final int TIMESTAMP =OracleTypes.TIMESTAMP;
public static final int BOOLEAN = OracleTypes.BOOLEAN;
private static final String PAGE_SQL_TEMPLETE = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}"; //oracle
/***
*
* @desc分页查询
* @author yujuan
* @param jdbcTemplate
* @param sql
* @param page
* @param rows
* @param objs
* @return
*/
public static List<Map<String, Object>> queryPageList(JdbcTemplate jdbcTemplate,String sql, int page, int rows,Object... params){
//封装分页SQL
sql=createPageSql(sql, page, rows);
return jdbcTemplate.queryForList(sql,params);
}
/***
*
* @desc查询总数
* @author yujuan
* @param jdbcTemplate
* @param sql
* @param objs
* @return
*/
public static Integer queryDataTotal(JdbcTemplate jdbcTemplate,String sql,Object... params){
//封装分页SQL
return jdbcTemplate.queryForInt(sql,params);
}
/***
*
* @desc分页查询（返回count,list）
* @author yujuan
* @param jdbcTemplate
* @param sql
* @param page
* @param rows
* @param objs
* @return
*/
public static QueryResultObject queryPage(JdbcTemplate jdbcTemplate,String sql, int page, int rows,Object... params){
List<Map<String, Object>> list= queryPageList( jdbcTemplate, sql, page, rows, params);
Integer count= queryDataTotal(jdbcTemplate, sql, params);
return RestUtils.wrappQueryResult(list, count);
}
/***
*
* @desc 按照数据库类型，封装SQL
* @author yujuan
* @param sql
* @param page
* @param rows
* @return
*/
private static String createPageSql(String sql, int page, int rows){
int beginIndex = (page-1)*rows;
int endIndex = beginIndex+rows;
String[] sqlParam = {sql,endIndex+"",beginIndex+""};
sql = MessageFormat.format(PAGE_SQL_TEMPLETE, sqlParam);
return sql;
}
/***
*
* @desc 执行存储过程（无返回值）
* @author yujuan
* @param procedureName
* @param params
* @param jdbcTemplate
* @return
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public static Boolean executeProcedure(JdbcTemplate jdbcTemplate,String procedureName,final Object[]params){
StringBuffer sqlbuffer = new StringBuffer();
if (params == null) {
sqlbuffer.append("{call ");
sqlbuffer.append(procedureName);
sqlbuffer.append("()}");
} else {
sqlbuffer.append("{call ");
sqlbuffer.append(procedureName);
sqlbuffer.append("(");
for (int i = 0; i < params.length; i++) {
sqlbuffer.append("?,");
}
if (params.length > 0) {
sqlbuffer.delete(sqlbuffer.length() - 1, sqlbuffer.length());
}
sqlbuffer.append(")}");
}
final String sql = sqlbuffer.toString();
Boolean result=true;
try{
jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement cs = con.prepareCall(sql);
return setParameters(params, cs,0);
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
return cs.execute();
}
});
}catch (Exception e) {
result=false;
e.printStackTrace();
}
return result;
}
/***
*
* @desc执行存储过程(函数) 返回值非集合
* @author yujuan
* @param procedureName
* @param params
* @param jdbcTemplate
* @param outType
* @return
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public static Object executeProcedure(JdbcTemplate jdbcTemplate,String procedureName,final Object[]params,final int returnType){
StringBuffer sqlbuffer = new StringBuffer();
if (params == null) {
sqlbuffer.append("{?=call ");
sqlbuffer.append(procedureName);
sqlbuffer.append("()}");
} else {
sqlbuffer.append("{?=call ");
sqlbuffer.append(procedureName);
sqlbuffer.append("(");
for (int i = 0; i < params.length; i++) {
sqlbuffer.append("?,");
}
if (params.length > 0) {
sqlbuffer.delete(sqlbuffer.length() - 1, sqlbuffer.length());
}
sqlbuffer.append(")}");
}
final String sql = sqlbuffer.toString();
Object result = jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement cs = con.prepareCall(sql, ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
cs.registerOutParameter(1, returnType);// 注册输出参数的类型
return setParameters(params, cs,1);
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
cs.execute();
Object obj = cs.getObject(1);//
return obj;
}
});
return result;
}
/***
*
* @desc 执行存储过程（返回list）
* @author yujuan
* @param procedureName
* @param params
* @param jdbcTemplate
* @return
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public static List<Map<String,Object>> execProcedureByList(JdbcTemplate jdbcTemplate,String procedureName,final Object[]params){
StringBuffer sqlbuffer = new StringBuffer();
if (params == null) {
sqlbuffer.append("{?=call ");
sqlbuffer.append(procedureName);
sqlbuffer.append("()}");
} else {
sqlbuffer.append("{?=call ");
sqlbuffer.append(procedureName);
sqlbuffer.append("(");
for (int i = 0; i < params.length; i++) {
sqlbuffer.append("?,");
}
if (params.length > 0) {
sqlbuffer.delete(sqlbuffer.length() - 1, sqlbuffer.length());
}
sqlbuffer.append(")}");
}
final String sql = sqlbuffer.toString();
List<Map<String,Object>> resultList = (List<Map<String,Object>>) jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement cs = con.prepareCall(sql, ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
cs.registerOutParameter(1, OracleTypes.CURSOR);// 注册输出参数的类型
return setParameters(params, cs,1);
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(1);// 获取游标一行的值
List<Map<String,Object>> resultsMap = ConvertResultSetToList(rs);
rs.close();
return resultsMap;
}
});
return resultList;
}
/***
* 设置存储过程参数
* @param params
* @param cs
* @param step
* @return
* @throws SQLException
*/
@SuppressWarnings("rawtypes")
private static CallableStatement setParameters(Object[] params,CallableStatement cs,int step) throws SQLException {
if (params != null) {
for (int i = 1, j = 0; j < params.length && i <= params.length; i++, j++) {
if (params[j] == null) {
cs.setObject(i + step, null);
continue;
}
Class parameterTypeClass = params[j].getClass();
String parameterTypeName = parameterTypeClass.getName();
if (parameterTypeClass == java.lang.Integer.class) {
cs.setInt(i + step, ((Integer) params[j]).intValue());
} else if (parameterTypeClass == java.lang.Float.class) {
cs.setFloat(i + step, ((Float) params[j]).floatValue());
} else if (parameterTypeClass == java.lang.String.class) {
String tmpvalue = "";
if (params[j] != null) {
tmpvalue = StrUtil.replaceString(params[j].toString(), "'", """);
}
cs.setString(i + step, tmpvalue);
} else if (parameterTypeName.equals("java.sql.Timestamp")) {
cs.setTimestamp(i + step, (java.sql.Timestamp) params[j]);
} else if (parameterTypeName.equals("java.sql.Date")) {
cs.setDate(i + step, (java.sql.Date) params[j]);
}
}
}
return cs;
}
/***
* rs转换list
* @desc
* @author yujuan
* @param rs
* @return
* @throws SQLException
*/
private static List<Map<String,Object>> ConvertResultSetToList(ResultSet rs) throws SQLException {
ResultSetMetaData rsmd = null;
List<Map<String,Object>> rows = new ArrayList<Map<String,Object>>();
if (rs != null) {
rsmd = rs.getMetaData();
// rs.beforeFirst();
while (rs.next()) {
Map<String,Object> map=new Hashtable<String, Object>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
String columnName = rsmd.getColumnName(i + 1).toLowerCase();
switch (rsmd.getColumnType(i + 1)) {
case Types.NUMERIC:
if (rs.getString(columnName) == null)
map.put(columnName, "0");
else
map.put(columnName, rs.getString(columnName));
break;
case Types.VARCHAR:
map.put(columnName, rs.getString(columnName) == null ? "" : rs.getString(columnName));
break;
case Types.INTEGER:
map.put(columnName, rs.getString(columnName) == null ? "" : rs.getString(columnName));
break;
case Types.DATE:
{
java.sql.Date date = rs.getDate(columnName);
if (date == null){
map.put(columnName, "");
}else{
map.put(columnName, rs.getDate(columnName));
}
break;
}
case Types.TIMESTAMP:
{
java.sql.Timestamp timestamp = rs.getTimestamp(columnName);
if (timestamp == null){
map.put(columnName, "");
}else{
map.put(columnName, rs.getTimestamp(columnName));
}
break;
}
case Types.BLOB:
{
Blob blobdata = rs.getBlob(columnName);
if (blobdata == null){
map.put(columnName, "");
}else{
map.put(columnName, blobdata);
}
break;
}
case Types.CLOB:
{
Clob blobdata = rs.getClob(columnName);
String text=null;
try{
text=getStringFromClob(blobdata);
}catch(Exception e){
e.printStackTrace();
}
if (blobdata == null){
map.put(columnName, "");
}else{
map.put(columnName, text);
}
break;
}
case Types.LONGVARCHAR:
{
java.io.Reader long_out = rs.getCharacterStream(columnName);
if (long_out != null)
{
char[] long_buf=new char[8192];
StringBuffer buffer=new StringBuffer();
int len=0;
try {
while((len = long_out.read(long_buf))>0){
buffer.append(long_buf,0,len);
}
map.put(columnName,buffer.toString());
buffer=null;
} catch (IOException e) {
// TODO
}finally {
try{
long_out.close();
}catch(Exception e){
e.printStackTrace();
}
long_buf=null;
}
}
break;
}
default:
{
map.put(columnName, rs.getString(columnName) == null ? "" : rs.getString(columnName));
break;
}
}
}
rows.add(map);
}
}
return rows;
}
private static String getStringFromClob(Clob clob)throws Exception{
String result= "";
java.io.Reader in=null;
try {
in = clob.getCharacterStream();
if (in == null) return null;
StringBuffer sb = new StringBuffer(4096);
int i = in.read();
while (i != -1) {
sb.append((char) i);
i = in.read();
}
in.close();
result=sb.toString();
} catch (Exception e) {
e.printStackTrace();
}finally{
if(in!=null){
in.close();
}
}
return result;
}
/***
*
* @desc 获取查询参数
* @author yujuan
* @param ids
* @param paramname
* @param param
* @return
*/
public static Map<String,Object> getQueryParamByIds(String ids,String paramname){
List<Object> paramlist=new ArrayList<Object>();
Map<String,Object> resultmap=new Hashtable<String, Object>();
StringBuffer paramsql=new StringBuffer();
String []idsarr=ids.split(",");
for (int i = 0; i < idsarr.length; i++) {
if(i==0&&idsarr.length==1){
paramsql.append(" and ").append(paramname).append("=?");
}else if(i==0){
paramsql.append(" and (").append(paramname).append("=?");
}else if(i<idsarr.length-1){
paramsql.append(" or ").append(paramname).append("=?");
}else{
paramsql.append(" or ").append(paramname).append("=?)");
}
paramlist.add(idsarr[i]);
}
resultmap.put("PARAM_SQL", paramsql.toString());
resultmap.put("PARAM_List", paramlist);
return resultmap;
}
}