Java & Dynamic SQL

[***SAM***]

HI! can any one help - cant get my head around how to pass string typed by the user on the client into SQL statement on the server side (using RMI).
The server is definately getting the string contents in CASE "G" but I dont know where to go from here.

SERVER-


import java.sql.*;
import java.net.*;
import java.io.*;
import java.util.*;

public class HouseServer


{

public static void main(String[] args)
{



// Net and IO objects
ServerSocket ss= null;
Socket s = null;
BufferedReader bf = null;
PrintWriter pw = null;

// Queries

// Query which retireves all products
String allQuery = "Select * from HouseDetails";
// Query which retrieves products which are out of stock
String bedsQuery = "Select * from HouseDetails where beds = 2";
String getArea ="";
String queryString = "";
String messageBack = "";

// Database objects
Connection cn = null;
Statement query = null;
ResultSet rs = null;
String lineRead= "";
// ************************** THIS IS THE QUERY I AM STRUGGLING WITH *************************
String areaQuery = "SELECT area "+ "FROM HouseDetails "+ "WHERE "+" (area='" +getArea+"')";



ArrayList houseList = new ArrayList();
Iterator houseIterator;




try
{
// Server socket set up on port 2000
System.out.println("Running.......");
ss = new ServerSocket(2000);
System.out.println("...Server socket set up");
// Set up a database
Database db = new Database
("jane", "", "sun.jdbc.odbc.JdbcOdbcDriver", "jdbcdbc:", "Houses");
System.out.println("...Database set up");
// Get a connection to the database
Database.establishConnection();
cn = Database.getConnection();
System.out.println("...Database connection set up");
//Set up a query object
query = cn.createStatement();
// Loop waiting for connections -
int count = 1;
while(true)
{
System.out.println("...Waiting for connection "+count);
count++;
s = ss.accept();
//Set up streams, the print writer must flush so use true as the second argument
pw = new PrintWriter(s.getOutputStream(), true);
bf = new BufferedReader(new InputStreamReader(s.getInputStream()));
// looper is used to terminate the loop when a client finishes
boolean looper = true;
while(true)
{
// Read a command from the client
lineRead = bf.readLine();
switch(lineRead.charAt(0))//
{
// Out of Stock command
case '2':{
queryString = bedsQuery;
break;
}
// All pizzas
case 'A':{
queryString = allQuery;

break;

}

//search area
case 'G':{

getArea = bf.readLine();
queryString = areaQuery;
break;
}

// Client has terminated
case 'E':{
looper = false;
break;
}

}
//Check if client has terminated
if(!looper) break;
// Client has not terminated
// Execute the required query and create result set
rs = query.executeQuery(queryString);
// Create collection of pizzas
houseList = new ArrayList();
// Process the rows that have been extracted
// Place them in pizzaList
House extractedHouse;
while(rs.next())
{
// Extract out the contents of a row and set up a HOUSE object extractedHOUSE
// Code to be inserted below

extractedHouse = new House(rs.getString(1),rs.getString(2),
rs.getInt(3),rs.getInt(4));

// Add the HOUSE object to the ArrayList of pizza objects
// Strictly no reason for doing this they can be sent
// directly back to the client, I have done it to illustrate the
// use of long messages

houseList.add( extractedHouse );

}
// Form the collection of products, each terminated by asterisk
houseIterator = houseList.iterator();
// messageBack is to be concatenated to so initialise it to the empty string
messageBack= "";
while(houseIterator.hasNext())
{
messageBack+=(House)houseIterator.next()+"*";
}
// Now send back the collection string to the client for display
pw.println( messageBack);
}
}
}
catch(Exception e)
{System.out.println("Trouble setting up the database "+e);}
// Close database connection
try
{
cn.close();
}
catch(Exception e)
{System.out.println("Problem closing connection");}







}
}

**********************************************************
Client
*****



import java.net.*;
import java.io.*;
import java.util.StringTokenizer;


import java.awt.*;
import java.awt.event.* ;

