Beginner’s Guide to E.T.L (Extract, Transform and Load) – Connection Set-Up

Connection Setup for connection type: PostgreSQL

[We are now setting up a connection to A database i.e if your source is a database]
There are 3 ways of access provided for Connections using PostgreSQL
a. Native (JBDC)
b. ODBC
c. JNDI

a. Native (JBDC)
I. Enter the Connection Name: Anyname
II. Select the connection type: PostgreSQL
III. Host Name: localhost [This can be an ip address]
Database Name: LearnETL[Name of the database you are using]
Port Number: 5432 or your required Port Number
User Name: Your database user name
Password: Your database password
IV. Test Connections and OK.

b. JNDI
Here we need to go to the data-integration folder and open up the sub-folder “simple-jndi” and edit jdbc.properties

Here we need to write the following code:

ETL_Connection/type=javax.sql.DataSource
ETL_Connection/driver=org.postgresql.Driver
ETL_Connection/url=jdbc:postgresql://localhost:5432/LearnETL
ETL_Connection/user=postgres
ETL_Connection/password=postgres

ETL_Connection: name of the connection
localhost:5432/LearnETL: localhost is the host name, 5432 is the port number and LearnETL is the Database name.
user: username
password: Password

Save and back to the Database connection
Restart your PDI.

and in the Setup, select JNDI and for
JNDI Name : name of your connection [ETL_Connection]

c. ODBC

This is not commonly used but what will be needed

are as follows:

1.Install the PostgreSQL ODBC driver which can be downloaded.
2.Select the PostgreSQL ODBC Unicode and then
3. Setup
enter Data source Source, Name, Server, Username, Password and Port. Test and Save if Connection is OK.!
4. a bundle of JAR files to be copied in your Java folder as this ODBC bundle files has been discontinued in JAVA 8 Bridge

Thank You
Sohail Izebhijie

Cancelling long running queries using Jdbc API

Cancelling long running queries using Jdbc API

In this blog, I will share my experimentation with query cancellation timer. The objective is to cancel those queries which take a lot of time to execute.

The Jdbc API provides Statement.cancel() method to cancel the statement being run. But, this method should be
called from a different thread than the one that is executing the SQL query. In order to call that method we need a reference to the statement object also.

So, I have made a sample code that uses the Java Concurrency API to achieve the task.

The java concurrent API ExecutorService can be used to execute a task in a new thread. When we submit a task like a Runnable or Callable to the ExecutorService, it returns a Future object. The Future object can be used to get the results from the other thread.

Here in this example, ExecutorService is a single thread executor. The object QueryExecutor is a Callable, which means it is nothing but a Runnable that returns an object. Where as a Runnable doen’t return anything as void is the return type of run() method. QueryExecutor returns a JsonObject.

The queryExecutor object is submitted to the ExecutorService, which returns a Futureobject.

The future object has a get method that takes the timeout and the timeout unit. Upon timeout, the execution of the other thread will be stopped and the TimeoutExceptionoccurrs. Otherwise the return value will be obtained.

class JdbcService {
    private static final Logger logger = LoggerFactory.getLogger(JdbcService.class);

    public JsonObject query(@Nullable Connection connection, String sql) {
        if (connection == null || sql == null) {
            throw new IllegalArgumentException("Nopes! The connection object or sql is null");
        }
        if (logger.isDebugEnabled()) {
            logger.debug("JDBC query executor has started.");
        }

        long now = System.currentTimeMillis();
        Statement statement = null;
        ExecutorService executor = Executors.newSingleThreadExecutor();
        Future<JsonObject> future = null;
        try {
            statement = connection.createStatement();
            int jdbcQueryCancellationTime = Configurations.getCancellationTime();

            QueryExecutor queryExecutor = new QueryExecutor(statement, sql);

            //Submit the callable to the executor. Start the thread
            future = executor.submit(jdbcQueryExecutor);

            //Wait for specified time limit. Otherwise cancel statement
            JsonObject queryResult = future.get(jdbcQueryCancellationTime, TimeUnit.SECONDS);
            executor.shutdown();

            long time = System.currentTimeMillis();

            if (logger.isInfoEnabled()) {
                logger.info(String.format("Execution time taken for the query %s is %s milliseconds", sql,
                        (time - now)));
            }

            return queryResult;
        } catch (SQLException | InterruptedException | ExecutionException ex) {
            cancelStatement(statement, ex);
            throw new QueryException("Failed to query the database.", ex);
        } catch (TimeoutException ex) {
            cancelStatement(statement, ex);
            future.cancel(true);
            throw new JdbcQueryTimeoutException("Timeout exception occurred. Increase the timeout limit.");
        } finally {
            DbUtils.closeQuietly(connection);
            DbUtils.closeQuietly(statement);
            if (!executor.isTerminated()) {
                logger.error("Forcefully shutting down the executor");
            }

            executor.shutdownNow();

            if (logger.isDebugEnabled()) {
                logger.debug("Successfully shutdown the jdbc query executor service");
            }
        }
    }

