Oracle - Dealing with Dates

When using Oracle, it is very normal to deal with date, date conversion, date manipulation. Below is a quick guide on how to use them properly.

• trunc key words - trunc keywords is useful to eliminate the time field of the datetime column. You format the time field to a specific format you want also. For example, trunc(date_column); trunc()

Below is the table that shows the format parameters:

Unit	Valid format parameters
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year	IYYY, IY, I
Quarter	Q
Month	MONTH, MON, MM, RM
Week	WW
IW	IW
W	W
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI

Tomcat Cookbook

This post will log all the necessary content for playing with Tomcat AppServer:

1. Where to locate System.out.println logs?
   
   Ans: In Unix, it is located in the log file "catalina.out". In Windows, it is located in the log file "stdout.log"
   

Struts 2 Cookbook

This is the place I record down all my experiences in using Struts 2

1. To bypass the validation when submitting the actions:
   
   
   
   1. Put onclick="form.onsubmit=null" on the s:submit tag
   
   
   2. In the struts-core.jar, change the struts-default.xml. Inside this file, it defines "input","browse","cancel","back" as excluded methods during validation. You can add in more as a customization for struts 2 framework.
   
   
   
   

Penetrate "Members Login" site

To penetrate through the "Members Login" site, we need to follow below steps:

1. Download and Install AccessDiver v4.92


2. Set the "my skill" to "expert".


3. Find the page that having username and password field


4. Copy the page link to the "SERVER" field in AccessDiver


5. Set the "proxy" in AccessDiver


6. Checked "Use Web Proxies" and use "Rotate proxies" and select "login to try before swapping" and input 1 as increment seed.


7. In "proxy skipping", checked the first 3 checkboxes


8. In "proxy handing", checked the first checkbox


9. Install a comprehend wordlist


10. Start the penetrate test.

Dealing with CLOBs

No matter dealing with which DB provider. It will always be needed to deal with Clob Object. Especially you would like to store useful information as Image in the Database. Here provides some tips in dealing with them.

1. To inject some information as a CLOB Object with some normal table properties:
   
   
   Use a PreparedStatement with INSERT SQL and set in all the attributes (setString, setInt, blah blah).
   
   Use an CLOB.empty_lob() for the Clob field.
   
   Execute the query
   
   
   Select out the newly added entry
   
   
   CLOB clob = rs.getCLOB("TRXMESSAGE");
   
   int count = clob.putString(1, txnMessage);
   
   rs.close();
   
   
   Commit at last
   



2. To inject the CLOB Object (Easier version):
   
   Use the command:
   
   opstmt = (OraclePreparedStatement)conn.prepareStatement(sql);
   
   opstmt.setStringForClob(1,str);
   
   opstmt.executeUpdate();
   



3. To retrieve the CLOB Object properly:
   In the source code provided below shows several way that you can manipulate the CLOBs.
   

package org.axiondb.types;

import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Clob;
import java.sql.SQLException;

import junit.framework.TestCase;

import org.axiondb.jdbc.AxionClob;

