Reputation: 1221
So I am trying to find an example online about creating a preparedStatement that has an sql query referencing multiple tables.
For e.g. The examples I've encountered so far are always
e.g.
s = conn.prepareStatement ("DELETE FROM Users WHERE id_user = ?");
s.setInt (1, 2);
where there is only one table involved, and the method exists in the same class of the database table. E.g. User.class , user table in database.
The query that I have requires me to set the place holder from another table/class. In this case, my method exists in the User.class, however, it requires a the binding from a Group object.
SELECT DISTINCT *
FROM usuarios
WHERE NOT EXISTS
(SELECT * FROM usuarios_grupos
WHERE usuarios_grupos.id_grupo = ?
AND usuarios_grupos.id_usuario = usuarios.id_usuario);
Will the method be the following:
public List<Usuarious> list(Grupos groups) throws DAOExceptions {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<Usuarious> users = new ArrayList<Usuarious>();
try {
connection = daoFactory.getConnection();
preparedStatement = connection.prepareStatement(SQL_LIST_ALL);
preparedStatement.setInt(1, groups.getId_grupo());
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
users.add(mapUser(resultSet));
}
} catch (SQLException e) {
throw new DAOExceptions(e);
} finally {
close(connection, preparedStatement, resultSet);
}
return users;
}
or will it be written differently?? because I seem to be getting a NPE with this, and from the examples I've seen online. The query always reference 1 table. Is what I'm doing here wrong?
okay here is my method for groups.getId_grupo(), which exists in my Group.class:
public class Grupos {
Integer id_grupo;
String descricao;
public Grupos() {
}
public Grupos(Integer id_grupo, String descricao) {
this.id_grupo = id_grupo;
this.descricao = descricao;
}
public Grupos(Integer id_grupo) {
this.id_grupo = id_grupo;
}
public String getDescricao() {
return descricao;
}
public void setDescricao(String descricao) {
this.descricao = descricao;
}
public Integer getId_grupo() {
return id_grupo;
}
public void setId_grupo(Integer id_grupo) {
this.id_grupo = id_grupo;
}
}
I am calling my List list(Grupos groups) method in my ManagedBean
public class UsuariousGruposBean implements Serializable {
private Usuarious user = new Usuarious();
private Grupos grps = new Grupos();
private UsuariousGrupos userGroups = new UsuariousGrupos();
protected final UsuariousDAO userDAO = daoFactory.getUserDAO();
protected final GruposDAO grpDAO = daoFactory.getGruposDAO();
protected final UsuariousGruposDAO userGrpDAO = daoFactory.getUsuariousGruposDAO();
private List<Usuarious> listOfUsuarios;
private List<Grupos> listOfGrps;
private List<UsuariousGrupos> listOfUserGroups;
public UsuariousGruposBean() {
}
public List<Usuarious> getListOfUsuarios() throws DAOExceptions {
List<Usuarious> usuariosList = userDAO.list(grps);
listOfUsuarios = usuariosList;
return listOfUsuarios;
}
Upvotes: 0
Views: 1607
Reputation: 20323
First instance in your code can throw NPE is at:
preparedStatement = connection.prepareStatement(SQL_LIST_ALL);
if your connection is null, your connection factory didnt return you one, check if you have a valid connection
Second place :
groups.getId_grupo()
Check if your groups is null or not
If these are not the reasons then please post your stacktrace.
Upvotes: 3
Reputation: 28697
The stack trace of your NPE should help tell you where the issue is (line #, etc.). From what I can tell, your SQL and the way you are calling it is all valid.
Is it possible that you're receiving a null groups
parameter, such that calling groups.getId_grupo()
is throwing the NPE?
Upvotes: 0