• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

How to insert separate row for unique login count each day in database table

 
Greenhorn
Posts: 25
Hibernate Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am working on a struts based web application using postgresql DB. I need to display last 15 days of login history of my website login users as a bar chart for website admin. I have tried to generate a bar chart using JFreeChart library. It is taking two Database coloumns and creating a bar chart as a image on the fly(dynamically). My problem is that I don't have any idea that how to store the count of unique login per day for last 15 days in database. Main problem is, it should maintain a new row for each new date and the entries should be counted date-wise.

How to get the logic so that whenever date is changing it should go to insert a new row for each new date? Any help will be appriciated.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Do you want to track unique logins, that is, one user's login will be counted only once on every given day?

You'll need a table containing rows, say, DATE and USERNAME. When a user logs in, you'll check whether a record for current date and the username already exists in the table, if it does, do nothing, if it doesn't, insert a new row with current date and the username. If you define a primary key on the table (and you should do it), you can just try to insert the row on each login and just handle possible primary key collision gracefully.

To obtain the number of logins per day, you'd just select count of records (COUNT(*)) grouped by the date. You could create a view with this query to plug it into JFreeChart seamlessly.

If you don't want to keep a longer history, you also need to have a process in place that will delete records older than 15 days every day.
 
Shailesh Kumar Saxena
Greenhorn
Posts: 25
Hibernate Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Martin

Thank you for a soon reply. I got some basic idea.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic