Java-MySQL Program
It turns out that configuring Perl wasn’t the last step for my student instance. It appears that I neglected to configure my student instance to support Java connectivity to MySQL. This post reviews the configuration of Java to run programs against MySQL. It also covers the new syntax on how you register a DriverManager, and avoid Java compilation errors with the older syntax.
In prior posts, I’ve shown how to use Perl , PHP, Python, and Ruby languages to query a MySQL database on Linux.
You need to install the Open JDK libraries with the yum utility command:
yum install -y java-1.7.0-openjdk* |
It should generate the following log output:
Loaded plugins: langpacks, refresh-packagekit Package 1:java-1.7.0-openjdk-1.7.0.75-2.5.4.2.fc20.x86_64 already installed and latest version Package 1:java-1.7.0-openjdk-headless-1.7.0.75-2.5.4.2.fc20.x86_64 already installed and latest version Resolving Dependencies --> Running transaction check ---> Package java-1.7.0-openjdk-accessibility.x86_64 1:1.7.0.75-2.5.4.2.fc20 will be installed --> Processing Dependency: java-atk-wrapper for package: 1:java-1.7.0-openjdk-accessibility-1.7.0.75-2.5.4.2.fc20.x86_64 ---> Package java-1.7.0-openjdk-demo.x86_64 1:1.7.0.75-2.5.4.2.fc20 will be installed ---> Package java-1.7.0-openjdk-devel.x86_64 1:1.7.0.75-2.5.4.2.fc20 will be installed ---> Package java-1.7.0-openjdk-javadoc.noarch 1:1.7.0.75-2.5.4.2.fc20 will be installed ---> Package java-1.7.0-openjdk-src.x86_64 1:1.7.0.75-2.5.4.2.fc20 will be installed --> Running transaction check ---> Package java-atk-wrapper.x86_64 0:0.30.4-4.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: java-1.7.0-openjdk-accessibility x86_64 1:1.7.0.75-2.5.4.2.fc20 updates 32 k java-1.7.0-openjdk-demo x86_64 1:1.7.0.75-2.5.4.2.fc20 updates 1.9 M java-1.7.0-openjdk-devel x86_64 1:1.7.0.75-2.5.4.2.fc20 updates 9.2 M java-1.7.0-openjdk-javadoc noarch 1:1.7.0.75-2.5.4.2.fc20 updates 14 M java-1.7.0-openjdk-src x86_64 1:1.7.0.75-2.5.4.2.fc20 updates 39 M Installing for dependencies: java-atk-wrapper x86_64 0.30.4-4.fc20 fedora 71 k Transaction Summary ================================================================================ Install 12 Packages (+1 Dependent package) Total download size: 163 M Installed size: 765 M Downloading packages: (1/6): java-1.7.0-openjdk-accessibility-1.7.0.75-2.5.4.2.f | 32 kB 00:00 (2/6): java-1.7.0-openjdk-demo-1.7.0.75-2.5.4.2.fc20.x86_6 | 1.9 MB 00:02 (3/6): java-1.7.0-openjdk-devel-1.7.0.75-2.5.4.2.fc20.x86_ | 9.2 MB 00:05 (4/6): java-1.7.0-openjdk-javadoc-1.7.0.75-2.5.4.2.fc20.no | 14 MB 00:04 (5/6): java-atk-wrapper-0.30.4-4.fc20.x86_64.rpm | 71 kB 00:00 (6/6): java-1.7.0-openjdk-src-1.7.0.75-2.5.4.2.fc20.x86_6 | 39 MB 00:23 -------------------------------------------------------------------------------- Total 4.5 MB/s | 163 MB 00:36 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : java-atk-wrapper-0.30.4-4.fc20.x86_64 3/13 Installing : 1:java-1.7.0-openjdk-accessibility-1.7.0.75-2.5.4.2.fc20 4/13 Installing : 1:java-1.7.0-openjdk-devel-1.7.0.75-2.5.4.2.fc20.x86_64 9/13 Installing : 1:java-1.7.0-openjdk-src-1.7.0.75-2.5.4.2.fc20.x86_64 10/13 Installing : 1:java-1.7.0-openjdk-javadoc-1.7.0.75-2.5.4.2.fc20.noarc 12/13 Installing : 1:java-1.7.0-openjdk-demo-1.7.0.75-2.5.4.2.fc20.x86_64 13/13 Verifying : 1:java-1.7.0-openjdk-demo-1.7.0.75-2.5.4.2.fc20.x86_64 2/13 Verifying : 1:java-1.7.0-openjdk-javadoc-1.7.0.75-2.5.4.2.fc20.noarc 3/13 Verifying : java-atk-wrapper-0.30.4-4.fc20.x86_64 5/13 Verifying : 1:java-1.7.0-openjdk-accessibility-1.7.0.75-2.5.4.2.fc20 6/13 Verifying : 1:java-1.7.0-openjdk-devel-1.7.0.75-2.5.4.2.fc20.x86_64 8/13 Verifying : 1:java-1.7.0-openjdk-src-1.7.0.75-2.5.4.2.fc20.x86_64 12/13 Installed: java-1.7.0-openjdk-accessibility.x86_64 1:1.7.0.75-2.5.4.2.fc20 java-1.7.0-openjdk-demo.x86_64 1:1.7.0.75-2.5.4.2.fc20 java-1.7.0-openjdk-devel.x86_64 1:1.7.0.75-2.5.4.2.fc20 java-1.7.0-openjdk-javadoc.noarch 1:1.7.0.75-2.5.4.2.fc20 java-1.7.0-openjdk-src.x86_64 1:1.7.0.75-2.5.4.2.fc20 Dependency Installed: java-atk-wrapper.x86_64 0:0.30.4-4.fc20 Complete! |
You can find the Java compiler’s version with the following command:
javac -version |
It should show you the following Java version:
javac 1.7.0_75 |
Next, you need to install the mysql-connector-java library with yum like this:
yum install -y mysql-connector-java |
It should generate the following installation output:
Loaded plugins: langpacks, refresh-packagekit mysql-connectors-community | 2.5 kB 00:00 mysql-tools-community | 2.5 kB 00:00 mysql56-community | 2.5 kB 00:00 pgdg93 | 3.6 kB 00:00 updates/20/x86_64/metalink | 15 kB 00:00 Resolving Dependencies --> Running transaction check ---> Package mysql-connector-java.noarch 1:5.1.28-1.fc20 will be installed --> Processing Dependency: jta >= 1.0 for package: 1:mysql-connector-java-5.1.28-1.fc20.noarch --> Processing Dependency: slf4j for package: 1:mysql-connector-java-5.1.28-1.fc20.noarch --> Running transaction check ---> Package geronimo-jta.noarch 0:1.1.1-15.fc20 will be installed ---> Package slf4j.noarch 0:1.7.5-3.fc20 will be installed --> Processing Dependency: mvn(log4j:log4j) for package: slf4j-1.7.5-3.fc20.noarch --> Processing Dependency: mvn(javassist:javassist) for package: slf4j-1.7.5-3.fc20.noarch --> Processing Dependency: mvn(commons-logging:commons-logging) for package: slf4j-1.7.5-3.fc20.noarch --> Processing Dependency: mvn(commons-lang:commons-lang) for package: slf4j-1.7.5-3.fc20.noarch --> Processing Dependency: mvn(ch.qos.cal10n:cal10n-api) for package: slf4j-1.7.5-3.fc20.noarch --> Running transaction check ---> Package apache-commons-lang.noarch 0:2.6-13.fc20 will be installed ---> Package apache-commons-logging.noarch 0:1.1.3-8.fc20 will be installed --> Processing Dependency: mvn(logkit:logkit) for package: apache-commons-logging-1.1.3-8.fc20.noarch --> Processing Dependency: mvn(avalon-framework:avalon-framework-api) for package: apache-commons-logging-1.1.3-8.fc20.noarch ---> Package cal10n.noarch 0:0.7.7-3.fc20 will be installed ---> Package javassist.noarch 0:3.16.1-6.fc20 will be installed ---> Package log4j.noarch 0:1.2.17-14.fc20 will be installed --> Processing Dependency: mvn(org.apache.geronimo.specs:geronimo-jms_1.1_spec) for package: log4j-1.2.17-14.fc20.noarch --> Processing Dependency: mvn(javax.mail:mail) for package: log4j-1.2.17-14.fc20.noarch --> Running transaction check ---> Package avalon-framework.noarch 0:4.3-9.fc20 will be installed --> Processing Dependency: xalan-j2 for package: avalon-framework-4.3-9.fc20.noarch ---> Package avalon-logkit.noarch 0:2.1-13.fc20 will be installed --> Processing Dependency: tomcat-servlet-3.0-api for package: avalon-logkit-2.1-13.fc20.noarch ---> Package geronimo-jms.noarch 0:1.1.1-17.fc20 will be installed ---> Package javamail.noarch 0:1.5.0-6.fc20 will be installed --> Running transaction check ---> Package tomcat-servlet-3.0-api.noarch 0:7.0.52-2.fc20 will be installed ---> Package xalan-j2.noarch 0:2.7.1-22.fc20 will be installed --> Processing Dependency: xerces-j2 for package: xalan-j2-2.7.1-22.fc20.noarch --> Processing Dependency: osgi(org.apache.xerces) for package: xalan-j2-2.7.1-22.fc20.noarch --> Running transaction check ---> Package xerces-j2.noarch 0:2.11.0-17.fc20 will be installed --> Processing Dependency: xml-commons-resolver >= 1.2 for package: xerces-j2-2.11.0-17.fc20.noarch --> Processing Dependency: xml-commons-apis >= 1.4.01 for package: xerces-j2-2.11.0-17.fc20.noarch --> Processing Dependency: osgi(org.apache.xml.resolver) for package: xerces-j2-2.11.0-17.fc20.noarch --> Processing Dependency: osgi(javax.xml) for package: xerces-j2-2.11.0-17.fc20.noarch --> Running transaction check ---> Package xml-commons-apis.noarch 0:1.4.01-14.fc20 will be installed ---> Package xml-commons-resolver.noarch 0:1.2-14.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: mysql-connector-java noarch 1:5.1.28-1.fc20 updates 1.3 M Installing for dependencies: apache-commons-lang noarch 2.6-13.fc20 fedora 281 k apache-commons-logging noarch 1.1.3-8.fc20 updates 78 k avalon-framework noarch 4.3-9.fc20 fedora 87 k avalon-logkit noarch 2.1-13.fc20 fedora 87 k cal10n noarch 0.7.7-3.fc20 fedora 37 k geronimo-jms noarch 1.1.1-17.fc20 fedora 32 k geronimo-jta noarch 1.1.1-15.fc20 fedora 21 k javamail noarch 1.5.0-6.fc20 fedora 606 k javassist noarch 3.16.1-6.fc20 fedora 626 k log4j noarch 1.2.17-14.fc20 fedora 449 k slf4j noarch 1.7.5-3.fc20 fedora 173 k tomcat-servlet-3.0-api noarch 7.0.52-2.fc20 updates 207 k xalan-j2 noarch 2.7.1-22.fc20 updates 1.9 M xerces-j2 noarch 2.11.0-17.fc20 updates 1.1 M xml-commons-apis noarch 1.4.01-14.fc20 fedora 227 k xml-commons-resolver noarch 1.2-14.fc20 fedora 108 k Transaction Summary ================================================================================ Install 1 Package (+16 Dependent packages) Total download size: 7.3 M Installed size: 10 M Downloading packages: (1/17): apache-commons-logging-1.1.3-8.fc20.noarch.rpm | 78 kB 00:00 (2/17): apache-commons-lang-2.6-13.fc20.noarch.rpm | 281 kB 00:00 (3/17): avalon-framework-4.3-9.fc20.noarch.rpm | 87 kB 00:00 (4/17): avalon-logkit-2.1-13.fc20.noarch.rpm | 87 kB 00:00 (5/17): cal10n-0.7.7-3.fc20.noarch.rpm | 37 kB 00:00 (6/17): geronimo-jms-1.1.1-17.fc20.noarch.rpm | 32 kB 00:00 (7/17): geronimo-jta-1.1.1-15.fc20.noarch.rpm | 21 kB 00:00 (8/17): javamail-1.5.0-6.fc20.noarch.rpm | 606 kB 00:00 (9/17): javassist-3.16.1-6.fc20.noarch.rpm | 626 kB 00:00 (10/17): log4j-1.2.17-14.fc20.noarch.rpm | 449 kB 00:00 (11/17): slf4j-1.7.5-3.fc20.noarch.rpm | 173 kB 00:00 (12/17): mysql-connector-java-5.1.28-1.fc20.noarch.rpm | 1.3 MB 00:01 (13/17): tomcat-servlet-3.0-api-7.0.52-2.fc20.noarch.rpm | 207 kB 00:00 (14/17): xalan-j2-2.7.1-22.fc20.noarch.rpm | 1.9 MB 00:00 (15/17): xerces-j2-2.11.0-17.fc20.noarch.rpm | 1.1 MB 00:00 (16/17): xml-commons-apis-1.4.01-14.fc20.noarch.rpm | 227 kB 00:00 (17/17): xml-commons-resolver-1.2-14.fc20.noarch.rpm | 108 kB 00:00 -------------------------------------------------------------------------------- Total 1.3 MB/s | 7.3 MB 00:05 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : xml-commons-apis-1.4.01-14.fc20.noarch 1/17 Installing : geronimo-jms-1.1.1-17.fc20.noarch 2/17 Installing : xml-commons-resolver-1.2-14.fc20.noarch 3/17 Installing : xerces-j2-2.11.0-17.fc20.noarch 4/17 Installing : xalan-j2-2.7.1-22.fc20.noarch 5/17 Installing : javamail-1.5.0-6.fc20.noarch 6/17 Installing : log4j-1.2.17-14.fc20.noarch 7/17 Installing : tomcat-servlet-3.0-api-7.0.52-2.fc20.noarch 8/17 Installing : avalon-framework-4.3-9.fc20.noarch 9/17 Installing : avalon-logkit-2.1-13.fc20.noarch 10/17 Installing : apache-commons-logging-1.1.3-8.fc20.noarch 11/17 Installing : javassist-3.16.1-6.fc20.noarch 12/17 Installing : cal10n-0.7.7-3.fc20.noarch 13/17 Installing : apache-commons-lang-2.6-13.fc20.noarch 14/17 Installing : slf4j-1.7.5-3.fc20.noarch 15/17 Installing : geronimo-jta-1.1.1-15.fc20.noarch 16/17 Installing : 1:mysql-connector-java-5.1.28-1.fc20.noarch 17/17 Verifying : geronimo-jta-1.1.1-15.fc20.noarch 1/17 Verifying : geronimo-jms-1.1.1-17.fc20.noarch 2/17 Verifying : xalan-j2-2.7.1-22.fc20.noarch 3/17 Verifying : apache-commons-lang-2.6-13.fc20.noarch 4/17 Verifying : slf4j-1.7.5-3.fc20.noarch 5/17 Verifying : log4j-1.2.17-14.fc20.noarch 6/17 Verifying : avalon-framework-4.3-9.fc20.noarch 7/17 Verifying : xerces-j2-2.11.0-17.fc20.noarch 8/17 Verifying : cal10n-0.7.7-3.fc20.noarch 9/17 Verifying : avalon-logkit-2.1-13.fc20.noarch 10/17 Verifying : 1:mysql-connector-java-5.1.28-1.fc20.noarch 11/17 Verifying : xml-commons-resolver-1.2-14.fc20.noarch 12/17 Verifying : xml-commons-apis-1.4.01-14.fc20.noarch 13/17 Verifying : javassist-3.16.1-6.fc20.noarch 14/17 Verifying : tomcat-servlet-3.0-api-7.0.52-2.fc20.noarch 15/17 Verifying : javamail-1.5.0-6.fc20.noarch 16/17 Verifying : apache-commons-logging-1.1.3-8.fc20.noarch 17/17 Installed: mysql-connector-java.noarch 1:5.1.28-1.fc20 Dependency Installed: apache-commons-lang.noarch 0:2.6-13.fc20 apache-commons-logging.noarch 0:1.1.3-8.fc20 avalon-framework.noarch 0:4.3-9.fc20 avalon-logkit.noarch 0:2.1-13.fc20 cal10n.noarch 0:0.7.7-3.fc20 geronimo-jms.noarch 0:1.1.1-17.fc20 geronimo-jta.noarch 0:1.1.1-15.fc20 javamail.noarch 0:1.5.0-6.fc20 javassist.noarch 0:3.16.1-6.fc20 log4j.noarch 0:1.2.17-14.fc20 slf4j.noarch 0:1.7.5-3.fc20 tomcat-servlet-3.0-api.noarch 0:7.0.52-2.fc20 xalan-j2.noarch 0:2.7.1-22.fc20 xerces-j2.noarch 0:2.11.0-17.fc20 xml-commons-apis.noarch 0:1.4.01-14.fc20 xml-commons-resolver.noarch 0:1.2-14.fc20 Complete! |
I must write too much Java code for the Windows platform because I didn’t notice the change in how the DriverManager should be instantiated. Initially, I wrote the program using the following declaration for the DriverManager class:
30 | DriverManager.registerDriver(new com.mysql.jdbc.Driver()); |
While it worked on Windows, the same syntax in the MySQL.java program raised two errors on the Linux server. One for the declaration of the com.mysql.jdbc.Driver class and another trying to declare an instance of Driver class.
These are the two errors:
MySQL.java:5: error: package com.mysql.jdbc does not exist import com.mysql.jdbc.Driver; ^ MySQL.java:31: error: package com.mysql.jdbc does not exist DriverManager.registerDriver(new com.mysql.jdbc.Driver()); ^ |
I rewrote the MySQL.java program as follows, and it works on both implementations:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | // Import classes. import java.sql.*; /* You can't include the following on Linux without raising an exception. */ // import com.mysql.jdbc.Driver; public class MySQL { public MySQL() { /* Declare variables that require explicit assignments because they're addressed in the finally block. */ Connection conn = null; Statement stmt = null; ResultSet rset = null; /* Declare other variables. */ String url; String username = "student"; String password = "student"; String database = "studentdb"; String hostname = "localhost"; String port = "3306"; String sql; /* Attempt a connection. */ try { // Set URL. url = "jdbc:mysql://" + hostname + ":" + port + "/" + database; // Create instance of MySQL. Class.forName ("com.mysql.jdbc.Driver").newInstance(); conn = DriverManager.getConnection (url, username, password); // Query the version of the database. sql = "SELECT version()"; stmt = conn.createStatement(); rset = stmt.executeQuery(sql); System.out.println ("Database connection established"); // Read row returns for one column. while (rset.next()) { System.out.println("MySQL Version [" + rset.getString(1) + "]"); } } catch (SQLException e) { System.err.println ("Cannot connect to database server:"); System.out.println(e.getMessage()); } catch (ClassNotFoundException e) { System.err.println ("Cannot connect to database server:"); System.out.println(e.getMessage()); } catch (InstantiationException e) { System.err.println ("Cannot connect to database server:"); System.out.println(e.getMessage()); } catch (IllegalAccessException e) { System.err.println ("Cannot connect to database server:"); System.out.println(e.getMessage()); } finally { if (conn != null) { try { rset.close(); stmt.close(); conn.close(); System.out.println ("Database connection terminated"); } catch (Exception e) { /* ignore close errors */ } } } } /* Unit test. */ public static void main(String args[]) { new MySQL(); } } |
The old approach to the DriverManager and Driver classes disallows the use of three of the exceptions in the sample code: ClassNotFoundException, InstantiationException, and IllegalAccessException classes. The new syntax works on Linux, Mac OS X, and Windows. If you’re running on Mac OS X, you need to import the following additional library in the MySQL.java program:
import com.apple.eawt.*; |
Before you compile the MySQL.java program, you need to put the mysql-connector-java.jar and your present working directory into your environment’s $CLASSPATH variable. You can set the $CLASSPATH variable at the command-line or embed the following in your .bashrc file:
export CLASSPATH=/usr/share/java/mysql-connector-java.jar:. |
If you embedded it in the .bashrc file, you need to source that file or restart your terminal session, which resources the .bashrc for you. You can source your .bashrc file from an active Terminal session in your home directory with this syntax:
. ./.bashrc |
If you’re new to Java and the MySQL Connector/J, you compile the MySQL.java program with the following syntax. At least, it works when you have the MySQL.java source file in the present working directory and want to create the class file in the same directory. You can find more about the javac command-line at the www.tutorialpoint.com web site>
javac -verbose -cp . MySQL.java |
Then, you can run it with the class file with this syntax:
java MySQL |
It should return the following:
Database connection established MySQL Version [5.6.24] Database connection terminated |
If you’d prefer to return data, you can replace line 34 in the MySQL.java program with a query against a table, like:
34 | sql = "SELECT item_title, item_rating FROM item"; |
Then, change line 42 in the MySQL.java program with syntax to manage the output, like:
42 | System.out.println(rset.getString(1) + ", " + rset.getString(2)); } |
Recompile it, and rerun the MySQL class file with this syntax:
java MySQL |
It should return the following:
Database connection established The Hunt for Red October, PG Star Wars I, PG Star Wars II, PG Star Wars II, PG Star Wars III, PG13 The Chronicles of Narnia, PG RoboCop, Mature Pirates of the Caribbean, Teen The Chronicles of Narnia, Everyone MarioKart, Everyone Splinter Cell, Teen Need for Speed, Everyone The DaVinci Code, Teen Cars, Everyone Beau Geste, PG I Remember Mama, NR Tora! Tora! Tora!, G A Man for All Seasons, G Hook, PG Around the World in 80 Days, G Harry Potter and the Chamber of Secrets, PG Camelot, G Database connection terminated |
As always, I hope this helps those looking for a solution.
Subscribe via RSS