JDBC MySQL

Coding

Java

JDBC

Install

Connector/J download

As 'root'...

cd /u01/img/jdbc

rpm -i mysql-connector-java-8.0.11-1.el7.noarch.rpm

This warning appears to be ignorable...

warning: mysql-connector-java-8.0.11-1.el7.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Use rpm to view installed files... the .jar file is the important one...

rpm -ql mysql-connector-java

/usr/share/java/mysql-connector-java-8.0.11.jar

Add the jar file location to the CLASSPATH environment variable

export CLASSPATH=$CLASSPATH:/usr/share/java/mysql-connector-java-8.0.11.jar

Example Code

This code retrieves information about MySQL databases and inserts into an Oracle database...

//=====================================================================// Name: cmdbMySQLInstance// Version: 00.01.00//// NOTE: DB_SIZE doesn't currently work properly////=====================================================================//import java.sql.*;
public class cmdbMySQLInstance{ public static void main (String args []) throws SQLException { System.out.println("========================================================="); System.out.println("cmdbMySQLInstance - Version 00.01.00"); System.out.println("=========================================================");
// Register Oracle JDBC Driver (for use later) DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
// Oracle connection String connects = "jdbc:oracle:thin:@ORAhostname:1523:ORAService";
// Create variables for MySQL connection string String url = "jdbc:mysql://MYSQLhostname/information_schema"; String username = "myuser"; String password = "*********";
// Declare the JDBC objects. Connection con = null; Statement stmt = null; ResultSet rset = null;
try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection(url, username, password); System.out.println("Database connected!");
// Create and execute an SQL statement that returns some data String SQL = "SELECT @@hostname, \n"+ " @@port, \n"+ " SUBSTR(@@version_comment,1,INSTR(@@version_comment,' ')), \n"+ " SUBSTR(@@version,INSTR(@@version,'-')+1,30), \n"+ " SUBSTR(@@version,1,INSTR(@@version,'-')-1), \n"+ " ROUND(SUM(data_length + index_length)) \n"+ "FROM information_schema.tables";
stmt=con.createStatement(); rset=stmt.executeQuery(SQL);
while(rset.next()) { String rhostnam = rset.getString(1); String rinstanc = rset.getString(2); String rvendorx = "Oracle"; String rproduct = rset.getString(3); String redition = rset.getString(4); String rversion = rset.getString(5); String rdb_role = "n/a"; String rdb_size = rset.getString(6);
// Insert data into Oracle CMDB Connection conncmdb = DriverManager.getConnection (connects,"oraUser","oraPassword");
CallableStatement stmtcmdb = null; try { stmtcmdb = conncmdb.prepareCall ("{call load_instance(?,?,?,?,?,?,?,?)}"); stmtcmdb.setString (1, rhostnam); stmtcmdb.setString (2, rinstanc); stmtcmdb.setString (3, rvendorx); stmtcmdb.setString (4, rproduct); stmtcmdb.setString (5, redition); stmtcmdb.setString (6, rversion); stmtcmdb.setString (7, rdb_role); stmtcmdb.setString (8, rdb_size); stmtcmdb.execute(); } finally{ if (stmtcmdb!=null) stmtcmdb.close(); } // end of finally } // end of while loop } catch (Exception e) { e.printStackTrace(); } finally { if (rset != null) try { rset.close(); } catch(Exception e) {} if (stmt != null) try { stmt.close(); } catch(Exception e) {} if (con != null) try { con.close(); } catch(Exception e) {} } // end of finally } // end of main} // end of class

Bibliography

https://dev.mysql.com/downloads/connector/j/https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-installing.htmlhttps://dev.mysql.com/doc/connector-j/8.0/en/connector-j-installing-classpath.htmlhttps://www.javatpoint.com/example-to-connect-to-the-mysql-databasehttps://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-connect-drivermanager.htmlhttps://stackoverflow.com/questions/2839321/connect-java-to-a-mysql-database