Java and Compose for MySQL

To connect with Java, first download and install MySQL's Connector/J JDBC driver.

You can then connect to a deployment using the following code:

import java.sql.*;

public class Main {

    public static void main(String[] args) {

       System.setProperty("javax.net.ssl.trustStore","/home/project/truststore");
        System.setProperty("javax.net.ssl.trustStorePassword","somepass");

       String user = "admin";
       String password = "mypass";
       String URL = "jdbc:mysql://aws-us-east-1-portal.23.dblayer.com:15942/compose" +

                "?verifyServerCertificate=true"+
                "&useSSL=true" +
                "&requireSSL=true";
        try {

            Connection conn = DriverManager.getConnection(URL, user, password);

            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SHOW DATABASES");

            while (rs.next()) {
                String dbname = rs.getString("Database");
                System.out.format("%s\n", dbname);
            }

            st.close();

        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("Failed");
        }
    }
}

Notes

• The basic information to set up a connection is within the connection string. We use that with the JDBC API and store that string in the variable URL:
• The username and password for the deployment are stored in separate variables, which are used to establish a connection with the JDBC DriverManager.getConnection() method.
• The connection string includes the host name, port, and database; the SSL options are appended to the connection string.
• The sample establishes an SSL connection, which is verified using the self-signed certificate. You can connect to the server without these options, but the connection would be insecure and would result in a warning from MySQL:

"Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default..."

• To allow the driver to process the certificate, the credentials are saved in a file called cert.pem. The certificate cannot be used directly. Instead, create a trust store to hold the certificate's credentials using keytool, The following command imports the cert.pem file, assigns it the alias composeCert, and defines the output file as a keystore called truststore. It uses the password henrythedog to unlock the keystore.

keytool -import --alias composeCert -file ./cert.pem -keystore ./truststore -storepass henrythedog

• The setProperty method on javax.net.ssl.trustStore and javax.net.ssl.trustStorePassword uses the information from the keystore. The system properties should be set towards the top of the program to run early at runtime; preferably before the Connection variable is set, so that they're locked in before starting an SSL connection.
• After creating the connection, a Statement object is returned and the query is executed against the object. This returns a ResultSet which is used to iterate through and print the database names.

Still Need Help?

If this article didn't solve things, summon a human and get some help!