无法获得查询结果 [英] Unable to get the result of query

问题描述

大家好，我很久以前就被困在这里了很长一段时间我不熟悉java帮助会非常感激

基本上我在指定日期之间得到所有客户的结果

bill_issued_on datatype = date \time in access

Hello everyone i am stuck at this for quite some time i am new to java help would be very appreciated
Basically i am getting result of all customers between specified dates
bill_issued_on datatype=date\time in access

String sDate=( (JTextField)startDateChooser.getDateEditor().getUiComponent() ).getText() ;
        String eDate=( (JTextField)endDateChooser.getDateEditor().getUiComponent() ).getText() ;
        DateFormat format = new SimpleDateFormat("YYYY-MM-dd ");
        Date sDateFinal = null;
        Date eDateFinal = null;
        System.out.println(sDate+"/"+eDate);
        try {
            sDateFinal = format.parse(sDate);
            eDateFinal = format.parse(eDate);
        } catch (ParseException ex) {
            Logger.getLogger(MonthlyRecord.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        String qry = "SELECT * FROM records where bill_issued_on BETWEEN '" + sDateFinal+ "' AND '" +eDateFinal+"'";
        System.out.println(qry);
         try {
            db.setStmt((Statement) db.getCon().createStatement());
            ResultSet rs = ((java.sql.Statement) db.getStmt()).executeQuery(qry);
            if(rs.next()){
                jTable1.setModel(DbUtils.resultSetToTableModel(rs));
                System.out.println("I was here");
            }
            
            else{
                System.out.println("False");
            }


        } catch (Exception e) {
            System.out.println("Problem in query \n" + e);
        }

输出：

output:

 SELECT * FROM records where bill_issued_on BETWEEN '2015-01-01' AND '2015-12-28'
Problem in query 
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

推荐答案

首先，您将 BETWEEN 应用于字符串，但是显然你需要把它应用到日期。请参阅，例如： http://www.techonthenet.com/sql/between.php [ ^ ]。



更糟糕的是，从一开始就构建查询的方式是错误的。您的查询是通过连接从UI获取的字符串组成的。不仅重复的字符串连接是低效的（因为字符串是不可变的;我是否必须解释为什么它会使重复连接变坏？），但是有更重要的问题：它打开了通向良好的大门已知的漏洞称为 SQL注入。从UI获取的文本可以是任何内容，包括...... SQL代码片段。



这是它的工作原理：http://xkcd.com/327 [ ^ ]。



怎么办？只需阅读有关此问题和主要补救措施：参数化语句： http://en.wikipedia.org/ wiki / SQL_injection [ ^ ]。



请参阅：

https：// www .owasp.org / index.php / Preventing_SQL_Injection_in_Java [ ^ ]，

http://docs.oracle.com /javase/tutorial/jdbc/basics/prepared.html [ ^ ]，

https://www.owasp .ORG / index.php的/ Query_Parameterizatio n_Cheat_Sheet [ ^ ]。



请查看我过去的答案以获取更多详细信息（它们在.NET上，因此对于特定于Java的说明，请参阅上面的链接）：

< a href =http://www.codeproject.com/Answers/508631/ERORplusINplusUPATEplusplusplusinpluscom-ExecuteNo#answer1>在com.ExecuteNonQuery（）; 中更新EROR [ ^ ]，

名称未显示在名称中？ [ ^ ]。

-SA

First of all, you are applying BETWEEN to strings, but apparently you need to apply it to dates. Please see, for example: http://www.techonthenet.com/sql/between.php[^].

Worse, the way you compose your query is wrong from the very beginning. Your query is composed by concatenation with strings taken from UI. Not only repeated string concatenation is inefficient (because strings are immutable; do I have to explain why it makes repeated concatenation bad?), but there is way more important issue: it opens the doors to a well-known exploit called SQL injection. The text taken fro UI can be anything, including… fragment of SQL code.

This is how it works: http://xkcd.com/327[^].

What to do? Just read about this problem and the main remedy: parametrized statements: http://en.wikipedia.org/wiki/SQL_injection[^].

Please see:
https://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java[^],
http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html[^],
https://www.owasp.org/index.php/Query_Parameterization_Cheat_Sheet[^].

Please see my past answers for some more detail (they are on .NET, so for Java-specific directions, see the links above):
EROR IN UPATE in com.ExecuteNonQuery();[^],
hi name is not displaying in name?[^].

—SA

使用MS Access数据库时，正确q uery是：

When you work with MS Access database, proper query is:

SELECT * FROM records where bill_issued_on BETWEEN #2015-01-01# AND #2015-12-28#

请参阅：在Access查询中使用日期作为条件的示例 [ ^ ]



BTW：由于查询性能的原因，我不建议使用 SELECT * 。而不是它，使用 SELECT< Field_List>



第二个提示：使用参数 [ ^ ]。

See this: Examples of using dates as criteria in Access queries[^]

BTW: i do not recommend to use SELECT *, because of query performance. Rather than it, use SELECT <Field_List>

Second tip: use parameters[^].

PARAMETERS [datefrom] DATE, [dateto] DATE;
SELECT <Field_list>
FROM TableName
WHERE DateField BETWEEN [datefrom] AND [dateto]

如何打电话给它？请参阅： Java程序员的ADO.NET [ ^ ]



祝你好运！

How to call it? See this: ADO.NET for the Java Programmer[^]

Good luck!

这篇关于无法获得查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！