在两个日期之间从My SQL搜索数据 [英] Search data from My SQL between two dates

问题描述

当我在一个月中的两个日期之间进行搜索时(例如，在02-02-2013和09-02-2013之间) 结果显示从03-01-2013到08-01-2013，从03-02-2013到08-02-2013.

When I do search between two dates in one month (for example between 02-02-2013 and 09-02-2013) result is showing from 03-01-2013 to 08-01-2013 and 03-02-2013 to 08-02-2013.

下面是我的代码:

try {
    dconfig dcf=new dconfig();
    java.sql.Connection connection;
    Class.forName(dcf.driver);
    connection=(com.mysql.jdbc.Connection) DriverManager.getConnection(dcf.StrUrl,dcf.StrUid,dcf.StrPwd);
    ResultSet rs = null;
    ResultSet rscount;
    String StrQr="";

    if (jobnamesearch.getText().trim().length()>0 ) {
        StrQr=StrQr + " and job_name like '%" + jobnamesearch.getText().trim() +"%' ";
    }

    if (datetosearch.getText().trim().length()>0) {
        StrQr=StrQr + " and date > '" + datesearch.getText().toString()+"' ";
        StrQr=StrQr + " and date < '" + datetosearch.getText().toString()+"' ";
    }

    if (datesearch.getText().trim().length()>0 && datetosearch.getText().trim().length()==0) {
        StrQr=StrQr + " and date like '%" + datesearch.getText().trim().toString()+ "%' ";
    }

    if (dwsearch.getText().trim().length()>0 ) {
        StrQr=StrQr + " and dw_no like '%" + dwsearch.getText().trim() +  "%' ";
    }

    if (remsearch.getText().trim().length()>0 ) {
        StrQr=StrQr + " and rem_no like '%" + remsearch.getText().trim() +  "%' ";
    }

    if (typesearch.getSelectedItem().toString().length()<11) {
        StrQr=StrQr + " and type like '%" + typesearch.getSelectedItem().toString() +  "%' ";
    }

    if (usersearch.getSelectedItem().toString().length()>0) {
        StrQr=StrQr + " and user like '%" + usersearch.getSelectedItem().toString() +  "%' ";
    }

    java.sql.PreparedStatement stmt=connection.prepareStatement("SELECT ID,job_name,details,type,dw_no,rem_no,user,date FROM tracker  where 1=1 " + StrQr +" order by ID DESC");
    java.sql.PreparedStatement stmtcount=connection.prepareStatement("SELECT Count(*) FROM tracker  where 1=1 " + StrQr +"");
    rs = stmt.executeQuery();

    rscount = stmtcount.executeQuery();
}

推荐答案

如果我不得不猜测，我会说它在这里:

If I had to guess, I'd say it's here:

if (datetosearch.getText().trim().length()>0) {
    StrQr=StrQr + " and date > '" + datesearch.getText().toString()+"' ";
    StrQr=StrQr + " and date < '" + datetosearch.getText().toString()+"' ";
}

您要说的日期>-尝试将其更改为> =和< =.

You're saying date > -- try changing that to >= and <=.

if (datetosearch.getText().trim().length()>0) {
    StrQr=StrQr + " and date >= '" + datesearch.getText().toString()+"' ";
    StrQr=StrQr + " and date <= '" + datetosearch.getText().toString()+"' ";
}

如果数据库中的日期是日期时间，则< =也将不起作用.您需要为其添加一天或附加23:59，因为它将在当天的午夜搜索.

Also if your dates in your database are datetimes, then the <= won't work either. You'll need to add a day to it or append 23:59 as it will be searching that day at midnight.

希望这会有所帮助.

BTW-这很容易受到SQL注入的攻击.考虑改用参数化查询.

BTW -- this is highly vulnerable to SQL Injection. Look into using parameterized queries instead.

这篇关于在两个日期之间从My SQL搜索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！