January 13, 2015 at 3:59 am
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");
January 13, 2015 at 4:32 am
Maybe ask this on an MS Access forum, since this is quite obviously not SQL Server.
You also have several security flaws with this code. Passwords stored in plain text been the more obvious one. I hope this is for a course, not something that's actually going to be used in production.
Additionally, there are few less efficient ways to see if a table is empty than
rs = stmt.executeQuery("SELECT * FROM Stocks");
if(!rs.next())
System.out.println("Stocks table contains no records.");
May I suggest you chat with who ever's teaching this course and get some help from them regarding the syntax error as well as all the other flaws in the code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2015 at 5:11 am
yeah its just for study's not a production app, there is data in the stocks table ... ok so i should try somewhere else Im doing my course correspondence and they offer no assistance with assignments so this is why im trying forums
January 13, 2015 at 5:34 am
To be honest, there's so much wrong that forums aren't going to be much help. You should probably see if you can find someone in your area that's willing to mentor a new developer. See if there are any Java usergroups in your area, if there are visit and see if you can find someone willing to help.
Do some reading:
Security basics (the OWASP top 10 on pluralsight's good)
Good password management, there's also a good course on pluralsight
Good Java coding practices. Dumping everything into main is not good practice
As for their being data in Stocks, that's half of problem. You're pulling the entire contents of the table across to see if there's a single row. Imagine what would happen if there were 100 000 000 rows in that table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply