Please help! execute a query from user input

[yesntu09]

Hi everybody,

So i am totally new to the programming world but I have manage so far to learn how to connect to mysql database to Java and execute a simple query. However, I would like to know if there's a way I could make this query to look like if the result of an user input.

Is this possible? For example: ask the user to enter the vendor name, then once they enter that information, the next question would be enter the vendor street name, and so forth. At the end I would the program to display the results of the query based on the answers the user entered. Here's my java program.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.*;


public class query {

  public static void main(String args[])  throws Exception {
    Connection connection = null;

    try 
    {
      String url = "jdbc:mysql://127.0.0.1/PlantCo";
      Class.forName("com.mysql.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1/Plantco", "root", "secure1");
      if(connection != null)
      {
         System.out.println("A database connection has been established");
      }    //end if(connection != null)
    } //end try
    catch(SQLException sqlException)
    {
       //handle any errors
       System.out.println("SQLException: " + sqlException.getMessage());
       System.out.println("SQLState: " + sqlException.getSQLState());
       System.out.println("VendorError: " + sqlException.getErrorCode());
    } //end catch
    catch(ClassNotFoundException classNotFoundException)
    {
       System.err.println(classNotFoundException.getMessage());
       classNotFoundException.printStackTrace();
       System.exit(1);  //terminate the programe
    }   //end catch(ClassNotFoundException classNotFoundException)

       Statement stmt = null;
       ResultSet rs = null;
    try 
    {
    stmt = connection.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    rs = stmt.executeQuery("SELECT vendorid, vendname FROM vendors");
     //or alternatively, if you don`t know ahead of time that
   //the query will be a SELECT...
   if (stmt.execute("SELECT vendorid, vendname FROM vendors"))
     {
       rs = stmt.getResultSet();
     }
       while (rs.next())
       {
        int id = rs.getInt("vendorid");
        String Name = rs.getString("vendname");

       }
    }
    finally
    {
         // it is a good idea to release resources in a finally() block
       //  in reverse-order of their creation if they are no longer needed
       if(rs != null)
       {
         try
           {
             rs.close();
           } //end try
           catch (SQLException sqlEx)
           {
              //ignore
           } // end catch
         }   //end if (rs != null)
      rs = null;
      if(stmt != null)
      {
        try
            {
              stmt.close();
            }
            catch (SQLException sqlEx)
            {
               //ignore
            } // end catch
         stmt = null;
       }  //end if (stmt != null)

      if(connection != null)
     {
       try
      {
       connection.close();
      } //end try
      catch(Exception exception)
      {
        System.out.println(exception.getMessage());
      }   // end catch (Exception exception)
     }  // end if (connection != null)
   }  // end finally
 }  // end main
} // end class

[javaAddict]

You can read the input from the console using the Scanner class and store that into variables. Then use these variables at your query:

String street = "myStreet";
String query = "SELECT vendorid, vendname FROM vendors where street = '" + street + "'";