Help with Java Database Query

Can someone guide me to a tutorial or some code that will show me how to return a result set and how I can use the resultset to populate an HTML/Javascript form?

Here is what I have done so far with Java (this compiles but I am not sure how to populate myResults):

///THANK YOU IN ADVANCE//////

import java.sql.*;
import java.applet.*;
import java.awt.*;


import java.net.*;
import java.io.*;
import java.util.*;
import javax.swing.*;

//These are bein imported, because eventually I want to pass them to an applet in HTML.


public class LoadDriver
{
private static String myResults = "";

public static void main(String[] Args)
{//0
try//1
{
Class.forName("org.gjt.mm.mysql.Driver").newInstance();

}
catch (Exception E)//1
{
System.err.println("Unable to load driver.");
E.printStackTrace();
}

}//0

public static String showTableImad()
{


try//2
{
Connection C = DriverManager.getConnection("jdbc:mysql://xxx.xxx.xxx/username?user=myname&password=mypass");

///////////////////////////
///////////////////////////
try//3
{
String tableName = "vars";
Statement Stmt = C.createStatement();
ResultSet RS = Stmt.executeQuery("select albumOwner from counter");
while (RS.next())
{
///THIS IS WHERE I need to collec the info I THINK
}
RS.close();
Stmt.close();
C.close();
}
catch (SQLException E)//3
{
System.out.println("SQLException: " + E.getMessage());
System.out.println("SQLState: " + E.getSQLState());
System.out.println("VendorError: " + E.getErrorCode());
}
////////////////////////
////////////////////////
}
catch (SQLException E)//2
{
System.out.println("SQLException: " + E.getMessage());
System.out.println("SQLState: " + E.getSQLState());
System.out.println("VendorError: " + E.getErrorCode());
}

return myResults;
}


}:confused: :confused: :confused: :mad:

A general concept to try and get used to when dealing with database access is to keep seperation between the code that does the accessing, and the code that deals with the results.

With that in mind, it works well to make a class that only holds methods which access the database for you. The methods usually return a String, or a class you define that has just a few attributes and getters/setters to deal with the attributes. (note: this is essentially a "java bean" class).

So the code you have listed works as your database accessing class. You should probably get rid of having a main() method, and maybe make a method to setup the JDBC connection/statement stuff.

A more appropriate name for showTableImad() would be getAlbumOwners(). I assume you are getting back multiple names? If so then change the method to return an ArrayList instead of String. In the method, have this variable:

ArrayList ownerList = new ArrayList();

And put this where your "///THIS IS WHERE I need to collec the info I THINK" is:

