[Paci]

Hey Guys I am working on an assignment on which I must do the Following:

Write a java program to query the StockTracker database for all users and all stocks
held by each user. List the user's ID, first name, and last name followed by their stocks,
including both the stock symbol and stock description (name). This must be a console
application that produces output to the screen.

The following demonstrates my table layout(Top label is actual name of table, bottom labels are actual column names)

//////////
//Stocks//
////////////////
//symbol//name//
////////////////

/////////
//Users/////////////////////////////////////
//userID//lastName//firstName//pswd//admin//
////////////////////////////////////////////

//////////////
//UserStocks//
//////////////////
//userID//symbol//
//////////////////

The code i have attempted to write compiles, but there is an error when I run the actual program. This is the error I recieve: Exception in thread "main" java.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Here is the code to the program I have created:

public class UserQuery
{

	public static void main(String[] args) throws Exception
	{
		String userID;
		String firstName;
		String lastName;
		String symbol;

		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String url = "jdbc:odbc:StockTracker";
		Connection con = DriverManager.getConnection(url);
		Statement stmt = con.createStatement();

		//Headings etc
		System.out.println("Stock holdings by user\n");
		System.out.println("User ID User Name\n        Stock - Description");
		System.out.println("-----------------------------------");

		//Retrieve data
		ResultSet rs = stmt.executeQuery("SELECT s.* FROM Users u, UserStocks us, Stocks s WHERE u.userID=us.userID AND us.stockID=s.name;");


		while(rs.next())
		{
			userID = rs.getString("us.userID");
			firstName = rs.getString("u.firstName");
			lastName = rs.getString("u.lastName");
			symbol = rs.getString("us.symbol");

			System.out.println(userID +"\t"+ firstName +"\t"+ lastName +"\t"+ symbol);

		}
	}

}

I know my error is somewhere with in line 22 so any advice would be greatly appreciated.
Please be patient with me as I like to ask questions so I can learn as much as possible, I have only started with SQL
in my course so my attempts may look silly.

[baavgai]

You only asked for s.* as a result. Also, the fields returned will NOT have "s." in front of them. Try:

SELECT us.userID, u.firstName, u.lastName, us.symbol
	FROM Users u, UserStocks us, Stocks s 
	WHERE u.userID=us.userID 
		AND us.stockID=s.name

In your code:

userID = rs.getString("userID");
firstName = rs.getString("firstName");
lastName = rs.getString("lastName");
symbol = rs.getString("symbol");

Also, try to use proper join syntax:

SELECT us.userID, u.firstName, u.lastName, us.symbol
	FROM Users u
		INNER JOIN UserStocks us 
			ON u.userID=us.userID 
		INNER JOIN Stocks s
			ON us.stockID=s.name

[Paci]

Thanks for the reply, I have revised my code under your guidance and this is what I have come up with:

I changed: us.stockID to: us. us.userID as it does not exist.

As before, it all compiles but now I have a new error when I run the program:
Syntax error (missing operator) in query expression between the following lines:

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

I have a feeling that the error is probably staring me in the face
and this is the rest of the code I have edited:

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

public class UserQuery
{

	public static void main(String[] args) throws Exception
	{
		String userID;
		String firstName;
		String lastName;
		String symbol;

		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String url = "jdbc:odbc:StockTracker";
		Connection con = DriverManager.getConnection(url);
		Statement stmt = con.createStatement();

		//Headings etc
		System.out.println("Stock holdings by user\n");
		System.out.println("User ID User Name\n        Stock - Description");
		System.out.println("-----------------------------------");

		//Retrieve data
		ResultSet rs = stmt.executeQuery("SELECT us.userID, u.firstName, u.lastName, us.symbol"
										+"FROM Users u"
										+"INNER JOIN UserStocks us "
										+"ON u.userID=us.userID "
										+"INNER JOIN Stocks s"
										+"ON us.userID=s.name");


		while(rs.next())
		{
			userID = rs.getString("userID");
			firstName = rs.getString("firstName");
			lastName = rs.getString("lastName");
			symbol = rs.getString("symbol");

			System.out.println(userID +"\t"+ firstName +"\t"+ lastName +"\t"+ symbol);
		}
	}

}

