CODEWITHSUNDEEP
javasqlswingjdbc
Bhuvanesh Desai
Bhuvanesh Desai

Reputation: 163

Getting column name instead of values in prepared statement

I am getting column name instead of values in prepared statement.

here is my piece of code: 

string q="select ? from EMPLOYEE where salary > ?";

Preparedstatement pst = connectionobject.preparedstatement(q);
pst.setstring(1, "FIRST_NAME");
pst.setint(2, 10000);

When I print out the result in JTable it shows FIRST_NAME in all rows.

Upvotes: 0

Views: 2518

Answers (2)

Youcef LAIDANI
Youcef LAIDANI

Reputation: 60016

This is not possible, with your way so to solve your problem.

Change your code like this :

String att = "FIRST_NAME";

string q="select " + att + " from EMPLOYEE where salary>?";

Preparedstatement pst=connectionobject.preparedstatement(q);
pst.setint(1,10000);

IF YOU AFRAID THAT THE USER CAN MAKE AN SQL Injection than use this solution

You should to check if your column exist in your table or not :

SELECT * 
    FROM information_schema.COLUMNS 
    WHERE 
        TABLE_SCHEMA = 'db_name' 
    AND TABLE_NAME = 'table_name' 
    AND COLUMN_NAME = 'column_name'

Like so :

public boolean column_exist(String att) {
    boolean succes = false;
    CreerConnection con = new CreerConnection();
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultat = null;
    try {
        connection = con.getConnection();

        statement = connection.prepareStatement("SELECT * \n"
                + "FROM information_schema.COLUMNS "
                + " WHERE"
                + " TABLE_SCHEMA = 'db_name'"
                + " AND TABLE_NAME = 'table_name'"
                + " AND COLUMN_NAME = ?");
        statement.setString(1, att);
        resultat = statement.executeQuery();

        if (resultat.next()) {
            succes = true;
        }

    } catch (SQLException e) {
        System.out.println("Exception = " + e);
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException ex) {
            }
        }
    }
    return succes;
}

If the column exist then you can continue like so:

if(column_exist(att)){
   string q="select " + att + " from EMPLOYEE where salary>?";

   Preparedstatement pst=connectionobject.preparedstatement(q);
   pst.setint(1,10000); 
}

Learn more here :

MySQL, Check if a column exists in a table with SQL

Hope this can help you.

Upvotes: 1

Matthieu Saleta
Matthieu Saleta

Reputation: 1508

Your preparedStatement must produce the query : select "FIRST_NAME" from EMPLOYEE where salary > 10000 instead of select FIRST_NAME from EMPLOYEE where salary > 10000.

So it returns the string "FIRST_NAME" for each row.

You could simply use a StringBuilder to replace the first '?' by your column_name.

Upvotes: 1

Related Questions