hi guys, I am Kyle.
I encountered NullPointerException timely when running my webapp using
Tomcat5.5.10, since it seems a little complicated, please be patient to hear my
description below. :-)
0. I use MySQL as db server, database configuration was correctly implemented.
1. I config DataSource according to documentation in the
/META-INF/context.xml in my webapp directory as below:
=================== /META-INF/context.xml ============================
<Context reloadable="false">
<Resource name="jdbc/ArmyDB"
auth="Container"
type="javax.sql.DataSource"
username="reaver"
password="123456"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/army?autoReconnect=true&useUnicode=true&characterEncoding=GBK"
maxActive="200"
maxIdle="30"
maxWait="10000"/>
</Context>
============================================================================
2. I wrote my DAO class called CommonDAO to offer basic access to db as
follows:
=================== CommonDAO ===================
import ... // import stuff
public class CommonDAO {
static Logger log = Logger.getLogger(CommonDAO.class.getName());
// jndi name
static final String jndi = "jdbc/ArmyDB";
private DataSource ds = null;
private Connection conn = null;
protected void init() {
// use container datasource
try {
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
this.ds = (DataSource) envCtx.lookup(jndi);
this.conn = ds.getConnection();
} catch (NamingException ne) {
log.debug(ne);
} catch (SQLException sqle) {
log.debug(sqle);
}
}
public CommonDAO() {
init();
}
public Connection getConnection() {
return this.conn;
}
protected void checkConn() throws SQLException {
if (this.conn == null || this.conn.isClosed()) {
init();
}
}
public PreparedStatement getPreparedStatement(String sql) {
PreparedStatement pstmt = null;
if (this.conn != null) {
try {
checkConn();
pstmt = conn.prepareStatement(sql);
} catch (SQLException e) {
log.info(e);
}
} else {
// this seems will not happened...
log.debug("null");
}
return pstmt;
}
// for query, select
public ResultSet executeQueryPSTMT(PreparedStatement pstmt) {
ResultSet rs = null;
try{
checkConn();
rs = pstmt.executeQuery();
} catch(SQLException sqle) {
log.info(sqle);
}
return rs;
}
// for insert, update, delete, DDLs..
public int executeUpdatePSTMT(PreparedStatement pstmt) {
int rowsAffected = -1;
try{
checkConn();
rowsAffected = pstmt.executeUpdate();
} catch(SQLException sqle) {
log.info(sqle);
}
return rowsAffected;
}
// select [fields...] from [tablename] where [conditions...]
public ResultSet executeQuery(String sql){
ResultSet rs = null;
try{
checkConn();
Statement stmt = this.conn.createStatement();
rs = stmt.executeQuery(sql);
} catch(SQLException sqle){
log.info(sqle);
}
return rs;
}
public int executeUpdate(String sql) {
int rowsAffected = -1;
try {
checkConn();
Statement stmt = this.conn.createStatement();
rowsAffected = stmt.executeUpdate(sql);
} catch (SQLException sqle) {
log.info(sqle);
}
return rowsAffected;
}
// insert: insert into [tablename][(col1, col2, ...)] values(v1, v2, ...)
public int executeInsert(String sql) {
return this.executeUpdate(sql);
}
// delete: delete from [tablename] where[condition]
public int executeDelete(String sql) {
return this.executeUpdate(sql);
}
// create, drop, alter...
public int executeDDL(String sql) {
return this.executeUpdate(sql);
}
public void destory() {
if (conn != null) {
try {
//conn.commit();
conn.close();
conn = null;
} catch (SQLException sqle) {
log.debug(sqle);
}
}
}
public List getAllTableNames() {
List list = new ArrayList();
try {
checkConn();
DatabaseMetaData dbmd = conn.getMetaData();
String[] types = {"TABLE"};
ResultSet rs = dbmd.getTables(null, null, "%", types);
while (rs.next()) {
String tableName = rs.getString(3);
list.add(tableName);
}
} catch (SQLException e) {
// TODO: handle exception
log.info(e);
}
return list;
}
public boolean supportsBatchUpdates() {
boolean bool = false;
try {
checkConn();
DatabaseMetaData dmd = conn.getMetaData();
if (dmd.supportsBatchUpdates()) {
bool = true;
}
} catch (SQLException e) {
// TODO: handle exception
}
return bool;
}
protected void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
}
rs = null;
}
}
protected void close(PreparedStatement pstmt) {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
}
pstmt = null;
}
}
protected void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
protected void rollback(Connection conn) {
if (conn != null) {
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
================================== ===================================
3. after the hole webapp was comleted, at first things run smoothly in my own
computer. I deployed it on customer's server, which has windows2000 as OS and
Oracle running as db server. I install MySQL, set up database, install
jdk1.5.0, and tomcat5.5.10, finally everything was done.
At first it runs ok. But my webapp crashed 2 times in 48 hours, that is, my
customer get the ugly exception report page with "HTTP 500" as title and
NullPointerException report. I follow these report, find that the exception was
caused from the code in the CommonDAO that try to get query result from some
table. Confident with my code, I thought the problem was that the webapp can
not get connection from DataSource, which means there must be some connection
leak problems with connection pool. So I review the Documentation, and add some
configuration to /META-INF/context.xml:
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
===============================
/META-INF/context.xml==========================
<Context reloadable="false">
<Resource name="jdbc/ArmyDB"
auth="Container"
type="javax.sql.DataSource"
username="reaver"
password="123456"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/army?autoReconnect=true&useUnicode=true&characterEncoding=GBK"
maxActive="200"
maxIdle="30"
maxWait="10000"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"/>
</Context>
=====================================================================
4. so far, customer has not report the ugly "http 500" exception page again,
but I can not be sure the problem is resolved. so, I made a simply load test
using jmeter.
first, I wrote a simply servlet called db test, which use CommonDAO to access
a simple table to retrive all data to webpages; then in jemeter test, I made a
simple http request to that servlet. in Threadgroup configuration, I set 80
threads, and request for ever.
I run the test, so far everything runs ok, both tomcat and my webapp are
healthy. Then, I run the test with the old /META-INF/context.xml file(that is,
with no <removeAbandoned="true"> atrribute), but things also runs ok!
so I am confused! if the NullPointerException was caused by connection leak,
the test on old configuration would finally cause my webapp crashed, but it did
not.
friends, please help me with this strange problem! any reply would be
appreciated. :-)
Kyle
---------------------------------
雅虎1G免费邮箱百分百防垃圾信
雅虎助手-搜索、杀毒、防骚扰
---------------------------------
无限容量雅虎相册，原图等大下载，超快速度，赶快抢注！
