JoSQL tutorial

By admin | April 06, 2014 | Updated : April 29, 2016 java, josql, No Comments

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.

As a developer, JoSQL (SQL for Java Objects) helps us to perform sql queries in collection of in-memory java objects.

We can perform following operations using JoSQL

1) where 2) like 3) group by 4) order by 5) limit 6) between

Referenece - > http://josql.sourceforge.net/

Required Libraries

You need to download

JoSQL

Following jar must be in classpath

JoSQL-2.2.jar
gentlyWEB-utils-1.1.jar

Employee.java

Here we are creating a domain object in order to do all the JoSQL Operations.

package com.test.josql;

public class Employee implements java.io.Serializable {

    private static final long    serialVersionUID    = 1L;

    private long                employeeId;
    private String                employeeName;
    private Integer                salary;

    public long getEmployeeId() {
        return employeeId;
    }

    public void setEmployeeId(long employeeId) {
        this.employeeId = employeeId;
    }

    public String getEmployeeName() {
        return employeeName;
    }

    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }

    @Override
    public String toString() {
        return "Employee [employeeId=" + employeeId + ", employeeName=" + employeeName + "]";
    }

    public Integer getSalary() {
        return salary;
    }

    public void setSalary(Integer salary) {
        this.salary = salary;
    }

}

JoSQL Where Clause Example

package com.test.josql;

import java.util.ArrayList;
import java.util.List;

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

public class JoSQLExample {

    public static void main(String[] args) throws QueryExecutionException, QueryParseException {

        List<Employee> empList = new ArrayList<Employee>();
        Employee emp1 = new Employee();
        emp1.setEmployeeId(1);
        emp1.setEmployeeName("Rockey");
        emp1.setSalary(1000);

        Employee emp2 = new Employee();
        emp2.setEmployeeId(2);
        emp2.setEmployeeName("Sonia");
        emp2.setSalary(1500);

        Employee emp3 = new Employee();
        emp3.setEmployeeId(3);
        emp3.setEmployeeName("Sinu");
        emp3.setSalary(1200);

        Employee emp4 = new Employee();
        emp4.setEmployeeId(4);
        emp4.setEmployeeName("Rose");
        emp4.setSalary(1100);

        Employee emp5 = new Employee();
        emp5.setEmployeeId(5);
        emp5.setEmployeeName("Sinoy");
        emp5.setSalary(1400);

        empList.add(emp1);
        empList.add(emp2);
        empList.add(emp3);
        empList.add(emp4);
        empList.add(emp5);

        Query q = new Query();

        // where
        q.parse("SELECT * FROM com.test.josql.Employee WHERE employeeName=:employeeName");
        q.setVariable("employeeName", "Sinu");

        QueryResults execute = q.execute(empList);
        List<?> results = execute.getResults();
        System.out.println(results);
    }

}

Output

[Employee [employeeId=3, employeeName=Sinu]]

JoSQL Like Example

package com.test.josql;

import java.util.ArrayList;
import java.util.List;

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

public class JoSQLExample {

    public static void main(String[] args) throws QueryExecutionException, QueryParseException {

        List<Employee> empList = new ArrayList<Employee>();
        Employee emp1 = new Employee();
        emp1.setEmployeeId(1);
        emp1.setEmployeeName("Rockey");
        emp1.setSalary(1000);

        Employee emp2 = new Employee();
        emp2.setEmployeeId(2);
        emp2.setEmployeeName("Sonia");
        emp2.setSalary(1500);

        Employee emp3 = new Employee();
        emp3.setEmployeeId(3);
        emp3.setEmployeeName("Sinu");
        emp3.setSalary(1200);

        Employee emp4 = new Employee();
        emp4.setEmployeeId(4);
        emp4.setEmployeeName("Rose");
        emp4.setSalary(1100);

        Employee emp5 = new Employee();
        emp5.setEmployeeId(5);
        emp5.setEmployeeName("Sinoy");
        emp5.setSalary(1400);

        empList.add(emp1);
        empList.add(emp2);
        empList.add(emp3);
        empList.add(emp4);
        empList.add(emp5);

        Query q = new Query();

        // like
        q.parse("SELECT * From com.test.josql.Employee where employeeName LIKE '%Si%'");

        QueryResults execute = q.execute(empList);
        List<?> results = execute.getResults();
        System.out.println(results);
    }

}

