albert's blog: JoSQL (SQL for Java Objects)

Requirement
If I have a Java collection as bellows:

1
2
3

Employee [empNo=1, name=Albert, gender=M, onBoardDate=Thu Feb 05 15:20:44 CST 2009]
Employee [empNo=2, name=Mandy, gender=null, onBoardDate=Fri Mar 05 15:20:44 CST 2010]
Employee [empNo=3, name=Verio, gender=M, onBoardDate=Thu Feb 05 15:20:44 CST 2009]

If I would like to utilize SQL statements to do search, which tool can help me?

Solution
JoSQL (SQL for Java Objects) provides the ability for a developer to apply a SQL statement to a collection of Java Objects.
JoSQL provides the ability to search, order and group ANY Java objects and should be applied when you want to perform SQL-like queries on a collection of Java Objects.

Prerequisite
Configure the two jar files in your classpath:

Example
In this example, it will demonstrate:

find all employee objects
find all male employee objects
find employee objects which named "Mandy"
find all male employee object which onBoardDate between 2009/01/01 to 2009/12/31

package test;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.josql.Query;
import org.josql.QueryExecutionException;
import org.josql.QueryParseException;
import org.josql.QueryResults;

import test.vo.Employee;

/**
 * The Class JoSqlTest.
 */
public class JoSqlTest {

    private List<Employee> employees = new ArrayList<Employee>();

    /**
     * The main method.
     * 
     * @param args
     *            the arguments
     */
    public static void main(String[] args) {
        JoSqlTest test = new JoSqlTest();
        test.setupData();
        test.findAllEmployees();
        test.findAllMaleEmployees();
        test.findByEmployeeName("Mandy");
        test.findByGenderAndOnBoardDate("M", "2009/01/01", "2009/12/31");
    }

    /**
     * Select all employees.
     */
    public void findAllEmployees() {
        System.out.println("\n * find all employees");

        String sql = "select * from test.vo.Employee";

        // Create a new Query.
        Query query = new Query();
        try {
            // Parse the SQL you are going to use.
            query.parse(sql);

            // Execute the query.
            QueryResults qr = query.execute(employees);

            // Get the query results.
            List<Employee> result = qr.getResults();

            // Print search research
            for (Employee employee : result) {
                System.out.println(employee.toString());
            }

        } catch (QueryParseException e) {
            throw new RuntimeException(e);
        } catch (QueryExecutionException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * Select all male employees.
     */
    public void findAllMaleEmployees() {
        System.out.println("\n * find all male employees");

        String sql = "select * from test.vo.Employee where gender = :gender";

        // Create a new Query.
        Query query = new Query();
        try {
            // Parse the SQL you are going to use.
            query.parse(sql);

            // set variable
            query.setVariable("gender", "M");

            // Execute the query.
            QueryResults queryResults = query.execute(employees);

            // Get the query results.
            List<Employee> result = queryResults.getResults();

            // Print search research
            for (Employee employee : result) {
                System.out.println(employee.toString());
            }

        } catch (QueryParseException e) {
            throw new RuntimeException(e);
        } catch (QueryExecutionException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * Find by employee name.
     * 
     * @param name
     *            the name
     */
    public void findByEmployeeName(String name) {

        System.out.println("\n * find by employee name");

        String sql = "select * from test.vo.Employee where name = :name";

        // Create a new Query.
        Query query = new Query();
        try {
            // Parse the SQL you are going to use.
            query.parse(sql);

            // set variable
            query.setVariable("name", name);

            // Execute the query.
            QueryResults queryResults = query.execute(employees);

            // Get the query results.
            List<Employee> result = queryResults.getResults();

            // Print search research
            for (Employee employee : result) {
                System.out.println(employee.toString());
            }

        } catch (QueryParseException e) {
            throw new RuntimeException(e);
        } catch (QueryExecutionException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * Find by gender and on board date.
     * 
     * @param gender
     *            the gender
     * @param fromDate
     *            the from date
     * @param toDate
     *            the to date
     */
    public void findByGenderAndOnBoardDate(String gender, String fromDate, String toDate) {
        System.out.println("\n * find By Gender And OnBoardDate");

        String sql = "select * from test.vo.Employee where gender = :gender "
                + " and onBoardDate between :fromDate and :toDate ";
        // Create a new Query.
        Query query = new Query();
        try {
            // Parse the SQL you are going to use.
            query.parse(sql);

            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
            // set variable
            query.setVariable("gender", gender);
            query.setVariable("fromDate", dateFormat.parse(fromDate));
            query.setVariable("toDate", dateFormat.parse(toDate));

            // Execute the query.
            QueryResults queryResults = query.execute(employees);

            // Get the query results.
            List<Employee> result = queryResults.getResults();

            // Print search research
            for (Employee employee : result) {
                System.out.println(employee.toString());
            }

        } catch (QueryParseException e) {
            throw new RuntimeException(e);
        } catch (QueryExecutionException e) {
            throw new RuntimeException(e);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * Setup data.
     */
    public void setupData() {
        System.out.println("\n * setup data");

        Employee albert = new Employee();
        albert.setEmpNo(1L);
        albert.setName("Albert");
        albert.setGender("M");
        Calendar cal1 = Calendar.getInstance();
        cal1.set(Calendar.YEAR, 2009);
        cal1.set(Calendar.MONTH, 1);
        cal1.set(Calendar.DATE, 5);
        albert.setOnBoardDate(cal1.getTime());

        Employee mandy = new Employee();
        mandy.setEmpNo(2L);
        mandy.setName("Mandy");
        Calendar cal2 = Calendar.getInstance();
        cal2.set(Calendar.YEAR, 2010);
        cal2.set(Calendar.MONTH, 2);
        cal2.set(Calendar.DATE, 5);
        mandy.setOnBoardDate(cal2.getTime());

        Employee verio = new Employee();
        verio.setEmpNo(3L);
        verio.setName("Verio");
        verio.setGender("M");
        Calendar cal3 = Calendar.getInstance();
        cal3.set(Calendar.YEAR, 2009);
        cal3.set(Calendar.MONTH, 8);
        cal3.set(Calendar.DATE, 10);
        verio.setOnBoardDate(cal1.getTime());

        employees.add(albert);
        employees.add(mandy);
        employees.add(verio);
    }

}

Console:

 * setup data

 * find all employees
Employee [empNo=1, name=Albert, gender=M, onBoardDate=Thu Feb 05 16:00:48 CST 2009]
Employee [empNo=2, name=Mandy, gender=null, onBoardDate=Fri Mar 05 16:00:48 CST 2010]
Employee [empNo=3, name=Verio, gender=M, onBoardDate=Thu Feb 05 16:00:48 CST 2009]

 * find all male employees
Employee [empNo=1, name=Albert, gender=M, onBoardDate=Thu Feb 05 16:00:48 CST 2009]
Employee [empNo=3, name=Verio, gender=M, onBoardDate=Thu Feb 05 16:00:48 CST 2009]

 * find by employee name
Employee [empNo=2, name=Mandy, gender=null, onBoardDate=Fri Mar 05 16:00:48 CST 2010]

 * find By Gender And OnBoardDate
Employee [empNo=1, name=Albert, gender=M, onBoardDate=Thu Feb 05 16:00:48 CST 2009]
Employee [empNo=3, name=Verio, gender=M, onBoardDate=Thu Feb 05 16:00:48 CST 2009]

Reference
[1] http://josql.sourceforge.net/

albert's blog

Total Pageviews

2015/06/18

JoSQL (SQL for Java Objects)

No comments: