MVC Data access class

[Rain Draken]

Hi everyone I am still learning and this is the first time I am working with MVC and data access objects. I have been struggling with this question for a week or so now. I get the exact output that I need to get but the question says 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 can be a console application that produces output to the screen, as shown in Figure 11-80. Use the project, StockTracker, to add additional users and stocks, if necessary. For an extra challenge, use only the StockTrackerDB data access class to obtain data from the database.) Ok so my question is this my program draws its information via SQL statements directly from the StockTracker Microsoft database but it doesn't feel firth as that last part of the question states for an extra challenge, use only the StockTrackerDB data access class to obtain data from the database, but every time I try to use the StockTrackerDB  class I don't seem to get it to link to the database. I haven't included any lines of code yet as I don't know the posting rules for code, I don't want to bombard anyone with hundreds of lines of code unless it is allowed to do so here. Thank you I don't expect to get the answers handed to me I just simply want to understand what I need to do to get data from the database via this StockTrackerDB program A point in the right direction so to speak. Please let me know if I must upload my code for you to help me with this and how much I must upload thank you

[Campbell Ritchie]

Welcome to the Ranch

You shoul‍d post as much code as will show the problem. Also please search this forum; there is something familiar about part of your question and you might find that somebody else has had the same problem. By stock, do you mean investments?
I am afraid I am finding it a bit hard to identify the actual problem; is it to do with connecting to the database? If so, please show what code you are using for the database connection and also whether you need a password (whcih you shou‍ld obscure on a public website).

[Rain Draken]

Hi thank you for your assistance. I have added my working code that draws from the access database itself, I have added the StockTrackerDB as well as the User. I'm not struggling with getting the required data from the access database itself its using the StockTrackerDB as a go between that gives me headaches, basically drawing all user information through the StockTrackerDB which seems to be a static program I keep getting null pointer exceptions ext.

[Rain Draken]

Ok I see that java files are not allowed in attachments so I will send it as text

[Rain Draken]

Ok this is my program DatabaseQuery

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

public class DatabaseQuery
{
	private Connection con = null;

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

		// The following is the code needed to view the database
        // query result in the console view with the needed
       // adjustments to match the required output

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

		// Read and display admin01 data in the database.
		ResultSet rs = stmt.executeQuery(
                "SELECT * FROM Users AS u INNER JOIN UserStocks AS us ON u.userID = us.userID");
		rs = stmt.executeQuery(
                "SELECT * FROM Users WHERE userID = '"+"admin01"+"'");

		while(rs.next())
		{
			System.out.println(rs.getString("userID") + " " +
                         rs.getString("firstName") + " " + rs.getString("lastName"));
		}

		rs = stmt.executeQuery(
                "SELECT * FROM UserStocks AS us INNER JOIN Stocks AS" +
                " s ON us.symbol = s.symbol WHERE userID = '"+"admin01"+"'");

		while(rs.next())
		{
			System.out.println("        " +
                        rs.getString("symbol") + "    " + rs.getString("name"));
		}

		System.out.println("");
		System.out.println("");

		rs = stmt.executeQuery(
                "SELECT * FROM Users WHERE userID = '"+"user01"+"'");

		while(rs.next())
		{
			System.out.println(rs.getString(
                        "UserID") + "  " + rs.getString("firstName") +
                        " " + rs.getString("lastName"));
		}

		rs = stmt.executeQuery(
                "SELECT * FROM UserStocks AS us INNER JOIN Stocks AS" +
                " s ON us.symbol = s.symbol WHERE userID = '"+"user01"+"'");

		while(rs.next())
		{
			System.out.println(
                        "        " + rs.getString("symbol") + "    " +
                        rs.getString("name"));
		}

		System.out.println("");
		System.out.println("");

		rs = stmt.executeQuery(
                "SELECT * FROM Users WHERE userID = '"+"user02"+"'");

		while(rs.next())
		{
			System.out.println(
                        rs.getString("UserID") + "  " +
                        rs.getString("firstName") + " " + rs.getString("lastName"));
		}

		rs = stmt.executeQuery("SELECT * FROM UserStocks AS" +
                " us INNER JOIN Stocks AS s ON us.symbol = s.symbol WHERE userID = '"+
                "user02"+"'");

		while(rs.next())
		{
			System.out.println(
                        "        " + rs.getString("symbol") + "    " +
                         rs.getString("name"));
		}


		stmt.close(); // closing Statement also closes ResultSet
		System.out.println("");
		System.out.println("");
		System.out.println("Report done.");

	} // end of main()
}

and this is the StockTrackerDB class

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

public class StockTrackerDB
{
    private Connection con = null;

    //Constructor; maks database connection
    public StockTrackerDB() throws ClassNotFoundException, SQLException
    {
        if(con == null)
        {
            String url = "jdbc:odbc:StockTracker";

            try
            {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            }
            catch(ClassNotFoundException ex)
            {
                throw new ClassNotFoundException(ex.getMessage() +
                		  "\nCannot locate sun.jdbc.odbc.JdbcOdbcDriver");
            }

            try
            {
                con = DriverManager.getConnection(url);
            }
            catch(SQLException ex)
            {
                throw new SQLException(ex.getMessage() +
                		  "\nCannot open database connection for "+url);
            }
        }
    }

    //Close makes database connection: null reference to connection
    public void close() throws SQLException, IOException, ClassNotFoundException
    {
        con.close();
        con = null;
    }

    //Method to serialize object to byte array
    private byte[] serializeObj(Object obj) throws IOException
    {
        ByteArrayOutputStream baOStream = new ByteArrayOutputStream();
        ObjectOutputStream objOStream = new ObjectOutputStream(baOStream);

        objOStream.writeObject(obj); //object must be Serializable
        objOStream.flush();
        objOStream.close();
        return baOStream.toByteArray(); //returns stream as byte array
    }

    //Method to deserialize byte from a byte array into an object
    private Object deserializeObj(byte[] buf)
    					 throws IOException, ClassNotFoundException
    {
        Object obj = null;

        if(buf !=null)
        {
            ObjectInputStream objIStream =
            	  new ObjectInputStream(new ByteArrayInputStream(buf));

            obj = objIStream.readObject(); //IOException, ClassNotFoundException
        }
        return obj;
    }

    /////////////////////////////////////////////////////////////////////////////
    //Methods for adding a record to a table
    /////////////////////////////////////////////////////////////////////////////
    //add to the Stock Table
    public void addStock(String stockSymbol, String stockDesc)
    			throws SQLException, IOException, ClassNotFoundException
    {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("INSERT INTO Stocks VALUES ('"
        				  +stockSymbol+"'"
        				  +",'"+stockDesc+"')");
        stmt.close();
    }

    //add to the Users Table
    public boolean addUser(User user) throws SQLException, IOException,
    				 ClassNotFoundException
    {
        boolean result = false;

        String dbUserID;
        String dbLastName;
        String dbFirstName;
        Password dbPswd;
        boolean isAdmin;

        dbUserID = user.getUserID();

        if(getUser(dbUserID) == null)
        {
            dbLastName = user.getLastName();
            dbFirstName = user.getFirstName();
            Password pswd = user.getPassword();
            isAdmin = user.isAdmin();

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

            pStmt.setString(1, dbUserID);
            pStmt.setString(2, dbLastName);
            pStmt.setString(3, dbFirstName);
            pStmt.setBytes(4,serializeObj(pswd));
            pStmt.setBoolean(5, isAdmin);
            pStmt.executeUpdate();
            pStmt.close();
            result = true;
        }
        else
            throw new IOException ("User exists - cannot add");

        return result;
    }

    //add to the UserStocks Table
    public void addUserStocks(String userID, String stockSymbol)
    			throws SQLException, IOException, ClassNotFoundException
    {
        Statement stmt = con.createStatement();

        stmt.executeUpdate("INSERT INTO UserStocks VALUES('"
        				  +userID+"'"
        				  +",'"+stockSymbol+"')");
        stmt.close();
    }

    /////////////////////////////////////////////////////////////////////////////////
    //Methods for updating a record to a table
    /////////////////////////////////////////////////////////////////////////////////

