Java: 在 PreparedStatement 中填充 SQLite 参数导致错误。

huangapple go评论81阅读模式
英文:

Java: SQLite parameter filling in a PreparedStatement results in an error

问题

以下是翻译好的内容:

        Scanner scanner = new Scanner(System.in);
        Connection db = DriverManager.getConnection("jdbc:sqlite:kurssit.db");
        Statement s = db.createStatement();
        PreparedStatement p;
        p = db.prepareStatement("SELECT SUM(K.laajuus) "
                + "FROM Suoritukset S, Kurssit K "
                + "WHERE S.kurssi_id = K.id AND S.paivays LIKE ?;");
        p.setString(1, scanner.nextLine());
        try {
            ResultSet r = p.executeQuery();
            System.out.println("Amount: " + r.getString(1));
        } catch (SQLException e) {
            System.out.println("Data not found.");
        }
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0
	at org.sqlite.core.CorePreparedStatement.batch(CorePreparedStatement.java:121)
	at org.sqlite.jdbc3.JDBC3PreparedStatement.setString(JDBC3PreparedStatement.java:421)
	at Main.main(Main.java:28)
C:\Users\user\AppData\Local\NetBeans\Cache\12.0\executor-snippets\run.xml:111: 发生以下错误:
C:\Users\user\AppData\Local\NetBeans\Cache\12.0\executor-snippets\run.xml:68: Java 返回了: 1

在此缩短的代码版本中,出现错误的第 28 行是 p.setString(1, scanner.nextLine());

//Imports
import java.sql.*;
import java.util.*;

非常感谢。

英文:

I am trying to read data from a SQL database from Java using SQLite-JDBC, but upon filling parameters to a PreparedStatement I am getting an error. Important parts of the code and the error are below.

        Scanner scanner = new Scanner(System.in);
        Connection db = DriverManager.getConnection("jdbc:sqlite:kurssit.db");
        Statement s = db.createStatement();
        PreparedStatement p;
                        p = db.prepareStatement("SELECT SUM(K.laajuus) "
                                + "FROM Suoritukset S, Kurssit K "
                                + "WHERE S.kurssi_id = K.id AND S.paivays LIKE '" + "?" + "%';");
                        p.setString(1, scanner.nextLine());
                        try {
                            ResultSet r = p.executeQuery();
                            System.out.println("Amount: " + r.getString(1));
                        } catch (SQLException e) {
                            System.out.println("Data not found.");
                        }
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0
	at org.sqlite.core.CorePreparedStatement.batch(CorePreparedStatement.java:121)
	at org.sqlite.jdbc3.JDBC3PreparedStatement.setString(JDBC3PreparedStatement.java:421)
	at Main.main(Main.java:28)
C:\Users\user\AppData\Local\NetBeans\Cache.0\executor-snippets\run.xml:111: The following error occurred while executing this line:
C:\Users\user\AppData\Local\NetBeans\Cache.0\executor-snippets\run.xml:68: Java returned: 1

The line 28, the line of the error, is in this shortened version of the code the one saying p.setString(1, scanner.nextLine());.

//Imports
import java.sql.*;
import java.util.*;

Many thanks in advance.

答案1

得分: 2

以下是翻译好的内容:

SQL语句构建不正确。
? 占位符不能位于单引号内,因为如果是这样,它将不被视为占位符,而只是一个字符串。
setString() 后,它将被正确地替换为字符串。
更改为:

p = db.prepareStatement(
  "SELECT SUM(K.laajuus) FROM Suoritukset S, Kurssit K WHERE S.kurssi_id = K.id AND S.paivays LIKE ? || '%';"
);

另外需要注意的是,您应该使用正确的连接语法:

p = db.prepareStatement(
  "SELECT SUM(K.laajuus) FROM Suoritukset S INNER JOIN Kurssit K ON S.kurssi_id = K.id WHERE S.paivays LIKE ? || '%';"
);
英文:

The sql statement is not constructed correctly.<br/>
The ? placeholder must not be inside single quotes, because if it is then it is not considered as a placeholder but just a string.<br/>
It will be replaced properly by a string after setString().<br/>
Change to this:

p = db.prepareStatement(
  &quot;SELECT SUM(K.laajuus) FROM Suoritukset S, Kurssit K WHERE S.kurssi_id = K.id AND S.paivays LIKE ? || &#39;%&#39;;&quot;
);

As a side note, you should use proper join syntax:

p = db.prepareStatement(
  &quot;SELECT SUM(K.laajuus) FROM Suoritukset S INNER JOIN Kurssit K ON S.kurssi_id = K.id WHERE S.paivays LIKE ? || &#39;%&#39;;&quot;
);

答案2

得分: -1

你需要像这样使用列名:

System.out.println("Amount: " + r.getString("COLUMN_NAME"));

或者

System.out.println("Amount: " + r.getNString(1));

其中,第一个方法允许你通过String名称访问列,而第二个方法允许你通过int数字访问表列。

英文:

You need to use the column name like this:

System.out.println(&quot;Amount: &quot; + r.getString(&quot;COLUMN_NAME&quot;));

or

System.out.println(&quot;Amount: &quot; + r.getNString(1));

The first of these, allows you to access the column given a String name, while the second allows you to access a table column using an int number.

huangapple
  • 本文由 发表于 2020年10月4日 23:33:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/64196587.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定