Scriptella provides a simple way to perform various transformations in JavaScript (or other scripting language which have a corresponding driver).
Our example transformation consists of 3 steps:
1) Select rows from source table.
2) Transform a column value from number to text
3) Insert a transformed value into a destination table.

   1  
   2  <!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
   3  <etl>
   4      <connection id="db" driver="auto" url="jdbc:hsqldb:mem:tst" user="sa" password="" classpath="../lib/hsqldb.jar"/>
   5      <connection id="js" driver="script"/> 
   6      <connection id="log" driver="text"/> <!-- For printing debug information on the console -->
   7  
   8      <script connection-id="db">
   9          CREATE TABLE Table_In (
  10              Error_Code INT
  11          );
  12          CREATE TABLE Table_Out (
  13              Error VARCHAR(10)
  14          );
  15          
  16          INSERT INTO Table_IN VALUES (1);
  17          INSERT INTO Table_IN VALUES (7);
  18      </script>
  19  
  20      <query connection-id="db">
  21          SELECT * FROM Table_In
  22          <script connection-id="log">
  23              Transforming $Error_Code
  24          </script>
  25          <!-- Transformation is described as an enclosing query
  26           which is executed before nested elements -->
  27          <query connection-id="js"> 
  28              <![CDATA[
  29                 if (Error_Code < 5) {
  30                      Error_Code='WARNING'; //Set a transformed value
  31                 } else {
  32                      Error_Code='ERROR'; //Set a transformed value
  33                 }
  34                 query.next(); //Don't forget to trigger nested scripts execution
  35              ]]>
  36              <script connection-id="db">
  37                  <!-- Insert transformed value -->
  38                  INSERT INTO Table_Out VALUES (?Error_Code); 
  39              </script>
  40              <script connection-id="log">
  41                  Transformed to $Error_Code
  42              </script>
  43          </query>
  44      </query>
  45  </etl>

Scriptella ETL provides several ways to execute ETL files:

Invocation from Ant

   1  
   2  <taskdef resource="antscriptella.properties" classpath="/path/to/scriptella.jar[;additional_drivers.jar]"/>
   3  <etl file="path/to/etl/file/> <!-- Execute ETL file from specified location -->

Command-Line Execution
Just type scriptella to run the file named etl.xml in the current directory. Alternatively you can use Java launcher:

   1  
   2  java -jar scriptella.jar [arguments]

Executing ETL Files from Java
It is extremely easy to run Scriptella ETL files from java code. Just make sure scriptella.jar is on classpath and use any of the following methods to execute an ETL file:

   1  
   2  EtlExecutor.newExecutor(new File("etl.xml")).execute(); //Execute etl.xml file
   3  EtlExecutor.newExecutor(getClass().getResource("etl.xml")).execute(); //Execute etl.xml file loaded from classpath
   4  EtlExecutor.newExecutor(
   5      servletContext.getResource("/WEB-INF/etl.xml")).execute(); //Execute etl.xml file from web application WEB-INF dir

Integration with Spring Framework

   1  
   2  <beans>
   3      <!-- Spring beans declarations -->
   4  
   5      <!-- Spring managed bean which executes etl.xml file -->
   6      <bean id="executor" class="scriptella.driver.spring.EtlExecutorBean">
   7          <property name="configLocation" value="etl.xml"/>
   8      </bean>
   9  </beans>

The usage of executor is straightforward:

   1  
   2  EtlExecutor exec = (EtlExecutor) beanFactory.getBean("executor");
   3  exec.execute();

See Spring Driver JavaDoc for additional details.

The following code demonstrates how to produce CSV files with dynamic
file name pattern based on a current day. The produced files have the following naming format:
TABLE_NAME_MM_DD_YYYY.csv

   1  
   2  <!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
   3  <etl>
   4      <properties> <!-- Configure table name -->
   5          table_name=test
   6      </properties>
   7      <connection id="in" driver="auto" url="jdbc:oracle:thin:@localhost:1521:ORCL" 
   8        classpath="ojdbc14.jar" user="scott" password="tiger"/>
   9      <connection id="out" driver="csv" url="${table_name}_${etl.date.now('MM_dd_yyyy')}.csv" />
  10      <query connection-id="in"> <!-- Query table rows -->
  11          SELECT * FROM ${table_name}
  12          <script connection-id="out"> <!-- Export each row into a CSV -->
  13              $ID, $Name, $Surname <!-- Use column names from selected table -->
  14          </script>
  15      </query>
  16  </etl>

Use Scriptella ETL to run the example.

See How to execute an ETL file from command line, Ant or directly from Java .

The following example demonstrates how to split a large Scriptella ETL file into several parts. This example is based on a traditional XML parsed entities approach:

   1  
   2  <!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd"
   3  [
   4      <!-- Declaring the first external parsed entity to include -->
   5      <!ENTITY part1 SYSTEM "part1.xml">
   6      
   7      <!-- Declaring the second external parsed entity to include -->
   8      <!ENTITY part2 SYSTEM "part2.xml">
   9  ]>
  10  <etl>
  11      <connection driver="text"/>
  12  
  13      <!-- Including file #1 -->
  14      &part1;
  15  
  16      <script>
  17          content of the script
  18      </script>
  19      
  20      <!-- Including file #2 -->
  21      &part2;
  22  
  23  </etl>

The following code displays country names and ISO-codes for the locales supported by the JVM.

   1  
   2          for (Locale locale : Locale.getAvailableLocales()) {
   3              final String contry = locale.getDisplayCountry();
   4              if (contry.length() > 0) {
   5                  System.out.println("Country = " + contry + ". ISO code: " + locale.getISO3Country());
   6              }
   7          }

getClass().getPackage().getName() is not a reliable solution, because getPackage() can be null depending on a class loader.

   1  
   2  static final String PACKAGE_NAME = MyClass.class.getName().substring(0, 
   3              MyClass.class.getName().lastIndexOf('.'));

The following Scriptella ETL simple usage example imports RSS file into a database table.

   1  
   2  <!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
   3  <etl>
   4      <connection id="in" driver="xpath" url="http://snippets.dzone.com/rss"/>
   5      <connection id="db" driver="hsqldb" url="jdbc:hsqldb:db/rss" user="sa" classpath="hsqldb.jar"/>
   6  classpath="hsqldb.jar"/>
   7      <query connection-id="in">
   8          /rss/channel/item
   9          <script connection-id="db">
  10              INSERT INTO Rss (ID, Title, Description, Link) 
  11              VALUES (?rownum, ?title, ?description, ?link);
  12          </script>
  13      </query>
  14  </etl>

Here is the full version of the example described above. It creates an RSS table, downloads rss file, inserts rss records into a database, converts rss.xml to a plain text file and saves it to rss.txt.

   1  
   2  <!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
   3  <etl>
   4      <connection id="in" driver="xpath" url="http://snippets.dzone.com/rss"/>
   5      <connection id="out" driver="text" url="rss.txt"/>
   6      <connection id="db" driver="hsqldb" url="jdbc:hsqldb:db/rss" user="sa" classpath="hsqldb.jar"/>
   7      <script connection-id="db">
   8         CREATE TABLE Rss (
   9             ID Integer,
  10             Title VARCHAR(255),
  11             Description VARCHAR(255),   
  12             Link VARCHAR(255)
  13  
  14         )
  15      </script>
  16      <query connection-id="in">
  17          /rss/channel/item
  18          <script connection-id="out">
  19              Title: $title
  20              Description: [
  21              ${description.substring(0, 20)}...
  22              ]
  23              Link: $link
  24              ----------------------------------
  25          </script>
  26          <script connection-id="db">
  27              INSERT INTO Rss (ID, Title, Description, Link) 
  28              VALUES (?rownum, ?title, ?description, ?link);
  29          </script>
  30      </query>
  31  </etl>

