Executing java program on redshift

Question

For example I have coded the following php code and run on redshift like below.

<?php

$connect = pg_connect("host=xx.xxxx.us-east-1.redshift.amazonaws.com port=xxxx dbname=testdb user=xxxx password=xxxxxx");

$query = "select name, email from mytable LIMIT 7";
$result = pg_query($connect,$query);
while(($row = pg_fetch_array($result)) != null) {
        $name = $row[0];
        $email = $row[1];

        echo $name." <<>> ".$email." <<>> ";
}
?>

I saved the above php file and executed in the below way.

myuser@ip-20-143-43-144:/home/myuser$ php runquery.php     <Enter>

Then it gives me the output.

I want to test the same thing with java. So I took the example from the aws and saved in the same location.

public class ConnectToCluster {

    static final String dbURL = "jdbc:postgresql://xxxx.xxxxx.us-east-1.redshift.amazonaws.com:xxxx/testdb";
    static final String MasterUsername = "XXXX";
    static final String MasterUserPassword = "XXXXXX";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try{
           //Dynamically load postgresql driver at runtime.
           Class.forName("org.postgresql.Driver");

           //Open a connection and define properties.
           System.out.println("Connecting to database...");
           Properties props = new Properties();

           //Uncomment the following line if using a keystore.
           //props.setProperty("ssl", "true");  
           props.setProperty("user", MasterUsername);
           props.setProperty("password", MasterUserPassword);
           conn = DriverManager.getConnection(dbURL, props);

           //Try a simple query.
           System.out.println("Listing system tables...");
           stmt = conn.createStatement();
           String sql;
           sql = "select * from information_schema.tables;";
           ResultSet rs = stmt.executeQuery(sql);

           //Get the data from the result set.
           while(rs.next()){
              //Retrieve two columns.
              String catalog = rs.getString("table_catalog");
              String name = rs.getString("table_name");

              //Display values.
              System.out.print("Catalog: " + catalog);
              System.out.println(", Name: " + name);
           }
           rs.close();
           stmt.close();
           conn.close();
        }catch(Exception ex){
           //For convenience, handle all errors here.
           ex.printStackTrace();
        }finally{
           //Finally block to close resources.
           try{
              if(stmt!=null)
                 stmt.close();
           }catch(Exception ex){
           }// nothing we can do
           try{
              if(conn!=null)
                 conn.close();
           }catch(Exception ex){
              ex.printStackTrace();
           }
        }
        System.out.println("Finished connectivity test.");
     }
 }

I saved this .java file too in the same location. So, can someone please tell me how to run this java file on command as I did for php.

This isn't really an redshift related question. What you are asking is "How to run Java program in command prompt" — Tomasz Tybulewicz
– Tomasz Tybulewicz, Commented Jan 20, 2014 at 12:51

Masashi M · Accepted Answer · 2014-01-20 21:24:08Z

1

(1) Download a postgresql JDBC driver from the following link and put it in the same directory of ConnectToCluster.java.

http://jdbc.postgresql.org/download/postgresql-8.4-703.jdbc4.jar

(2) Add the missing lines(import java library) in the head of the java file.

 import java.sql.*;
 import java.util.*;

 public class ConnectToCluster {
 ...

(3) Run the following command.

 javac ConnectToCluster.java && java -cp .:postgresql-8.4-703.jdbc4.jar ConnectToCluster

answered Jan 20, 2014 at 21:24

Masashi M

2,76723 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rick Over a year ago

Thank you so much for your answer. Do I need to compile every time? I want this program to be run everyday at particular time, I heard that cronjob would do this. If you could suggest on this, I would be thankful.

Masashi M Over a year ago

No, once you compiled the java file, you don't need to compile it every time. Speaking of cronjob, you need to change the classpath(cp option) to use the absolute path like "-cp /path/to/your-java-file-dir/:/path/to/your-java-file-dir/postgresql-8.4-703.jdbc4.jar" because the current directory of cronjob is different.

rick Over a year ago

Okay, thanks. I have one more jar file to be included in that project, do I need to use comma as separator and continue? Like -cp.:postgresql-8.4-703.jdbc4.jar, anotherjarfile.jar

rick Over a year ago

I got it worked by compiling and executing it in two separate commands. Like javac -cp.:one.jar:two.jar and java -cp.:one.jar:two.jar

Collectives™ on Stack Overflow

Executing java program on redshift

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related