我用连接池连接sql server执行“select sum(pv) from s_saleslog”（pv在表中是money类型的）这句sql语句时会出现如下的错误，其中“opendb”是连接池一部分的类名，表的名字是“s_saleslog”opendb.executeQuery(): [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]列 's_saleslog.sys_id' 在选择列表中无效，因为该列未包含在聚合函数中，并且没有 GROUP BY 子句。
java.lang.NullPointerExceptionsql语句在不使用连接池时正常执行，在连接池中就不行，是不是这个sql语句有哪个地方需要改进？另外“select count(*) from s_saleslog”在连接池中执行正常
java.lang.NullPointerExceptionsql语句在不使用连接池时正常执行，在连接池中就不行，是不是这个sql语句有哪个地方需要改进？另外“select count(*) from s_saleslog”在连接池中执行正常
楼主【mysteron】截止到2008-07-16 11:04:30的历史汇总数据(不包括此帖)：
发帖的总数量:5 发帖的总分数:100 每贴平均分数:20
回帖的总数量:3 得分贴总数量:1 回帖的得分率:33%
结贴的总数量:5 结贴的总分数:100
无满意结贴数:0 无满意结贴分:0
未结的帖子数:0 未结的总分数:0
结贴的百分比:100.00% 结分的百分比:100.00%
无满意结贴率:0.00 % 无满意结分率:0.00 %
敬礼!
import java.sql.*;
import java.util.*;public class ConnectionMgr {
String driver;
String url;
String user;
String pwd;
int initCon; //连接池中初始创建连接数
int maxCon; //连接池中允许创建的最大的连接数
int incCon; //请求连接数大于活动连接数时，允许请求用户创建的连接数(必须大于0)
int currCon; //当前活动连接数，包括已经从连接池中分配出去的连接和连接池中的可分配连接
PreparedStatement pstmt;
Hashtable connectionPool;
Hashtable releaseconnectionPool;
int overtime = 60;
public ConnectionMgr(String driver, String url, String user, String pwd,
int initCon, int maxCon, int incCon) {
connectionPool = new Hashtable();
releaseconnectionPool = new Hashtable();
this.driver = driver;
this.url = url;
this.user = user;
this.pwd = pwd;
this.initCon = initCon;
this.maxCon = maxCon;
this.incCon = incCon;
init();
} public void init() {
try {
Class.forName(driver);
for (int i = 0; i < initCon; i++) {
try {
Connection con = DriverManager.getConnection(url, user, pwd);
connectionPool.put(con, new Integer(0));
System.out.println("Create connection " + i + " :" + con);
} catch (Exception e) {
System.err.println(
"ConnectionMgr.init(): failed to create connection " +
i);
}
}
} catch (Exception e) {
System.err.println(
"ConnectionMgr.init(): failed to load jdbc driver: " +
driver);
}
currCon = connectionPool.size();
} public synchronized Connection requestCon() throws Exception {
Connection con = null;
Enumeration eConnections;
con = null;
eConnections = connectionPool.keys();
if (eConnections.hasMoreElements()) {
con = (Connection) eConnections.nextElement();
if (con.isClosed()) {
connectionPool.remove(con);
currCon--;
// 递归调用自己,尝试再次获取可用连接
con = requestCon();
} else if (con == null) {
con = requestCon();
} else {
//成功请求连接
connectionPool.remove(con);
releaseconnectionPool.put(con, new java.util.Date());
System.out.println("requestCon:" + con);
}
} else {
if (currCon >= maxCon) {
boolean overtimecon = false;
eConnections = releaseconnectionPool.keys();
for (; eConnections.hasMoreElements(); ) {
con = (Connection) eConnections.nextElement();
java.util.Date date = (java.util.Date)
releaseconnectionPool.get(con);
java.util.Date crrdate = new java.util.Date();
long time = (crrdate.getTime() - date.getTime()) / 1000;
if (time > overtime && con != null) {
releaseconnectionPool.remove(con);
connectionPool.put(con, new Integer(0));
overtimecon = true;
}
}
con = null;
if (overtimecon) {
con = requestCon();
} else {
System.err.println("No connection available! ");
Exception e = new Exception("No connection available!");
throw e;
}
} else {
int iTotal = currCon + incCon;
iTotal = iTotal <= maxCon ? iTotal : maxCon;
try {
for (int i = currCon; i < iTotal; i++) {
con = DriverManager.getConnection(url, user, pwd);
connectionPool.put(con, new Integer(0));
currCon++;
System.out.println("Create connection " + i + " :" +
con);
}
// 递归调用自己,尝试再次获取可用连接
} catch (Exception e) {
System.err.println(
"ConnectionMgr.requestCon(): failed to create connection " +
(currCon + 1));
} finally {
con = requestCon();
}
}
}
return con;
} public synchronized void releaseCon(Connection con) {
try {
if (con != null) {
if (con.isClosed()) {
currCon--;
releaseconnectionPool.remove(con);
} else {
releaseconnectionPool.remove(con);
connectionPool.put(con, new Integer(0));
}
}
} catch (Exception e) {} finally {
System.out.println("releaseCon:" + con);
}
} public void closeConnections() throws Exception {
System.out.println("closing connection...");
Enumeration[] Connections = {connectionPool.keys(),
releaseconnectionPool.keys()};
for (int i = 0; i < Connections.length; i++) {
while (Connections[i].hasMoreElements()) {
Connection con = (Connection) Connections[i].nextElement();
con.close();
}
}
currCon = 0;
connectionPool.clear();
releaseconnectionPool.clear();
}
}
import java.io.*;public class opendb {
final String Webserver = "127.0.0.1"; //WEB服务器IP地址
final String SID = "DatabaseName=member"; //WEB数据库服务器SID
final String UserID = "test"; //数据库用户名
final String Password = "123"; //数据库密码
final String sDBDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String sConnStr = "jdbc:microsoft:sqlserver://" + Webserver + ":1433;" +
SID;
public static ConnectionMgr connectionMgr = null;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
int initCon = 5; //连接池中初始创建连接数
int maxCon = 100; //连接池中允许创建的最大的连接数
int incCon = 1; //请求连接数大于活动连接数时，允许请求用户创建的连接数(必须大于0)
public opendb() {
if (connectionMgr == null) {
connectionMgr = new ConnectionMgr(sDBDriver, sConnStr, UserID,
Password, initCon, maxCon, incCon);
}
} public ResultSet executeQuery(String sql) throws Exception {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn == null) {
conn = connectionMgr.requestCon();
}
stmt = conn.createStatement(java.sql.ResultSet.
TYPE_SCROLL_INSENSITIVE,
java.sql.ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
System.err.println("opendb.executeQuery(): " + e.getMessage());
}
return rs;
} public void executeInsert(String sql) throws Exception {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn == null) {
conn = connectionMgr.requestCon();
}
stmt = conn.createStatement();
stmt.executeUpdate(sql);
} catch (SQLException e) {
System.err.println("opendb.executeUpdate():" + e.getMessage());
}
} public void executeUpdate(String sql) throws Exception {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn == null) {
conn = connectionMgr.requestCon();
}
stmt = conn.createStatement();
stmt.executeUpdate(sql);
stmt.close();
} catch (SQLException e) {
System.err.println("opendb.executeUpdate():" + e.getMessage());
}
} public void executeDelete(String sql) throws Exception {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn == null) {
conn = connectionMgr.requestCon();
}
stmt = conn.createStatement();
stmt.executeUpdate(sql);
} catch (SQLException e) {
System.err.println("opendb.executeDelete():" + e.getMessage());
}
}
}