代码如下：
|
/**
*
*/
package com.sotaof.struts.db.utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;
import javax.naming.InitialContext;
import javax.sql.DataSource;
/**
* @Title: DbUtils.java
* @Package com.sotaof.struts.db.utils
* @Description: TODO
* @author A18ccms A18ccms_gmail_com
* @date 2013-2-4 下午06:30:36
* @version V1.0
*/
public class DbUtils {
private static Logger logger = Logger.getLogger("DbUtils");
public static int execute(String sql, List<Object> paramList) throws Exception
{
if(sql == null || sql.trim().equals(""))
{
logger.info("parameter is valid!");
}
Connection conn = null;
PreparedStatement pstmt = null;
int result = 0;
try
{
conn = DbUtils.getConnection();
pstmt = DbUtils.getPreparedStatement(conn, sql);
setPreparedStatementParam(pstmt, paramList);
if(pstmt == null)
{
return -1;
}
result = pstmt.executeUpdate();
}
catch(Exception e)
{
logger.info(e.getMessage());
throw new Exception(e);
}
finally
{
closeStatement(pstmt);
closeConn(conn);
}
return result;
}
|
|
public static Connection getConnection() throws Exception
{
InitialContext cxt = new InitialContext();
if(cxt == null)
{
throw new Exception("no context!");
}
DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/orcl");
if(ds == null)
{
throw new Exception("Data source not found!");
}
return ds.getConnection();
}
public static PreparedStatement getPreparedStatement(Connection conn, String sql) throws Exception
{
if(conn == null || sql == null || sql.trim().equals(""))
{
return null;
}
PreparedStatement pstmt = conn.prepareStatement(sql.trim());
return pstmt;
}
public static void setPreparedStatementParam(PreparedStatement pstmt, List<Object> paramList) throws Exception
{
if(pstmt == null || paramList == null || paramList.isEmpty())
{
return;
}
DateFormat df = DateFormat.getDateTimeInstance();
for(int i = 0; i < paramList.size(); i++)
{
if(paramList.get(i) instanceof Integer)
{
int paramValue = ((Integer) paramList.get(i)).intValue();
pstmt.setInt(i + 1, paramValue);
}
else if(paramList.get(i) instanceof Float)
{
float paramValue = ((Float) paramList.get(i)).floatValue();
pstmt.setFloat(i + 1, paramValue);
}
else if(paramList.get(i) instanceof Double)
{
double paramValue = ((Double) paramList.get(i)).doubleValue();
pstmt.setDouble(i + 1, paramValue);
}
else if(paramList.get(i) instanceof Date)
{
pstmt.setString(i + 1, df.format((Date)paramList.get(i)));
}
else if(paramList.get(i) instanceof Long)
{
long paramValue = ((Long)paramList.get(i)).longValue();
pstmt.setLong(i + 1, paramValue);
}
else if(paramList.get(i) instanceof String)
{
pstmt.setString(i + 1, (String)paramList.get(i));
}
}
return;
}
private static void closeConn(Connection conn)
{
if(conn == null)
{
return;
}
try
{
conn.close();
}
catch(SQLException e)
{
logger.info(e.getMessage());
}
}
private static void closeStatement(Statement stmt)
{
if(stmt == null)
{
return;
}
try
{
stmt.close();
}
catch(SQLException e)
{
logger.info(e.getMessage());
}
}
private static void closeResultSet(ResultSet rs)
{
if(rs == null)
{
return;
}
try
{
rs.close();
}
catch(SQLException e)
{
logger.info(e.getMessage());
}
}
private static ResultSet getResultSet(PreparedStatement pstmt) throws Exception
{
if(pstmt == null)
{
return null;
}
ResultSet rs = pstmt.executeQuery();
return rs;
}
public static List<Map<String,String>> getQueryList(String sql, List<Object> paramList) throws Exception
{
if(sql == null || sql.trim().equals(""))
{
logger.info("parameter is valid!");
return null;
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Map<String,String>> queryList = null;
try
{
conn = DbUtils.getConnection();
pstmt = DbUtils.getPreparedStatement(conn, sql);
setPreparedStatementParam(pstmt, paramList);
if(pstmt == null)
{
return null;
}
rs = DbUtils.getResultSet(pstmt);
queryList = DbUtils.getQueryList(rs);
}
catch(Exception e)
{
logger.info(e.getMessage());
throw new Exception();
}
finally
{
closeResultSet(rs);
closeStatement(pstmt);
closeConn(conn);
}
return queryList;
}
private static List<Map<String,String>> getQueryList(ResultSet rs) throws Exception
{
if(rs == null)
{
return null;
}
ResultSetMetaData rsMetaData = rs.getMetaData();
int columnCount = rsMetaData.getColumnCount();
List<Map<String,String>> dataList = new ArrayList<Map<String,String>>();
while(rs.next())
{
Map<String,String> dataMap = new HashMap<String,String>();
for(int i = 0; i < columnCount; i++)
{
dataMap.put(rsMetaData.getColumnName(i+1), rs.getString(i+1));
}
dataList.add(dataMap);
}
return dataList;
}
}
|
不过我认为这种方法虽然封装性比较好，也比较好管理，但是当出现异常时，对于错误的查找非常的麻烦，所以我个人很少使用这样的方法，不过如果这样的数据库麻烦，那么就用Hibernate框架吧(如果你的数据库够强大的话)。
最新内容请见作者的GitHub页：http://qaseven.github.io/
