# Code Samples

## Execute a Query

Code snippet to execute a query and get a result from e6data.

```java
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class Main {

   public static void main(String[] args) throws SQLException, ClassNotFoundException {

       Class.forName("io.e6.jdbc.driver.E6Driver");
       Properties properties = new Properties();
       properties.put("user", "<USER>"); // copy the username from e6data cluster connection info page in the e6data console
       properties.put("password", "<PASSWORD"); // password is the Personal Access Token (PAT) generated in the e6data cluster connector tab

       String connectStr = "jdbc:e6data://<HOSTNAME>:<PORT>/database=<DATABASE>&catalog=<CATALOG_NAME>&secure=true&cluster-uuid=<CLUSTER UUID>";
       Connection connection = DriverManager.getConnection(connectStr, properties);

       Statement statement = connection.createStatement();
       ResultSet resultSet = statement.executeQuery("Select current_timestamp");

       ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
       while (resultSet.next()) {
           int columnCount = resultSetMetaData.getColumnCount();
           List<String> currentLine = new ArrayList<>();
           for (int i = 1; i <= columnCount; i++) {
               Object object = resultSet.getObject(i);
               if (object == null) {
                   currentLine.add("null");
               } else {
                   currentLine.add(object.toString());
               }

           }
           String value = String.join(" | ", currentLine);
           System.out.println(value);
       }

       resultSet.close();
       statement.close();
       connection.close();
   }
}
```

## Get Query Time Metrics

The following code runs a query and returns the amount of time taken to execute the query in seconds.

```java
import org.apache.commons.lang3.time.StopWatch;
import java.sql.*;
import java.util.*;

public class JDBCQueryTime {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        Class.forName("io.e6.jdbc.driver.E6Driver");
        Properties properties = new Properties();
        properties.put("user", "<USER>"); // copy the username from e6data cluster connection info page in the e6data console
        properties.put("password", "<PASSWORD>"); // password is the Personal Access Token (PAT) generated in the e6data cluster connector tab

        String connectStr = "jdbc:e6data://<HOSTNAME>:<PORT>/database=<DATABASE>&catalog=<CATALOG_NAME>&secure=true&cluster-uuid=<CLUSTER UUID>";

        Connection connection = DriverManager.getConnection(connectStr, properties);

        Statement statement = connection.createStatement();
        StopWatch stopWatch = StopWatch.createStarted();
        ResultSet resultSet =  statement.executeQuery("<QUERY>"); // add the SQL query text here

        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        while (resultSet.next()) {
            int columnCount = resultSetMetaData.getColumnCount();
            List<String> currentLine = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                Object object = resultSet.getObject(i);
                if (object == null) {
                    currentLine.add("null");
                } else {
                    currentLine.add(object.toString());
                }

            }
            String value = String.join(" | ", currentLine);
            System.out.println(value);
        }
        stopWatch.stop();
        long TimeTaken = stopWatch.getTime();
        System.out.println("Time Taken in milliseconds: " + TimeTaken);

        resultSet.close();
        statement.close();
        connection.close();
    }
}
```

## Use of Timestamps <a href="#usage-of-timestamp" id="usage-of-timestamp"></a>

To use timestamps in queries that are run using the JDBC driver the timezone must be set to UTC. e.g.:

```java
 Timestamp ts_start = new Timestamp(
                ZonedDateTime.of(2020, 04, 07, 05, 26, 21, 0,
                        ZoneId.of("UTC")
                ).toInstant().toEpochMilli());
        Timestamp ts_end = new Timestamp(
                ZonedDateTime.of(2020, 04, 07, 05, 27, 07, 0,
                        ZoneId.of("UTC")
                ).toInstant().toEpochMilli());

        statement.setObject(1, ts_start);
        statement.setObject(2, ts_end);

        ResultSet resultSet = statement.executeQuery();
```

## Advanced Use Case - Execute a Parameterized Query

```java
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class Main {

   public static void main(String[] args) throws SQLException, ClassNotFoundException {

       Class.forName("io.e6.jdbc.driver.E6Driver");
       Properties properties = new Properties();
       properties.put("user", "<USER>"); // copy the username from e6data cluster connection info page in the e6data console
       properties.put("password", "<PASSWORD>"); // password is the Personal Access Token (PAT) generated in the e6data cluster connector tab

       String connectStr = "jdbc:e6data://<HOSTNAME>:<PORT>/database=<DATABASE>&catalog=<CATALOG_NAME>&secure=true&cluster-uuid=<CLUSTER UUID>"; // get the connection details from the e6data cluster connector tab
       Connection connection = DriverManager.getConnection(connectStr, properties);

       String sql = "select count(*) from date_dim where d_year between ? and ?";
       PreparedStatement statement = connection.prepareStatement(sql);

       statement.setObject(1, 2000);
       statement.setObject(2, 2001);

       ResultSet resultSet = statement.executeQuery();

       ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
       while (resultSet.next()) {
           int columnCount = resultSetMetaData.getColumnCount();
           List<String> currentLine = new ArrayList<>();
           for (int i = 1; i <= columnCount; i++) {
               Object object = resultSet.getObject(i);
               if (object == null) {
                   currentLine.add("null");
               } else {
                   currentLine.add(object.toString());
               }

           }
           String value = String.join(" | ", currentLine);
           System.out.println(value);
       }

       resultSet.close();
       statement.close();
       connection.close();
   }
}
```

## Auto Resume

To enable Auto Resume while establishing a connection using the JDBC Driver, include auto-resume=true as a parameter in the connection string. This ensures that the cluster automatically resumes when a query is executed, preventing the need for manual intervention.

{% hint style="info" %}
**Note:** Auto Resume must also be enabled at the cluster level for this setting to take effect. If Auto Resume is not enabled at the cluster level, the cluster will not resume automatically, even if the connection string includes the auto-resume=true parameter.
{% endhint %}

```
jdbc:e6data://<HOSTNAME>:<PORT>/database=<DATABASE>&catalog=<CATALOG_NAME>&secure=true&cluster-uuid=<CLUSTER_UUID>&auto-resume=true
```