13

I encountered the following error when I was executing my application:

java.sql.SQLException: No value specified for parameter 1

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 get 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 is able to display the list of groups from the database. However, when I select an individual row within my data table, it takes some time for the application to establish connection with my database to display the selected result.

Also, it is weird that the application is able to display certain selected results quicker than others. Does it have anything to do with the Exception I pointed out at the beginning?

Error:

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)
Massimiliano Kraus
  • 3,638
  • 5
  • 27
  • 47
ShaunK
  • 1,181
  • 5
  • 22
  • 41
  • at which line does the error occur? – oers Nov 18 '11 at 13:13
  • the error occurs at these two lines: ResultSet rs = stmt.executeQuery(); and List usuariosGruposList = userGrpDAO.select(var2); – ShaunK Nov 18 '11 at 13:18
  • 1
    are you sure your select is not "select id_usuario, id_grupo from usuarios_grupos where id_grupo = ?". This would explain the missing value for parameter 1. – oers Nov 18 '11 at 13:21
  • humm I actually tried that, and I get the following error. – ShaunK Nov 18 '11 at 13:31
  • the new error seems to be a new problem. Maybe you should open a separate question for that and accept a solution for the sql exception here. – oers Nov 18 '11 at 13:56

5 Answers5

15

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

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

The conn is clearly a homegrown wrapper around JDBC code. Your particular problem is likely caused by the code behind the getPreparedStatement() method. It's apparently appending a ? to the SQL string before delegating through to the real connection.prepareStatement() method.

You probably don't want to hear this, but your JDBC approach is totally broken. This design indicates that the JDBC Connection is hold as a static or instance variable which is threadunsafe.

You need to totally rewrite it so that it boils down to the following proper usage and variable scoping:

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:

Unrelated to the concrete question, you've another problem. The following exception

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

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

See also:

Community
  • 1
  • 1
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • Hey thanks for information and help! I am going through it now. Sorry, I am new to JDBC and Java web application development – ShaunK Nov 18 '11 at 13:57
3

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

In this case, you are using prepared statements but there is nothing to prepare, you make the query string by hand.

Also, you may run into additional problems because you're concatenating an Integer between aposthrophes. Numeric 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 actually you should use something like getStatement() or use the getPreparedStatement() correctly (place a ? at the position of var and setInteger() to place it in.

So the final solution would be:

stmt = conn.getPreparedStatement("select id_usuario, id_grupo from usuarios_grupos where id_grupo = ?;");
stmt.setInt(1, var);
Raidok
  • 774
  • 8
  • 19
2

If you use

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

You have to

stmt.setInt(1, var);

before

ResultSet rs = stmt.executeQuery();

To assign a value to the first parameter (i.e. Parameter 1). If not your exception will occur.

oers
  • 18,436
  • 13
  • 66
  • 75
  • 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) – ShaunK Nov 18 '11 at 13:43
  • well var2 is my selected id when I select the row. I am trying to pass it into my select (Int var) method in the dao. – ShaunK Nov 18 '11 at 13:45
0

Recently I have also met this problem, in my case I used jdbcTemplate batchUpdate method to batch insert records

void batchInsert(final List<EntryDataDO> doList) {
    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            final EntryDataDO dataDO = doList.get(i);
            if (dataDO == null){
                return;
            }
            ps.setString(1, dataDO.getId());
            ps.setString(2, dataDO.getDataCode());
            ...
        }

        @Override
        public int getBatchSize() {
            return doList.size();
        }
    });
}

Reason is that if the first element of doList is null and it will directly return and it will cause Caused by: java.sql.SQLException: No value specified for parameter 1 but if not first element is null, the batchUpdate is ok but the records will duplicate e.g. the second element is null, the second will duplicate the first record 

insert into t (id,...) values (1,...),(1,...),(3,...)

So the solution is: 1. first exclude null element from doList 2. then call batchInsert(doListWithoutNull) that is to say: cannot exclude element inner org.springframework.jdbc.core.BatchPreparedStatementSetter#setValues

zhuguowei
  • 8,401
  • 16
  • 70
  • 106
0
ResultSet rs = stmt.executeQuery();

Don't use this line before operation performed.

Andreas
  • 5,393
  • 9
  • 44
  • 53
Gautam Singh Rajput
  • 1