View Javadoc
1   package org.woehlke.logfileloader.core.dao.impl;
2   
3   import org.springframework.beans.factory.annotation.Autowired;
4   import org.springframework.data.domain.Page;
5   import org.springframework.data.domain.PageImpl;
6   import org.springframework.data.domain.Pageable;
7   import org.springframework.jdbc.core.JdbcTemplate;
8   import org.springframework.stereotype.Repository;
9   import org.woehlke.logfileloader.core.dao.ReportsDao;
10  import org.woehlke.logfileloader.core.model.*;
11  import org.woehlke.logfileloader.core.dao.rowmapper.*;
12  
13  import javax.sql.DataSource;
14  import java.util.List;
15  
16  /**
17   * Created with IntelliJ IDEA.
18   * User: tw
19   * Date: 04.09.13
20   * Time: 12:21
21   * To change this template use File | Settings | File Templates.
22   */
23  @Repository
24  public class ReportsDaoImpl implements ReportsDao {
25  
26      private JdbcTemplate jdbcTemplate;
27  
28      @Autowired
29      public void setDataSource(DataSource dataSource) {
30          this.jdbcTemplate = new JdbcTemplate(dataSource);
31      }
32  
33      @Override
34      public Page<IpNumbersReportItem> listIpNumbers(Pageable pageable) {
35          String sql1 = "select IP.id as id,ip,count(ip) as nr from IP,LINEITEM where LINEITEM.ip_id=IP.id group by ip,id order by nr DESC";
36          String sql2 = "select count(nr) from ("+sql1+") as COUNT";
37          long total = jdbcTemplate.queryForLong(sql2);
38          List<IpNumbersReportItem> list = jdbcTemplate.query(sql1, new IpNumbersReportItemMapper());
39          int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
40          Page<IpNumbersReportItem> page =new PageImpl<IpNumbersReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
41          return page;
42      }
43  
44      @Override
45      public Page<BrowserReportItem> listBrowser(Pageable pageable) {
46          String sql1 = "select BROWSER.id as id,browser,count(browser) as nr from BROWSER,LINEITEM where LINEITEM.browser_id=BROWSER.id group by browser,id order by nr DESC";
47          String sql2 = "select count(nr) from ("+sql1+") as COUNT";
48          long total = jdbcTemplate.queryForLong(sql2);
49          List<BrowserReportItem> list = jdbcTemplate.query(sql1, new BrowserReportItemMapper());
50          int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
51          Page<BrowserReportItem> page = new PageImpl<BrowserReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
52          return page;
53      }
54  
55      @Override
56      public Page<PageReportItem> listPages(Pageable pageable) {
57          String sql1 = "select REQUEST.id as id,request,count(request) as nr from REQUEST,LINEITEM where LINEITEM.request_id=REQUEST.id group by request,id order by nr DESC";
58          String sql2 = "select count(nr) from ("+sql1+") as COUNT";
59          long total = jdbcTemplate.queryForLong(sql2);
60          List<PageReportItem> list = jdbcTemplate.query(sql1, new PageReportItemMapper());
61          int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
62          Page<PageReportItem> page = new PageImpl<PageReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
63          return page;
64      }
65  
66      @Override
67      public Page<PageReportItem> listUrlsForBrowser(long browserId,Pageable pageable) {
68          String sql1 = "select REQUEST.id as id,request,count(request) as nr from REQUEST,LINEITEM where LINEITEM.request_id=REQUEST.id and LINEITEM.browser_id=? group by request,id order by nr DESC";
69          String sql2 = "select count(nr) from ("+sql1+") as COUNT";
70          long total = jdbcTemplate.queryForLong(sql2,browserId);
71          List<PageReportItem> list = jdbcTemplate.query(sql1, new PageReportItemMapper(), browserId);
72          int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
73          Page<PageReportItem> page = new PageImpl<PageReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
74          return page;
75      }
76  
77      @Override
78      public Page<BrowserReportItem> listBrowserForUrls(long urlId, Pageable pageable) {
79          String sql1 = "select BROWSER.id as id,browser,count(browser) as nr from BROWSER,LINEITEM where LINEITEM.browser_id=BROWSER.id and LINEITEM.request_id=? group by browser,id order by nr DESC";
80          String sql2 = "select count(nr) from ("+sql1+") as COUNT";
81          long total = jdbcTemplate.queryForLong(sql2,urlId);
82          List<BrowserReportItem> list = jdbcTemplate.query(sql1, new BrowserReportItemMapper(),urlId);
83          int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
84          Page<BrowserReportItem> page = new PageImpl<BrowserReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
85          return page;
86      }
87  
88      @Override
89      public Page<HttpCodeReportItem> listHttpCodes(Pageable pageable) {
90          String sql1 = "select HTTPCODE.id as id,code,count(code) as nr from HTTPCODE,LINEITEM where LINEITEM.httpcode_id=HTTPCODE.id group by code,id order by nr DESC";
91          String sql2 = "select count(nr) from ("+sql1+") as COUNT";
92          long total = jdbcTemplate.queryForLong(sql2);
93          List<HttpCodeReportItem> list = jdbcTemplate.query(sql1, new HttpCodeReportItemMapper());
94          int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
95          Page<HttpCodeReportItem> page = new PageImpl<HttpCodeReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
96          return page;
97      }
98  
99      @Override
100     public Page<PageReportItem> listUrlsForHttpCodes(long httpCodeId, Pageable pageable) {
101         String sql1 = "select REQUEST.id as id,request,count(request) as nr from REQUEST,LINEITEM where LINEITEM.request_id=REQUEST.id and LINEITEM.httpcode_id=? group by request,id order by nr DESC";
102         String sql2 = "select count(nr) from ("+sql1+") as COUNT";
103         long total = jdbcTemplate.queryForLong(sql2,httpCodeId);
104         List<PageReportItem> list = jdbcTemplate.query(sql1, new PageReportItemMapper(), httpCodeId);
105         int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
106         Page<PageReportItem> page = new PageImpl<PageReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
107         return page;
108     }
109 
110     @Override
111     public Page<BrowserReportItem> listBrowserForHttpCodes(long httpCodeId, Pageable pageable) {
112         String sql1 = "select BROWSER.id as id,browser,count(browser) as nr from BROWSER,LINEITEM where LINEITEM.browser_id=BROWSER.id and LINEITEM.httpcode_id=? group by browser,id order by nr DESC";
113         String sql2 = "select count(nr) from ("+sql1+") as COUNT";
114         long total = jdbcTemplate.queryForLong(sql2,httpCodeId);
115         List<BrowserReportItem> list = jdbcTemplate.query(sql1, new BrowserReportItemMapper(),httpCodeId);
116         int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
117         Page<BrowserReportItem> page = new PageImpl<BrowserReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
118         return page;
119     }
120 
121     @Override
122     public Page<IpNumbersReportItem> listIpNumbersForHttpCodes(long httpCodeId, Pageable pageable) {
123         String sql1 = "select IP.id as id,ip,count(ip) as nr from IP,LINEITEM where LINEITEM.ip_id=IP.id and LINEITEM.httpcode_id=? group by ip,id order by nr DESC";
124         String sql2 = "select count(nr) from ("+sql1+") as COUNT";
125         long total = jdbcTemplate.queryForLong(sql2,httpCodeId);
126         List<IpNumbersReportItem> list = jdbcTemplate.query(sql1, new IpNumbersReportItemMapper(),httpCodeId);
127         int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
128         Page<IpNumbersReportItem> page = new PageImpl<IpNumbersReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
129         return page;
130     }
131 
132     @Override
133     public Page<PageReportItem> listUrlsForIpNumber(long ipNumberId, Pageable pageable) {
134         String sql1 = "select REQUEST.id as id,request,count(request) as nr from REQUEST,LINEITEM where LINEITEM.request_id=REQUEST.id and LINEITEM.ip_id=? group by request,id order by nr DESC";
135         String sql2 = "select count(nr) from ("+sql1+") as COUNT";
136         long total = jdbcTemplate.queryForLong(sql2,ipNumberId);
137         List<PageReportItem> list = jdbcTemplate.query(sql1, new PageReportItemMapper(), ipNumberId);
138         int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
139         Page<PageReportItem> page = new PageImpl<PageReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
140         return page;
141     }
142 
143     @Override
144     public Page<TimelineDaysItem> listDays(Pageable pageable) {
145         String sql1 = "select DAY.id as id,day,count(day) as nr from DAY,LINEITEM where LINEITEM.day_id=DAY.id group by day,id order by day DESC";
146         String sql2 = "select count(nr) from ("+sql1+") as COUNT";
147         long total = jdbcTemplate.queryForLong(sql2);
148         List<TimelineDaysItem> list = jdbcTemplate.query(sql1, new TimelineDaysItemMapper());
149         int offset=pageable.getOffset();
150         int pageSize=pageable.getPageSize();
151         int toIndex=(offset+pageSize>list.size())?list.size():(offset+pageSize);
152         Page<TimelineDaysItem> page = new PageImpl<TimelineDaysItem>(list.subList(offset,toIndex),pageable,total);
153         return page;
154     }
155 
156     @Override
157     public Page<HttpCodeReportItem> listHttpCodesForDay(long dayId, Pageable pageable) {
158         String sql1 = "select HTTPCODE.id as id,code,count(code) as nr from HTTPCODE,LINEITEM where LINEITEM.httpcode_id=HTTPCODE.id and LINEITEM.day_id=? group by code,id order by nr DESC";
159         String sql2 = "select count(nr) from ("+sql1+") as COUNT";
160         long total = jdbcTemplate.queryForLong(sql2,dayId);
161         List<HttpCodeReportItem> list = jdbcTemplate.query(sql1, new HttpCodeReportItemMapper(),dayId);
162         int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
163         Page<HttpCodeReportItem> page = new PageImpl<HttpCodeReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
164         return page;
165     }
166 
167     @Override
168     public Page<PageReportItem> listUrlsForDay(long dayId, Pageable pageable) {
169         String sql1 = "select REQUEST.id as id,request,count(request) as nr from REQUEST,LINEITEM where LINEITEM.request_id=REQUEST.id and LINEITEM.day_id=? group by request,id order by nr DESC";
170         String sql2 = "select count(nr) from ("+sql1+") as COUNT";
171         long total = jdbcTemplate.queryForLong(sql2,dayId);
172         List<PageReportItem> list = jdbcTemplate.query(sql1, new PageReportItemMapper(), dayId);
173         int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
174         Page<PageReportItem> page = new PageImpl<PageReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
175         return page;
176     }
177 
178     @Override
179     public Page<BrowserReportItem> listBrowserForDay(long dayId, Pageable pageable) {
180         String sql1 = "select BROWSER.id as id,browser,count(browser) as nr from BROWSER,LINEITEM where LINEITEM.browser_id=BROWSER.id and LINEITEM.day_id=? group by browser,id order by nr DESC";
181         String sql2 = "select count(nr) from ("+sql1+") as COUNT";
182         long total = jdbcTemplate.queryForLong(sql2,dayId);
183         List<BrowserReportItem> list = jdbcTemplate.query(sql1, new BrowserReportItemMapper(),dayId);
184         int toIndex=pageable.getOffset()+pageable.getPageSize()>list.size()?list.size():pageable.getOffset()+pageable.getPageSize();
185         Page<BrowserReportItem> page = new PageImpl<BrowserReportItem>(list.subList(pageable.getOffset(),toIndex),pageable,total);
186         return page;
187     }
188 }