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