| 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)); |
| } |
| } |