    //updating the Users table
    public boolean updUser(User user) throws SQLException, IOException,
    				 ClassNotFoundException
    {
        boolean result = false;

        String dbUserID;
        String dbLastName;
        String dbFirstName;
        Password dbPswd;
        boolean isAdmin;

        dbUserID = user.getUserID();

        if(getUser(dbUserID) != null)
        {
            dbLastName = user.getLastName();
            dbFirstName = user.getFirstName();
            Password pswd = user.getPassword();
            isAdmin = user.isAdmin();

            PreparedStatement pStmt = con.prepareStatement(
            "UPDATE Users SET lastName = ?,"
               +" firstName = ?, pswd = ?, admin = ? WHERE userID = ?");

            pStmt.setString(1, dbLastName);
            pStmt.setString(2, dbFirstName);
            pStmt.setBytes(3, serializeObj(pswd));
            pStmt.setBoolean(4, isAdmin);
            pStmt.setString(5, dbUserID);

            pStmt.executeUpdate();
            pStmt.close();
            result = true;
        }
        else
            throw new IOException(
           "User does not exist - cannot update.");

        return result;
    }


    /////////////////////////////////////////////////////////////////////////////////
    //Methods for deleting a record from a table
    /////////////////////////////////////////////////////////////////////////////////

    //delete a record from the Stocks table
    private void delStock(String stockSymbol)
    			 throws SQLException, IOException, ClassNotFoundException
    {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("DELETE FROM Stocks WHERE "
        				  +"symbol = '"+stockSymbol+"'");
        stmt.close();
    }

    //delete a record from the Users Table
    public void delUser(User user) throws SQLException, IOException,
    					ClassNotFoundException
    {
        String dbUserID;
        String stockSymbol;

        Statement stmt = con.createStatement();

        try {
            con.setAutoCommit(false);

            dbUserID = user.getUserID();
            if(getUser(dbUserID) != null)
            {
                ResultSet rs1 = stmt.executeQuery(
                "SELECT userID, symbol "+
                "FROM UserStocks WHERE userID = '"+dbUserID+"'");

                while(rs1.next())
                {
                    try
                    {
                        stockSymbol = rs1.getString("symbol");
                        delUserStocks(dbUserID, stockSymbol);
                    }
                    catch(SQLException ex)
                    {
                        throw new SQLException("Deletioon of user stock holding failed: "
                        					  +ex.getMessage());
                    }
                } //end of loop thru UserStocks

                try
                {  //holdings deleted, now delete user
                    stmt.executeUpdate("DELETE FROM Users WHERE "
                    			  +"userID = '"+dbUserID+"'");
                }
                catch(SQLException ex)
                {
                    throw new SQLException("User deletion failed: "+ex.getMessage());
                }
            }
            else
                throw new IOException("User not found in database - cannot delete.");

            try
            {
                con.commit();
            }
            catch(SQLException ex)
            {
                throw new SQLException("Transaction commit failed: "+ex.getMessage());
            }
        }
        catch(SQLException ex)
        {
            try
            {
                con.rollback();
            }
            catch(SQLException sqx)
            {
                throw new SQLException("Transaction failed then rollback failed: "
                				  +sqx.getMessage());
            }

            //Transaction failed, was rolled back
            throw new SQLException("Transaction failed; was rolled back: "
            					  +ex.getMessage());
        }

        stmt.close();
    }

    //delete a record from the UserStocks Table
    public void delUserStocks(String userID, String stockSymbol)
    			throws SQLException, IOException, ClassNotFoundException
    {
        Statement stmt = con.createStatement();
        ResultSet rs;

        stmt.executeUpdate("DELETE FROM UserStocks WHERE "
        				  +"userID = '"+userID+"'"
        				  +"AND symbol = '"+stockSymbol+"'");

        rs = stmt.executeQuery("SELECT symbol FROM UserStocks "
        				  +"WHERE symbol = '"+stockSymbol+"'");

        if(!rs.next()) // no users have this stock
            delStock(stockSymbol);

        stmt.close();

    }

    /////////////////////////////////////////////////////////////////////////////////
    //Methods for listing record data from a table
    //Ordered by:
    //     methods that obtain individual field(s),
    //     methods that obtain a complete record, and
    //     methods that obtain multiple records
    /////////////////////////////////////////////////////////////////////////////////