(Also want to thank Alpha02 for his tutorial on using MYSQL with java http://www.dreaminco...sql-with-java/)

[baavgai]

You ususally get burnt by this in code based string concats.

+"FROM Users u"
+"INNER JOIN UserStocks us "

Put another way:

+"FROM Users uINNER JOIN UserStocks us "

I always use + " ... as a standard. An extra space wont kill you, but lack of it can.

[Paci]

Thank you that spacing tip was really helpful, that's going straight on my SQL notes page .
This is what I have been left with:
Syntax error (missing operator) in query expression 'u.userID = us.userID INNER JOIN Stocks s ON us.userID = s.name'.

Newly edited code as to your spacing advice:
Any more tips would be highly appreciated.

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

I initially thought it might have been a missing semicolon missing at the end of the SQL code as pbl pointed out in a similar post but it did not make any difference in my specific case.

Is there any chance that It has to do with my table setup? UserStocks is made up from the Stocks and Users tables and I have no problem displaying data from just the Users and Stocks tables. The problem could also be that I am using the wrong columns, as each userID can have one or more Stock names.

I know these questions may seem fairly basic but to put things in perspective I have tried to contact my so called free online tutor that comes with my course, but I am still waiting for replies from previous chapters and I have always had to either skip or figure out every question by myself.

[Paci]

Alright I have been playing around with my code and tried a different approach, all compiles and runs with no errors. Problem is line 38 does not display output?

(I can send the code to the rest of the program if needed)

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


public class UserQuery
{
	public static void main(String[] args)throws Exception
	{
		String userID;
		String firstName;
		String lastName;
		String symbol;
		String name;

		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String url = "jdbc:odbc:StockTracker";
		Connection con = DriverManager.getConnection(url);
		Statement stmt = con.createStatement();

		//Headings etc
		System.out.println("Stock holdings by user\n");
		System.out.println("User ID User Name\n        Stock - Description");
		System.out.println("-----------------------------------");

		//Retrieve data
		ResultSet rs1 = stmt.executeQuery("SELECT *FROM Users u,UserStocks us,Stocks s "
										  +"WHERE us.userID = u.userID AND s.symbol = us.symbol ");

		 while(rs1.next())
		{
			userID = rs1.getString("userID");
			firstName = rs1.getString("firstName");
			lastName = rs1.getString("lastName");
			symbol = rs1.getString("symbol");
			name = rs1.getString("name");

			System.out.println(userID +"\t"+ firstName +"\t"+ lastName +"\t"+ symbol+ "\t"+ name);
		}

	  }
}

[Paci]

Can anyone help?

[e_i_pi]

I'm no Java expert, but I know my SQL (no pun intended). Line 27 of your code above reads:

ResultSet rs1 = stmt.executeQuery("SELECT *FROM Users u,UserStocks us,Stocks s "

So, there's no space between the * and the FROM keyword.

Also, SELECTing * is a bad practice, and this query is a prime example of why. Since you are SELECTing all columns, and the column names resolve to the column name only (not the table name and column name) then your result set will have these attributes:

// From users table
userID
lastName
firstName
pswd
admin
// From user stocks table
userID
symbol
// From stocks table
symbol
name

I'm not sure how Java handles these cases, but if it's anything like the languages I program in, it would chuck a fit at there being multiple attributes with the same name. I'd suggest changing line 27 to this and seeing what happens:

ResultSet rs1 = stmt.executeQuery("SELECT u.userID, u.firstName, u.lastName, s.symbol, s.name FROM Users u,UserStocks us,Stocks s "

EDIT: It's also more efficient in the DB to SELECT only the attributes that you need.

This post has been edited by e_i_pi: 22 May 2012 - 12:14 AM

[Paci]

Awesome advice but I managed to figure out most of it.
So glad I am almost there.
Only problem is the format is wrong, it displays as the example in picture attachment.

If anyone could point me in the right direction it would be appreciated
Here is the product of my many attempts:

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


public class UserQuery
{
	public static void main(String[] args)throws Exception
	{
		String userID;
		String firstName;
		String lastName;
		String symbol;
		String name;

		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String url = "jdbc:odbc:StockTracker";
		Connection con = DriverManager.getConnection(url);
		Statement stmt = con.createStatement();

		//Headings etc
		System.out.println("Stock holdings by user\n");
		System.out.println("User ID  User Name\n         Stock - Description");
		System.out.println("-------- ---------------------------");

		//Retrieve data
		ResultSet rs = stmt.executeQuery("SELECT * FROM Users u,UserStocks us,Stocks s WHERE us.userID = u.userID AND s.symbol = us.symbol ");

		while(rs.next())
		{
			userID = 		 rs.getString("userID"); 		//works
			firstName =		 rs.getString("firstName"); 	//works
			lastName = 		 rs.getString("lastName");		//works
			symbol = 		 rs.getString("symbol");		//works now
			name = 			 rs.getString("name");			//works now

			System.out.println(userID +"\t"+ firstName +"\t"+ lastName +"\t");
			System.out.println("\t"+symbol+"\t"+ name);
			System.out.println("\n");
		}
		System.out.println("Report done.");
	  }
}

Attached image(s)

• 

[baavgai]

You just check to see if you've already done that one... ah, hell:

String lastUserId = null;
while(rs.next()) {
	String userID = rs.getString("userID");
	if (userID.equals(lastUserId)) {
//...

[Paci]

Hey I tried your method this way but still no luck, just brings up all the users names , I need some way to turn the lastUserID into the previous userID but if I try something like lastUserID= UserID then it says userID has not been initialized. And I cannot initialize: lastUserID= userID inside the result set as then it only displays one of the users set of data with only one 'symbol' and 'name' when I need all of the users symbols and names underneath their 'userID's.

Having a brain overload here.

		String lastUserId = null;
		while(rs.next())
		{
			userID = 		 rs.getString("userID");
			firstName =		 rs.getString("firstName");
			lastName = 		 rs.getString("lastName");
			System.out.println(userID +"\t"+ firstName +"\t"+ lastName +"\t");

			if (userID.equals(lastUserId))
			{
				symbol = 		 rs.getString("symbol");
				name = 			 rs.getString("name");
				System.out.println("\t"+symbol+"\t"+ name+"\t"+symbol+"\t");
				break;
			}

[baavgai]

You know, I thought I already gave the answer... You are so profoundly missing the point. What the hell is a break statement doing in there?

Fine:

String lastUserId = null;
while(rs.next()) {
	String userID = rs.getString("userID");
	if (userID.equals(lastUserId)) {
		System.out.print("\t\t\t");
	} else {
		System.out.print(userID + "\t" + rs.getString("firstName") + "\t" + rs.getString("lastName") +"\t");
		lastUserId = userID;
	}
	System.out.println("\t" + rs.getString("symbol") + "\t" + rs.getString("name"));
}
System.out.println("Report done.");

[Paci]

Thank you, sorry for making you agitated, as I said I am trying my best.
All I realized I had to do was remove my 'String userID;' in my main method and your
previous solution would have been perfect.

Hope you have an awesome day, you helped big time.

Working code:

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


public class UserQuery
{
	public static void main(String[] args)throws Exception
	{
		//String userID;
		String firstName;
		String lastName;
		String symbol;
		String name;

		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String url = "jdbc:odbc:StockTracker";
		Connection con = DriverManager.getConnection(url);
		Statement stmt = con.createStatement();

		//Headings etc
		System.out.println("Stock holdings by user\n");
		System.out.println("User ID  User Name\n         Stock - Description");
		System.out.println("-------- ---------------------------");

		//Retrieve data
		ResultSet rs = stmt.executeQuery("SELECT * FROM Users u,UserStocks us,Stocks s WHERE us.userID = u.userID AND s.symbol = us.symbol ");

		String lastUserId = null;
		while(rs.next())
		{
			String userID = rs.getString("userID");
			if (userID.equals(lastUserId))
			{
				System.out.print("\t\t\t");
			} else

			{
				System.out.print(userID + "\t" + rs.getString("firstName") + "\t" + rs.getString("lastName") +"\t");
				lastUserId = userID;
			}
			System.out.println("\t" + rs.getString("symbol") + "\t" + rs.getString("name"));
		}
		System.out.println("Report done.");
	  }
}