我和我的同事在做web测试时发现一个诡异的问题
我们都用各自的用户名正常登录后,对数据库进行增删改查的操作,比如:
select * from bill where userId='"+userId+"'"
但是奇怪的是有时候查询到的不是自己录入的记录,是别人的;
而别人会查询到我的记录。
我查了下,代码应该不会有错,因为userId登录后肯定不会变,相当于一个常量了。
那为什么还会出现上面的情况呢,请同志们看看啊,谢谢。
我猜是不是没有使用ThreadLocal,大家在用同一条线程操作数据库啊?
[b]问题补充:[/b]
对的,的却是后登录者替换了前面的!!!
[b]问题补充:[/b]
hearken01大虾,可以可以给个例子代码,麻烦您了,谢谢!
[b]问题补充:[/b]
这个是我使用的数据库类,看下有什么问题
[code="java"]
public class DBConnection {
public ThreadLocal threadConnection = new ThreadLocal();
public Connection conn = null;
public DBConnection() {
}
public Connection getMySqlConnection() {
try {
Class.forName(Constants.driver);
conn = DriverManager.getConnection(Constants.url, Constants.name,Constants.password);
if (conn == null)
throw new SQLException("Can't connect MsSql!");
} catch (ClassNotFoundException e2) {
e2.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public Connection getCurrentConnection() throws SQLException {
conn = (Connection) threadConnection.get();
if (conn == null) {
System.out.println("--------------打开链接---------------------");
conn = getMySqlConnection();
threadConnection.set(conn);
}
return conn;
}
public Connection getCurrentConnection(boolean isTransaction)
throws SQLException {
conn = (Connection) threadConnection.get();
if (conn == null) {
conn = getMySqlConnection();
threadConnection.set(conn);
}
if (isTransaction)
conn.setAutoCommit(false);
return conn;
}
public void closeCurrentConnection() {
try {
Connection conn = (Connection) threadConnection.get();
threadConnection.set(null);
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void closeConnection(Connection conn) throws SQLException {
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void beginTransaction() {
try {
getCurrentConnection().setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void commitTransaction() {
try {
getCurrentConnection().commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void rollbackTransaction() {
try {
getCurrentConnection().rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
public int executeUpdate(String sqlQuery, String sqlValue[])throws Exception {
int count = 0;
try {
PreparedStatement ps = conn.prepareStatement(sqlQuery);
if (sqlValue != null) {
for (int i = 0; i < sqlValue.length; i++)
ps.setString(i + 1, sqlValue[i]);
}
count = ps.executeUpdate();
} catch (Exception e) {
throw e;
}
return count;
}
//记录数
public int executeQuery(String sql){
ResultSet rs = null;
PreparedStatement ps;
int count=0;
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery(sql);
rs.next();
count=rs.getInt(1);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return count;
}
public ArrayList executeQueryList(String sql) {
Statement st = null;
ArrayList alResult = new ArrayList();
try {
st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
String colum[] = new String[count];
for (int i = 0; i < colum.length; i++)
if (rsmd.getColumnName(i + 1) != null)
colum[i] = rsmd.getColumnName(i + 1);
else
colum[i] = rsmd.getColumnLabel(i + 1);
HashMap hdRow = null;
String fieldValue = null;
for (; rs.next(); alResult.add(hdRow)) {
hdRow = new HashMap();
for (int i = 0; i < colum.length; i++) {
int iType = rsmd.getColumnType(i + 1);
if (iType == 2 || iType == 3) {
if (rsmd.getScale(i + 1) == 0)
fieldValue = String.valueOf(rs.getLong(i + 1));
else
fieldValue = rs.getString(i + 1);
} else if (iType == 8)
fieldValue = String.valueOf(rs.getDouble(i + 1));
else if (iType == 6 || iType == 7)
fieldValue = String.valueOf(rs.getFloat(i + 1));
else
fieldValue = rs.getString(i + 1);
if (fieldValue == null)
fieldValue = "";
else
fieldValue = fieldValue.trim();
hdRow.put(colum[i], fieldValue);//.toLowerCase()
}
}
} catch (Exception e) {}
return alResult;
}
public static void main(String arg[]) throws Exception {
DBConnection db = new DBConnection();
db.getCurrentConnection();
}
}
[/code]
[b]问题补充:[/b]
我在servlet里这样用的,有什么不妥?
[code="java"]
public class LoginServlet extends HttpServlet{
private DBConnection db;
public void init() throws ServletException {
db = new DBConnection();//打开链接
try {
db.getCurrentConnection();
} catch (SQLException e1) {
}
}
public void destroy() {
db.closeCurrentConnection();
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String sql="select * from bill where userId='"+userId+"'" ;
List list=db.executeQueryList(sql);
.........
}
}
[/code]
[b]问题补充:[/b]
To hearken01:
我把ThreadLocal部分去掉,还是不行啊。
[b]问题补充:[/b]
To 蔡华江:
这里别省?什么意思,完整些啊,麻烦了。
[b]问题补充:[/b]
To Ihavegotyou:
没有static的都。
[b]问题补充:[/b]
To 蔡华江:
[code="java"]
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String sql="select * from bill where userId='"+userId+"'" ;
List list=db.executeQueryList(sql);
request.setAttribute("list", list);
request.getRequestDispatcher("infoList.jsp").forward(request, response);
}
[/code]
省略号里就这些啊,也没什么特别的啊
[b]问题补充:[/b]
TO hearken01:
userId就是登录的时候表单里的数据,这样子:
[code="java"]
String userId=request.getParameter("userId");
String password=request.getParameter("password");
String sql="select * from user where userId='"+userId+"' and password='"+password+"'";
List list=db.executeQueryList(sql);
if(list==null||list.size()==0){//没有记录
......
}else{
request.getSession().setAttribute(Constants.USER_KEY, userId);//一旦登录成功,创建session
......
}
[/code]
就是这么做的,也没什么特别的。
[b]问题补充:[/b]
to hearken01:
后台打印出来是变了,可为什么变了呢?这个。。。。 :cry:
[b]问题补充:[/b]
to hearken01:
恩,不过我也在不同的机器上试过2个帐号,结果都一样查询错乱。
[b]问题补充:[/b]
to hearken01:
你当初是怎么解决的呢?
“我就把那些公共的方法,改成了要传入request和response,接下来就没这样的问题出现了,我想你的问题应该是请求和响应错乱了。”
QQ聊吧,50342016