Mixing java and mysql

[bad biddy]

I can get the program to compile but I keep getting a error.

Here is my code. I'll post the error code below.

[root@engagelinux ~]# cat ProgramOne.java
        import java.sql.Connection;
        import java.sql.DriverManager;
        import java.sql.ResultSet;
        import java.sql.SQLException;
        import java.sql.Statement;

        public class ProgramOne {

                public static void main(String[]args){

                //Establish connection to MySQL database
                String connectionURL = "root:mysql://localhost/warehouse";

                Connection connection = null;
                ResultSet resultSet = null;
                StringBuffer sb = new StringBuffer();
                try {

                //Load the database driver
                Class.forName("com.mysql.root.Driver");

                //Get a connection to the database
                connection = DriverManager.getConnection(connectionURL, "root", "daba19DE");

                //Select the data from the database
                String sql = "select * from products;";
                Statement statement = connection.createStatement();
                statement.executeQuery (sql);
                resultSet = statement.getResultSet();

                System.out.println("PRODUCT ID" + "\t" + "PRODUCT NAME" + "\t" + "SUPPLIER ID" + "\t" + "CATEGORY ID" + "\t" + "UNIT PRICE");
                while(resultSet.next()){
                        sb.append(resultSet.getInt("productID") + "\t");
                        sb.append(resultSet.getString("productName") + "\t");
                        sb.append(resultSet.getInt("supplierID") + "\t");
                        sb.append(resultSet.getInt("categoryID") + "\t");
                        sb.append(resultSet.getFloat("unitPrice") + "\t");
                        sb.append("\n");
                }
                }

                catch(Exception e) {
                        System.out.println("e.getMessage():"+e.getMessage());
                        System.out.println("e.getLocalizedMessage():"+e.getLocalizedMessage());
                        System.out.println("e.getStackTrace():"+e.getStackTrace());
                }

                finally{
                        if(resultSet != null){
                try {

                        connection.close();
                } catch(SQLException e) {
                        System.out.println("Exception occured while closing database connection.");
                        e.printStackTrace();
                        }
                }
                }
                System.out.println(sb.toString());
                }
                }

Error code:

[root@engagelinux ~]# java ProgramOne
e.getMessage():com.mysql.root.Driver not found in gnu.gcj.runtime.SystemClassLoader{urls=[file:./], parent=gnu.gcj.runtime.ExtensionClassLoader{urls=[], parent=null}}
e.getLocalizedMessage():com.mysql.root.Driver not found in gnu.gcj.runtime.SystemClassLoader{urls=[file:./], parent=gnu.gcj.runtime.ExtensionClassLoader{urls=[], parent=null}}
e.getStackTrace():[Ljava.lang.StackTraceElement;@5c80aefa

Can you help me figure this out.

[wsaryada]

The JDBC url for MySQL database is jdbc:mysql://localhost/warehouse and the JDBC driver class is com.mysql.jdbc.Driver.

[bad biddy]

Thanks for the quick reply.

I changed it to jdbc and I'm getting this error when trying to run it:

[root@engagelinux ~]# java ProgramOne
e.getMessage():com.mysql.jdbc.Driver not found in gnu.gcj.runtime.SystemClassLoader{urls=[file:./], parent=gnu.gcj.runtime.ExtensionClassLoader{urls=[], parent=null}}
e.getLocalizedMessage():com.mysql.jdbc.Driver not found in gnu.gcj.runtime.SystemClassLoader{urls=[file:./], parent=gnu.gcj.runtime.ExtensionClassLoader{urls=[], parent=null}}
e.getStackTrace():[Ljava.lang.StackTraceElement;@40518ddb

[wsaryada]

Make sure you have the MySQL JDBC driver and it is available in your application classpath. If you don't have it you can download it here: MySQL :: Download Connector/J

[bad biddy]

getting closer to figuring it out. I setup my CLASSPATH.

Now its saying that access is denied

[root@engagelinux ~]# java ProgramOne
e.getMessage():Access denied for user 'root'@'localhost' (using password: YES)
e.getLocalizedMessage():Access denied for user 'root'@'localhost' (using password: YES)
e.getStackTrace():[Ljava.lang.StackTraceElement;@54b5ac36

You can see in my code from the first post I use my username and password within the code.

   //Get a connection to the database
                connection = DriverManager.getConnection(connectionURL, "root", "daba19DE");

This is on a school network that is why its requiring a username and password I would assume. At least from the example given the teacher uses his username and password

[wsaryada]

Make sure that the username and password required to connect to the database is valid. If you said that the database is in the school network then you have to know the IP address of the database server instead of using localhost.

[bad biddy]

I asked someone from class about it and he said that mysql won't let root do a remote connection to the database so i had to create a MySQL username and password.

So i created a account and password and now it gives me this error

[root@engagelinux ~]# java ProgramOne
e.getMessage():com.mysql.jdbc.Driver not found in gnu.gcj.runtime.SystemClassLoader{urls=[file:./], parent=gnu.gcj.runtime.ExtensionClassLoader{urls=[], parent=null}}
e.getLocalizedMessage():com.mysql.jdbc.Driver not found in gnu.gcj.runtime.SystemClassLoader{urls=[file:./], parent=gnu.gcj.runtime.ExtensionClassLoader{urls=[], parent=null}}
e.getStackTrace():[Ljava.lang.StackTraceElement;@63747f6b

[wsaryada]

I think you've lost your classpath configuration again that's why it didn't find the JDBC driver again. It clearly said that it couldn't find the com.mysql.jdbc.Driver class.

[bad biddy]

I reestablished the class path and boom what do ya know another error this one is crazy....

[root@engagelinux ~]# java ProgramOne
e.getMessage():You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '????????????????' at line 1
e.getLocalizedMessage():You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '????????????????' at line 1
e.getStackTrace():[Ljava.lang.StackTraceElement;@503eb8d2

[wsaryada]

That means you have an error in your SQL query. Can you post what query you are trying to execute?

[bad biddy]

Isn't this the SQL query?

   //Select the data from the database
                String sql = "select * from products;";
                Statement statement = connection.createStatement();
                statement.executeQuery (sql);
                resultSet = statement.getResultSet();
 
                System.out.println("PRODUCT ID" + "\t" + "PRODUCT NAME" + "\t" + "SUPPLIER ID" + "\t" + "CATEGORY ID" + "\t" + "UNIT PRICE");
                while(resultSet.next()){
                        sb.append(resultSet.getInt("productID") + "\t");
                        sb.append(resultSet.getString("productName") + "\t");
                        sb.append(resultSet.getInt("supplierID") + "\t");
                        sb.append(resultSet.getInt("categoryID") + "\t");
                        sb.append(resultSet.getFloat("unitPrice") + "\t");
                        sb.append("\n");
                }
                }

[DarrylBurke]

Moved from New to Java

db

[wsaryada]

I didn't see any error with the select * from products; query. Are you sure this is the query you were executing?

[Tolls]

Can you do printStackTrace() instead of faffing with those three logging statements?
You're losing your stack trace, which might explain what's happening.

[bad biddy]

I don't know how to do a printStackTrace()

This is all the code I have for the program besides the database I created in MySQL.

[root@engagelinux ~]# cat ProgramOne.java
        import java.sql.Connection;
        import java.sql.DriverManager;
        import java.sql.ResultSet;
        import java.sql.SQLException;
        import java.sql.Statement;
 
        public class ProgramOne {
 
                public static void main(String[]args){
 
                //Establish connection to MySQL database
                String connectionURL = "root:mysql://localhost/warehouse";
 
                Connection connection = null;
                ResultSet resultSet = null;
                StringBuffer sb = new StringBuffer();
                try {
 
                //Load the database driver
                Class.forName("com.mysql.root.Driver");
 
                //Get a connection to the database
                connection = DriverManager.getConnection(connectionURL, "root", "daba19DE");
 
                //Select the data from the database
                String sql = "select * from products;";
                Statement statement = connection.createStatement();
                statement.executeQuery (sql);
                resultSet = statement.getResultSet();
 
                System.out.println("PRODUCT ID" + "\t" + "PRODUCT NAME" + "\t" + "SUPPLIER ID" + "\t" + "CATEGORY ID" + "\t" + "UNIT PRICE");
                while(resultSet.next()){
                        sb.append(resultSet.getInt("productID") + "\t");
                        sb.append(resultSet.getString("productName") + "\t");
                        sb.append(resultSet.getInt("supplierID") + "\t");
                        sb.append(resultSet.getInt("categoryID") + "\t");
                        sb.append(resultSet.getFloat("unitPrice") + "\t");
                        sb.append("\n");
                }
                }
 
                catch(Exception e) {
                        System.out.println("e.getMessage():"+e.getMessage());
                        System.out.println("e.getLocalizedMessage():"+e.getLocalizedMessage());
                        System.out.println("e.getStackTrace():"+e.getStackTrace());
                }
 
                finally{
                        if(resultSet != null){
                try {
 
                        connection.close();
                } catch(SQLException e) {
                        System.out.println("Exception occured while closing database connection.");
                        e.printStackTrace();
                        }
                }
                }
                System.out.println(sb.toString());
                }
                }

[DarrylBurke]

I don't know how to do a printStackTrace()

                } catch(SQLException e) {
                        System.out.println("Exception occured while closing database connection.");
                        e.printStackTrace(); // <---- what's that?
                        }
                }

Huh?

db

[bad biddy]

Lol umm.... I guess I do.... I basically copied the entire code from a example the professor has given me.

[bad biddy]

Ok so I think the problem is with the classpath not linking the JDBC driver

So let me start over...

[root@engagelinux ~]# ls
Desktop           Downloads           Music                                nmap-5.51.tgz    PrintClassPath.java  ProgramTwo.class  Templates
DetermineOs.java  GetSystemTime.java  mysql-connector-java-5.1.18-bin.jar  nmap-5.51.tgz.1  ProgramOne.class     ProgramTwo.java   Videos
Documents         java3con.sp         nmap-5.51                            Pictures         ProgramOne.java      Public

As you can see i have downloaded mysql-connector-java-5.1.18-bin.jar using yum install command

I have tried setting the CLASSPATH to it but maybe I'm doing it wrong.... How would you guys set the class path in my instance??

This file is located in my root.

[Tolls]

So what is the full exception you are now getting when you use printStackTrace()?

Also, how are you running this?

Finally, I would stick all my dependencies inside a lib folder and add that folder to my runtime classpath.
If I was running from the command line that would be:
java -cp <path to jars>/*.jar;. <my main class>

[bad biddy]

Ok Tolls you might have to walk me through this.. Im just starting java.

Basically at college we have all been given a private/ virtual machine to work with. We connect to that machine via ssh through putty.

BTW Im running a 64 bit CentOS

So your saying I should move my .jar file to a folder inside the lib folder?

When i type whereis java I get this result:

[root@engagelinux ~]# whereis java
java: /usr/bin/java /etc/java /usr/lib/java /usr/share/java

Here is the result when i type ls:

[root@engagelinux ~]# ls
Desktop             mysql-connector-java-5.1.18-bin.jar  ProgramOne.java
DetermineOs.java    nmap-5.51                            ProgramTwo.class
Documents           nmap-5.51.tgz                        ProgramTwo.java
Downloads           nmap-5.51.tgz.1                      Public
GetSystemTime.java  Pictures                             Templates
java3con.sp         PrintClassPath.java                  Videos
Music               ProgramOne.class

So I should move mysql-connector-java-5.1.18-bin.jar to a folder within in lib or move it to the java folder within lib?