JoSQL tutorial

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

  1. JoSQL

Following jar must be in classpath

  1. JoSQL-2.2.jar
  2. 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]]










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