1.与连接池相关连
/**
* getCon
* 功能:取得连接
* @return Connection 连接
*/
public static synchronized Connection getCon() throws wlglException {
Connection con = null;
Context initCtx = null;
Context envCtx =null;
DataSource ds = null;
String DBConPool ="jdbc/DBConPool";
//读取连接池信息,及异常处理.
try {
initCtx = new InitialContext();
envCtx = (Context) initCtx.lookup("java:comp/env");
ds = (DataSource) envCtx.lookup(DBConPool);
} catch (javax.naming.NamingException ex) {
wlglException.ProcessMainWebExceptionMessage("201",
"查找数据Tomcat连接池jndi失败,jndi=" + DBConPool + ".", ex);
}
//取得连接
try {
con = ds.getConnection();
}
catch (java.sql.SQLException ex) {
wlglException.ProcessMainWebExceptionMessage("202", "取数据连接池失败:", ex);
}
return con;
}
2. 执行sql查询,同时打印相关sql语句有助于调试,通过ArrayList传递参数。
public ResultSet exeQuery(String sqlStr, ArrayList params,
int resultSetConcurrency) throws
wlglException {
try {
mypstmt = myCon.prepareStatement(sqlStr,
java.sql.ResultSet.
TYPE_SCROLL_INSENSITIVE,
resultSetConcurrency);
Iterator itx = params.iterator();
int i = 1;
while (itx.hasNext()) {
Object param = itx.next();
if (param != null) {
if (Integer.class.isInstance(param)) {
mypstmt.setInt(i, ((Integer) param).intValue());
}
else if (String.class.isInstance(param)) {
mypstmt.setString(i, ((String) param));
}
else if (Double.class.isInstance(param)) {
mypstmt.setDouble(i, ((Double) param).doubleValue());
}
else if (Boolean.class.isInstance(param)) {
mypstmt.setBoolean(i, ((Boolean) param).booleanValue());
}
} else {
mypstmt.setString(i, "");
}
i = i + 1;
}
myRs = mypstmt.executeQuery();// mypstmt.executeUpdate();
}
catch (SQLException ex) {
wlglException.ProcessMainWebExceptionMessage("204",
"execute SQL失败:SQLStr:" + sqlStr + "/nParams:" + params +
".", ex);
}
getCount();
System.out.println("SQLStr是:" + sqlStr);
System.out.println("params是:" + params);
return myRs;
}
3.得到一行数据及标题
public void exeQueryOneRowTitlesAndDatas(String sqlStr, ArrayList params,
ArrayList titles, ArrayList datas) throws
wlglException {
try {
mypstmt = myCon.prepareStatement(sqlStr);
Iterator itx = params.iterator();
int i = 1;
while (itx.hasNext()) {
Object param = itx.next();
if (Integer.class.isInstance(param)) {
mypstmt.setInt(i, ((Integer) param).intValue());
}
else if (String.class.isInstance(param)) {
mypstmt.setString(i, ((String) param));
}
else if (Double.class.isInstance(param)) {
mypstmt.setDouble(i, ((Double) param).doubleValue());
}
else if (Boolean.class.isInstance(param)) {
mypstmt.setBoolean(i, ((Boolean) param).booleanValue());
}
i = i + 1;
}
myRs = mypstmt.executeQuery();
ResultSetMetaData md = myRs.getMetaData();
boolean hasNext = false;
if (myRs.next()) {
hasNext = true;
} else {
hasNext = false;
}
for (i = 1; i <= md.getColumnCount(); i++) {
if (hasNext) {
//临时增加处理数据类型为bit类型的.0,1
//Date: 2008-6-15
//author: szj
int tmp=md.getColumnType(i);
if(tmp==java.sql.Types.BIT)
{
if(myRs.getByte(i)==1)
datas.add("1");
else
datas.add("0");
}
else
{
datas.add(myRs.getString(i));
}
titles.add(md.getColumnName(i));
} else {
datas.add("");
}
}
} catch (SQLException ex) {
wlglException.ProcessMainWebExceptionMessage("208",
"执行单行返回结果SQL失败:SQLStr:" + sqlStr + "/nParams:" + params +
".", ex);
}
System.err.println("SQLStr是:" + sqlStr);
System.err.println("params是:" + params);
}