public abstract class AbstractClobTest extends TestCase {

public AbstractClobTest(String testName) {
super(testName);
}

protected String _text = "The quick brown fox jumped over the lazy dogs.";

protected abstract AxionClob getClob() throws Exception;

public void testGetAsciiStream() throws Exception {
Clob clob = getClob();
InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals(_text,buf.toString());
in.close();
}

public void testGetCharacterStream() throws Exception {
Clob clob = getClob();
Reader in = clob.getCharacterStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals(_text,buf.toString());
in.close();
}

public void testTruncate() throws Exception {
AxionClob clob = getClob();
clob.truncate(("The quick".length()));
Reader in = clob.getCharacterStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("The quick",buf.toString());
in.close();
}

public void testGetSubstring() throws Exception {
AxionClob clob = getClob();
String substring = null;
try {
substring = clob.getSubString(("The quick".length()), " brown fox jumped".length());
} catch(SQLException e) {
return; // exit on unsupported
}
assertEquals(" brown fox jumped",substring);
}

public void testPositionOfString() throws Exception {
AxionClob clob = getClob();
String substring = " brown fox jumped";
long pos = -1;
try {
pos = clob.position(substring,3L);
} catch(SQLException e) {
return; // exit on unsupported
}
assertEquals(_text.indexOf(substring,3),(int)pos);
}

public void testPositionOfClob() throws Exception {
AxionClob clob = getClob();
String substring = " brown fox jumped";
long pos = -1;
try {
pos = clob.position(new StringClob(substring),3L);
} catch(SQLException e) {
return; // exit on unsupported
}
assertEquals(_text.indexOf(substring,3),(int)pos);
}

public void testPositionOfNullString() throws Exception {
AxionClob clob = getClob();
try {
clob.position((String)null,0L);
fail("Expected SQLException");
} catch(SQLException e) {
// expected
}
}

public void testPositionOfNullClob() throws Exception {
AxionClob clob = getClob();
try {
clob.position((Clob)null,0L);
fail("Expected SQLException");
} catch(SQLException e) {
// expected
}
}

public void testPositionOfStringNotFound() throws Exception {
AxionClob clob = getClob();
String substring = " bROWn fox jumped";
long pos = -1;
try {
pos = clob.position(substring,3L);
} catch(SQLException e) {
return; // exit on unsupported
}
assertEquals((int)pos,_text.indexOf(substring,3));
}

public void testSetAsciiStream() throws Exception {
AxionClob clob = getClob();
OutputStream out = null;
try {
out = clob.setAsciiStream(0L);
} catch(SQLException e) {
return; // exit on unsupported
}
out.write("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.".getBytes("US-ASCII"));
out.close();

InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.",buf.toString());
in.close();
}

public void testSetAsciiStream2() throws Exception {
AxionClob clob = getClob();
{
OutputStream out = null;
try {
out = clob.setAsciiStream(0L);
} catch(SQLException e) {
return; // exit on unsupported
}
out.write("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.".getBytes("US-ASCII"));
out.close();

InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.",buf.toString());
in.close();
}
clob.truncate(0);
{
OutputStream out = null;
try {
out = clob.setAsciiStream(0L);
} catch(SQLException e) {
return; // exit on unsupported
}
out.write("Every good boy does fine.".getBytes("US-ASCII"));
out.close();

InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("Every good boy does fine.",buf.toString());
in.close();
}
}

public void testSetCharacterStream() throws Exception {
AxionClob clob = getClob();
Writer out = null;
try {
out = clob.setCharacterStream(0L);
} catch(SQLException e) {
return; // exit on unsupported
}
out.write("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.");
out.close();

InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.",buf.toString());
in.close();
}

public void testSetCharacterStream2() throws Exception {
AxionClob clob = getClob();
{
Writer out = null;
try {
out = clob.setCharacterStream(0L);
} catch(SQLException e) {
return; // exit on unsupported
}
out.write("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.");
out.close();

InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.",buf.toString());
in.close();
}
clob.truncate(0);
{
Writer out = null;
try {
out = clob.setCharacterStream(0L);
} catch(SQLException e) {
return; // exit on unsupported
}
out.write("Every good boy does fine.");
out.close();

InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("Every good boy does fine.",buf.toString());
in.close();
}
}
}

Struts 2 Wrong Conceptual

1. It is true that Struts 2 Actions can be in Entity form however if you tied these 2 thing together. You will be having problem in placing View level attributes. Most of the time we will want to have different way in show in the UI and in representation in the backend level. The most correct design will be:
   
   class Action {
   
   private EntityA entityA
   
   private EntityB entityB
   }
   

Hibernate Retrieving Efficiency

Hibernate as what discussed in most of the forum is not able to handle batch of SQL queries efficiently. This can be proved by the experiments done below:

Testing SQL: SELECT * FROM CORPORATE c where c.STATUS = "ACTIVE".

Return 18K row of data, using normal SQL and setter getter mechanism to populate an entity. It takes 10 seconds

Return 18K row of data, using Hibernate Native SQL and addEntity mechanism. It takes 4 minutes.

Return 18K row of data, using Hibernate HQL. It takes 4 minutes also.

It indicates that it is a wrong concept that using Hibernate Native SQL will help in increasing the efficiency. Furthermore, it also proves that Hibernate did more "hidden" steps when populating an Entity.

Again it proves that Hibernate is not suitable in handling huge transactional data and batch processes.