Problem scenario
You have a MySQL database. Rather than use a SQL front-end application, you want to write a Java program that will run a SQL statement against the database. What should you do?
Solution
1. Install the Java compiler if it is not already installed. If you need assistance, see this posting.
2. Run this command:curl -Lk https://www.javatpoint.com/src/jdbc/mysql-connector.jar > /tmp/mysql-connector.jar
3. Then run this command: sudo find / -name ext | grep jre
4. Place the mysql-connector.jar in the directory found above (e.g., with sudo mv).
5. Set the CLASSPATH variable in two steps. First set it to where the .class file is for the Java file you are running. If the .class file of the program you are running is in /home/jdoe/javaprograms run this command: export CLASSPATH=/home/jdoe/javaprograms
6. Draft this command but substitute "/path/to/mysql-connector.jar" with the path referred to in step #4.
export CLASSPATH=$CLASSPATH:/path/to/mysql-connector.jar
7. Create a .java program with the following content:
import java.sql.*;
class MysqlCon{
public static void main(String args[]){
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
"jdbc:mysql://auroraendpoint-us-west-2b.abcd1234.us-west-2.rds.amazonaws.com","jdoe","password");
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("show databases;");
while(rs.next())
System.out.println(rs.getString(1) );
con.close();
}catch(Exception e){ System.out.println(e);}
8.a. Replace "auroraendpoint-us-west-2b.abcd1234.us-west-2.rds.amazonaws.com" with your Aurora MySQL database endpoint. It can be found in the AWS web console or with the AWS CLI. (If you need assistance installing and configuring the AWS CLI, see this posting.) With the AWS CLI, you can run this command to find it:
aws rds describe-db-instances | grep Address
(Unlike the traditional JDBC string for MySQL, the final word is not the database name if, and only if, you are using Aurora MySQL.) If you are not using an Aurora MySQL database, you will want to follow this format (for replacing "auroraendpoint-us-west-2b.abcd1234.us-west-2.rds.amazonaws.com"): "FQDN_of_host/database_name"
8.b. Replace "jdoe" with your MySQL database username.
8.c. Replace "password" with your MySQL database password.
9. Compile the program: javac MysqlCon.java
10. Run the program: java MysqlCon