    //Methods to access one or more individual fields

    //get a stock description from the Stocks table
    public String getStockDesc(String stockSymbol)
    			  throws SQLException, IOException, ClassNotFoundException
    {
        Statement stmt = con.createStatement();
        String stockDesc = null;

        ResultSet rs = stmt.executeQuery("SELECT symbol, name FROM Stocks "
        				+"WHERE symbol = '"+stockSymbol+"'");
        if(rs.next())
            stockDesc = rs.getString("name");
        rs.close();
        stmt.close();

        return stockDesc;
    }


    //Methods to access a complete record

    //get User data from the User table
    public User getUser(String userID) throws SQLException, IOException,
    				  ClassNotFoundException
    {
        Statement stmt = con.createStatement();

        String dbUserID;
        String dbLastName;
        String dbFirstName;
        Password dbPswd;
        boolean isAdmin;

        byte[] buf = null;
        User user = null;
        ResultSet rs = stmt.executeQuery("SELECT * FROM Users WHERE userID = '"
        			+userID+"'");
        if(rs.next())
        {
            dbUserID = rs.getString("userID");
            dbLastName = rs.getString("lastName");
            dbFirstName = rs.getString("firstName");

          // Do NOT use with JDK 1.2.2 using JDBC-ODBC bridge as
          // SQL NULL data value is not handled correctly.
            buf = rs.getBytes("pswd");
            dbPswd = (Password)deserializeObj(buf);

            isAdmin = rs.getBoolean("admin");
            user = new User(dbUserID, dbFirstName, dbLastName, dbPswd, isAdmin);
        }
        rs.close();
        stmt.close();

        return user; //User object created for userID
    }


    //Methods to access a list of records

    //get list of selected fields for all records from the Users Table
    public ArrayList listUsers() throws SQLException, IOException,
    					ClassNotFoundException
    {
        ArrayList aList = new ArrayList();
        Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT userID, firstName, lastName, admin "
        				+"FROM Users ORDER BY userID");

        while(rs.next())
        {
            aList.add(rs.getString("userID"));
            aList.add(rs.getString("firstName"));
            aList.add(rs.getString("lastName"));
            aList.add(new Boolean(rs.getBoolean("admin")));
        }

        rs.close();
        stmt.close();

        return aList;
    }

    //get all fields in all records for a given user from the UserStocks table
    public ArrayList listUserStocks(String userID) throws SQLException, IOException,
    				  ClassNotFoundException
    {
        ArrayList aList = new ArrayList();
        Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT * FROM UserStocks "
        			+"WHERE userID = '"+userID+"' ORDER BY symbol");
        while(rs.next())
            aList.add(rs.getString("symbol"));

        rs.close();
        stmt.close();

        return aList;
    }
}

[Carey Brown]

Please edit your last post to put Code tags around the second block of code.

Which line of code is giving you the null pointer exception? Post the full exception message.

[Rain Draken]

StockTrackerDB Class

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

public class StockTrackerDB
{
    private Connection con = null;

    //Constructor; maks database connection
    public StockTrackerDB() throws ClassNotFoundException, SQLException
    {
        if(con == null)
        {
            String url = "jdbc:odbc:StockTracker";

            try
            {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            }
            catch(ClassNotFoundException ex)
            {
                throw new ClassNotFoundException(ex.getMessage() +
                		  "\nCannot locate sun.jdbc.odbc.JdbcOdbcDriver");
            }

            try
            {
                con = DriverManager.getConnection(url);
            }
            catch(SQLException ex)
            {
                throw new SQLException(ex.getMessage() +
                		  "\nCannot open database connection for "+url);
            }
        }
    }

    //Close makes database connection: null reference to connection
    public void close() throws SQLException, IOException, ClassNotFoundException
    {
        con.close();
        con = null;
    }

    //Method to serialize object to byte array
    private byte[] serializeObj(Object obj) throws IOException
    {
        ByteArrayOutputStream baOStream = new ByteArrayOutputStream();
        ObjectOutputStream objOStream = new ObjectOutputStream(baOStream);

        objOStream.writeObject(obj); //object must be Serializable
        objOStream.flush();
        objOStream.close();
        return baOStream.toByteArray(); //returns stream as byte array
    }

