0,0 → 1,171 |
package ak.trafficcount; |
|
import java.util.Date; |
import java.util.List; |
import java.util.ArrayList; |
|
import javax.sql.DataSource; |
import java.sql.Connection; |
import java.sql.PreparedStatement; |
import java.sql.ResultSet; |
import java.sql.SQLException; |
|
import org.apache.commons.pool.ObjectPool; |
import org.apache.commons.pool.impl.GenericObjectPool; |
import org.apache.commons.dbcp.ConnectionFactory; |
import org.apache.commons.dbcp.PoolingDataSource; |
import org.apache.commons.dbcp.PoolableConnectionFactory; |
import org.apache.commons.dbcp.DriverManagerConnectionFactory; |
|
public class Logic |
{ |
private static DataSource dataSource; |
|
/** |
* @param year |
* @param month zero based |
*/ |
public static List getMonthReport( |
String host, String source, Integer year, Integer month) |
throws SQLException |
{ |
String query |
= "select host, source, (base + delta) as count, stamp from tcount" |
+ " where host = ?" |
+ " and source = ?" |
+ " and stamp in (" |
+ " (" |
+ " select min(stamp) from tcount " |
+ " where host = ?" |
+ " and source = ?" |
+ " and extract(month from stamp) = ?" |
+ " and extract(year from stamp) = ?" |
+ " group by extract(day from stamp)" |
+ " )" |
+ " union" |
+ " (" |
+ " select min(stamp) from tcount " |
+ " where host = ?" |
+ " and source = ?" |
+ " and extract(month from stamp) = ?" |
+ " and extract(year from stamp) = ?" |
+ " )" |
+ " )" |
+ " order by stamp"; |
|
Object[] params = new Object[] { |
host, source, host, source, month, year, host, source, month, year}; |
|
List result = new ArrayList(); |
|
Connection conn = null; |
PreparedStatement stmt = null; |
ResultSet rset = null; |
|
try { |
conn = dataSource.getConnection(); |
stmt = createPreparedStatement(conn, query, params); |
rset = stmt.executeQuery(); |
|
int numcols = rset.getMetaData().getColumnCount(); |
|
while(rset.next()) { |
for(int i = 1; i <= numcols; i++) { |
System.out.print("\t" + rset.getObject(i)); |
} |
System.out.println(); |
} |
} |
finally { |
try { rset.close(); } catch(Exception e) { } |
try { stmt.close(); } catch(Exception e) { } |
try { conn.close(); } catch(Exception e) { } |
} |
|
return result; |
} |
|
public static List getYearReport(String host, String source, int year) |
throws SQLException |
{ |
return null; |
} |
|
public static void initDataSource(String host, Integer port, |
String database, String user, String password) |
throws Exception |
{ |
String url; |
ObjectPool connectionPool; |
ConnectionFactory connectionFactory; |
PoolableConnectionFactory poolableConnectionFactory; |
|
url = formDataSourceUrl(host, port, database, user, password); |
Class.forName("org.postgresql.Driver"); |
|
connectionPool = new GenericObjectPool(null); |
connectionFactory = new DriverManagerConnectionFactory(url, null); |
poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, |
connectionPool, null, null, false, true); |
dataSource = new PoolingDataSource(connectionPool); |
} |
|
private static String formDataSourceUrl(String host, Integer port, |
String database, String user, String password) |
{ |
StringBuffer url = new StringBuffer("jdbc:postgresql://"); |
|
if(host == null) url.append("localhost"); |
else url.append(host); |
|
if(port != null) url.append(":").append(port); |
|
if(database == null) |
throw new RuntimeException("Database not specified"); |
|
url.append("/").append(database); |
|
if(user != null) { |
url.append("?user=").append(user); |
|
if(password != null) |
url.append("&password=").append(password); |
} |
|
return url.toString(); |
} |
|
private static PreparedStatement createPreparedStatement(Connection conn, |
String query, List params) |
throws SQLException |
{ |
return createPreparedStatement(conn, query, |
(params == null) ? null : params.toArray()); |
} |
|
private static PreparedStatement createPreparedStatement(Connection conn, |
String query, Object[] params) |
throws SQLException |
{ |
PreparedStatement stmt = conn.prepareStatement(query); |
|
if(params != null) { |
try { |
for(int i = 0; i < params.length; i++) |
stmt.setObject(i+1, params[i]); |
} |
finally { |
try { stmt.close(); } catch(Exception e) { } |
} |
} |
|
return stmt; |
} |
|
public static void main(String[] args) |
throws Exception |
{ |
initDataSource("sun", null, "trafficcount", "dbadmin", "krasota"); |
getMonthReport("sun", "tun0_out", new Integer(2003), new Integer(7)); |
} |
} |