query ms access database from the user

Thread view:

geletine - 20 May 2006 10:35 GMT

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:odbc: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

Reply to this Message

Bjorn Abelli - 20 May 2006 10:53 GMT

"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

Reply to this Message

geletine - 20 May 2006 11:29 GMT

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:odbc: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();
         }
     }
}

Reply to this Message

query ms access database from the user

Free Magazines