how to connect to a mysql database through eclipse

Question

I have an application running in eclipse kepler my database is on the AWS RDS MySQL type. I have imported : mysql-connector-java-gpl-5.1.31-.msi and aws-java-sdk-1.8.5.jar

I want to perform operations such as to create a new table, or insert update data etc on my RDS instance through java code, but AWS RDS hasnt provided APIs for the same. So I have been trying the following way :

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.DriverManager;
import com.amazonaws.AmazonClientException;
import com.amazonaws.auth.AWSCredentials;
import com.amazonaws.auth.profile.ProfileCredentialsProvider;
import com.amazonaws.regions.Region;
import com.amazonaws.regions.Regions;
import com.amazonaws.services.rds.AmazonRDSClient;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class try_AWS_RDS {

static AmazonRDSClient client;

private static void init() throws Exception {

AWSCredentials credentials = null;
try {
    credentials = new ProfileCredentialsProvider("default").getCredentials();
} catch (Exception e) {
    throw new AmazonClientException(
            "Cannot load the credentials from the credential profiles file.";
}    
client = new AmazonRDSClient(credentials);
Region r = Region.getRegion(myRegion);
client.setRegion(r);    
}
public static void main(String[] args) throws Exception {

    init();

    String s = new String();  
    StringBuffer sb = new StringBuffer(); 

    try
    {
    Class.forName("com.mysql.jdbc.Driver");

    String userName = "myUserName";
    String password = "myPassword";
    String url = "jdbc:mysql://myDataBaseURL";

    Connection c = (Connection) DriverManager.getConnection(url, userName, password);  
    Statement st = (Statement) c.createStatement();  

    FileReader fr = new FileReader(new File("C:/Users/.../src/createTable.sql"));
    BufferedReader br = new BufferedReader(fr);  

    while((s = br.readLine()) != null)  
    {  
        sb.append(s);  
    }  
    br.close();  

    // here is our splitter ! We use ";" as a delimiter for each request  
    // then we are sure to have well formed statements  
    String[] inst = sb.toString().split(";");          

    for(int i = 0; i<inst.length; i++)  
    {  
        // we ensure that there is no spaces before or after the request string  
        // in order to not execute empty statements  
        if(!inst[i].trim().equals(""))  
        {  
            st.executeUpdate(inst[i]);  
            System.out.println(">>"+inst[i]);  
        }  
    } 


} catch (Exception e)
{
    e.printStackTrace();
}
}
}

it is showing me the following error :

com.mysql.jdbc.JDBC4ResultSet@7baf7d com.mysql.jdbc.JDBC4ResultSet@7baf7d java.io.FileNotFoundException: mySQLFile.sql (The system cannot find the file specified) at java.io.FileInputStream.open(Native Method)com.mysql.jdbc.JDBC4ResultSet@7baf7d

at java.io.FileInputStream.<init>(Unknown Source)
at java.io.FileReader.<init>(Unknown Source)
at try1_S3Sample.main(try1_S3Sample.java:96)

please help. Also suggest if there's any other way to access/update the tables in AWS RDS

Answer 1

It's most likely not finding C:/Users/.../src/createTable.sql.

Answer 2

You are messing everything. No need to have aws-java-sdk-1.8.5.jar imported. This is aws-sdk java jar. For connecting to the RDS instance having MySql database. All you need is to import mysql-connector-java-5.1.18-bin.jar and have the access and credentials for the database launched in the RDS. Get the permission to connect to the database from your IP(Machine where code will run) And this code will connect to the database and you can execute the queries based on your credentials access.

public static void main(String[] args) 
{
    try{
        String host = "jdbc:mysql://rds-ip:3306/rds-database-name";
        String uName = "rds-database-username";
        String uPass = "rds-database-pasword";
        Connection con = DriverManager.getConnection( host, uName, uPass );
        Statement stmt = con.createStatement();
        String sql = "SELECT * FROM tablename";
        ResultSet rs = stmt.executeQuery(sql);

        rs.next();
        int id_col = rs.getInt("id");
        String column1 = rs.getString("column1");
        String column2 = rs.getString("column2");
         }
         catch(SQLException err){
        System.out.println(err.getMessage());
    }
}

Answer 3

Because the error is just a FileNotFoundException, to clarify if it is just a problem with the file path (capital letters, backslash/slash ...) try first to put your file in the root like C:\file.sql