Console SQL Query App Not Working Correctly

[ReignMan]

Hi Guys, my console SQL Query app is not giving me the desired output. I am sure its might have something to do with my SQL Query. Its missing the description (name field in stocks table ) of the stock items and it prints a new line for each user as per number of stocks an repeats the user details and then prints the stock listed by that user(only want the user listed once with all its stocks). I will add my files and current output and desired out put below.



Thanks in advanced for all and any assistance!



This code Makes my DB (I populate it with data)
MakeDB.java:

import java.sql.*; 
import java.io.*; 

public class MakeDB 
{ 
public static void main(String[]args) throws Exception 
{ 
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
String url="jdbc:odbc:StockTracker"; 

Connection con = DriverManager.getConnection(url); 
Statement stmt = con.createStatement(); 
//if index exsists will be deleted 
//if not exdsists ddisplay mesage continue execution! 
System.out.println("Dropping indexes & Tables"); 
try{ 
stmt.executeUpdate( 
"DROP INDEX PK_UserStocks on UserStocks"); 
} 
catch(Exception e) 
{ 
System.out.println("Could not drop primary key on UserStocks table:" 
} 
try 
{ 
stmt.executeUpdate( 
"DROP TABLE UserStocks"); 
} 
catch(Exception e) 
{ 
System.out.println("Could not drop UserStocks table: " 
+ e.getMessage()); 
} 
try 
{ 
stmt.executeUpdate("DROP TABLE Users"); 
} 
catch(Exception e) 
{ 
System.out.println("Could not drop Users table: " 
+ e.getMessage()); 
} 
try 
{ 
stmt.executeUpdate("DROP TABLE Stocks"); 
} 
catch(Exception e) 
{ 
System.out.println("Could not drop Stocks table: " 
+ e.getMessage()); 

} 


//////////Create the data base tables////////// 
System.out.println("Creating tables..............."); 

///Create Stock table with primary key index 
try 
{ 
System.out.println("Creating Stocks table with primary key index..."); 
stmt.executeUpdate( 
"CREATE TABLE Stocks ( symbol TEXT(8)NOT NULL CONSTRAINT PK_Stocks" 
+ " PRIMARY KEY, name TEXT(50) )"); 
} 
catch(Exception e) 
{ 
System.out.println("Exception creating the Stocks Table: " 
+ e.getMessage()); 
} 
///create Users table with primary index key 
try 
{ 
System.out.println("Creating Users table with primary key index..."); 
stmt.executeUpdate( 
"CREATE TABLE Users (userID TEXT(20)NOT NULL CONSTRAINT" 
+ " PK_Users PRIMARY KEY, lastName TEXT(30)NOT NULL,firstName" 
+ " TEXT(30)NOT NULL,pswd LONGBINARY,admin BIT )"); 
} 
catch(Exception e) 
{ 
System.out.println("Exception creating the Users Table: " 
+ e.getMessage()); 
} 
///create table with foreign keys to users and stocks table 
try 
{ 
System.out.println("Creating UserStocks table with primary key index..."); 
stmt.executeUpdate("CREATE TABLE UserStocks (userID TEXT(20) CONSTRAINT" 
+ " FK1_UserStocks REFERENCES Users (userID),symbol TEXT(8)," 
+ " CONSTRAINT FK2_UserStocks FOREIGN KEY (symbol) REFERENCES Stocks(symbol))"); 

} 
catch(Exception e) 
{ 
System.out.println("Exception creating the UserStocks Table: " 
+ e.getMessage()); 
} 
///create userStocks table primary key 
try 
{ 
System.out.println("Creating UserStocks table primary key index..."); 
stmt.executeUpdate("CREATE UNIQUE INDEX PK_UserStocks" 
+ " ON UserStocks(userID,Symbol) WITH PRIMARY DISALLOW NULL"); 
} 
catch(Exception e) 
{ 
System.out.println("Exception creating the UserStocks index: " 
+ e.getMessage()); 
} 
// Create 1 administrative user with password as initial data 

String userID= "admin01"; 
String firstName="Default"; 
String lastName = "Admin"; 
String initialPswd="admin01"; 
Password pswd = new Password(initialPswd); 
Boolean admin = true; 

PreparedStatement pStmt = 
con.prepareStatement( 
"INSERT INTO Users VALUES (?,?,?,?,?)"); 

try 
{ 
pStmt.setString(1, userID); 
pStmt.setString(2, lastName); 
pStmt.setString(3, firstName); 
pStmt.setBytes(4, serializeObj(pswd)); 
pStmt.setBoolean(5, admin); 
pStmt.executeUpdate(); 
} 
catch(Exception e) 
{ 
System.out.println("Exception inserting user test: " 
+e.getMessage()); 
} 
pStmt.close(); 

//readand display all user datain the data base. 
ResultSet rs = stmt.executeQuery("SELECT * FROM Users"); 

System.out.println("Datebase Created."); 
System.out.println("Displaying Data form Database...."); 
System.out.println("User table Contains : "); 

Password pswdFromDB; 
byte[] buf=null; 
while (rs.next()) 
{ 
System.out.println("Logon ID = "+ rs.getString("userId")); 
System.out.println("First name = "+ rs.getString("firstName")); 
System.out.println("Last name = "+ rs.getString("lastName")); 
System.out.println("Administrative = "+ rs.getBoolean("admin")); 
System.out.println("Inital password = "+ initialPswd); 


///SQL NULL data value is not handled correctly 
buf = rs.getBytes("pswd"); 
if (buf!=null) 
{ 
System.out.println("Password Object = " 
+ (pswdFromDB=(Password)deserializeObj(buf))); 
System.out.println ("autoExpires = " 
+ pswdFromDB.getAutoExpires()); 
System.out.println("Expireing now = " 
+ pswdFromDB.isExpiring()); 
System.out.println("Remaing uses = " 
+ pswdFromDB.getRemainingUses()+ ""); 


} 
else 
System.out.println("Password Object = NULL!"); 

} 
rs = stmt.executeQuery("SELECT * FROM Stocks"); 
if(!rs.next()) 
System.out.println("Stocks table contains no records."); 
else 
System.out.println("Stocks still contains records!"); 

rs = stmt.executeQuery("SELECT * FROM UserStocks"); 
if(!rs.next()) 
System.out.println("UserStocks table contains no records."); 
else 
System.out.println("UserStocks still contains records!"); 

stmt.close();//closeing statement also close ResultSet 


}//end of main 

//Method to write object to byte array and then insert into preoared statment 
public static byte[] serializeObj(Object obj) throws IOException 
{ 
ByteArrayOutputStream baOStream = new ByteArrayOutputStream(); 
ObjectOutputStream objOStream = new ObjectOutputStream(baOStream); 

objOStream.writeObject(obj);//obect must be serializeable 
objOStream.flush(); 
objOStream.close(); 
return baOStream.toByteArray();//Returns stream as string 
} 

public static Object deserializeObj(byte[]buf)throws IOException, ClassNotFoundException 
{ 
Object obj = null; 
if (buf!=null) 
{ 
ObjectInputStream objIStream= 
new ObjectInputStream(new ByteArrayInputStream(buf)); 

obj = objIStream.readObject();//throws IOException, ClassNotFoundException 
} 
return obj; 
} 
}//end of class

This is the section that Queries my DB my issue lies in here:

QueryDatabase.java:

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

public class DatabaseQuery 
{ 

public static void main(String[] args)throws Exception //main method 
{ 
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //load jdbc driver into JVM 
String url = "jdbc:odbc:StockTracker"; ///url of db 
Connection con = DriverManager.getConnection(url); 
Statement stmt = con.createStatement(); 

System.out.println("Stock holdings by User"); 
System.out.println("User ID User Name"); 
System.out.println(" Stock - Description"); 
System.out.println("-------------------------------------------"); 

ResultSet rs = stmt.executeQuery("SELECT Users.userID," 
+ " Users.firstName, Users.lastName, UserStocks.symbol," 
+ " UserStocks.symbol FROM Users LEFT JOIN UserStocks" 
+ " ON UserStocks.userID = Users.userID"); 

String prevId = null; 

while(rs.next()) 
{ 
String userId = rs.getString("userID"); 
if(prevId == null || !userId.equals(prevId)) 
{ 
System.out.println(userId + "\t" 
+ rs.getString("firstName")+"\t"+rs.getString("lastName")); 
System.out.println("\t" 
+rs.getString("Symbol"));//+"\t"+rs.getString("Symbol")); 
}//End if 

}//end while 
}//end main 
}//end class 

Output:
Stock holdings by User

User ID User Name
Stock - Description
-------------------------------------------
admin01 Default Admin
DELL
admin01 Default Admin
MSFT
admin01 Default Admin
ORCL
user01 Bill Buyout
DELL
user01 Bill Buyout
MSFT
user02 Fran Futures
MSFT
user02 Fran Futures
ORCL

Press any key to continue . . .

I would like the output as follows :

Stock holdings by User

User ID User Name
Stock - Description
-------------------------------------------
admin01 Default Admin
DELL Dell Computer Corporation
MSFT Microsoft Corporation
ORCL Oracle Corporation

user01 Bill Buyout
DELL Dell Computer Corporation
MSFT Microsoft Corporation

user02 Fran Futures
MSFT Microsoft Corporation
ORCL Oracle Corporation

Press any key to continue . . .

Any help would be awesome ..




This is my latest Attempt and issue (update to my Query):

after many more jours of googling this is what i have , but im getting a SQL syntax error at runtime :

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in que
ry expression 'UserStocks.userID = Users.userID LEFT JOIN Stocks ON UserStocks.symbol = Stocks.symbol'.

ResultSet rs = stmt.executeQuery("SELECT Users.userID," 
+ " Users.firstName, Users.lastName, UserStocks.symbol,Stocks.name" 
+ " FROM Users LEFT JOIN UserStocks " 
+ " ON UserStocks.userID = Users.userID LEFT JOIN Stocks" 
+ " ON UserStocks.symbol = Stocks.symbol"); 

Thanks for all and any assistance and help in advanced!!

[June7]

The semi-colon goes inside the quote marks.

+ " ON UserStocks.symbol = Stocks.symbol;")

