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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | * 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/