public class Eclient extends Frame implements ActionListener,WindowListener
{
//
// Declarations for network access
//
private PrintWriter pw = null;
private BufferedReader bf = null;
private Socket s = null;

// Code for method which sets up connection
private void setUpConnections()
{
try
{
// Socket on port 200 of localhost
s = new Socket("127.0.0.1", 2000);
// Don't forget to auto flush second argument true
pw = new PrintWriter(s.getOutputStream(), true);
bf = new BufferedReader(new InputStreamReader(s.getInputStream()));
}
catch(Exception e)
{System.out.println("Trouble setting up connection"+e);}

}
//
// Code for closing connections
//
private void closeConnections()
{
try
{
pw.close();
bf.close();
s.close();
}
catch(Exception e){System.out.println("Problem closing down connections ");}
}

private void process(String line)
{
// Processes the string of product details. Each is terminated by an
// asterisk, so set up a string tokenizer
StringTokenizer st = new StringTokenizer(line, "*");
// process the reply, placing each substring terminated by * on the
// text area resultsArea
resultsArea.setText("");
while(st.hasMoreElements())
resultsArea.append(st.nextToken()+"\n");
}




public Eclient()
{
setUpConnections();
setLayout(null);
setSize(442,328);
setVisible(false);
add(resultsArea);
resultsArea.setBounds(24,36,220,242);
add(twoBedButton);
twoBedButton.setBackground(java.awt.Color.black);
twoBedButton.setForeground(java.awt.Color.white);
twoBedButton.setBounds(288,96,94,36);
add(allButton);
allButton.setBackground(java.awt.Color.black);
allButton.setForeground(java.awt.Color.white);
allButton.setBounds(288,36,94,36);

add(searchLabel);
searchLabel.setBounds(288,186,94,36);
add(searchArea);
searchArea.setBounds(288,216,94,36);
add(searchButton);
searchButton.setBackground(java.awt.Color.black);
searchButton.setForeground(java.awt.Color.white);

searchButton.setBounds(390,216,30,36);

setTitle("Search House Database");
addWindowListener(this);
searchButton.addActionListener(this);
allButton.addActionListener(this);
twoBedButton.addActionListener(this);
}

public Eclient(String title)
{
this();
setTitle(title);
}

public void setVisible(boolean b)
{
if(b)
{
setLocation(50, 50);
}
super.setVisible(b);
}

static public void main(String args[])
{
(new Eclient()).setVisible(true);
}


//{{DECLARE_CONTROLS
TextArea resultsArea = new TextArea();
TextArea searchArea = new TextArea("",0,0,TextArea.SCROLLBARS_NONE);
Button twoBedButton = new Button("2 Bed");
Button allButton = new Button("All Houses");
Label searchLabel = new Label("Search by Area");
Button searchButton = new Button ("Go");
//}}


public void actionPerformed (ActionEvent a){
String s = a.getActionCommand( );
String g = searchArea.getText();
try{
if (s.equals( "All Houses"))
{
// Send A message to the server asking for all items
pw.println("A");
// Read reply
String rdString = bf.readLine();
// Process the string that has been sent back
process(rdString);
}

if (s.equals("2 Bed"))
{
// Send O message to the server asking for out of stock items
pw.println("2");
// Read reply
String rdString = bf.readLine();
// Process the string that has been sent back
process(rdString);
}

if (s.equals("Go"))
{
pw.println("G");
pw.println(g);

String rdString = bf.readLine();
process(rdString);
}

}
catch (Exception e) {
}
}
public void windowClosing(WindowEvent w ) {
// dispose method from the Frame class
pw.println("E"); // send E message to server for exit
this.dispose( );
this.closeConnections( );
// exit method from the System class
System.exit(0) ;
}

// The compiler insists that we provide some
// code for all the other methods in the
// WindowListener interface - it has its reasons
public void windowDeactivated(WindowEvent w ){ }
public void windowDeiconified(WindowEvent w ){ }
public void windowIconified( WindowEvent w){ }
public void windowOpened(WindowEvent w ){ }
public void windowActivated( WindowEvent w){ }
public void windowClosed(WindowEvent w ){ }
}


***********************************************************
House Class
**********

public class House
{
private String houseID, houseArea;
private int beds, price;

House(
String houseID, String houseArea, int beds, int price)
{
this.houseID = houseID;
this.houseArea = houseArea;
this.beds = beds;
this.price = price;
}

public String gethouseID()
{

return houseID;
}


public String gethouseArea()
{

return houseArea;
}


public int getbeds()
{

return beds;
}

public int getPrice()
{

return price;
}

public String toString()
{

return houseArea + " "+ beds + " " +price;
}
}

[Paul Rice]

String areaQuery = "SELECT area "+ "FROM HouseDetails "+ "WHERE "+" (area='" +getArea+"')";

Can I suggest you use the String.format().

String qreaQuery = String.format("SELECT %1$s FROM %2$s WHERE( area = %3$d)", area, HouseDetails, getArea);

[***SAM***]

Hi thanks for your reply, but I am getting the following errors:

F:\CollegeYear3\CBDA\Java Files\connections\HouseServer.java:29: cannot resolve symbol
symbol : variable area
location: class HouseServer
String areaQuery = String.format("SELECT %1$s FROM %2$s WHERE( area = %3$d)", area, HouseDetails, getArea);
^
F:\CollegeYear3\CBDA\Java Files\connections\HouseServer.java:29: cannot resolve symbol
symbol : variable HouseDetails
location: class HouseServer
String areaQuery = String.format("SELECT %1$s FROM %2$s WHERE( area = %3$d)", area, HouseDetails, getArea);
^
F:\CollegeYear3\CBDA\Java Files\connections\HouseServer.java:29: cannot resolve symbol
symbol : variable getArea
location: class HouseServer
String areaQuery = String.format("SELECT %1$s FROM %2$s WHERE( area = %3$d)", area, HouseDetails, getArea);

[Paul Rice]

The code I wrote I made up based on a line of code of yours:

 // ************************** THIS IS THE QUERY I AM STRUGGLING WITH *************************
String areaQuery = "SELECT area "+ "FROM HouseDetails "+ "WHERE "+" (area='" +getArea+"')";

It's just an example of how to use String.format() to create a dynamic SQL statement. You'll have to figure out the proper SELECT statement and the proper variables.

[Println]

Try :

PreparedStatement query;

...
...

case 'G':{
     getArea = bf.readLine();
     query =  cn.prepareStatement("SELECT area FROM HouseDetails WHERE"
              +  " (area like ?)");
     query.setString(1,getArea);
     break;
}

...
...

rs = query.executeQuery();

There are lots of other methods for preparedStatement
eg: setInt(int field,int value);
For each getter in ResultSet there is a setter in PreparedStatement;
PreparedStatements are also reusable as long as close() is not called on it.

HTH