数据源DBAccess01

最新推荐文章于 2021-04-10 04:44:45 发布

iteye_1491

最新推荐文章于 2021-04-10 04:44:45 发布

阅读量85

点赞数

分类专栏：系统设计文章标签： java 数据库

本文链接：https://blog.csdn.net/iteye_1491/article/details/82295982

版权

系统设计专栏收录该内容

55 篇文章 0 订阅

订阅专栏

本文介绍了一个用于Java应用程序中执行数据库查询操作的实用类DBAccess。该类通过使用PreparedStatement和ResultSet来实现参数化的SQL查询，并返回查询结果为HashMap集合列表。文章详细展示了如何通过这个实用类连接数据库、执行SQL语句并处理结果。

摘要由CSDN通过智能技术生成

import java.sql.*;
import java.util.*;

import javax.naming.*;
import javax.sql.*;

import org.apache.log4j.Logger;

import com.huawei.ngbss.esb.FileManager;
public class DBAccess
{
// 默认的数据源
public static final String defaultDataSource = "JDBC/ESB";

private static Logger logger = Logger.getLogger(DBAccess.class);

public static ArrayList<HashMap<String, String>> execQuerySQL(
final String sql, List<String> param)
{
ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();

logger.info("Get connection begin ...");
Connection conn = getConnection();
logger.info("Get connection Successful");
PreparedStatement ps = null;
ResultSet rs = null;
String sqlPre = sql;
try
{
// 增加最大记录限制,防止内存溢出
sqlPre = getSql(sql, sqlPre);
logger.info("Prepare sql statement:[" + sqlPre + ']');
ps = conn.prepareStatement(sqlPre);

logger.info("Prepare sql parameters:" + param.toString());
for (int i = 0; i < param.size(); i++)
{
ps.setString(i + 1, (String) param.get(i));
}

logger.info("Execute sql begin ...");
rs = ps.executeQuery();
logger.info("Execute sql Successful");

ResultSetMetaData rsmd = null;
int rowcount = 0;
int columnnum = 0;
logger.info("Prepare return data begin...");
while (rs.next())
{
rowcount = rs.getRow();
if (rowcount == 1)
{
rsmd = rs.getMetaData();
columnnum = rsmd.getColumnCount();
}
HashMap<String, String> map = new HashMap<String, String>();
for (int j = 1; j <= columnnum; j++)
{
map.put(rsmd.getColumnName(j), rs.getString(j));
logger.info("\tRownum:[" + rs.getRow() + "],\t"
+ rsmd.getColumnName(j) + '[' + rs.getString(j)
+ ']');
}
list.add(map);
}

rs.close();

logger.info("Prepare return data successful. row count: "
+ rowcount);
}
catch (Exception e)
{
logger.error("DataBase query exception! Message:" + e.toString()
+ "\n\t\tSqlText: " + sql + "\n\t\tparam: "
+ param.toString());
throw new BaseException(e);
}
finally
{
if (null != ps)
{
try
{
ps.close();
logger.info("Close PreparedStatement Successful");
}
catch (SQLException e)
{
logger.error("Close PreparedStatement error! Message:"
+ e.toString());

ps = null;
}
}
if (null != conn)
{
try
{
conn.close();
logger.info("Close Connection Successful");
}
catch (SQLException e)
{
logger.error("Close connection error! Message:"
+ e.toString());

conn = null;
}
}
}

return list;
}