java.sql.SQLException: Invalid value for getInt() - 'Glomindz Support'

Question

I am not able to retrieve column values from my database table with the following coding a message has been displayed in the console:

java.sql.SQLException: Invalid value for getInt() - 'Glomindz Support'

My code is:

package com.glomindz.mercuri.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import com.glomindz.mercuri.util.MySingleTon;

public class UserServicesDAO {

    private Connection connection;

    public UserServicesDAO() {
        // connection = new MySingleTon().getConnection();
        connection = MySingleTon.getInstance().getConnection();

    }

    public void get_all_data() {
    }

    public Map<Integer, String> get_all_data1() {
        HashMap<Integer, String> result = new HashMap<Integer, String>();
        String query = "SELECT * FROM spl_user_master";
        try {
            PreparedStatement stmt = connection.prepareStatement(query);
            boolean execute = stmt.execute();
            System.out.println(execute);
            ResultSet resultSet = stmt.getResultSet();
            System.out.println(resultSet.getMetaData());
            while (resultSet.next()) {
                result.put(resultSet.getInt(1), resultSet.getString("id"));
                result.put(resultSet.getInt(2), resultSet.getString("name"));
                result.put(resultSet.getInt(3), resultSet.getString("email"));
                result.put(resultSet.getInt(4), resultSet.getString("mobile"));
                result.put(resultSet.getInt(5), resultSet.getString("password"));
                result.put(resultSet.getInt(6), resultSet.getString("role"));
                result.put(resultSet.getInt(7), resultSet.getString("status"));
                result.put(resultSet.getInt(8),
                        resultSet.getString("last_update"));
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;
    }

    public static void main(String[] args) {
        new UserServicesDAO().get_all_data1();
    }
}

My db table schema is:

id  name    email   mobile  password    role    status  last_update
1   Glomindz Support    [email protected]    9854087006  cbf91a71c11d5ec348b0c7e9b2f0055e    admin   1   2013-05-02 22:05:14
2   Amarjyoti Das   [email protected]    9864092598  88f2dccb02b2a20615211e5492f85204    admin   1   2013-04-26 05:44:41

Answer 1

You retrieve every column as an int for the key. I assume that some of these columns represent Strings or Dates.

while(resultSet.next()){
    result.put(resultSet.getInt(1),resultSet.getString("id"));
    result.put(resultSet.getInt(2),resultSet.getString("name")); //Most likely a String
    result.put(resultSet.getInt(3),resultSet.getString("email"));
    result.put(resultSet.getInt(4),resultSet.getString("mobile"));
    result.put(resultSet.getInt(5),resultSet.getString("password"));
    result.put(resultSet.getInt(6),resultSet.getString("role"));
    result.put(resultSet.getInt(7),resultSet.getString("status"));
    result.put(resultSet.getInt(8),resultSet.getString("last_update"));  //Most likely a date
}

The inconsistencies between the data types and the object/value returned by the getInt() method causes the error. I would suggest building/modeling an object in your domain that stores rows from the table. Something like:

public class User{
    private Integer id;
    private String name;
    private String email;
    private String mobile;
    private String password;
    private String role;
    private String status;
    private Date lastUpdate;

    /*  Get and set methods for each field */
}

Then build a Map containing the object as the value and the id as the key:

   //Use Map interface here, also notice generic arguments <Integer,User>
   Map<Integer, User> result = new HashMap<Integer, User>();
   try {
        PreparedStatement stmt = connection.prepareStatement(query);
        boolean execute = stmt.execute();
        System.out.println(execute);
        ResultSet resultSet = stmt.getResultSet();
        System.out.println(resultSet.getMetaData());
        while(resultSet.next()){
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setName(resultSet.getString("name");
            user.setEmail(resultSet.getString("email");
            //do this for each field, using appropriate method for type...

            //then add to map
            result.put(user.getId(), user);

        }