Output

[Employee [employeeId=3, employeeName=Sinu], Employee [employeeId=5, employeeName=Sinoy]]

JoSQL Group By Example

package com.test.josql;

import java.util.ArrayList;
import java.util.List;

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

public class JoSQLExample {

    public static void main(String[] args) throws QueryExecutionException, QueryParseException {

        List<Employee> empList = new ArrayList<Employee>();
        Employee emp1 = new Employee();
        emp1.setEmployeeId(1);
        emp1.setEmployeeName("Rockey");
        emp1.setSalary(1000);

        Employee emp2 = new Employee();
        emp2.setEmployeeId(2);
        emp2.setEmployeeName("Sonia");
        emp2.setSalary(1500);

        Employee emp3 = new Employee();
        emp3.setEmployeeId(3);
        emp3.setEmployeeName("Sinu");
        emp3.setSalary(1200);

        Employee emp4 = new Employee();
        emp4.setEmployeeId(4);
        emp4.setEmployeeName("Rose");
        emp4.setSalary(1100);

        Employee emp5 = new Employee();
        emp5.setEmployeeId(5);
        emp5.setEmployeeName("Sinoy");
        emp5.setSalary(1400);

        empList.add(emp1);
        empList.add(emp2);
        empList.add(emp3);
        empList.add(emp4);
        empList.add(emp5);

        Query q = new Query();

        // group by
        q.parse("SELECT employeeName,salary From com.test.josql.Employee GROUP BY employeeName,salary GROUP BY ORDER salary");

        QueryResults execute = q.execute(empList);
        List<?> results = execute.getResults();
        System.out.println(results);
    }

}

Output

[[Rockey, 1000], [Rose, 1100], [Sinu, 1200], [Sinoy, 1400], [Sonia, 1500]]

JoSQL Order By Example

package com.test.josql;

import java.util.ArrayList;
import java.util.List;

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

public class JoSQLExample {

    public static void main(String[] args) throws QueryExecutionException, QueryParseException {

        List<Employee> empList = new ArrayList<Employee>();
        Employee emp1 = new Employee();
        emp1.setEmployeeId(1);
        emp1.setEmployeeName("Rockey");
        emp1.setSalary(1000);

        Employee emp2 = new Employee();
        emp2.setEmployeeId(2);
        emp2.setEmployeeName("Sonia");
        emp2.setSalary(1500);

        Employee emp3 = new Employee();
        emp3.setEmployeeId(3);
        emp3.setEmployeeName("Sinu");
        emp3.setSalary(1200);

        Employee emp4 = new Employee();
        emp4.setEmployeeId(4);
        emp4.setEmployeeName("Rose");
        emp4.setSalary(1100);

        Employee emp5 = new Employee();
        emp5.setEmployeeId(5);
        emp5.setEmployeeName("Sinoy");
        emp5.setSalary(1400);

        empList.add(emp1);
        empList.add(emp2);
        empList.add(emp3);
        empList.add(emp4);
        empList.add(emp5);

        Query q = new Query();

        // order by
        q.parse("SELECT * From com.test.josql.Employee ORDER BY employeeName");

        QueryResults execute = q.execute(empList);
        List<?> results = execute.getResults();
        System.out.println(results);
    }
}

Output

[Employee [employeeId=1, employeeName=Rockey], Employee [employeeId=4, employeeName=Rose], Employee [employeeId=5, employeeName=Sinoy], Employee [employeeId=3, employeeName=Sinu], Employee [employeeId=2, employeeName=Sonia]]

JoSQL Limit Example

package com.test.josql;

import java.util.ArrayList;
import java.util.List;

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

public class JoSQLExample {

    public static void main(String[] args) throws QueryExecutionException, QueryParseException {

        List<Employee> empList = new ArrayList<Employee>();
        Employee emp1 = new Employee();
        emp1.setEmployeeId(1);
        emp1.setEmployeeName("Rockey");
        emp1.setSalary(1000);

        Employee emp2 = new Employee();
        emp2.setEmployeeId(2);
        emp2.setEmployeeName("Sonia");
        emp2.setSalary(1500);

        Employee emp3 = new Employee();
        emp3.setEmployeeId(3);
        emp3.setEmployeeName("Sinu");
        emp3.setSalary(1200);

        Employee emp4 = new Employee();
        emp4.setEmployeeId(4);
        emp4.setEmployeeName("Rose");
        emp4.setSalary(1100);

        Employee emp5 = new Employee();
        emp5.setEmployeeId(5);
        emp5.setEmployeeName("Sinoy");
        emp5.setSalary(1400);

        empList.add(emp1);
        empList.add(emp2);
        empList.add(emp3);
        empList.add(emp4);
        empList.add(emp5);

        Query q = new Query();

        // lmit
        q.parse("SELECT * From com.test.josql.Employee LIMIT 1, 2");

        QueryResults execute = q.execute(empList);
        List<?> results = execute.getResults();
        System.out.println(results);
    }
}

