java not passing int to mysql

[peter m hayward]

this is my code snippet

  while (resultSet.next()) {
            // It is possible to get the columns via name of the column
            // also possible to get the columns via the column number which starts at 1
            String[] bookdata = new String[17];


            for (int i = 2; i <= resultSet.getMetaData().getColumnCount(); i++) {



                bookdata[i - 1] = resultSet.getString(i);
            }


            System.out.println(bookdata[1]);
            System.out.println();
            System.out.println(bookdata[2]);
            System.out.println();
            System.out.println(bookdata[3]);
            System.out.println();
            System.out.println(bookdata[4]);
            String a =bookdata[4];
            //System.out.println(a);
            System.out.println();
            System.out.println(bookdata[5]);
            System.out.println();
            System.out.println(bookdata[6]);
            System.out.println();
            System.out.println(bookdata[7]);
            System.out.println();
            System.out.println(bookdata[8]);
            System.out.println();
            System.out.println(bookdata[9]);
            System.out.println();
            System.out.println(bookdata[10]);
            System.out.println();
            System.out.println(bookdata[11]);
            System.out.println();
            System.out.println(bookdata[12]);
            System.out.println();
            System.out.println(bookdata[13]);
            System.out.println();
            System.out.println(bookdata[14]);
            System.out.println();
            System.out.println(bookdata[15]);
            System.out.println();
            resultSet = statement
 .executeQuery("select * from author_tbl where author_id ='a'");       <<<<<<<<<<<< this is where i am having the problem
            AuthorResultSet(resultSet);            

in this example the result of the first query is that the author value =35216 if this
is place instead of the variable a then it =works so why is wrong with placing a in the
query that stops it working correctly






}



}

    private void AuthorResultSet(ResultSet resultSet) throws SQLException, Exception {

        while (resultSet.next()) {
            String Author = resultSet.getString(2);

            String FirstName;
            String LastName;



            System.out.println(Author);

        }


    }

    private void formatResultSet(ResultSet resultSet) throws SQLException, Exception {

        while (resultSet.next()) {
            String Author = resultSet.getString(2);

            String FirstName;
            String LastName;



            System.out.println(Author);

        }


    }
    // You need to close the resultSet

    private void close() throws SQLException {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null) {
                statement.close();
            }

            if (connect != null) {
                connect.close();
            }
        } catch (Exception e) {
        }


    }

[Peter Johnson]

The PHP forum isn't the correct place for help with Java JDBC code, moving to our JDBC forum where you are more likely to get help.

[Martin Vajsar]

Welcome to the Ranch, Peter!

If I understand you correctly, you expect this line

  resultSet = statement.executeQuery("select * from author_tbl where author_id ='a'")

to run a query which will find records where author_id equals to the value stored in local variable a.

It does't work this way. What you need to do is use a PreparedStatement and place a question mark instead of the bits you want to replace with a value. You then use separate method to specify the value of the parameter:

  PreparedStatement ps = connection.prepareStatement("select * from author_tbl where author_id=?");
  ps.setString(1, a);
  resultSet = ps.executeQuery();

There are still issues to handle: you need to close all resultset, statements and connections in the finally clause (much easier with Java 7 try-with-resources clause), and you should avoid implicit conversions (I'd guess that author_id is an integer, if it is, you should use setInt or setLong, not setString).

I'd suggest reading Oracle's JDBC tutorial, it is a very good resource on JDBC. If there is something unclear in the tutorial, please feel free to ask here.