jdbc and query multiple databases using single parameter

Question

What I am trying to accomplish: I have ambulance data in multiple databases. The gps of ambulance tracks in one database, dispatch records in another and medical records in a third. They all have in common a unique key leading back to a fire/ems incident. In this case I would like use JDBC to query two databases using a parameter. My parameter is a unique ID of an emergency incident. I want to accomplish a full report of an incident from multiple database sources based on that unique ID. As you can see from my code shown below I am passing the parameter twice - once in each try catch block (preStatement.setString(1, "MLC140701045015");).

My goal is to have a gui with a drop down list. The user chooses an incident and a report is generated. The parameter is only passed once from a drop down list in a gui.

My question is: Can this parameter be passed only once? I have a liberal arts degree, not a computer science degree. You cannot be too simplistic for me. Please be explicit in your reply.

   package javaDatabase;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;

public class PreparedStatements
{

    public static void main(String[] args)
    {
        // obtain information from dispatch
        Connection conn = null;
        Statement stmt = null;
        try
        {
            conn = DriverManager
                    .getConnection

("jdbc:sqlserver://999.99.99.99;database=Incidents;integratedsecurity=false;user=myUser;password=myPassw

ord");
            PreparedStatement preStatement = conn
                    .prepareStatement("SELECT I_EventNumber, I_Address, 

I_tTimeDispatch FROM IIncident WHERE I_EventNumber = ?");
            // pass a parameter
            preStatement.setString(1, "MLC140701045015");

            ResultSet rset = preStatement.executeQuery();
            // output the results of the dispatch
            while (rset.next())
            {
                String IncidentID = rset.getString("I_EventNumber");
                String Address = rset.getString("I_Address");
                String dtmIncident = rset.getString("I_tTimeDispatch");

                System.out.println(IncidentID + ", " + Address + ", "
                        + dtmIncident);
            }
            // clean up the program and close the connections
        } catch (SQLException ex)
        {
            ex.printStackTrace();
        } finally
        {
            try
            {
                if (stmt != null)
                    stmt.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException ex)
            {
                ex.printStackTrace();
            }
        }

        // obtain information from gps database.
        Connection avlconn = null;
        Statement avlstmt = null;
        try
        {
            avlconn = DriverManager
                    .getConnection

("jdbc:sqlserver://777.77.77.77;database=gps_tracks;integratedsecurity=false;user=myUser;password=myPass

");
            PreparedStatement preStatement = avlconn
                    .prepareStatement("SELECT RTRIM(AssignedIncident) AS [Mission], 

RTRIM(CallSign) AS [Ambulance], RTRIM(UnitStatus) AS [Disposition], DateTimeStamp, Longitude, Latitude 

FROM HISTORY_201407 WHERE AssignedIncident = ?");
            // pass the same parameter previously. Why must this be done twice?
            preStatement.setString(1, "MLC140701045015");
            // return a result of the gps tracks
            ResultSet rset = preStatement.executeQuery();

            while (rset.next())
            {
                String mission = rset.getString("Mission");
                String truck = rset.getString("Ambulance");
                String disposition = rset.getString("Disposition");
                Date dateTime = rset.getDate("DateTimeStamp");
                Double longitude = rset.getDouble("Longitude");
                Double latitude = rset.getDouble("Latitude");
                System.out.println(mission + ", " + truck + ", " + disposition
                        + ", " + dateTime + ", " + longitude + ", " + latitude);
            }
            // clean up the program and close out the connections
        } catch (SQLException ex)
        {
            ex.printStackTrace();
        } finally
        {
            try
            {
                if (avlstmt != null)
                    avlstmt.close();
                if (avlconn != null)
                    avlconn.close();
            } catch (SQLException ex)
            {
                ex.printStackTrace();
            }
        }
    }
}

jdbc and query multiple databases using single parameter

Answers (1)

Related Questions