Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
com.loocme.sys.util.DbUtil Maven / Gradle / Ivy
package com.loocme.sys.util;
import lombok.extern.slf4j.Slf4j;
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.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 数据库操作帮助类
*
* @author loocme
*
*/
@Slf4j
public class DbUtil
{
private static final int PRECISION_INTEGER = 5;
/**
* 通过sql查询获取list集合
*
* @param conn
* 数据库连接
* @param sql
* 查询的sql语句
* @param params
* 查询的参数,替换sql中where条件里的param=?value
* @return 查询的结果集
*/
public static List> queryList(Connection conn,
String sql, Map params)
{
if (null == conn)
{
log.error("数据库连接为null");
return null;
}
List> list = new ArrayList>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
List paramList = new ArrayList();
sql = SqlUtil.getPstmtSql(sql, params, paramList);
pstmt = conn.prepareStatement(sql);
fillSqlPstmtParams(pstmt, paramList);
rs = pstmt.executeQuery();
// 获取列信息
String[] colNames = getColNames(rs.getMetaData());
Map lineMap = null;
while (rs.next())
{
lineMap = new HashMap(16);
for (int i = 0; i < colNames.length; i++)
{
lineMap.put(colNames[i], rs.getObject(colNames[i]));
}
list.add(lineMap);
}
}
catch (SQLException e)
{
log.error("sql exception. ", e);
}
finally
{
try
{
if (null != rs)
{
rs.close();
}
if (pstmt != null)
{
pstmt.close();
}
}
catch (SQLException e)
{
log.error("sql exception. ", e);
}
}
return list;
}
private static void fillSqlPstmtParams(PreparedStatement pstmt, List paramList) throws SQLException
{
for (int i = 0; i < paramList.size(); i++)
{
Object value = paramList.get(i);
if (value == null)
{
pstmt.setString(i + 1, null);
}
else if (value.getClass() == String.class)
{
pstmt.setString(i + 1, (String) value);
}
else if (value.getClass() == Integer.class)
{
pstmt.setInt(i + 1, (Integer) value);
}
else if (value.getClass() == Long.class)
{
pstmt.setLong(i + 1, (Long) value);
}
else if (value.getClass() == Double.class)
{
pstmt.setDouble(i + 1, (Double) value);
}
else if (value.getClass() == Date.class)
{
pstmt.setTimestamp(i + 1,
new Timestamp(((Date) value).getTime()));
}
else if (value instanceof byte[])
{
pstmt.setBytes(i + 1, (byte[]) value);
}
else
{
log.error("sql查询中暂不支持参数数据类型({})", value.getClass());
}
}
}
/**
* 通过结果集meta信息获取列名称
*
* @param rsmd
* 结果集meta信息
* @return 所有列名称
*/
private static String[] getColNames(ResultSetMetaData rsmd)
{
String[] colNames = null;
try
{
int count = rsmd.getColumnCount();
colNames = new String[count];
for (int i = 0; i < count; i++)
{
colNames[i] = rsmd.getColumnName(i + 1);
}
}
catch (SQLException e)
{
log.error("sql exception. ", e);
}
return colNames;
}
/**
* 通过sql查询一条记录:: 若未查询到记录,返回null; 若查询到多条记录,返回第一条记录;
*
* @param conn
* 数据库连接
* @param sql
* 查询的sql语句
* @param params
* 查询的参数,替换sql中where条件里的param=?value
* @return 查询到的结果信息
*/
public static Map queryOne(Connection conn, String sql,
Map params)
{
List> list = queryList(conn, sql, params);
return ListUtil.isNotNull(list) ? list.get(0) : null;
}
/**
* 获取数据库连接
*
* @param driver
* 驱动类名
* @param url
* 数据库地址
* @param username
* 用户名
* @param password
* 密码
* @return 数据库连接
*/
public static Connection getConnection(String driver, String url,
String username, String password)
{
try
{
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username,
password);
return conn;
}
catch (ClassNotFoundException e)
{
log.error("class not found exception. ", e);
}
catch (SQLException e)
{
log.error("sql exception. ", e);
}
return null;
}
/**
* 通过表名获取表的字段信息,主键信息,索引信息
*
* @param conn
* 数据库连接
* @param tableName
* 表名
* @param columnInfos
* 返回的字段信息
* @param pkNames
* 返回的主键信息
* @param dbIndexMap
* 返回的索引信息
*/
/**
private static void getColumnsInfo(Connection conn, String tableName,
List> columnInfos, List pkNames,
Map> dbIndexMap)
{
tableName = tableName.toUpperCase();
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
// 获取所有列
if (null != columnInfos)
{
columnInfos.clear();
String sql = "SELECT * FROM " + tableName + " WHERE 1=0";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
Map tempMap = null;
for (int i = 0; i < count; i++)
{
tempMap = new HashMap();
tempMap.put("ColumnName", rsmd.getColumnName(i + 1));
tempMap.put("ColumnLabel", rsmd.getColumnLabel(i + 1));
tempMap.put("ColumnType", rsmd.getColumnType(i + 1) + "");
tempMap.put("ColumnTypeName",
rsmd.getColumnTypeName(i + 1));
tempMap.put("ColumnDisplaySize",
rsmd.getColumnDisplaySize(i + 1) + "");
tempMap.put("ColumnClassName",
rsmd.getColumnClassName(i + 1));
tempMap.put("Scale", rsmd.getScale(i + 1) + "");
tempMap.put("Precision", rsmd.getPrecision(i + 1) + "");
tempMap.put("Nullable", rsmd.isNullable(i + 1) + "");
columnInfos.add(tempMap);
}
}
// 获取主键列
if (null != pkNames)
{
pkNames.clear();
String schema = null, tname = tableName;
int tindex = tableName.indexOf(".");
if (tindex != -1)
{
schema = tableName.substring(0, tindex);
tname = tableName.substring(tindex + 1);
}
rs = conn.getMetaData().getPrimaryKeys(null, schema, tname);
while (rs.next())
{
pkNames.add(rs.getString("COLUMN_NAME"));
}
}
// 获取表上的索引
if (null != dbIndexMap)
{
dbIndexMap.clear();
String sql = "SELECT A.INDEX_NAME,A.TABLE_NAME,A.COLUMN_NAME,B.UNIQUENESS "
+ "FROM USER_IND_COLUMNS A "
+ "LEFT OUTER JOIN USER_INDEXES B ON A.INDEX_NAME=B.INDEX_NAME "
+ "WHERE A.TABLE_NAME='" + tableName + "'";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
List indexNameList = null;
while (rs.next())
{
String indexName = rs.getString("INDEX_NAME");
if (dbIndexMap.containsKey(indexName))
{
indexNameList = dbIndexMap.get(indexName);
}
else
{
indexNameList = new ArrayList();
indexNameList.add(rs.getString("UNIQUENESS"));
}
indexNameList.add(rs.getString("COLUMN_NAME"));
dbIndexMap.put(indexName, indexNameList);
}
}
}
catch (SQLException e)
{
LOGGER.error("", e);
}
finally
{
try
{
if (null != rs) rs.close();
if (null != pstmt) pstmt.close();
}
catch (SQLException e)
{
LOGGER.error("", e);
}
}
}
*/
/**
* 通过数据库类型获取相应的java类型
*
* @param columnType
* 数据库类型
* @param precision
* 长度
* @param scale
* 精度
* @return 相应的java类型
*/
public static String getJavaClass(String columnType, String precision,
String scale)
{
String javaClass = "java.lang.String";
int columnTypeInt = StringUtil.getInteger(columnType);
switch (columnTypeInt)
{
case Types.BLOB:
case Types.CHAR:
case Types.CLOB:
case Types.NCHAR:
case Types.NCLOB:
case Types.NVARCHAR:
case Types.VARCHAR:
javaClass = "java.lang.String";
break;
case Types.DATE:
case Types.TIME:
case Types.TIMESTAMP:
javaClass = "java.util.Date";
break;
case Types.BIT:
case Types.INTEGER:
case Types.TINYINT:
case Types.BOOLEAN:
javaClass = "java.lang.Integer";
break;
case Types.BIGINT:
javaClass = "java.lang.Long";
break;
case Types.DECIMAL:
case Types.DOUBLE:
case Types.FLOAT:
javaClass = "java.lang.Double";
break;
case Types.NUMERIC:
int scaleInt = StringUtil.getInteger(scale);
if (scaleInt > 0)
{
javaClass = "java.lang.Double";
}
else
{
int precisionInt = StringUtil.getInteger(precision);
if (precisionInt <= PRECISION_INTEGER)
{
javaClass = "java.lang.Integer";
}
else
{
javaClass = "java.lang.Long";
}
}
break;
default:
log.error("未定义的数据库字段类型({})", columnTypeInt);
break;
}
return javaClass;
}
public static int execute(Connection conn, String sql)
{
List sqlList = new ArrayList();
sqlList.add(sql);
return execute(conn, sqlList);
}
public static int execute(Connection conn, List sqlList)
{
if (ListUtil.isNull(sqlList))
{
return 0;
}
PreparedStatement pstmt = null;
int[] retArr = new int[sqlList.size()];
try
{
conn.setAutoCommit(false);
for (int i = 0; i < sqlList.size(); i++)
{
String sql = sqlList.get(i);
pstmt = conn.prepareStatement(sql);
retArr[i] = pstmt.executeUpdate();
}
conn.commit();
}
catch (SQLException e)
{
log.error("sql exception. ", e);
return -1;
}
finally
{
try
{
if (null != pstmt)
{
pstmt.close();
}
if (null != conn)
{
conn.setAutoCommit(true);
}
}
catch (SQLException e)
{
log.error("sql exception. ", e);
}
}
int retCount = 0;
for (int i = 0; i < retArr.length; i++)
{
retCount += retArr[i];
}
return retCount;
}
}