    //Method to deserialize byte from a byte array into an object
    private Object deserializeObj(byte[] buf)
    					 throws IOException, ClassNotFoundException
    {
        Object obj = null;

        if(buf !=null)
        {
            ObjectInputStream objIStream =
            	  new ObjectInputStream(new ByteArrayInputStream(buf));

            obj = objIStream.readObject(); //IOException, ClassNotFoundException
        }
        return obj;
    }

    /////////////////////////////////////////////////////////////////////////////
    //Methods for adding a record to a table
    /////////////////////////////////////////////////////////////////////////////
    //add to the Stock Table
    public void addStock(String stockSymbol, String stockDesc)
    			throws SQLException, IOException, ClassNotFoundException
    {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("INSERT INTO Stocks VALUES ('"
        				  +stockSymbol+"'"
        				  +",'"+stockDesc+"')");
        stmt.close();
    }

    //add to the Users Table
    public boolean addUser(User user) throws SQLException, IOException,
    										 ClassNotFoundException
    {
        boolean result = false;

        String dbUserID;
        String dbLastName;
        String dbFirstName;
        Password dbPswd;
        boolean isAdmin;

        dbUserID = user.getUserID();

        if(getUser(dbUserID) == null)
        {
            dbLastName = user.getLastName();
            dbFirstName = user.getFirstName();
            Password pswd = user.getPassword();
            isAdmin = user.isAdmin();

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

            pStmt.setString(1, dbUserID);
            pStmt.setString(2, dbLastName);
            pStmt.setString(3, dbFirstName);
            pStmt.setBytes(4,serializeObj(pswd));
            pStmt.setBoolean(5, isAdmin);
            pStmt.executeUpdate();
            pStmt.close();
            result = true;
        }
        else
            throw new IOException ("User exists - cannot add");

        return result;
    }

    //add to the UserStocks Table
    public void addUserStocks(String userID, String stockSymbol)
    			throws SQLException, IOException, ClassNotFoundException
    {
        Statement stmt = con.createStatement();

        stmt.executeUpdate("INSERT INTO UserStocks VALUES('"
        				  +userID+"'"
        				  +",'"+stockSymbol+"')");
        stmt.close();
    }

    /////////////////////////////////////////////////////////////////////////////////
    //Methods for updating a record to a table
    /////////////////////////////////////////////////////////////////////////////////

    //updating the Users table
    public boolean updUser(User user) throws SQLException, IOException,
    										 ClassNotFoundException
    {
        boolean result = false;

        String dbUserID;
        String dbLastName;
        String dbFirstName;
        Password dbPswd;
        boolean isAdmin;

        dbUserID = user.getUserID();

        if(getUser(dbUserID) != null)
        {
            dbLastName = user.getLastName();
            dbFirstName = user.getFirstName();
            Password pswd = user.getPassword();
            isAdmin = user.isAdmin();

            PreparedStatement pStmt = con.prepareStatement("UPDATE Users SET lastName = ?,"
            					   +" firstName = ?, pswd = ?, admin = ? WHERE userID = ?");

            pStmt.setString(1, dbLastName);
            pStmt.setString(2, dbFirstName);
            pStmt.setBytes(3, serializeObj(pswd));
            pStmt.setBoolean(4, isAdmin);
            pStmt.setString(5, dbUserID);

            pStmt.executeUpdate();
            pStmt.close();
            result = true;
        }
        else
            throw new IOException("User does not exist - cannot update.");

        return result;
    }


    /////////////////////////////////////////////////////////////////////////////////
    //Methods for deleting a record from a table
    /////////////////////////////////////////////////////////////////////////////////

    //delete a record from the Stocks table
    private void delStock(String stockSymbol)
    			 throws SQLException, IOException, ClassNotFoundException
    {
        Statement stmt = con.createStatement();
        stmt.executeUpdate("DELETE FROM Stocks WHERE "
        				  +"symbol = '"+stockSymbol+"'");
        stmt.close();
    }

