Mixing java and mysql

[bad biddy]

bump... still trying to figure this out

[Tolls]

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?

A normal java project is structured:

<My-Project-Directory>
-- src
-- classes (though you may simpyl have the classes in the src depending on how you compile)
-- lib (this is where you dependent jar files go)

That's a basic structure. As it says, classes is optional and would involve using the -d option of javac. Without that then your class files will be placed beside the source files in the src folder (which may be fine for your needs).
That lib directory is the one I'm talking about.

Now, assuming you're in the <My-Project-Directory> directory you would do this to compile:
javac -cp ./src;./lib/*.jar <classes to compile eg src/somepackage/yourclass.java>

To run:
java -cp ./src;./lib/*.jar <your main class eg somepackage.yourclass>

[wsaryada]

Hi, you'd better take a little bit time to read about the classpath in Java, here is a tutorial on it PATH and CLASSPATH (The Java™ Tutorials > Essential Classes > The Platform Environment).

Basically if you work from a terminal just use the -cp option to define the classpath when you are executing your program using the java command. That was Toll tried to tell you on the previous answer. Just place the mysql.jar in the same location with your class.

For example:

java -cp .:mysql-connector-java-5.1.18-bin.jar ProgramOne

The dot symbol (.) is to add the current directory into the path so it can find your ProgramOne class.

[Tolls]

And that post reminded me, you'll probably have to check whether it's a ';' or a ':' for the separator inbetween different paths.
I can never remember.

[DarrylBurke]

And that post reminded me, you'll probably have to check whether it's a ';' or a ':' for the separator inbetween different paths.
I can never remember.

Semicolon for Windows, colon for Linux. Mnemonic: many programmers believe Windows is half as good as Linux.

db <-- Windows user

[bad biddy]

[root@engagelinux ~]# java -cp .:mysql-connector-java-5.1.18-bin.jar ProgramOne
e.getMessage():You have an error in your SQL syntax; check the manual that corre                                                                                   
sponds 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 t                                                                                      hat corresponds to your MySQL server version for the right syntax to use near '?                                                                                      ???????????????' at line 1
e.getStackTrace():[Ljava.lang.StackTraceElement;@1d8c3835

[root@engagelinux ~]# java -cp .:mysql-connector-java-5.1.18-bin.jar ProgramOne
e.getMessage():You have an error in your SQL syntax; check the manual that corre sponds 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 t hat corresponds to your MySQL server version for the right syntax to use near '? ???????????????' at line 1
e.getStackTrace():[Ljava.lang.StackTraceElement;@1d8c3835


I repasted the error because it says it gets cut off when using the code tags.

[bad biddy]

I can compile and run local java files just fine.

When I compile the class file is created within the same directory. That is how I will keep it.

When I run java -version I receive

[root@engagelinux ~]# java -version
java version "1.5.0"
gij (GNU libgcj) version 4.4.6 20110731 (Red Hat 4.4.6-3)

Copyright (C) 2007 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

So the path must be setup correctly. Right?

[DarrylBurke]

[QUOTE=bad biddy;278889]

gij (GNU libgcj) version 4.4.6 20110731 (Red Hat 4.4.6-3)

Another name for GNU Java aka gcj is non-Java.

Get rid of it and install an Oracle JDK.

db

[bad biddy]

I installed oracle jdk

here is the java -version command now

[root@engagelinux ~]# java -version
java version "1.6.0_22"
OpenJDK Runtime Environment (IcedTea6 1.10.6) (rhel-1.43.1.10.6.el6_2-x86_64)
OpenJDK 64-Bit Server VM (build 20.0-b11, mixed mode)

Now that we have a new java installed. I compiled it and tried to run the program
Here is the error code. Like before its not seeing the jdbc driver. I just have to setup the CLASSPATH and hopefully that fixes the driver issue.

[root@engagelinux ~]# java ProgramOne
e.getMessage():com.mysql.jdbc.Driver
e.getLocalizedMessage():com.mysql.jdbc.Driver
e.getStackTrace():[Ljava.lang.StackTraceElement;@5d888759

With a Classpath you are pointing it to a file or location for the java driver correct? Do I want to point it to use/lib/java or to another place?

[root@engagelinux ~]# whereis java
java: /usr/bin/java /etc/java /usr/lib/java /usr/share/java /usr/share/man/man1/java.1.gz

[bad biddy]

I tried following this guide for setting class path but I'm still getting jdbc error

How to Set Java Home on CentOS | eHow.com

[bad biddy]

Still haven't figured out setting the class path...

[Tolls]

The classpath is fine.
That error is nothing to do with the classpath.
Exactly what it is to do with requires you to priont the stack trace, as I requested waaaaay back up the thread!

[bad biddy]

I just added the print stack trace below those other error messages-

[root@engagelinux ~]# java ProgramOne
e.getMessage():com.mysql.jdbc.Driver
e.getLocalizedMessage():com.mysql.jdbc.Driver
e.getStackTrace():[Ljava.lang.StackTraceElement;@2e6e1408
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
        at java.net.URLClassLoader$1.run(URLClassLoader.java:217)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:205)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:321)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:294)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:266)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:186)
        at ProgramOne.main(ProgramOne.java:20)

like this:

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

[Tolls]

Post #26 was not a ClassNotFoundException.
Launch your code with whatever command you used in that post, and then post the full stack trace.

[bad biddy]

I don't get what you mean.... that is how I run the program:

I just: java ProgramOne to run it. Those are the errors that popped up.

How do i show the full stack trace?

[Tolls]

[root@engagelinux ~]# java -cp .:mysql-connector-java-5.1.18-bin.jar ProgramOne
e.getMessage():You have an error in your SQL syntax; check the manual that corre                                                                                   
sponds 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 t                                                                                      hat corresponds to your MySQL server version for the right syntax to use near '?                                                                                      ???????????????' at line 1
e.getStackTrace():[Ljava.lang.StackTraceElement;@1d8c3835

[root@engagelinux ~]# java -cp .:mysql-connector-java-5.1.18-bin.jar ProgramOne
e.getMessage():You have an error in your SQL syntax; check the manual that corre sponds 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 t hat corresponds to your MySQL server version for the right syntax to use near '? ???????????????' at line 1
e.getStackTrace():[Ljava.lang.StackTraceElement;@1d8c3835


I repasted the error because it says it gets cut off when using the code tags.

This is post #26.
That exception is not the ClassNotFound one.
It is different.
How you ran your code here is the correct way, as it found your JDBC driver.

Duplicate this and then give us the full exception with stack trace.

[bad biddy]

It works!!!!

[root@engagelinux ~]# java -cp .:mysql-connector-java-5.1.18-bin.jar ProgramOne
PRODUCT ID      PRODUCT NAME    SUPPLIER ID     CATEGORY ID     UNIT PRICE
1       Flash Disk      1000    2000    10.0
2       SSD Disk        1000    2000    100.0
3       SD Card 1000    2000    15.0
4       USB Drive       1000    2000    29.0
5       Micro SD Drive  1000    2000    10.0
6       Head Phone      1000    2001    9.99

I just gotta edit the code now for the project.

[bad biddy]

I need to figure out my spacing now.. as you can tell its off by quite a bit

[bad biddy]

Nevermind the spacing thats how its suppose to be looking at our example...

Part two of the assignment is basically to create a program two that will add additional products to the MySQL database.

Here is the code for it.

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

        public class ProgramTwo {

                public static void main(String[]args){
                //Establish connection to MYSQL database
                String connectionURL = "jdbc:mysql://localhost/warehouse";
                Connection connection = null;
                Statement statement = null;
                String sql = "insert into products (productName, supplierID, categoryID, unitPrice,) values ('Hard Disk','5555','6666','59.99')";
                String username = "bdewall";
                String password = "daba19DE";

        try{
                Class.forName("com.mysql.jdbc.Driver");
                connection = DriverManager.getConnection(connectionURL, username, password);
                statement = connection.createStatement();
                int count = statement.executeUpdate(sql);
                System.out.println(count + "rows updated.");

        }catch (ClassNotFoundException e){
                System.out.println("Unable to load MySql jdbc drivers.");
                e.printStackTrace();
        }catch(SQLException e) {
                System.out.println("Unable to establish connection with the database.");
                e.printStackTrace();

        }finally{
                if(connection !=null){
                try{
                connection.close();
                }catch(SQLException e){
                System.out.println("Exception occured while closing database connection.");
                e.printStackTrace();
                }
                }
                }
        }
        }

Its telling me that its unable to establish a connection to the database.

[root@engagelinux ~]# java -cp .:mysql-connector-java-5.1.18-bin.jar ProgramTwo
Unable to establish connection with the database.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ') values ('Hard Disk','5555','6666','59.99')' at line 1
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1749)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1666)
        at ProgramTwo.main(ProgramTwo.java:21)

[Tolls]

No it's not.
You are telling you it's unable to connect to the database.
Look at your catch block.

catch(SQLException e) {
   System.out.println("Unable to establish connection with the database.");
   e.printStackTrace();
}

Any SQLException cause your code to claim it has failed to connect.

Just stick to printing the stack trace. It has all the details you need for your exception.
In this case it's your SQL that's wrong.