java.sql.SQLException: no such column

Question

I've been given an assignment to demonstrate CRUD operations using sqlite. When I try to retrieve an item from the database my ResultSet doesn't seem the have the "id" column. I am quite stumped.

This is my ContactRepository class.

public class ContactRepositoryJDBC implements ContactRepository {

    private static Connector connector = new Connector();

    ...

    public Contact getById(int id) throws SQLException {
        Contact contact = null;

        try (Connection conn = connector.getConnection();
            PreparedStatement statement = conn
                    .prepareStatement(SQL.GET_CONTACT_BY_ID)) {
            statement.setInt(1, id);
            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    contact = new Contact();
                    contact.setId(resultSet.getInt("id"));
                    contact.setFirstName(resultSet.getString("first_name"));
                    contact.setSurname(resultSet.getString("surname"));
                    contact.setHomeNumber(resultSet.getString("home_number"));
                    contact.setCellNumber(resultSet.getString("cell_number"));
                    contact.setEmail(resultSet.getString("email"));
                }
            }
        }

        if (contact == null) {
            System.out.print("no contact with id " + id + " found : ");
        }
        return contact;
    }

...

}

I use a seperate class for my sql statements

public class SQL {

...

public static final String GET_CONTACT_BY_ID = "SELECT first_name, surname, home_number, cell_number, email FROM Contact WHERE id = ?";

...

}

I get an SQLException "no such column id"

Exception in thread "main" java.sql.SQLException: no such column: 'id'
at org.sqlite.RS.findColumn(RS.java:121)
at org.sqlite.RS.getInt(RS.java:293)
at net.phonebook.repository.ContactRepositoryJDBC.getById(ContactRepositoryJDBC.java:59)
at net.phonebook.model.app.App.main(App.java:31)

EDIT:

Contact model class

public class Contact {

    private int id;
    private String firstName;
    private String surname;
    private String homeNumber;
    private String cellNumber;
    private String email;

    public Contact(String firstName, String surname, String homeNumber,
            String cellNumber, String email) {
        this.firstName = firstName;
        this.surname = surname;
        this.homeNumber = homeNumber;
        this.cellNumber = cellNumber;
        this.email = email;
    }

    public Contact() {

    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    ... getters and setter for everything else
}

How did you create the database? Is there a column named "id"? — BadSkillz
– BadSkillz, Commented Jul 2, 2014 at 10:22
Your select statement doesn't contain id column after SELECT keyword. — Kasper Ziemianek
– Kasper Ziemianek, Commented Jul 2, 2014 at 10:23
Thank you guys. That worked. Now I feel silly for sitting for a few hours and not noticing it. Many thanks.! — silviugarlea
– silviugarlea, Commented Jul 2, 2014 at 10:27

laalto · Accepted Answer · 2014-07-02 10:23:21Z

6

Your selection does not include the column id and hence it is not in the result set. Add it to the selection:

public static final String GET_CONTACT_BY_ID = "SELECT id, first_name, surname, home_number, cell_number, email FROM Contact WHERE id = ?";

answered Jul 2, 2014 at 10:23

laalto

153k68 gold badges293 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sanjeev · Accepted Answer · 2014-07-02 10:24:55Z

1

Your SQL Query

public static final String GET_CONTACT_BY_ID = "SELECT first_name, surname, home_number, cell_number, email FROM Contact WHERE id = ?";

do not selects the id column. You can only retrieve those columns from result set which are selected in your query.

In Order to get id column value you need to modify your query to

public static final String GET_CONTACT_BY_ID = "SELECT id,first_name, surname, home_number, cell_number, email FROM Contact WHERE id = ?";

answered Jul 2, 2014 at 10:24

Sanjeev

9,9762 gold badges24 silver badges33 bronze badges

Comments

kenorb · Accepted Answer · 2014-07-02 10:38:58Z

1

While querying the database you used:

SELECT first_name, surname, home_number, cell_number, email FROM Contact WHERE id = ?

but from the resultset you are expecting id contact.setId(resultSet.getInt("id"));

hence the error change the query to:

SELECT id, first_name, surname, home_number, cell_number, email FROM Contact WHERE id = ?

edited Jul 2, 2014 at 10:38

kenorb

169k95 gold badges712 silver badges796 bronze badges

answered Jul 2, 2014 at 10:26

SparkOn

8,9665 gold badges31 silver badges36 bronze badges

Comments

Deepak Singh · Accepted Answer · 2014-07-02 10:30:53Z

0

Just check your database that 'CONTACT' table contains the column named as 'id'. This error is only arrived when there is column name mismatch.

Your Query is Correct

answered Jul 2, 2014 at 10:30

Deepak Singh

1

Collectives™ on Stack Overflow

java.sql.SQLException: no such column

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related