Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » UltraLiteJ » Using UltraLiteJ » Developing UltraLiteJ applications » Coding examples

 

Example: Inner join operations

This example demonstrates how to perform an inner join operation. In this scenario, every employee has corresponding department information. The join operation associates data from the employee table with corresponding data from the department table. The association is made with the department number in the employee table to locate the related information in the department table.

package ianywhere.ultralitej.demo;
import ianywhere.ultralitej.*;
/**
 * ReadInnerJoin -- sample program to demonstrate reading the Employee table
 * and joining to each row the corresponding Department row.
 */
public class ReadInnerJoin
{
    /**
     * mainline for program.
     *
     * @param args command-line arguments
     *
     */
    public static void main
        ( String[] args )
    {
        try {
            Configuration config = DatabaseManager.createConfigurationFile( "Demo1.ulj" );
            Connection conn = DatabaseManager.connect( config );
            PreparedStatement stmt = conn.prepareStatement(
                "SELECT E.number, E.last_name, E.first_name, E.age,"
                + " E.dept_no, D.name"
                + " FROM Employee E"
                + " JOIN Department D ON E.dept_no = D.dept_no"
                + " ORDER BY E.number"
            );
            ResultSet cursor = stmt.executeQuery();
            for( ; cursor.next(); ) {
                /* Can't access columns by name because no meta data */
                int emp_no = cursor.getInt( 1 /* "E.number" */ );
                String last_name = cursor.getString( 2 /* "E.last_name" */ );
                String first_name = cursor.getString( 3 /* "E.first_name" */ );
                int age = cursor.getInt( 4 /* "E.age" */ );
                int dept_no = cursor.getInt( 5 /* "E.dept_no" */ );
                String dept_name = cursor.getString( 6 /* "D.name" */ );
                System.out.println( first_name + ' ' + last_name );
                System.out.print( "  empl. no = " );
                System.out.print( emp_no );
                System.out.print( "  dept = " );
                System.out.println( dept_no );
                System.out.print( "  age = " );
                System.out.print( age );
                System.out.println( ", " + dept_name );
            }
            cursor.close();
            stmt.close();
            conn.release();
            Demo.display( "ReadInnerJoin completed successfully" );
        } catch( ULjException exc ) {
            Demo.displayException( exc );
        }
    }
}