To get records in between two date in mysql using java windows application

Question

I am entering two employees data so dynamically I am getting two tables e1 and e2 and on those tables I am storing attendance of particular employee.See the below tables ,Here date column type is date. two tables e1 and e2,

    |------------------||-----------------|
    | date    | present|| date   | present|
    |2015-9-7 |  1     ||2015-9-7|  1     |
    |2015-9-8 |  1     ||2015-9-8|  0     |
    |2015-9-9 |  1     ||2015-9-9|  1     |

To get the employee attendance, i have one text field to enter employee Id and I have two drop down list to choose dates i.e "dayfrom" shows 1,2..31 and "monthfrom" shows Jan,Feb..Dec and den samew another two drop down list "dayto" and "monthto".

so, my code is,

JTextField day1,day2,id;
Container c;
int t=0,in=0,re;
Integer mo1=new Integer(0);
Integer mo2=new Integer(0);
JButton sh,home,sha;
JLabel lday1,lday2,lid;
String tp;
String mo[]={"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"};
int it[]=new int[100];
int ct[]=new int[100];
Choice dob1,dob2,add1,add2;
JOptionPane jp = new JOptionPane();
public emp()// constructor
{

    lid = new JLabel("Id :");  
    lid.setForeground(Color.black);
    lday1 = new JLabel("Day From :");  
    lday1.setForeground(Color.black);
    lday2 = new JLabel("Day To:");  
    lday2.setForeground(Color.black);

    id = new JTextField(); 
    id.setForeground(Color.black); 
    id.setBackground(Color.white);
    day1 = new JTextField(); 
    day1.setForeground(Color.black); 
    day1.setBackground(Color.white);
    day2 = new JTextField(); 
    day2.setForeground(Color.black); 
    day2.setBackground(Color.white);
    sh = new JButton("Show"); 
    sh.setForeground(Color.white); 
    sh.setBackground(new Color(128,0,0));
    dob1 = new Choice();
    dob2 = new Choice();
    dob1.add("1");
    dob1.add("2");
    dob1.add("3");
    dob1.add("4");
    dob1.add("5");
    dob1.add("6");
    dob1.add("7");
    dob1.add("8");
    dob2.add("JAN");
    dob2.add("FEB");
    dob2.add("MAR");
    dob2.add("APR");
    dob2.add("MAY");
    dob1.setBounds(190,150,40,20);
    dob2.setBounds(250,150,55,20);
    add1 = new Choice();
    add2 = new Choice();
    add1.add("1");
    add1.add("2");
    add1.add("3");
    add1.add("4");
    add1.add("5");
    add1.add("6");
    add1.add("7");
    add1.add("8");
    add2.add("JAN");
    add2.add("FEB");
    add2.add("MAR");
    add2.add("APR");
    add2.add("MAY");
    c.add(lid);
    c.add(lday1);
    c.add(lday2);
    c.add(id);
    c.add(sh);
    c.add(dob1);
    c.add(dob2);
    c.add(add1);
    c.add(add2);
    setDefaultCloseOperation(EXIT_ON_CLOSE);
    setVisible(true);

}
public void actionPerformed(ActionEvent ae)
{   

    Object source = ae.getSource();
    if(source==sh)
    {
        re=0;
        if((id.getText()).length()!=0)
        {
        try
        {

            t=0;
            for(in=0;in<12;in++)
            {
                if(mo[in]==dob2.getSelectedItem())
                mo1=in+1;

            }
            ResultSet rs = stm.executeQuery("select * from e"+id.getText()+" where date_format(day,'%d')="+dob1.getSelectedItem()+" and date_format(day,'%m')="+mo1);
            while(rs.next())
            {   
                t=1;    
                re=rs.getInt("pre");
                if(re==1)
                jp.showMessageDialog(this,"Present";
                else
                jp.showMessageDialog(this,"Absent");
            }
            if(t==0)
            {
                jp.showMessageDialog(this,"Sorry, No Such Record exists");
                t=0;
            }

    }

}
But here I am getting output as "no such record exists".

Welcome to SO. Please reduce your code to a minimal running example. By the way, it is not good practice to mix up data model and view element within one class. — Uwe Allner, Sep 11 '15 at 05:37
Your problem is not clear as well. We can help if you tried something and doesn't work for you. Anyways, did you debug your code what's the generated select query ? did you debug with your IDE to find the query? If so execute the generated query in your db to check results. — Karthik R, Sep 11 '15 at 05:40
You're mixing Swing (light weight) and AWT (heavy weight) components, that's not going to end well. May you should consider using `JComboBox` or even one of the many third party date pickers — MadProgrammer, Sep 11 '15 at 05:41
[How do I query between two dates using MySQL?](http://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql), but I'd be making some `java.sql.Date` values and using a `PreparedStatement` to make your life easier — MadProgrammer, Sep 11 '15 at 05:42
`select * from e"+id.getText()+" where` ... `select * from ebob where` ... not sure how that doesn't complain about not been able to find the table — MadProgrammer, Sep 11 '15 at 05:44
in addition to the above, try debugging by putting your select statement into a string first, so that you can print it out. Then take this print out and see if you can run this query in a normal sql client session. — Scary Wombat, Sep 11 '15 at 05:44
actually I am new to windows app so don't have much idea.thanks for your comments. — Elizabeth Mohanty, Sep 11 '15 at 05:47
when I am executing the query in db I am not getting any result. — Elizabeth Mohanty, Sep 11 '15 at 06:01

score 0 · Answer 1 · answered Sep 11 '15 at 07:46

You can directly simple create mysql query like below:

SELECT * FROM [TABLE NAME] WHERE DATE(date) BETWEEN 'yyyy-mm-dd' AND 'yyy-mm-dd'

It will give you list of attendance for your selected employee. So you will create Date formatted string from your code and pass it to mysql query.

Hope it will help you.

To get records in between two date in mysql using java windows application

1 Answers1