query ms access database from the user

[geletine]

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("jdbcdbc: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

 

[Bjorn Abelli]

...

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

 

[geletine]

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