PDA

View Full Version : Need Help Pulling Data from Oracle Table (JDBC) (Newby)



kdelucia
04-11-2008, 06:24 PM
Let me start of by saying I'm very new to Java so the code may be messy/poorly coded. However, I've come a long way already and it's almost working. What I'm trying to do is prompt the user to enter a type, then a key. I use that type to determine the SQL statement to use then I use the key as part of the SQL statement.

If I enter 'custnum' as the type, then a string as my key, it finds the record fine.
If I any of my other valid types (lname, address or phone) and then enter my key, the result set is empty. I've verified that there are matching records for the type/key I'm entering.

I've tried stepping it through the debugger and looking at various variables along the way both with the working scenario and the not-working scenario, but I can't find anything. I'm sure it's a simple coding mistake, but I don't really know how to track it down.

Any ideas on what's going wrong would be greatly appreciated. Thanks!

//Start code
import java.sql.*;
import java.util.Scanner;

public class AccessCustomerTable
{
private static final String DEFAULT_DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String DEFAULT_URL = "jdbc:oracle:thin:@hostname.com:1521:orap1";
private static final String DEFAULT_USERNAME = "username";
private static final String DEFAULT_PASSWORD = "password";

//Set the SQL statements
private static final String SQLCUSTOMER_CUSTNUM =
"select CUSTNUM,CUST_LNAME,CUST_FNAME,CUST_PHONE,EMAIL,STAT_LVL,HH_NUM " +
"from EME.CUSTOMER " +
"where CUSTNUM=?";
private static final String SQLCUSTOMER_CUST_LNAME =
"select CUSTNUM,CUST_LNAME,CUST_FNAME,CUST_PHONE,EMAIL,STAT_LVL,HH_NUM " +
"from EME.CUSTOMER " +
"where CUST_LNAME=?";
private static final String SQLCUSTOMER_CUST_PHONE =
"select CUSTNUM,CUST_LNAME,CUST_FNAME,CUST_PHONE,EMAIL,STAT_LVL,HH_NUM " +
"from EME.CUSTOMER " +
"where CUST_PHONE=?";
private static final String SQLCUSTOMER_EMAIL =
"select CUSTNUM,CUST_LNAME,CUST_FNAME,CUST_PHONE,EMAIL,STAT_LVL,HH_NUM " +
"from EME.CUSTOMER " +
"where EMAIL=?";
private static final String SQLDETAIL =
"select TRANS_DATE,STORENUM,TERMINAL,TRANSID,OPERATOR,SALES_TOT," +
"AMOUNT_SPENT,AUTO_CPNS,PTS_EARNED,PTS_REDEEMED,RDMT_CPNS,ADD_CPNS," +
"USED_CPNS,OFFLINE_ALT_ID,RECON_IND " +
"from EME.CUSTOMERDETAIL " +
"where CUSTNUM=?";
private static final String SQLHOUSEHOLD =
"select PGM_PTS,PERIOD_PTS,PGM_RDM,PERIOD_RDM,SALES_TOT,AUTO_CPNS," +
"CARD_UCNT,MESSAGE,DISC_GRP,PT_MULTP,USERFEAT1,USERFEAT2,USERFEAT3,TARG_CPNS " +
"from EME.HOUSEHOLD " +
"where HH_NUM=?";

public static void main(String[] args)
{
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rset = null;

//declare all variables
String custNum = "";
String custLNm = "";

String transDt = "";
String storeNum = "";

String pgmPtsEarned = "";
String periodPtsEarned = "";

try {
//********** Get Customer Data **********
householdNum = "";
Class.forName(DEFAULT_DRIVER);
conn = DriverManager.getConnection(DEFAULT_URL,DEFAULT_USERNAME,DEFAULT_PASSWORD);
String lookupType = getUserInput("Lookup Type");

if (lookupType.toUpperCase().equals("CUSTNUM"))
{
stmt=conn.prepareStatement(SQLCUSTOMER_CUSTNUM);
}
else if (lookupType.toUpperCase().equals("LNAME"))
{
stmt=conn.prepareStatement(SQLCUSTOMER_CUST_LNAME);
}
else if (lookupType.toUpperCase().equals("PHONE"))
{
stmt=conn.prepareStatement(SQLCUSTOMER_CUST_PHONE);
}
else if (lookupType.toUpperCase().equals("ADDRESS"))
{
stmt=conn.prepareStatement(SQLCUSTOMER_EMAIL);
}
else
{
//stop execution with message
System.out.println("Invalid lookup type entered. Process stopping");
System.exit(0);
}
String lookupKey = getUserInput("Lookup Key");
System.out.println("Lookup Key is " + lookupKey);
stmt.setString(1,lookupKey);
rset = stmt.executeQuery();
//what if rset returns no records?
boolean empty=true;
while (rset.next())
{
empty=false;
custNum = rset.getString(1);
custLNm = rset.getString(2);
//. . .
}
if (empty)
{
System.out.println("No records found for " + lookupKey);
System.exit(0);
}

//********** Get Detail Data **********
stmt=conn.prepareStatement(SQLDETAIL);
//stmt.setString(1,customerId);
stmt.setString(1,custNum);
rset = stmt.executeQuery();
empty = true;
while (rset.next())
{
empty = false;
transDt = rset.getString(1);
storeNum = rset.getString(2);
// . . .
}
if (empty)
{
System.out.println("No detail records found for customer # " + custNum);
System.exit(0);
}
//********** Get Household Data **********
stmt=conn.prepareStatement(SQLHOUSEHOLD);
stmt.setString(1,householdNum);
rset = stmt.executeQuery();
empty = true;
while (rset.next())
{
empty = false;
pgmPtsEarned = rset.getString(1);
periodPtsEarned = rset.getString(2);
//. . .
}
if (empty)
{
System.out.println("No household records found for " + householdNum);
System.exit(0);
}
}
catch(Exception x)
{
System.out.println(x.getMessage());
x.printStackTrace();
}
finally
{
close(rset);
close(stmt);
close(conn);
}
}

private static void close(Connection conn)
{
try
{
if (conn != null)
{
conn.close();
}
}
catch (SQLException e)
{
e.printStackTrace();
}
}
private static void close(Statement stmt)
{
try
{
if (stmt != null)
{
stmt.close();
}
}
catch (SQLException e)
{
e.printStackTrace();
}
}
private static void close(ResultSet rset)
{
try
{
if (rset != null)
{
rset.close();

}
}
catch (SQLException e)
{
e.printStackTrace();
}
}
public static String getUserInput(String getVal)
{
Scanner sc = new Scanner(System.in);
//for now, I'll be expecting "CustNum", "LName", "Phone", "Address"
System.out.println("Enter " + getVal + ": ");
String returnStr = sc.nextLine();
return (String) returnStr;
}
}