java - 选择每个用户每天的首次登录时间

Question

我尝试选择用户第一次登录平台的时间。每个用户一天内可以多次进入平台，我在第一次选择时遇到了问题。

数据库访问类:

package poin;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DataBase {
    Connection conn ;
    Statement stmt; 
    List<Join> list = new ArrayList<Join>();

    public DataBase(){
        super();
    }

    public List<Join> getCard() throws Exception {

        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  
            conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;user=****;password=*******;database=********");

            stmt = conn.createStatement();

            String sql = "SELECT [Id] ,[Last Name] ,[First Name],[Field Time] FROM [******].[dbo].[Jointur] order by [Field Time]";

            ResultSet rs = stmt.executeQuery(sql);

            //STEP 5: Extract data from result set
            //rs.beforeFirst();
            //rs.next();
            //Join obj =new Join(); FAUTE CAR NOUS AVONS CR2ER UN SEUL OBJET POUR TOUTE ARRAY LIST IL FAUT ETRE A L INTERIEUR DE LA BOUCLE VOIR LA BOUCLE

             while(rs.next())
             {  
                Join obj =new Join();// 
                //Retrieve by column name
                obj.setCardHolderId(rs.getInt("ID"));

                obj.setNom(rs.getString("Last Name"));
                obj.setPrenom(rs.getString("First Name"));

                obj.setFieldTime(rs.getDate("Field Time"));
                obj.setTimestamp(rs.getTimestamp("Fiel Time"));

                list.add(obj); 
             }                     

             rs.close();
             stmt.close();
             conn.close();
          }
          catch (ClassNotFoundException e) {
              // TODO Auto-generated catch block
              e.printStackTrace();
          }
          return list;
    }
}

加入类(class)；该类包含插入到 ArrayList 中的对象类型:

package poin;

import java.sql.Timestamp;
import java.util.Date;

public class Join {

    private int cardHolderId;
    private String Nom;
    private String Prenom;
    private Date fieldTime;
    private Timestamp timestamp;

    public Timestamp getTimestamp() {
        return timestamp;
    }

    public void setTimestamp(Timestamp timestamp) {
        this.timestamp = timestamp;
    }

    public Join() {
        super();
    }

    public int getCardHolderId() {
        return cardHolderId;
    }

    public void setCardHolderId(int cardHolderId) {
        this.cardHolderId = cardHolderId;
    }

    public String getNom() {
        return Nom;
    }

    public void setNom(String nom) {
        Nom = nom;
    }

    public String getPrenom() {
        return Prenom;
    }

    public void setPrenom(String prenom) {
        Prenom = prenom;
    }

    public Date getFieldTime() {
        return fieldTime;
    }

    public void setFieldTime(Date fieldTime) {
        this.fieldTime = fieldTime;
    }
}

类测试:类包含 main

package poin;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.Date;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;

public class Test {

    static Date dateDebut;
    static Date dateFin;

    /**
     * @param args
     * @throws ParseException
     */
    public static void main(String[] args) throws ParseException {
        String dateEntre = "2016-03-03";
        String dateF     = "2016-03-03";

        SimpleDateFormat ft = new SimpleDateFormat ("yyyy-MM-dd"); 

        dateDebut= ft.parse(dateEntre); 
        dateFin=ft.parse(dateF);

        // TODO Auto-generated method stub
        DataBase obj = new DataBase();

        List<Join> list = new ArrayList<Join>();
        List<Join> result = new ArrayList<Join>();

        try {
            list=obj.getCard();
            //iterateur pour la liste extraite de la base de donnée
            Iterator<Join> it=result.iterator();

            //gregorian calendrier
            GregorianCalendar cal = new GregorianCalendar();
            cal.setTime(dateDebut);

            //dernier element de la liste: base de donnée

            Join obj2 = list.get(list.size()-1);

            // dernier identifiant de la carte dans la table
            int dernId = obj2.getCardHolderId(); 
            result.add(list.get(0));

            while(!cal.getTime().after(dateFin)){

                for (Join j : list){
                    //System.out.println(cal.getTime());
                    //Join o = it.next();
                    //boolean o2 = result.add(o);

                    if(j.getFieldTime().compareTo(cal.getTime())==0){
                        //ICI LE PROBLEME (translated: HERE IS THE PROBLEM)
                        for (Join i : result){
                            if (i.getCardHolderId() != j.getCardHolderId() && i.getFieldTime() != j.getFieldTime()) result.add(j);
                        }
                    }
                }

                cal.add(Calendar.DAY_OF_WEEK, 1);
            }

            for(Join k : list){
                System.out.println(k.getCardHolderId()+"     "+k.getNom());
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

Answer 1

如果您的数据存储在关系数据库中，通常最好使用 SQL 来获取所需的数据(而不是在代码中实现搜索逻辑)。

假设您的数据是这样的

        ID LAST_NAME FIRST_NAME FIELD_TIME        
---------- --------- ---------- -------------------
         1 A.        A.         01.01.2016 10:20:00 
         2 A.        A.         01.01.2016 12:22:00 
         3 A.        A.         03.01.2016 08:50:00 
         4 B.        B.         01.01.2016 15:08:00 

要获取每个用户的第一个日志和一天，您必须从日期时间列中获取该天(我使用在 Oracle 上工作的 TRUNC - 如果需要，请根据您的数据库进行调整)以及用户和组计算 MIN 日志时间的天。

select  
    LAST_NAME, FIRST_NAME, trunc(FIELD_TIME) log_day,  min(FIELD_TIME) FIRST_LOG
from 
    log_time
group by 
    LAST_NAME, FIRST_NAME, trunc(FIELD_TIME)
order by 
    1, 2, 3;

结果与预期一致，简单抓取游标即可获取数据

LAST_NAME FIRST_NAME LOG_DAY             FIRST_LOG         
--------- ---------- ------------------- -------------------
A.        A.         01.01.2016 00:00:00 01.01.2016 10:20:00 
A.        A.         03.01.2016 00:00:00 03.01.2016 08:50:00 
B.        B.         01.01.2016 00:00:00 01.01.2016 15:08:00