[ReignMan]

The semi-colon goes inside the quote marks.

+ " ON UserStocks.symbol = Stocks.symbol;")

Thanks I just did as you suggested no improvement still same error:

Stock holdings by User


User ID User Name
Stock - Description
-------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in que
ry expression 'UserStocks.userID = Users.userID LEFT JOIN Stocks ON UserStocks.s
ymbol = Stocks.symbol'.
Press any key to continue . . .

[June7]

Use Access query builder to get the correct SQL syntax. A pair of parens might be necessary around one of the JOIN expressions.

[ReignMan]

Yoh like a BOSS !!!

ResultSet rs = stmt.executeQuery("SELECT Users.userID,"
							+ " Users.firstName, Users.lastName, UserStocks.symbol,Stocks.name"
							+ " FROM (Users "
							+ " LEFT JOIN UserStocks ON UserStocks.userID = Users.userID)"
							+ " LEFT JOIN Stocks ON UserStocks.symbol = Stocks.symbol;");

Nice one worked like a charm !!!
Thanks a TON!!

But this is the result....

Stock holdings by User


User ID User Name
Stock - Description
-------------------------------------------
admin01 Default Admin
DELL Dell Computer Corp
admin01 Default Admin
MSFT Microsoft Computer Corp
admin01 Default Admin
ORCL Oracle Corp
user01 Bill Buyout
DELL Dell Computer Corp
user01 Bill Buyout
MSFT Microsoft Computer Corp
user02 Fran Futures
MSFT Microsoft Computer Corp
user02 Fran Futures
ORCL Oracle Corp
Press any key to continue . . .

How do I achieve my desired result posted above?

Thanks again!!

[June7]

That's not an Access question. That is Java programming (something I just started learning). But I will guess need another:

System.out.println()

[ReignMan]

ok so its not the Query then its not the need of another System.out.println(); im gona have to use a for loop or foreach loop then thanks for the help!

[ReignMan]

it was actually my logic i fixed the if else at the end