Scriptella ETL allows inserting files into a database. This is achieved by a simple bind variables extension syntax ?{file ...}.
The following sample initializes table of music tracks. Each track has a DATA field containing a file loaded from an external location. File song1.mp3 is stored in the same directory as etl.xml and song2.mp3 is loaded from the web:

   1  
   2      <!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
   3      <etl>
   4          <connection driver="hsqldb" url="jdbc:hsqldb:file:tracks" user="sa" classpath="hsqldb.jar"/>
   5          <script>
   6              CREATE TABLE Track (
   7                ID INT,
   8                ALBUM_ID INT,
   9                NAME VARCHAR(100),
  10                DATA LONGVARBINARY
  11              );
  12              <!-- Inserts file with path relative to ETL script location -->
  13              INSERT INTO Track(id, album_id, name, data) VALUES
  14                     (1, 1, 'Song1.mp3', ?{file 'song1.mp3'});
  15              <!-- Inserts file from an external URL-->
  16              INSERT INTO Track(id, album_id, name, data) VALUES
  17                     (2, 2, 'Song2.mp3', ?{file 'http://musicstoresample.com/song2.mp3'});
  18          </script>
  19      </etl>

This Scriptella ETL script copies all rows from Src_Table to Dest_Table.
Src_Table contains the following columns: id, first_name, last_name
Dest_Table contains the following columns: id, name
The name column of the Dest_Table is produced by a concatenation of first_name and last_name from the Src_Table
This example demonstrates HSQLDB-To-Oracle copy procedure, although it works between virtually any databases.

   1  
   2  <!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
   3  <etl>
   4    <connection id="in" driver="hsqldb" url="jdbc:hsqldb:file:demo" 
   5                classpath="hsqldb.jar" user="sa"/>
   6    <connection id="out" driver="oracle" url="jdbc:oracle:thin:@localhost:1521:ORCL" 
   7                classpath="ojdbc14.jar" user="scott" password="tiger"/>
   8    <!-- Copy all table rows from one to another database -->
   9    <query connection-id="in">
  10        SELECT * FROM Src_Table --Selects all rows
  11        <!-- For each row executes insert -->  
  12        <script connection-id="out"> 
  13            INSERT INTO Dest_Table(ID, Name) 
  14            VALUES (?id,?{first_name+' '+last_name})
  15        </script>
  16    </query>
  17  </etl>

This example demonstrates usage of Scriptella ETL Tool to load CSV data into a database table.

Input CSV file data.csv:

   1  
   2  id,priority,summary,status
   3  1,Critical,NullPointerException in Main class,Open
   4  5,Low,"Checkstyle, PMD, Findbugs issues",Reopened
   5  7,Low,Maven integration,Open
   6  10,High,SPI API,Closed

The CSV loading script has the following content:

   1  
   2  <!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
   3  <etl>
   4    <connection id="in" driver="csv" url="data.csv"/>
   5    <connection id="out" driver="oracle" url="jdbc:oracle:thin:@localhost:1521:ORCL" 
   6        classpath="ojdbc14.jar" user="scott" password="tiger"/>
   7    <!-- Copy all CSV rows to a database table -->
   8    <query connection-id="in">
   9        <!-- Empty query means select all columns -->
  10        <script connection-id="out">
  11            INSERT INTO Table_Name VALUES (?id,?priority, ?summary, ?status)
  12        </script>
  13    </query>
  14  </etl>

Use RegEx to filter CSV data:

   1  
   2  <query connection-id="in">
   3      <!--Select bugs with status open or reopened.-->
   4      ,,,open|reopened
   5      <!--Inserts imported rows into a database-->
   6      <script connection-id="out">
   7         INSERT INTO Table_Name VALUES (?id, ?priority, ?summary, ?status);
   8      </script>
   9  </query>