Sort Result Set using Apache Collections and Bean Utilities

The post outlines a simple sort mechanism using java, the same can also be achieved using JavaScript, jQuery or other libraries like YUI (For the Web). As I mentioned earlier this method is a plain old school java only approach. One major disadvantage though is the additional app server call if you are using this as part of a webpage.

Depending on your requirement and the amount of data to be sorted you might have to choose between the various JavaScript or this option.

Required Code -

Class - 

beanSort.java

package com.utils;

/* Make sure all of the required jars are added to the project
* All of the jars can be downloaded from the Apache website
* The below sort method required an additional app server call unlike jQuery or YUI which can sort on the client
* Browser.
*/
import org.apache.commons.beanutils.BeanComparator;
import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.RowSetDynaClass;
import org.apache.commons.collections.comparators.ComparableComparator;
import org.apache.commons.collections.comparators.ReverseComparator;
import java.util.*;
import java.sql.*;

public class beanSort {

private ListrowBeanList = new ArrayList();

public beanSort (RowSetDynaClass cRowSetDynaClass)
{
Iterator itr = cRowSetDynaClass.getRows().iterator();
while(itr.hasNext()) {
DynaBean bean = (DynaBean) itr.next();
rowBeanList.add(bean);
}
}

private void sort(String cField) {
BeanComparator beanComparator = new BeanComparator(cField);
Collections.sort(rowBeanList, beanComparator);
System.out.println(“–Ascending Order–”);
for (DynaBean b: rowBeanList)
{
System.out.println(b.get(“col1″)+”,”+b.get(“col2″)+”,”+b.get(“col3″));
}
}

private void reverseOrderSorting(String cField) {
BeanComparator reverseOrderBeanComparator = new BeanComparator(cField, new ReverseComparator(new ComparableComparator()));
Collections.sort(rowBeanList, reverseOrderBeanComparator);
System.out.println(“–Descending Order–”);
for (DynaBean b: rowBeanList)
{
System.out.println(b.get(“col1″)+”,”+b.get(“col2″)+”,”+b.get(“col3″));
}
}

public static void main(String[] args) throws Exception {
Class.forName(“oracle.jdbc.OracleDriver”);
Connection conn = DriverManager.getConnection(“jdbc:oracle:thin:@192.168.1.125:1521:oracle1″,”scott”,”tiger”);
PreparedStatement ps = conn.prepareStatement(“select ’1′ as col1 ,’2′ as col2,’3′ as col3 from dual union select ’2′ as col1 ,’1′ as col2 ,’5′ as col3 from dual union select ’3′ as col1 ,’1′ as col2 ,’2′ as col3 from dual”);
ResultSet rs = ps.executeQuery();

RowSetDynaClass rsdc = new RowSetDynaClass(rs);
conn.close();
String oField=”col1″;
beanSort obeanSort = new beanSort(rsdc);
obeanSort.sort(oField);
obeanSort.reverseOrderSorting(oField);

}
}

P.S – Sorry – No code highlighting plugin – I  should paid more attention to the wordpress plugin details and how they apply to wordpress.com (vs) wordpress.org before I signed up @ wordpress.com

External Libraries - 

  • commons-beanutils
  • commons-collection
  • commons-logging (in direct dependency for the comparator class)

Demo -


Simple Oracle JDBC using thin client

The following video outlines a vary simple JDBC connection to Oracle using thin client.

Code used in the presentation -

package com.connection;

import java.sql.*;

public class mainConnection {

/**

* @param args

*/

public static void main(String[] args)  throws Exception {

Class.forName(“oracle.jdbc.OracleDriver”);

Connection conn = DriverManager.getConnection(“jdbc:oracle:thin:@192.168.1.32:1521:oraclem11x”,

“test”,”test123″);

PreparedStatement ps = conn.prepareStatement(“select ’1′ as col1 ,’2′ as col2,’3′ as col3 from dual union select ’2′ as col1 ,’1′ as col2 ,’5′ as col3 from dual union select ’3′ as col1 ,’1′ as col2 ,’2′ as col3 from dual”);

ResultSet rs = ps.executeQuery();

while (rs.next()) {

System.out.println(rs.getString(“col1″) + rs.getString(“col3″)  + rs.getString(“col2″) );

}

}

}

Notes -

While downloading the jdbc driver, make sure you get the correct version based on the version of your local oracle database version.

Presentation -


PL SQL Remote Debug using SQL Developer

The following video outlines the usage of SQL Developer to remotely debug PL SQL procedures using JDWP protocol.

Some of the code snippets used in the presentation -

1. Sample Procedure for debug -

CREATE OR REPLACE procedure TEST.debug1 as
v1 int;
v2 int;
v3 int;
Begin
DBMS_DEBUG_JDWP.CONNECT_TCP(’192.168.1.7′,4000);
v1:= 134;
v2 := 3;
v3 := 4;
dbms_output.put_line(v1);
dbms_output.put_line(v2);
dbms_output.put_line(v3);
dbms_debug_jdwp.disconnect(dbms_debug_jdwp.current_session_id, dbms_debug_jdwp.current_session_serial);
End;
/

2. Calling Procedure -

Begin
TEST.debug1;
End;

Presentation -


Local Debug of simple Oracle stored procedure with Toad 10

Grant the required privileges -

Sample procedure to run the debug on -

Make sure the correct Toad debug option is selected -

Compile the procedure for debug, and a breakpoint by clicking on the left bar near the required line number -

Click on toolbar as shown in the picture, “Execute PL/SQL with Debugger” -

On this step you can change the input parameters for the procedure if any in your case -

Toad now takes control and halts at the given breakpoint -

You can now “Step In” or “Step out” or “Step Over” the line of execution, you can also look at the variable data (make sure Enable smart watches selected) -

End of execution -


DB2 Express quick start guide

Good site for learning DB2 express edition and high level concepts of DB2.

DB2 Express Learning


Follow

Get every new post delivered to your Inbox.