CODEWITHSUNDEEP
javamysqlsqluser-input
tamerica
tamerica

Reputation: 21

MySQL Query based on user input in Java

i'm new to SQL and Java and couldn't find anything to fix my problem. (closest thing: MySQL query based on user input) So I have a DB full of shows/events and I want users to "search" for a certain event or show. The user will input the name of the show/event into GUI and I want the query to return data associated with the user-input.

For example:

User is searching for the artist Zedd;

searchSet = statement.executeQuery("SELECT eventname,date FROM shows WHERE artist LIKE      'zedd' ");

The query is fixed, can the query be modified to search whatever the user input?

Something like:

String artist = "zeppelin"
searchSet = statement.executeQuery("SELECT eventname,date FROM shows WHERE artist LIKE "artist" ");

Thanks in advance for the help!

Upvotes: 2

Views: 3091

Answers (3)

Elliott Frisch
Elliott Frisch

Reputation: 201439

You should double check your shows second column. I'm not sure you can use a column named date. Anyway, you should certainly use a PreparedStatement with something like,

String sql = "SELECT eventname, eventdate FROM shows WHERE artist LIKE ?";
PreparedStatement ps = null;
ResultSet rs = null;

try {
  ps = conn.prepareStatement(sql);
  ps.setString(1, artist);
  rs = ps.executeQuery();
  while (rs.next()) {
    String eventName = rs.getString("eventname");
    Date eventDate = rs.getDate("eventdate");
    // Use your columns in your row here.
  }
} catch (Exception e) {
  e.printStackTrace();
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  if (ps != null) {
    try {
      ps.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

Upvotes: 3

Anthony Kong
Anthony Kong

Reputation: 40654

It would be something like this:

String artist = "zeppelin"
searchSet = statement.executeQuery("SELECT eventname, date FROM shows WHERE artist LIKE '%" + artist + "%'");

Note the sql wildcard string '%'

Upvotes: 0

Scary Wombat
Scary Wombat

Reputation: 44834

As per normal SQL, you statement would work if it was

String artist = "'%zeppelin%'";
searchSet = statement.executeQuery("SELECT eventname,date FROM shows WHERE artist LIKE " +  artist);

The String needs to be aurrounded with % and also quoted.

Have a look at preparedStement though, it has method to set each parameter separately and avoid SQL injection.

Upvotes: 0

Related Questions