JDBC 4.0 Driver 자동 로딩 사용하기

META-INF/services/java.sql.Driver 파일을 만든다. 

파일의 내용에 driver 클래스 이름을 적어 준다. 

ex) com.ibm.db2.jcc.DB2Driver

 

java.sql and javax.sql Features Introduced in the JDBC 4.0 API

• auto java.sql.Driver discovery -- no longer need to load a java.sql.Driver class via Class.forName
• National Character Set support added
• Support added for the SQL:2003 XML data type
• SQLException enhancements -- Added support for cause chaining; New SQLExceptions added for common SQLState class value codes
• Enhanced Blob/Clob functionality -- Support provided to create and free a Blob/Clob instance as well as additional methods added to improve accessiblity
• Support added for accessing a SQL ROWID
• Support added to allow a JDBC application to access an instance of a JDBC resource that has been wrapped by a vendor, usually in an application server or connection pooling environment.
• Availability to be notfied when a PreparedStatement that is associated with a PooledConnection has been closed or the driver determines is invalid

pivot / unpivot query

http://it.toolbox.com/blogs/db2luw/pivot-query-12757

http://it.toolbox.com/blogs/db2luw/unpivot-query-12798

CREATE TABLE Sales (Year INT, Quarter INT, Results INT)

YEAR QUARTER RESULTS 

----------- ----------- ----------- 

2004 1 20 

2004 2 30 

2004 3 15 

2004 4 10 

2005 1 18 

2005 2 40 

2005 3 12 

2005 4 27

SELECT Year,
MAX(CASE WHEN Quarter = 1
THEN Results END) AS Q1,

  
MAX(CASE WHEN Quarter = 2
THEN Results END) AS Q2,

  
MAX(CASE WHEN Quarter = 3
THEN Results END) AS Q3,

  
MAX(CASE WHEN Quarter = 4
THEN Results END) AS Q4 

FROM Sales 

GROUP BY Year

 
YEAR Q1 Q2 Q3 Q4
----------- ----------- ----------- ----------- ----------- 
2004 20 30 15 10 
2005 18 40 12 27
 
 
CREATE TABLE SalesAgg
( year INTEGER,
q1 INTEGER,
q2 INTEGER,
q3 INTEGER,
q4 INTEGER );
 
YEAR Q1 Q2 Q3 Q4 
----------- ----------- ----------- ----------- ----------- 
2004 20 30 15 10 
2005 18 40 12 27
 
SELECT S.Year, Q.Quarter, Q.Results 
FROM SalesAgg AS S, 
  TABLE (VALUES(1, S.q1),
(2, S.q2),
(3, S.q3),
(4, S.q4))
AS Q(Quarter, Results);
 
YEAR QUARTER RESULTS 
----------- ----------- ----------- 
2004 1 20 
2004 2 30 
2004 3 15 
2004 4 10 
2005 1 18 
2005 2 40 
2005 3 12 
2005 4 27