ownerList.add(RS.getString("albumOwner");

Return the ownerList. There is your "result set" in list form. You can then use that any way you want to populate elsewhere.

I hope this helps. I was a bit general but it looks like you have a good start.

-- Steven

Quote:

---

Originally posted by reinkesm
A general concept to try and get used to when dealing with database access is to keep seperation between the code that does the accessing, and the code that deals with the results.

With that in mind, it works well to make a class that only holds methods which access the database for you. The methods usually return a String, or a class you define that has just a few attributes and getters/setters to deal with the attributes. (note: this is essentially a "java bean" class).

So the code you have listed works as your database accessing class. You should probably get rid of having a main() method, and maybe make a method to setup the JDBC connection/statement stuff.

A more appropriate name for showTableImad() would be getAlbumOwners(). I assume you are getting back multiple names? If so then change the method to return an ArrayList instead of String. In the method, have this variable:

ArrayList ownerList = new ArrayList();

And put this where your "///THIS IS WHERE I need to collec the info I THINK" is:

ownerList.add(RS.getString("albumOwner");

Return the ownerList. There is your "result set" in list form. You can then use that any way you want to populate elsewhere.

I hope this helps. I was a bit general but it looks like you have a good start.

-- Steven

---

Steven, Thanks for your reply. I did the changes you suggested and this is what I came up with. I have 2 compile errors and I am not sure what to do with them:



import java.sql.*;
import java.applet.*;
import java.awt.*;


import java.net.*;
import java.io.*;
import java.util.*;
import javax.swing.*;

public class LoadNew
{
public static ResultSet myConnection()
{//0
try//1
{
Class.forName("org.gjt.mm.mysql.Driver").newInstance();

}
catch (Exception E)//1
{
System.err.println("Unable to load driver.");
E.printStackTrace();
}

try//2
{
Connection C = DriverManager.getConnection("jdbc:mysql://xxxx?user=xxxxx&password=xxxxx");
Statement Stmt = C.createStatement();
}
catch (SQLException E)//2
{
System.out.println("SQLException: " + E.getMessage());
System.out.println("SQLState: " + E.getSQLState());
System.out.println("VendorError: " + E.getErrorCode());
}

return myConnection();
}//0

public static ArrayList showOwners()
{

try//3
{
myConnection();
ResultSet RS = Stmt.executeQuery("select albumOwner from counter"); //ERROR HERE
while (RS.next())
{
ArrayList ownerList = new ArrayList();
ownerList.add(RS.getString("albumOwner"));
}
RS.close();
//Stmt.close();
//C.close();
}
catch (SQLException E)//3
{
System.out.println("SQLException: " + E.getMessage());
System.out.println("SQLState: " + E.getSQLState());
System.out.println("VendorError: " + E.getErrorCode());
}

return ownerList;//// ERROR HERE
}


}:confused:

First, the compile errors.
The first error involves the way you have your myConnection() method set. You want to return a Connection object and not a ResultSet form that, so the signature should be:

Code:

---

public static Connection myConnection()

---

And the return line should be:

Code:

---

return C;

---

So you've got myConnection giving you a Connection, but then when you call that in showOwner(), make sure to assign the Connection object to something. Where you have:

Code:

---

myConnection();
ResultSet RS = Stmt.executeQuery("select albumOwner from counter");

---

try this:

Code:

---

Connection conn = myConnection();  // assigning the Connection
Statement Stmt = conn.createStatement(); // taken out of myConnection()
ResultSet RS = Stmt.executeQuery("select albumOwner from counter");

---

I think that'll solve the first error.
The second error might have to do with this section:

Code:

---

while (RS.next())
{
ArrayList ownerList = new ArrayList();
ownerList.add(RS.getString("albumOwner"));
}

---

When you declare a variable inside some logic, such as if, or while loops, the compiler doesn't like it because it knows there is a chance it will never be created (if the while is never entered). We also want to move the ownerList declaration because if you have it there, the ArrayList will be cleared each time you loop, and your list would end up with only one item in it. Move the "ArrayList ownerList = new ArrayList()" line to the top of the method.

Lastly, uncomment your closes of the result set and statement. The reason you had to comment them out is because they didn't exist inside that method, but now they will and you want to make sure to close them each time.

In case you aren't familiar with how to use ArrayList, the javadoc is at: http://java.sun.com/j2se/1.4.1/docs/...ArrayList.html

Hope you can get it working.

-- Steven

Quote:

---

Originally posted by reinkesm
And the return line should be:

Code:

---

return C;

---

---

Thanks for the suggestions they fixed up a couple of the errors. I just want to mention that with respect to the return line.. they are still giving me errors.. both of the return lines are not making java happy:

Code:

---

import java.sql.*;
import java.applet.*;
import java.awt.*;


import java.net.*;
import java.io.*;
import java.util.*;
import javax.swing.*;

 public class LoadNew
 {
        public static Connection myConnection()
        {//0
                try//1
                {
                        Class.forName("org.gjt.mm.mysql.Driver").newInstance();

                }
                catch (Exception E)//1
                {
                        System.err.println("Unable to load driver.");
                        E.printStackTrace();
                }

                try//2
                {
                          Connection C = DriverManager.getConnection("jdbc:mysql://xxx?user=xxxxx&password=xxxxx");
                        Statement Stmt = C.createStatement();
                }
                catch (SQLException E)//2
                {
                        System.out.println("SQLException: " + E.getMessage());
                        System.out.println("SQLState:    " + E.getSQLState());
                        System.out.println("VendorError:  " + E.getErrorCode());
                }

                return C;//ERROR HERE
        }//0

    public static ArrayList showOwners()
    {

                        try//3
                        {
                                        Connection conn = myConnection();  // assigning the Connection
                                        Statement Stmt = conn.createStatement(); // taken out of myConnection()
                                        ResultSet RS = Stmt.executeQuery("select albumOwner from counter");
                                        ArrayList ownerList = new ArrayList();
                                        while (RS.next())
                                        {
                                                ownerList.add(RS.getString("albumOwner"));
                                        }
                                        RS.close();
                                        Stmt.close();
                                        conn.close();
                        }
                        catch (SQLException E)//3
                        {
                                System.out.println("SQLException: " + E.getMessage());
                                System.out.println("SQLState:    " + E.getSQLState());
                                System.out.println("VendorError:  " + E.getErrorCode());
                        }

                return ownerList;//// ERROR HERE
          }


}

---

:)

Ahh, is the error message something about not being visible or defined?

What I said about variables being declared inside conrol structures (if's, whiles, etc) is also true for inside try blocks. Declaring the variable inside the try{} iteself isn't an error, but you get the error when you try and access that variable outside the try block, which in your case is the return line.

So... move the declarations up and out of the try blocks they are in.

In myConnection(), put "Connection C = null;" as the first line. then later you just need:

Code:

---

C = DriverManager.getConnection("jdbc:mysql://xxx?user=xxxxx&password=xxxxx");

---

In showOwners() move, "ArrayList ownerList = new ArrayList();" to the first line, before any try{}.

Not to nag, but you aren't "showing" anything in your method so I still say call it "getOwners" so it makes more sense. Personal preference. :)

-- Steven

Quote:

---

Originally posted by reinkesm
Ahh, is the error message something about not being visible or defined?

.
.
.
.
.
Not to nag, but you aren't "showing" anything in your method so I still say call it "getOwners" so it makes more sense. Personal preference. :)

-- Steven

---

Thanks for your suggestions Steven, my code is now compiling, now to check that the code really works..
I have added a paint method to access the results or at least one of the results in the array. I am able to use a regular string and return it to the screen, but not the array.

Code:

---

import java.sql.*;
import java.applet.*;
import java.awt.*;


import java.net.*;
import java.io.*;
import java.util.*;
import javax.swing.*;

 public class LoadNew extends JApplet
 {
        public static Connection myConnection()
        {//0
                Connection C = null;
                try//1
                {
                        Class.forName("org.gjt.mm.mysql.Driver").newInstance();

                }
                catch (Exception E)//1
                {
                        System.err.println("Unable to load driver.");
                        E.printStackTrace();
                }

                try//2
                {
                          C = DriverManager.getConnection("jdbc:mysql://xxx?user=xxxxx&password=xxxx");
                        Statement Stmt = C.createStatement();
                }
                catch (SQLException E)//2
                {
                        System.out.println("SQLException: " + E.getMessage());
                        System.out.println("SQLState:    " + E.getSQLState());
                        System.out.println("VendorError:  " + E.getErrorCode());
                }

                return C;
        }//0

    public static ArrayList getOwners()
    {
                        ArrayList ownerList = new ArrayList();

                        try//3
                        {
                                        Connection conn = myConnection();  // assigning the Connection
                                        Statement Stmt = conn.createStatement(); // taken out of myConnection()
                                        ResultSet RS = Stmt.executeQuery("select albumOwner from counter");
                                        while (RS.next())
                                        {
                                                ownerList.add(RS.getString("albumOwner"));
                                        }
                                        RS.close();
                                        Stmt.close();
                                        conn.close();
                        }
                        catch (SQLException E)//3
                        {
                                System.out.println("SQLException: " + E.getMessage());
                                System.out.println("SQLState:    " + E.getSQLState());
                                System.out.println("VendorError:  " + E.getErrorCode());
                        }

                return ownerList;
          }
        public void paint(Graphics g)
        {
                {
                        ArrayList value = getOwners();
                        String test = (String)value.get(1); //THIS DOES NOT WORK
                        //String test = "Hello"; //THIS WORKS
                        g.drawString(test, 10, 10);
                }
        }


}

---

Well the syntax on pulling the value out of the ArrayList is correct. What error are you getting? IndexOutOfBoundsException? Make sure that getOwners() is actually populating the ownerList. If it isn't being populated, then your ArrayList is empty so that'd be why you can't get the String value from it. You can add the line, "System.out.println(value);" below after your call to getOwners() in paint() to see what value looks like. If value isn't being populated, make sure your query is actually finding some matching results in the database.

If it's a different error, post the error.

You may already know this, but I'll throw it up anyway. Here's a good way to loop through your ArrayList when you need to:

Code:

---

String owner = null;
java.util.Iterator ownerIter = value.iterator();
while (iter.hasNext()) {
  String owner = (String)onwerIter.next();
  // deal with each owner here
}

---

Quote:

---

Originally posted by reinkesm
[B]Well the syntax on pulling the value out of the ArrayList is correct. What error are you getting? IndexOutOfBoundsException? Make sure that getOwners() is actually populating the ownerList. If it isn't being populated, then your ArrayList is empty so that'd be why you can't get the String value from it. You can add the line, "System.out.println(value);" below after your call to getOwners() in paint() to see what value looks like. If value isn't being populated, make sure your query is actually finding some matching results in the database.

If it's a different error, post the error.

---

This is the error I have, which doesn't make sense, because I have the driver in my classpath and also, it worked on another piece of code before. (i.e. rows were returned)

Code:

---

Unable to load driver.

java.lang.ClassNotFoundException: org.gjt.mm.mysql.Driver
.
.
.
AND
java.lang.NullPointerException
        at LoadNew.getOwners(LoadNew.java:49)
.
.
.
.

---

:confused:

Quote:

---

Originally posted by imroue


This is the error I have, which doesn't make sense, because I have the driver in my classpath and also, it worked on another piece of code before. (i.e. rows were returned)

Code:

---

Unable to load driver.

java.lang.ClassNotFoundException: org.gjt.mm.mysql.Driver
.
.
.
AND
java.lang.NullPointerException
        at LoadNew.getOwners(LoadNew.java:49)
.
.
.
.

---

:confused:

---

Quote:

---

This is what I have in my user variable classpath (winxp)

Code:

---

.;C:\j2sdk1.4.0;C:\j2sdk1.4.0\jre\lib\ext;C:\j2sdk1.4.0\lib\tools.jar

---

The jdbc class driver is under the C:\j2sdk1.4.0\jre\lib\ext;

---

Has anyone ran into this problem????
Any Ideas??:( :( :(