    //delete a record from the Users Table
    public void delUser(User user) throws SQLException, IOException,
    										ClassNotFoundException
    {
        String dbUserID;
        String stockSymbol;

        Statement stmt = con.createStatement();

        try {
            con.setAutoCommit(false);

            dbUserID = user.getUserID();
            if(getUser(dbUserID) != null)
            {
                ResultSet rs1 = stmt.executeQuery("SELECT userID, symbol "+"FROM UserStocks WHERE userID = '"+dbUserID+"'");

                while(rs1.next())
                {
                    try
                    {
                        stockSymbol = rs1.getString("symbol");
                        delUserStocks(dbUserID, stockSymbol);
                    }
                    catch(SQLException ex)
                    {
                        throw new SQLException("Deletioon of user stock holding failed: "
                        					  +ex.getMessage());
                    }
                } //end of loop thru UserStocks

                try
                {  //holdings deleted, now delete user
                    stmt.executeUpdate("DELETE FROM Users WHERE "
                    			  +"userID = '"+dbUserID+"'");
                }
                catch(SQLException ex)
                {
                    throw new SQLException("User deletion failed: "+ex.getMessage());
                }
            }
            else
                throw new IOException("User not found in database - cannot delete.");

            try
            {
                con.commit();
            }
            catch(SQLException ex)
            {
                throw new SQLException("Transaction commit failed: "+ex.getMessage());
            }
        }
        catch(SQLException ex)
        {
            try
            {
                con.rollback();
            }
            catch(SQLException sqx)
            {
                throw new SQLException("Transaction failed then rollback failed: "
                					  +sqx.getMessage());
            }

            //Transaction failed, was rolled back
            throw new SQLException("Transaction failed; was rolled back: "
            					  +ex.getMessage());
        }

        stmt.close();
    }

    //delete a record from the UserStocks Table
    public void delUserStocks(String userID, String stockSymbol)
    			throws SQLException, IOException, ClassNotFoundException
    {
        Statement stmt = con.createStatement();
        ResultSet rs;

        stmt.executeUpdate("DELETE FROM UserStocks WHERE "
        				  +"userID = '"+userID+"'"
        				  +"AND symbol = '"+stockSymbol+"'");

        rs = stmt.executeQuery("SELECT symbol FROM UserStocks "
        					  +"WHERE symbol = '"+stockSymbol+"'");

        if(!rs.next()) // no users have this stock
            delStock(stockSymbol);

        stmt.close();

    }

    /////////////////////////////////////////////////////////////////////////////////
    //Methods for listing record data from a table
    //Ordered by:
    //     methods that obtain individual field(s),
    //     methods that obtain a complete record, and
    //     methods that obtain multiple records
    /////////////////////////////////////////////////////////////////////////////////

    //Methods to access one or more individual fields

    //get a stock description from the Stocks table
    public String getStockDesc(String stockSymbol)
    			  throws SQLException, IOException, ClassNotFoundException
    {
        Statement stmt = con.createStatement();
        String stockDesc = null;

        ResultSet rs = stmt.executeQuery("SELECT symbol, name FROM Stocks "
        								+"WHERE symbol = '"+stockSymbol+"'");
        if(rs.next())
            stockDesc = rs.getString("name");
        rs.close();
        stmt.close();

        return stockDesc;
    }


    //Methods to access a complete record

    //get User data from the User table
    public User getUser(String userID) throws SQLException, IOException,
    										  ClassNotFoundException
    {
        Statement stmt = con.createStatement();

        String dbUserID;
        String dbLastName;
        String dbFirstName;
        Password dbPswd;
        boolean isAdmin;

        byte[] buf = null;
        User user = null;
        ResultSet rs = stmt.executeQuery("SELECT * FROM Users WHERE userID = '"
        								+userID+"'");
        if(rs.next())
        {
            dbUserID = rs.getString("userID");
            dbLastName = rs.getString("lastName");
            dbFirstName = rs.getString("firstName");

          // Do NOT use with JDK 1.2.2 using JDBC-ODBC bridge as
          // SQL NULL data value is not handled correctly.
            buf = rs.getBytes("pswd");
            dbPswd = (Password)deserializeObj(buf);

            isAdmin = rs.getBoolean("admin");
            user = new User(dbUserID, dbFirstName, dbLastName, dbPswd, isAdmin);
        }
        rs.close();
        stmt.close();

        return user; //User object created for userID
    }