    private void cancelStatement(Statement statement, Exception ex) {
        logger.error("Cancelling the current query statement. Timeout occurred.", ex);
        try {
            statement.cancel();
        } catch (SQLException exception) {
            logger.error("Calling cancel() on the Statement issued exception. Details are: ", exception);
        }
    }
}

One important caution is that upon TimeoutException the future.cancel(true) method should be called. And the executor should be shutdown once the task is complete.

Hope you understood the self explanatory code sample. In case of queries comment on this post so that I can reply.

Thank you for reading this post and wish you happy coding.

Rajasekhar
Java BI Developer
Helical IT Solutions

SPRING JDBC

While working with database using plain old JDBC, it becomes cumbersome to write unnecessary code to handle exceptions, opening and closing database connections etc. But Spring JDBC Framework takes care of all the low-level details starting from opening the connection, prepare and execute the SQL statement, process exceptions, handle transactions and finally close the connection.

 

Spring JDBC provides several approaches and correspondingly different classes to interface with the database

The Spring Framework’s JDBC abstraction framework consists of four different packages, namely

core

datasource

object

support

1.Core

org.springframework.jdbc.core.

SubPackage:

1.1 org.springframework.jdbc.core.simple

1.2 org.springframework.jdbc.core.namedparam

  1. Datasource

org.springframework.jdbc.datasource

SubPackage:

2.1 org.springfamework.jdbc.datasource.embedded

3.object

org.springframework.jdbc.object

4.support

org.springframework.jdbc.support

 

org.springframework.jdbc.core.

This package contains the JdbcTemplate class and its various callback interfaces, plus a variety of related classes.

It’s subpackage named org.springframework.jdbc.core.simple contains the SimpleJdbcInsert and SimpleJdbcCall classes. Another subpackage named org.springframework.jdbc.core.namedparam contains the NamedParameterJdbcTemplate class and the related support classes.

 

The org.springframework.jdbc.datasource package contains a utility class for easy DataSource access, and various simple DataSource implementations that can be used for testing and running unmodified JDBC code outside of a Java EE container. A subpackage named org.springfamework.jdbc.datasource.embedded provides support for creating in-memory database instances using Java database engines such as HSQL and H2.

 

Here, we are using the JdbcTemplate object in the EmployeeDao class, so we are passing it by the setter method but you can use constructor also.

<?xml version=”1.0″ encoding=”UTF-8″?>

<beans

xmlns=”http://www.springframework.org/schema/beans”

xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”

xmlns:p=”http://www.springframework.org/schema/p”

xsi:schemaLocation=”http://www.springframework.org/schema/beans

http://www.springframework.org/schema/beans/spring-beans-3.0.xsd”>

<bean id=”ds” class=”org.springframework.jdbc.datasource.DriverManagerDataSource”>

<property name=”driverClassName” value=”oracle.jdbc.driver.OracleDriver” />

<property name=”url” value=”jdbc:oracle:thin:@localhost:1521:xe” />

<property name=”username” value=”system” />

<property name=”password” value=”oracle” />

</bean>

<bean id=”jdbcTemplate” class=”org.springframework.jdbc.core.JdbcTemplate”>

<property name=”dataSource” ref=”ds”></property>

</bean>

<bean id=”edao” class=”com.helical.hdidao”>

<property name=”jdbcTemplate” ref=”jdbcTemplate”></property>

</bean>

</beans>

JDBC Connection Pooling in jaspersoft

How JDBC Resources and Connection Pools Work Together in Jasper Report’s server

 

JasperReports Server can access any database using the JDBC (Java DataBase Connectivity) API. We can configure two types of data sources in the repository:

  • JDBC data source – Establishes a direct connection to the database server using its JDBC driver. JasperReports Server configures and manages the connections to the database. By default, the maximum number of simultaneous connections for each data source is five
  • JNDI data source – Calls the JNDI (Java Naming and Directory Interface) service of the application server to locate a database connection. The application server manages the connections to the database and shares them between all applications that it hosts. The configuration of the application server determines the number of connections that are shared.

 

At runtime, here’s what happens when Jasper Report’s server connects to a database:

 

  1. The Jasper Server gets the JDBC resource (data source) associated with the database by making a call through the JNDI API.

Given the resource’s JNDI name, the naming and directory service locates the JDBC resource. Each JDBC resource specifies a connection pool.

  1. Via the JDBC resource, the Jasper Server gets a database connection.

Behind the scenes, the server retrieves a physical connection from the connection pool that corresponds to the database. The pool defines connection attributes such as the database name (URL), user name, and password.

  1. Now that it is connected to the database, the server can read the database.

The server access the database by making calls to the JDBC API. The JDBC driver translates the application’s JDBC calls into the protocol of the database server.

Server doesn’t have to validate every single time the server uses the connection, which improves performance. So when user runs a report, the server requests a connection from the pool. When the thread is finished using the connection, it returns it to the pool, so that it can be used by any other threads.

Once the report is rendered and user does not perform any action on report like passing parameter etc, then connection sits idle. JDBC Connection pooling enables the idle connection to be used by some other thread. When the user does not access the DB, the server automatically returns the connection to connection pool after certain time. Once it’s back in the pool, the connection is available.