SQLException: parameter 1 is not specified

Question

While running my application, I found the following error:

java.sql.SQLException: parameter 1 is not specified

What does it mean?

My UserGroup list in my dao:

 public List<UsuariousGrupos> select(Integer var) { List<UsuariousGrupos> ug= null; try { conn.Connection(); stmt = conn.getPreparedStatement("select id_usuario, id_grupo from usuarios_grupos where id_grupo ='" + var + "'"); ResultSet rs = stmt.executeQuery(); ug = new ArrayList<UsuariousGrupos>(); while (rs.next()) { ug.add(getUserGrupos(rs)); } } catch (SQLException e) { e.printStackTrace(); } finally { conn.Disconnected(); } return ug; } public UsuariousGrupos getUserGrupos(ResultSet rs) { try { UsuariousGrupos ug = new UsuariousGrupos(rs.getInt("id_usuario"), rs.getInt("id_grupo")); return ug; } catch (SQLException e) { e.printStackTrace(); } return null; } 

My list of user groups in my managed bean:

 public List<UsuariousGrupos> getListOfUserGroups() { List<UsuariousGrupos> usuariosGruposList = userGrpDAO.select(var2); listOfUserGroups = usuariosGruposList; return listOfUserGroups; } 

My JSF page:

  <p:dataTable var="usergroups" value="#{usuariousGruposBean.listOfUserGroups}"> <p:column headerText="" style="height: 0" rendered="false"> <h:outputText value="#{usergroups.id_grupo}"/> </p:column> 

My data table can display a list of groups from the database. However, when I select a single row in my data table, the application needs to establish a connection to my database in order to display the selected result.

In addition, it is strange that the application can display certain selected results faster than others. Does this have anything to do with the Exception, which I indicated at the beginning?

Mistake:

 Disconnected Connected!! java.sql.SQLException: No value specified for parameter 1 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929) at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2560) at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2536) at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2462) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2216) at br.dao.UsuariousGruposDAO.select(UsuariousGruposDAO.java:126) at br.view.UsuariousGruposBean.getListOfUserGroups(UsuariousGruposBean.java:54) SEVERE: Error Rendering View[/index.xhtml] javax.el.ELException: /index.xhtml @61,99 value="#{usuariousGruposBean.listOfUserGroups}": Error reading 'listOfUserGroups' on type br.view.UsuariousGruposBean at com.sun.faces.facelets.el.TagValueExpression.getValue(TagValueExpression.java:114) at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:194) at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:182) 

Answer 1

There is no method like Connection() and getPreparedStatement() on java.sql.Connection .

 conn.Connection(); stmt = conn.getPreparedStatement("select id_usuario, id_grupo from usuarios_grupos where id_grupo ='" + var + "'"); 

conn is clearly a home wrapper around JDBC code. Your specific problem is most likely caused by the getPreparedStatement() method code. Apparently he is adding a line ? to an SQL string before passing it to the actual connection.prepareStatement() method.

You probably don't want to hear this, but your JDBC approach is completely broken. This construct indicates that JDBC Connection saved as a static or instance variable that is threadunsafe .

You need to completely rewrite it so that it comes down to the following proper use and variation:

 public List<UsuariousGrupos> select(Integer idGrupo) throws SQLException { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; List<UsuariousGrupos> usuariousGrupos = new ArrayList<UsariousGrupos>(); try { connection = database.getConnection(); statement = connection.prepareStatement("select id_usuario, id_grupo from usuarios_grupos where id_grupo = ?"); statement.setInt(1, idGrupo); resultSet = statement.executeQuery(); while (resultSet.next()) { usuariousGrupos.add(mapUsuariousGrupos(resultSet)); } } finally { if (resultSet != null) try { resultSet.close(); } catch (SQLException ignore) {} if (statement != null) try { statement.close(); } catch (SQLException ignore) {} if (connection != null) try { connection.close(); } catch (SQLException ignore) {} } return usuariousGrupos; } 

See also:

• How to declare a global static class in Java?

---

Not tied to a specific issue, you have another problem. Next exception

javax.el.ELException: /index.xhtml @ 61.99 value = "# {usuariousGruposBean.listOfUserGroups}": Error reading 'listOfUserGroups' by type br.view.UsuariousGruposBean

indicates that you are doing JDBC stuff inside the getter method instead of the (post) constructor or (action) listener method. This is also a very bad idea, because a getter can be called more than once during a render response. Correct it accordingly.

See also:

• Why JSF calls getters several times

Answer 2

Usually you get this error when using prepared statements and forget to set the parameter with index 1.

In this case, you use prepared statements, but there is nothing to prepare, you make the query string manually.

In addition, you may run into additional problems because you are integrating Integer between the apostles. Numerical values ​​go without them.

So it should be like this:

 stmt = conn.getPreparedStatement("select id_usuario, id_grupo from usuarios_grupos where id_grupo = " + var + ";"); 

But in fact, you should use something like getStatement () or use getPreparedStatement () correctly (put a? At var and setInteger () to put it.

Thus, the final decision will be:

 stmt = conn.getPreparedStatement("select id_usuario, id_grupo from usuarios_grupos where id_grupo = ?;"); stmt.setInt(1, var); 

Answer 3

If you use

 stmt = conn.getPreparedStatement("select id_usuario, id_grupo from usuarios_grupos where id_grupo = ?); 

You should

 stmt.setInt(1, var); 

before

 ResultSet rs = stmt.executeQuery(); 

Assigning a value to the first parameter (i.e., parameter 1). If your exception does not happen.