confusion over using values in Java within SQL statements

Question

I need to retrieve a list of vehicle registrations from an SQL database and put them into a dropdown list in the java GUI. Once a Vehicle Registration is selected (as part of a login sequence) from the list I want to use this in the "where" statement of subsequent SQL queries made to the database to get things like inventory statuses etc that are only for that vehicle.

I have done the first part i.e. retrieved the list from the database and displayed the veh regs in the dropdown for the user to select, I have managed to display this in an optional panel as well as display it in another class in a Jlabel box but I cannot seem to figure out how to use this selected veh reg in a seperate query class i.e as part of a select/where statement?

I have been trying to find a solution to this but am getting confused on whether to use an array list and put the registration number into this and then how to make this available to other classes so I can retrieve the value to use in the SQL statement. I am quite lost now so any advice in the right direction will be very helpful.

I am very new to Java and programming altogether so if you feel the need to make sarcastic (you are such a noob) type comments then don't bother posting!

This is the code that grabs the registrations from the database and adds them to the combo box in one GUI:

private void populateRegistration() {
    try {
        // create a connection to database
        pst = conn.prepareStatement("SELECT VehicleRegistrationNumber FROM vehicle;");
        // create a query to get vehicle regs
        rs = pst.executeQuery();
        // add vehicle regs to combobox
        while (rs.next()) {
            jComboBox1.addItem(rs.getString("VehicleRegistrationNumber"));
        }
    } catch (SQLException ex) {
        Logger.getLogger(Login_GUI.class.getName()).log(Level.SEVERE, null, ex);
    }

This is the code In another GUI Class that shows this vehicle registration in a dialogue box and then shows it in a text field in the GUI:

    public ArrayList<String> getTableContent()
    {
    DatabaseConnection db = new DatabaseConnection();


    try {

         //Sql Return Statement
        String newQuery= "SELECT P.PatientFirstName, P.PatientLastName, P.PatientHouseNumber, P.PatientStreetName, P.PatientPostcode, P.PatientBreathing, P.ProblemInformation, C.AMPDSCategory, \n" +
        "I.NumberHurt,T.TaskClosed, H.HospitalSpaceAvailable, H.HospitalName, H.HospitalPostcode, V.VehicleRegistrationNumber, EM.DateTimeReported\n" +
        "FROM Patient AS P\n" +
        "JOIN Category AS C\n" +
        "ON C.Category_ID = P.CategoryID\n" +
        "--AND P.Patient_ID = 2\n" +
        "JOIN Incident AS I\n" +
        "ON I.Incident_ID = P.IncidentID\n" +
        "JOIN TASK AS T\n" +
        "ON T.IncidentID = I.Incident_ID\n" +
        "JOIN Hospital AS H\n" +
        "ON H.Hospital_ID = T.HospitalID\n" +
        "JOIN Vehicle AS V\n" +
        "ON T.Task_ID = V.TaskID\n" +
        "JOIN ECCPersonnel AS EC\n" +
        "ON I.ECCPersonnelID = EC.ECCPersonnel_ID\n" +
        "JOIN EmergencyCall AS EM\n" +
        "ON EC.CallID = EM.Call_ID\n" +
        "WHERE T.Task_ID=1" +
        "--WHERE V.VehicleRegistrationNumber = '?'";

I know the SQL is ugly but it works so I will refine it later if I get time. I believe I need to define the ? but have no idea how to reference the ? to the vehReg value!!

Please post the code that is confusing you or that you are unable to solve and regarding that noob thing don't worry teachers scold us that doesnt mean they want to hurt us they just want us to be perfect — SparkOn
– SparkOn, Commented Jun 22, 2014 at 16:46
I have uploaded code for two classes, the first code from a login_GUI and the second group of code is from a second GUI. I just want to be able to use the value of vehReg in SQL queries in other classes such as inventory, status and task classes if possible hence why I have added it to an Arraylist. — Phil
– Phil, Commented Jun 22, 2014 at 16:55
I have looked at other similar issues on here including this one (stackoverflow.com/questions/9607076/…) but I seem be missing something as I cannot get it to work! — Phil
– Phil, Commented Jun 22, 2014 at 16:57
just make a static arraylist initialize it with the values you want and call it in another class with the class name — SparkOn
– SparkOn, Commented Jun 22, 2014 at 17:00

Serge Ballesta · Accepted Answer · 2014-06-22 17:16:50Z

0

First you should allways close what you have opened. So in populateRegistration() do not forget rs.close() and pst.close().

Next to reference the ? to the vehReg, you create a PreparedStatement and call its setString method (supposing vehReg is a String).

pst = conn.prepareStatement(newQuery);
pst.setString(1, vehReg);
rs = pst.executeQuery();

(try, catch, declarations, and actual read ommitted for brevity)

Remember that columns are numbered starting by 1.

answered Jun 22, 2014 at 17:16

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phil Over a year ago

Great, thank you very much. The closing statements were on my to do list so thank you for those as well as that will save me some time.

Collectives™ on Stack Overflow

confusion over using values in Java within SQL statements

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related