JBoss3.0.0 & MySql insert problem
milder60 Jan 6, 2003 4:11 AMHi,
My problem is that my user creation works sporadically. Often when somebody creates a user, they can't log in right away even though the data is visible in the database. If I restart the server (or wait n minutes) the data can be selected and a user can log in. Even thru mysqlfront I can see that the data has been inserted, but I cannot select a user via
"SELECT * FROM user_detail WHERE user_name='auser'"
(auser being the recently created user)
Does JBoss queue updates or something? Or perhaps it's a setting in mysql? Any thoughts/ideas would be greatly appreciated
Cheers,
Dave
CODE:
Here is my code for inserting data:
/**
* Inserts a new user into database
* @param UserDetail user
* @return void
*
* */
public void insert (UserDetail user) throws UserNameExistsException{
Connection con = null;
Statement stmt = null;
String insertSQL = " INSERT INTO MOXROCKS.USER_DETAIL ( " +
" USER_ID , FIRST_NAME ," +
" LAST_NAME , ADDRESS1 ," +
" ADDRESS2, EMAIL_ADDRESS ," +
" NICKNAME , USER_NAME ," +
" CREATED_DATE, UPDATED_DATE, " +
" PERMISSIONS ) " +
" VALUES " +
" ( 0 , " +
DBUtil.str(user.getFirstName()) + " , " +
DBUtil.str(user.getLastName()) +" , " +
DBUtil.str(user.getAddress1()) +" , " +
DBUtil.str(user.getAddress2()) +" , " +
DBUtil.str(user.getEmail()) +" , " +
DBUtil.str(user.getNickName()) +" , " +
DBUtil.str(user.getUserName())+" , "+
" CURDATE() , " +
" CURDATE(), "+
MRPermissions.getDefaultAccess()+" ) " ;
String insertPrincipals = " INSERT INTO MOXROCKS.PRINCIPALS (PRINCIPAL_ID, PASSWORD) "+
" VALUES ( "+DBUtil.str(user.getUserName())+ ", "+DBUtil.str(PasswordUtils.encrypt(user.getPassword()))+" )";
String insertRoles = " INSERT INTO MOXROCKS.ROLES (PRINCIPAL_ID, ROLE, ROLE_GROUP) " +
" VALUES ("+DBUtil.str(user.getUserName())+" , "+ DBUtil.str(user.getUserRole().getRoleName())+" , " +
DBUtil.str(user.getUserRole().getRoleGroup()) + " )";
sLog.debug("insertPrincipals sql = " +insertPrincipals);
sLog.debug("insertRoles sql = " +insertRoles);
sLog.debug("insertSQL sql = " +insertSQL);
try {
//check to see if this userName already exists. Throws UserNameExistsException
this.checkUserNameExists(user);
con = ConnectionManager.getConnection();
stmt = con.createStatement();
//con.setAutoCommit(false);
stmt.addBatch(insertSQL);
stmt.addBatch(insertRoles);
stmt.addBatch(insertPrincipals);
int results [] = stmt.executeBatch();
//con.commit();
for (int x=0; x<results.length; x++){
sLog.debug("results["+x+"]="+results[x]);
}
} catch(UserNameExistsException un) {
sLog.debug("User name exists. Exiting and throwing UserNameExistsException");
throw new UserNameExistsException();
} catch(SQLException e) {
//error, role back
try {
con.rollback();
} catch(Exception ex) {
ex.printStackTrace();
}
e.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
finally {
ConnectionManager.close(stmt);
ConnectionManager.close(con);
}
}