DAO-object gets from DB only limited count of objects. Only Clear - Build - Deploy can help.
I have DB with connected by foreign keys entities. One of them is User. Other one - Book. Those entities are stored in tables Users and Library. In table Users stores 100 rows. In table Library stores 1050 rows.
User entity class.
public class User {
private long id;
private String firstName;
private String lastName;
private String email;
private String login;
private String password;
private Gender gender;
private boolean confirmed;
private boolean banned;
private String registrationDate;
private boolean notify;
private Role role;
@Override
public String toString(){
return id + " " + firstName + " " + lastName + " " + email + " " + login + " " + password + " " + gender
+ " " + confirmed + " " + banned + " " + registrationDate + " " + notify + " " + role;
}
public List<String> toStringList(){
List<String> result = new ArrayList<String>();
result.add(String.valueOf(id));
result.add(firstName);
result.add(lastName);
result.add(email);
result.add(login);
result.add(password);
result.add(String.valueOf(gender.toInt()));
result.add(confirmed?"1":"0");
result.add(banned?"1":"0");
result.add(registrationDate);
result.add(notify?"1":"0");
result.add(String.valueOf(role.getId()));
return result;
}
public User() {
}
public User(long id) {
this.id = id;
}
public boolean isBanned() {
return banned;
}
public void setBanned(boolean banned) {
this.banned = banned;
}
public boolean isConfirmed() {
return confirmed;
}
public void setConfirmed(boolean confirmed) {
this.confirmed = confirmed;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public Gender getGender() {
return gender;
}
public void setGender(Gender gender) {
this.gender = gender;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getLogin() {
return login;
}
public void setLogin(String login) {
this.login = login;
}
public boolean isNotify() {
return notify;
}
public void setNotify(boolean notify) {
this.notify = notify;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getRegistrationDate() {
return registrationDate;
}
public void setRegistrationDate(String registrationDate) {
this.registrationDate = registrationDate.substring(0, 10);
}
public Role getRole() {
return role;
}
public void setRole(Role role) {
this.role = role;
}
}
UserDao class with CRUD methods.
public class UserDao {
private Connection connection;
public UserDao() {
connection = ConnectionProvider.getConnection();
}
public boolean createUser(User user) {
try {
String sqlRequest =
"INSERT INTO Users (ID,FIRST_NAME,LAST_NAME,EMAIL,LOGIN,PASSWORD," +
"GENDER,CONFIRMED,BANNED,REGISTRATION_DATE,NOTIFY,ROLE) " +
"values(?,'?','?','?','?','?',?, ?, ?, TO_DATE('?','yyyy-mm-dd'), ?, ?)";
PreparedStatement ps = connection.prepareStatement(sqlRequest);
ps.setLong(1, user.getId());
ps.setString(2, user.getFirstName());
ps.setString(3, user.getLastName());
ps.setString(4, user.getEmail());
ps.setString(5, user.getLogin());
ps.setString(6, user.getPassword());
ps.setInt(7, user.getGender().toInt());
ps.setBoolean(8, user.isConfirmed());
ps.setBoolean(9, user.isBanned());
ps.setString(10, user.getRegistrationDate());
ps.setBoolean(11, user.isNotify());
ps.setInt(12, user.getRole().getId());
ps.executeUpdate();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return true;
}
public User getUserById(long id) {
User user = new User();
try {
String sqlRequest =
"SELECT * FROM Users WHERE id=?";
PreparedStatement ps = connection.prepareStatement(sqlRequest);
ps.setLong(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
user.setId(rs.getLong("id"));
user.setFirstName(rs.getString("FIRST_NAME"));
user.setLastName(rs.getString("LAST_NAME"));
user.setEmail(rs.getString("EMAIL"));
user.setLogin(rs.getString("LOGIN"));
user.setPassword(rs.getString("PASSWORD"));
user.setGender(Gender.getGender(rs.getInt("GENDER")));
user.setConfirmed(rs.getInt("CONFIRMED")==1);
user.setBanned(rs.getInt("BANNED")==1);
//String[] regDate = rs.getString("REGISTRATION_DATE").split(".");
//user.setRegistrationDate(Date.valueOf(regDate[2]+"-"+regDate[1]+"-"+regDate[0]));
user.setRegistrationDate(rs.getString("REGISTRATION_DATE"));
user.setNotify(rs.getInt("NOTIFY")==1);
user.setRole(new RoleDao().getRoleById(rs.getInt("ROLE")));
//user.setRole(new Role(1, "Administrator"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
public List<User> getAllUsers() {
List<User> users = new ArrayList<User>();
try {
String sqlRequest =
"SELECT * FROM Users";
PreparedStatement ps = connection.prepareStatement(sqlRequest);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
User user = new User();
user.setId(rs.getLong("id"));
user.setFirstName(rs.getString("FIRST_NAME"));
user.setLastName(rs.getString("LAST_NAME"));
user.setEmail(rs.getString("EMAIL"));
user.setLogin(rs.getString("LOGIN"));
user.setPassword(rs.getString("PASSWORD"));
user.setGender(Gender.getGender(rs.getInt("GENDER")));
user.setConfirmed(rs.getInt("CONFIRMED")==1);
user.setBanned(rs.getInt("BANNED")==1);
user.setRegistrationDate(rs.getString("REGISTRATION_DATE"));
user.setNotify(rs.getInt("NOTIFY")==1);
user.setRole(new RoleDao().getRoleById(rs.getInt("ROLE")));
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
public boolean removeUser(int id) {
try {
String sqlRequest = "DELETE FROM users WHERE id=?";
PreparedStatement ps = connection.prepareStatement(sqlRequest);
ps.setInt(1, id);
ps.executeUpdate();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return true;
}
public boolean editUser(User user) {
try {
String sqlRequest = "UPDATE Users SET FIRST_NAME='?', LAST_NAME='?', " +
"EMAIL='?', LOGIN='?', PASSWORD='?', GENDER=?, CONFIRMED=?, " +
"BANNED=?, REGISTRATION_DATE=TO_DATE('?','yyyy-mm-dd'), NOTIFY=?, ROLE=? WHERE ID=?";
PreparedStatement ps = connection.prepareStatement(sqlRequest);
String[] userParams = new String[12];
userParams = user.toStringList().toArray(userParams);
for(int i=1; i < 12; i++)
ps.setString(i, userParams[i]);
ps.setString(12, userParams[0]);
ps.executeUpdate();
connection.prepareStatement("commit").executeUpdate();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return true;
}
}
Entity class and DAO for Library are similarly.
ConnectionProvider class. Stores Connection like singleton object.
public class ConnectionProvider {
private static Connection con = null;
private ConnectionProvider(){}
public static Connection getConnection() {
if (con != null)
return con;
else {
try {
Locale.setDefault(Locale.ENGLISH);
Context ic = new InitialContext();
DataSource dataSource = (DataSource) ic.lookup("jdbc/test");
con = dataSource.getConnection();
}
catch(NamingException e)
{
System.out.println("Cannot retrieve jdbc/test"+e.getMessage());
}
catch(SQLException e)
{
e.printStackTrace();
}
return con;
}
}
}
Body of JSP page for testing Dao. When this code works, you can refresh page 3 times correctly. And on 4th time you'll get blank page.
<%
List<User> users = new UserDao().getAllUsers();
for(User u : users){
out.println(u.toString()+"<br/>");
}
%>
When this code works, you can load page 1 time correctly (you'll see all 1050 records). And on refreshing you'll get blank page.
<%
List<Library> books = new LibraryDao().getAllBooks();
for(TransferObject.Library b : books){
out.println(b.toString()+"<br/>");
}
%>
Please, if anyone knows, say what is my pronlem. It's very oddly problem and I don't understand why it happens. I can see limited count of records from DB and then methods getAllUsers() or getAllBooks() returns null.
