Generating total in Excel using R1C1 References

If you have to import CSV or RPT(SQL Server) files into Excel and then manipulate it to generate a report, you can have these formulae (R1C1 Style) in your scratch pad, and cut and paste them into appropriate cells without even thinking about editing them.

You may have to turn on support for R1C1 addressing in MS-Excel by checking off
Tools -> Options -> General (tab) -> R1C1 Reference style.

Here is the formula that I used to sum up all the values in a given column from the
top to the cell just above the total.

"=SUM(R1C:R[-1]C)" - note that no numeric value follows C to address current column.

Top down Web Service Re-generation using JDeveloper

You may have noticed (or got burnt!) that JDeveloper overwrites your server side Java implementation class with a skeleton whenever you have to re-generate a web service due to changes to WSDL or Schemas?

Here is a way around this issue.

Your application server container will find your implementation class, if you do the following.

• Write your own service implementation class with appropriate operations and make it extend JDeveloper generated server skeleton class.
• Locate element <servlet> in /public_html/WEB-INF/web.xml
• Modify <servlet>/<servlet-class>element to refer to your class (package.classname) instead of JDeveloper generated class.
  

Mapping a Java Object to two tables using Hibernate - Possible?

One can hibernate one or more than one object to a single table in a database.
Is it possible for an object to be mapped to two different tables? The answer is , No. But the desired effect can be achieved with a minimal effort by taking advantage of Hibernate's support for inheritance hierarchy as follows.

One may wonder where is the need for this kind of behavior.

When an object is persisted in a table that is a queue table or used like a scratch pad (a reader process may delete a record from this table after consuming it), there may be a need to capture the persisted object in a secondary table for logging, auditing or reconciliation purposes.

Here are the steps ...

1. Create a new POJO(say com.mycom.hb.module.SecondaryObject) and make it extend your base Hibernate POJO(say PrimaryObject) that is already hibernated. This object should look simple because the secondary table is exactly same as the primary table.
(It is not a big issue if the secondary table includes more fields. Just implement
the setters and getters for the additional fields if necessary.)

2. Externalize all the property mappings from primary.hbm.xml in a standalone file namely props.xml. This goes a long way and saves a lot of work for secondary table mapping.

3. Use entity reference declaration as shown in hibernate documentation to include property mappings for primary.hbm.xml from props.xml, defined in Step 2. (Chapter 9.1.6 - Table per concrete class, using implicit polymorphism - This is NOT the mapping approach we will take, but used as the reference for entity declaration and references.)

4. Map newly created POJO in secondary.hbm.xml and use entity references to pull in properties from props.xml. The mapping strategy for this approach is going to be "Table per subclass" and the mapping file for the secondary.hbm.xml may look like the following. Remember that making secondary object extend primary object in Java class definition is not just enough. It need to be specified in the joined-subclass definition as follows to get this working.

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"
[<!ENTITY myprops SYSTEM "com/mycom/hb/module/props.xml">]>
<hibernate-mapping>
<joined-subclass
name="com.mycom.hb.module.SecondaryObject"
table="SECONDARY_TABLE"
schema="app_schema"
extends="com.mycom.hb.module.PrimaryObject">
<key column="REC_NO" /><!-- primary key -->
&myprops;
</joined-subclass>
</hibernate-mapping>


5. Include secondary.hbm.xml in your hibernate_configuration.cfg.xml

6. Build and populate SecondaryObject and persist them from your data access classes. After persisting SecondaryObject, you could find data actually persisted in both PRIMARY_TABLE and also a copy of it in SECONDARY_TABLE.

Distinguishing numbers from strings in a String(varchar) coulmn.

Have you ever come across a need to filter out numbers(or number like) that are stored in a column along with string values?

e.g. Let us say myTable.myColumn of type varchar(50) has the following values.
    EA-234
    1234567
    CT/234
    9876543
    10203040
    AB/04/02/09

and you want to select only the rows that have "1234567, 9876543 and 10203040".
The following query is very consise and efficient than building a "like" search for all allowed prefixes.

select * from myTable
where myColumn between '0' and 'A'
and myColumn not like 'A%'

Remember that the ranges are inclusive in a "between" clause, so you may have to eliminate all the values begining with "A". One may be able to use a different filter such as "and len(myColumn) = 7" or rewrite the where clause as
myColumn >= '0' and < 'A' to achieve it instead of "not like" depending on actual data.

Note: It may noted that "9" cannot be used in the above query instead of "A" because the filter will not include values such as "99" or "921" etc.