    //Methods to access a list of records

    //get list of selected fields for all records from the Users Table
    public ArrayList listUsers() throws SQLException, IOException,
    									ClassNotFoundException
    {
        ArrayList aList = new ArrayList();
        Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT userID, firstName, lastName, admin "
        								+"FROM Users ORDER BY userID");

        while(rs.next())
        {
            aList.add(rs.getString("userID"));
            aList.add(rs.getString("firstName"));
            aList.add(rs.getString("lastName"));
            aList.add(new Boolean(rs.getBoolean("admin")));
        }

        rs.close();
        stmt.close();

        return aList;
    }

    //get all fields in all records for a given user from the UserStocks table
    public ArrayList listUserStocks(String userID) throws SQLException, IOException,
    													  ClassNotFoundException
    {
        ArrayList aList = new ArrayList();
        Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT * FROM UserStocks "
        								+"WHERE userID = '"+userID+"' ORDER BY symbol");
        while(rs.next())
            aList.add(rs.getString("symbol"));

        rs.close();
        stmt.close();

        return aList;
    }
}

[Carey Brown]

I corrected this method. See comments.

    //get list of selected fields for all records from the Users Table
    public ArrayList<User> listUsers() throws SQLException, IOException, // ADDED TYPE
                                        ClassNotFoundException
    {
        ArrayList<User> aList = new ArrayList<>(); // ADDED TYPE
        Statement stmt = con.createStatement();
 
        ResultSet rs = stmt.executeQuery("SELECT userID, firstName, lastName, admin "
                                        +"FROM Users ORDER BY userID");
 
        while(rs.next())
        {
            aList.add( new User( ... ) ); // ADD User to list
         }
 
        rs.close();
        stmt.close();
 
        return aList;
    }

[Rain Draken]

My problem is when I change my code to connect to the StockTrackerDB class

StockTrackerDB db;
User user = null;
String userID;
userID = "admin01";

when I try to link my userID to the database directly I have no problem getting all the user related info in the database like firstName, lastName ext as in my DatabaseQuery code I gave first, that is working perfectly from the database but when I want to connect the StockTrackerDB my results are empty and I think I understand why sort of the StockTrackerDB uses a logon class to input userID and password, but this program has to draw all the information for all three the users in the database but If I say userID = db.getUserID(userID), or userID = user.getUserID(userID). Also the logon and basically all of these other classes are using javax swing whereas this is using a console for output which I think shouldn't be a problem I am sure I am just missing something small.

[Rain Draken]

Do I use SQL statements to draw the results from the StockTrackerDB class or do I get it from a method in the StockTrackerDB by assigning it to a variable on the programs side and if I use SQL statements isn't it then getting the info from the database directly.

[Carey Brown]

Seems like all SQL should be encapsulated in the StockTrackerDB class. Your application should access the database through this class.

[Rain Draken]

I see that you are better than the book simplified the Arraylist method which I got straight out of the book nice making it a lot less lines of code

[Rain Draken]

That is what I thought to that all SQL should be on the StockTrackerDB side but If I want to change my DatabaseQuery program to draw information via the StockTrackerDB I come up empty handed I am missing something maybe I call the wrong method I am calling the getUser method.

[Campbell Ritchie]

Carey Brown wrote:Please edit your last post to put Code tags around the second block of code. . . . .

I edited his code to add the missing code tags and also shortened the long lines (that is how you do it, but it will be much better if you shorten lines as you go). At least the second class wasn't as bad as the first one for long lines. I hope I don't have to edit it again and make it look different

Don't write System.out.println("). Write System.out.println(). In fact I would prefer to use System.out.printf and use %n a few times. I am not certain, but I think there is a few milliseconds' overhead every time you access the screen, so you will probably get better performance if you have a few big print calls than lots of small ones.
Don't set local variables to null. Let them go out of scope and the garbage collections can happily get rid of the object from memory whenever it is necessary.
Why are you using IO Exceptions when you have a nonexistent user? That looks to me like a scenario where a different sort of Exception might be better.

As CB suggests, the two classes appear to be completely independent of each other. Please tell us the full details about this exception; it is much easier to find if we have the line numbers. Maybe I had better not edit the second post, otherwise all the line numbers will change.