Output

[Employee [employeeId=1, employeeName=Rockey], Employee [employeeId=2, employeeName=Sonia]]

JoSQL Between Example

package com.test.josql;

import java.util.ArrayList;
import java.util.List;

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

public class JoSQLExample {

    public static void main(String[] args) throws QueryExecutionException, QueryParseException {

        List<Employee> empList = new ArrayList<Employee>();
        Employee emp1 = new Employee();
        emp1.setEmployeeId(1);
        emp1.setEmployeeName("Rockey");
        emp1.setSalary(1000);

        Employee emp2 = new Employee();
        emp2.setEmployeeId(2);
        emp2.setEmployeeName("Sonia");
        emp2.setSalary(1500);

        Employee emp3 = new Employee();
        emp3.setEmployeeId(3);
        emp3.setEmployeeName("Sinu");
        emp3.setSalary(1200);

        Employee emp4 = new Employee();
        emp4.setEmployeeId(4);
        emp4.setEmployeeName("Rose");
        emp4.setSalary(1100);

        Employee emp5 = new Employee();
        emp5.setEmployeeId(5);
        emp5.setEmployeeName("Sinoy");
        emp5.setSalary(1400);

        empList.add(emp1);
        empList.add(emp2);
        empList.add(emp3);
        empList.add(emp4);
        empList.add(emp5);

        Query q = new Query();

        // between
        q.parse("SELECT * From com.test.josql.Employee WHERE salary BETWEEN 1200 AND 2000");

        QueryResults execute = q.execute(empList);
        List<?> results = execute.getResults();
        System.out.println(results);
    }
}

Output

[Employee [employeeId=2, employeeName=Sonia], Employee [employeeId=3, employeeName=Sinu], Employee [employeeId=5, employeeName=Sinoy]]

JoSQL Upper Example

package com.test.josql;

import java.util.ArrayList;
import java.util.List;

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

public class JoSQLExample {

    public static void main(String[] args) throws QueryExecutionException, QueryParseException {

        List<Employee> empList = new ArrayList<Employee>();
        Employee emp1 = new Employee();
        emp1.setEmployeeId(1);
        emp1.setEmployeeName("Rockey");
        emp1.setSalary(1000);

        Employee emp2 = new Employee();
        emp2.setEmployeeId(2);
        emp2.setEmployeeName("Sonia");
        emp2.setSalary(1500);

        Employee emp3 = new Employee();
        emp3.setEmployeeId(3);
        emp3.setEmployeeName("Sinu");
        emp3.setSalary(1200);

        Employee emp4 = new Employee();
        emp4.setEmployeeId(4);
        emp4.setEmployeeName("Rose");
        emp4.setSalary(1100);

        Employee emp5 = new Employee();
        emp5.setEmployeeId(5);
        emp5.setEmployeeName("Sinoy");
        emp5.setSalary(1400);

        empList.add(emp1);
        empList.add(emp2);
        empList.add(emp3);
        empList.add(emp4);
        empList.add(emp5);

        Query q = new Query();

        // upper
        q.parse("SELECT upper(employeeName) From com.test.josql.Employee WHERE employeeName=:employeeName");
        q.setVariable("employeeName", "Sinu");

        QueryResults execute = q.execute(empList);
        List<?> results = execute.getResults();
        System.out.println(results);
    }
}

Output

[[SINU]]

You might also like following tutorials :

Your email address will not be published. Required fields are marked *

Name*

Email*

Website

Required Libraries

Employee.java

JoSQL Where Clause Example

Output

JoSQL Like Example

Output

JoSQL Group By Example

Output

JoSQL Order By Example

Output

JoSQL Limit Example

Output

JoSQL Between Example

Output

JoSQL Upper Example

Output

Related Posts