英文:
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(
"SELECT SUM(K.laajuus) FROM Suoritukset S, Kurssit K WHERE S.kurssi_id = K.id AND S.paivays LIKE ? || '%';"
);
As a side note, you should use proper join syntax:
p = db.prepareStatement(
"SELECT SUM(K.laajuus) FROM Suoritukset S INNER JOIN Kurssit K ON S.kurssi_id = K.id WHERE S.paivays LIKE ? || '%';"
);
答案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("Amount: " + r.getString("COLUMN_NAME"));
or
System.out.println("Amount: " + 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论