[Carey Brown]

Can you post a snippit of the lines that are coming up empty?

StockTrackerDB db = new StockTrackerDB();
User user = db.getUser("user01" );
System.out.println( user ); // ASSUMES User HAS A toString() METHOD

[Campbell Ritchie]

Carey Brown wrote:. . . System.out.println( user ); // ASSUMES User HAS A toString() METHOD

Every Object has a toString method. If it isn't an Object then it is null or a primitive, and you can print nulls and primitives, too. You should be able to pass absolutely anything to println and get some sort of output and no Exceptions.

[Rain Draken]

Ok below is the first problem the first name and last name variables haven't been initialized and also this is drawing from the user class not the StockTrackerDB class

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

public class Database
{
	private Connection con = null;

	public static void main(String[] args) throws Exception
	{
		String stockSymbol;
	   	StockTrackerDB db = new StockTrackerDB();
    	User user = db.getUser("admin01");
    	String firstName;
    	String lastName;
    	String userID;
    	userID = user.getUserID();
    	if(userID == "admin01")
    	{
			firstName = user.getFirstName();
			lastName = user.getLastName("admin");
		}

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

		// Read and display admin01 data in the database.

		if(userID == "admin01")
		{
			System.out.println(userID + " " + firstName + " " + lastName);
		}

C:\Users\Pieter\Desktop\Java\Java\Chapter11\DatabaseQueryResult\Database.java:47: variable firstName might not have been initialized
System.out.println(userID + " " + firstName + " " + lastName);
                                                         ^
C:\Users\Pieter\Desktop\Java\Java\Chapter11\DatabaseQueryResult\Database.java:47: variable lastName might not have been initialized
System.out.println(userID + " " + firstName + " " + lastName);
                                                                           ^
2 errors

Tool completed with exit code 1

[Knute Snortum]

You can see that the variables will not be uninitialized, but the compiler can't.  You can clear this up just by giving the variables a default value when declaring them:

        String firstName = "";
        String lastName = "";
        String userID = "";

[Rain Draken]

if I try to link the variables of lastName and firstName to db.getUser() I get an error because user returns a User variable not a String variable if I link it to db.listUsers() same story and if I link it to user.getFirstName() it compiles but doesn't print out anything to the console just blank

[Campbell Ritchie]

That is not an Exception, but a compiler error. It stems, I think, from your having a main method far too long. I think you should have some sort of class which encapsulates those names (etc) as fields, and a constructor which initialises them.
At present, however, those are local variables. Unlike fields, local variables don't have default values. There is in theory a risk that the local variable is put onto the stack without being given a value and instead you use the previous data on the stack—which is bound to be incorrect. So a rule was introduced to prevent such a disaster: all local variables must be definitely assigned before use. What happens if you never enter the if on line 18? Your name fields won't have values assigned them, and that is not permissible. That would not happen if you have a class. You might end up with the name field pointing to null, but that is a different story. You should be able to organise the flow of control so you get some data out of the database and use those data.

By the way: you will never enter the if on line 18 because of inappropriate use of the == operator.

[Carey Brown]

This will cause you grief

if(userID == "admin01")

When comparing objects like String, you should use the equals() method.

[Carey Brown]

Campbell Ritchie wrote:

Carey Brown wrote:. . . System.out.println( user ); // ASSUMES User HAS A toString() METHOD

Every Object has a toString method. If it isn't an Object then it is null or a primitive, and you can print nulls and primitives, too. You should be able to pass absolutely anything to println and get some sort of output and no Exceptions.

I meant a useful toString() method.

[Rain Draken]

Thank you for the advice I will work on it tonight and let you all know tomorrow what I have accomplished with this you have all been amazingly helpful instead of giving just code snippets you have given me real advice which is much better thank you

[Campbell Ritchie]

Carey Brown wrote:. . . I meant a useful toString() method.

But even User@0x1234abcd is better than nothing.

[Campbell Ritchie]

Rain Draken wrote:Thank you for the advice . . .

That's a pleasure Whether that includes thanks for advice on the toString method, well, I shall leave you to work that out for yourself.