query ms access database from the user

Discussion in 'Java' started by geletine, May 20, 2006.

  1. geletine

    geletine Guest

    At college i have been given this piece of untidy code because it mixes
    a gui interface with the console, i prefare one or the other, but its
    not my code, i understand what it does.
    It opens the database CarDB by using the java odbc (Open Database
    Connectivity ) driver
    and searches for the row make with nissan and displays to the screen
    as text in a gui text box the columns Registration, Model, Year, Price
    in the same row as the nissan .
    I would like the programme to ask the user which car their want to
    search for .
    Unfortunately i do not learn at college or have taught myself sql.
    If i am understanding proberly the line
    ResultSet rec = st.executeQuery( "SELECT Registration, Model, Year,
    Price FROM Table1 where Make='nissan'");
    is where the query starts, but its hardcoded in, i presume i need to
    have a line similar to
    ResultSet rec = st.executeQuery( "SELECT Registration, Model, Year,
    Price FROM Table1 request Make=' '") or something similar, am i along
    the correct lines?

    here is the code..

    import java.sql.*;
    //import javax.swing.JOptionPane;
    //import javax.swing.JTextArea;
    import java.io.*;
    public class Car2 {
    public static void main(String args[]){
    Connection connection;
    Statement st;
    String out="";

    JTextArea display=new JTextArea();
    try{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    connection= DriverManager.getConnection("jdbc:eek:dbc:CarDB","","");
    st = connection.createStatement();
    statement = connection.createStatement();

    ResultSet rec = st.executeQuery( "SELECT Registration,
    Model, Year, Price FROM Table1 where Make='nissan'");
    while (rec.next()) {
    out=" nissan: " +
    rec.getString("Registration")+"\t"+
    rec.getString("Model")+"\t"+ rec.getString("Year") +
    "\t"+ rec.getString("Price");
    display.setText(out);
    JOptionPane.showMessageDialog(null,display);
    }
    }catch (Exception e) {
    e.printStackTrace();
    }
    }
    }


    thanks in advance
     
    geletine, May 20, 2006
    #1
    1. Advertising

  2. geletine

    Bjorn Abelli Guest

    "geletine" wrote...

    > If i am understanding proberly the line
    > ResultSet rec = st.executeQuery( "SELECT Registration, Model, Year,
    > Price FROM Table1 where Make='nissan'");


    That's where the query is executed, just as the method name proposes...
    ;-)

    > is where the query starts, but its hardcoded in, i presume
    > i need to have a line similar to
    > ResultSet rec = st.executeQuery( "SELECT Registration, Model,
    > Year, Price FROM Table1 request Make=' '") or something similar,
    > am i along the correct lines?


    Not even close...

    You have to get the "make" from the user *before* you make the call to the
    database.

    When you have that, you can use a PreparedStatement to "insert" the value
    into the query.

    String make = ... // received from user

    ....

    PreparedStatement ps =
    connection.prepareStatement(
    "SELECT Registration, Model, Year, Price " +
    "FROM Table1 where Make = ? ");

    ps.setString(1, make);

    ResultSet rec = ps.executeQuery();

    > thanks in advance


    You're welcome.

    /// Bjorn A



    Inviato da X-Privat.Org - Registrazione gratuita http://www.x-privat.org/join.php
     
    Bjorn Abelli, May 20, 2006
    #2
    1. Advertising

  3. geletine

    geletine Guest

    Thank you very much , i converted that code into a console only app
    which i think is alot cleaner .

    here is the code which works

    import java.sql.*;
    import java.io.*;
    public class Car2 {
    public static void main(String args[]){
    Connection connection;
    Statement st;
    String out="";
    String display="";
    String make = "";
    BufferedReader info = new BufferedReader ( new InputStreamReader (
    System.in ) );

    try{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    connection= DriverManager.getConnection("jdbc:eek:dbc:CarDB","","");
    st = connection.createStatement();

    System.out.println("Enter name of car");
    make = info.readLine();

    PreparedStatement ps =
    connection.prepareStatement(
    "SELECT Registration, Model, Year, Price " +
    "FROM Table1 where Make = ? ");


    ps.setString(1, make);


    ResultSet rec = ps.executeQuery();


    while (rec.next()) {
    out=" : " + rec.getString("Registration")+"\t"+

    rec.getString("Model")+"\t"+ rec.getString("Year") +
    "\t"+ rec.getString("Price");

    display = out;
    System.out.println (display);
    }
    }catch (Exception e) {
    e.printStackTrace();
    }
    }
    }
     
    geletine, May 20, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?U1M=?=
    Replies:
    3
    Views:
    3,879
    Juan T. Llibre
    May 15, 2005
  2. Learner
    Replies:
    1
    Views:
    1,024
    Marina Levit [MVP]
    Jan 30, 2006
  3. HuskerGrad

    query problem with Access database

    HuskerGrad, Feb 28, 2007, in forum: Java
    Replies:
    4
    Views:
    464
    HuskerGrad
    Feb 28, 2007
  4. powerrun
    Replies:
    5
    Views:
    144
    Ray at
    Oct 23, 2003
  5. George Stout
    Replies:
    4
    Views:
    225
    George Stout
    Jan 21, 2004
Loading...

Share This Page