java - SQLite 查询未在 Java 中运行
问题描述
我使用 DB Browser for SQLite 创建了一个数据库。现在我正在尝试运行查询以从数据库中检索数据,但我遇到了一个问题。当我在 DB Browser 中运行查询时,查询运行顺利。当我尝试在 Java 中运行相同的查询时,我收到一条错误消息。我对查询字符串进行了三次检查。我希望确保我没有SQLite
错误地使用关键字。为什么我在运行 Java 查询时会收到错误消息?
SELECT
subject.id, main.name, main.link, subject.name, main_subject.weight
FROM
main_subject
LEFT JOIN
main ON main_subject.mainId = main.id
LEFT JOIN
subject ON main_subject.subjectId = subject.id
MCVE:
主要的
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import java.io.File;
import java.io.IOException;
import java.lang.reflect.Type;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
* @author sedrick
*/
public class Main
{
public static void main(String[] args)
{
try (DatabaseHandler databaseHandler = new DatabaseHandler()) {
List<BySubjectItemTwo> bySubjectItemTwos = databaseHandler.getBySubjectItems();
bySubjectItemTwos.forEach(System.out::println);
//databaseHandler.getByTitleItems().forEach(System.out::println);
}
catch (Exception ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
数据库处理器
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author sedrick
*/
public class DatabaseHandler implements AutoCloseable
{
String dbString = "jdbc:sqlite:qDatabase.sqlite3";
private Connection conn;
public DatabaseHandler()
{
try {
conn = DriverManager.getConnection(dbString);
System.out.println("Connected to qDatabase!");
}
catch (SQLException ex) {
Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);
}
}
@Override
public void close() throws Exception
{
conn.close();
}
public List<BySubjectItemTwo> getBySubjectItems()
{
List<BySubjectItemTwo> returnList = new ArrayList();
String sqlString = "SELECT subject.id, main.name, main.link, subject.name, main_subject.weight FROM main_subject LEFT JOIN main ON main_subject.mainId = main.id LEFT JOIN subject ON main_subject.subjectId = subject.id";
try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sqlString)) {
while (rs.next()) {
System.out.println(rs.getInt("subject.id") + " - " + rs.getString("main.name") + " - " + rs.getString("main.link") + " - " + rs.getString("subject.name") + " - " + rs.getInt("main_subject.weight"));
//returnList.add(new BySubjectItemTwo(rs.getInt("subject.id"), rs.getString("main.name"), rs.getString("main.link"), rs.getString("subject.name"), rs.getInt("main_subject.weight")));
}
}
catch (SQLException e) {
System.out.println(e.getMessage());
}
return returnList;
}
}
BySubjectItemTwo
/**
*
* @author sedrick
*/
public class BySubjectItemTwo
{
private int subjectId;
private String mainName;
private String mainLink;
private String subjectName;
private int mainSubjectWeight;
public BySubjectItemTwo(int subjectId, String mainName, String mainLink, String subjectName, int mainSubjectWeight)
{
this.subjectId = subjectId;
this.mainName = mainName;
this.mainLink = mainLink;
this.subjectName = subjectName;
this.mainSubjectWeight = mainSubjectWeight;
}
public int getSubjectWeight()
{
return mainSubjectWeight;
}
public void setSubjectWeight(int mainSubjectWeight)
{
this.mainSubjectWeight = mainSubjectWeight;
}
public int getSubjectId()
{
return subjectId;
}
public void setSubjectId(int subjectId)
{
this.subjectId = subjectId;
}
public String getMainName()
{
return mainName;
}
public void setMainName(String mainName)
{
this.mainName = mainName;
}
public String getMainLink()
{
return mainLink;
}
public void setMainLink(String mainLink)
{
this.mainLink = mainLink;
}
public String getSubjectName()
{
return subjectName;
}
public void setSubjectName(String subjectName)
{
this.subjectName = subjectName;
}
@Override
public String toString()
{
StringBuilder sb = new StringBuilder();
sb.append("BySubjectItemTwo{subjectId=").append(subjectId);
sb.append(", mainName=").append(mainName);
sb.append(", mainLink=").append(mainLink);
sb.append(", subjectName=").append(subjectName);
sb.append(", subjectWeight=").append(mainSubjectWeight);
sb.append('}');
return sb.toString();
}
}
错误
----------< sed.work:CreateDatabaseByTitleAndSubjectDatabase >----------
Building CreateDatabaseByTitleAndSubjectDatabase 1.0-SNAPSHOT
--------------------------------[ jar ]---------------------------------
--- exec-maven-plugin:1.5.0:exec (default-cli) @ CreateDatabaseByTitleAndSubjectDatabase ---
Connected to qDatabase!
[SQLITE_ERROR] SQL error or missing database (ambiguous column name: subject.id)
------------------------------------------------------------------------
BUILD SUCCESS
------------------------------------------------------------------------
Total time: 1.484 s
Finished at: 2020-10-16T12:42:35-05:00
------------------------------------------------------------------------
Netbean 的信息
Product Version: Apache NetBeans IDE 12.1
Java: 15; Java HotSpot(TM) 64-Bit Server VM 15+36-1562
Runtime: Java(TM) SE Runtime Environment 15+36-1562
System: Windows 10 version 10.0 running on amd64; Cp1252; en_US (nb)
Maven Project
解决方案
这个:
FROM main_subject, main, subject
是您查询的问题。
, main, subject
从你的陈述中删除。
表main
和subject
在连接后正确LEFT
连接。
当您也在FROM
子句中使用它们时,这会创建额外的引用并CROSS
连接到相同的表。
此外,虽然 SQLite 允许查询返回超过 1 个具有相同名称的列,但最好为列设置别名,以便在结果集中所有列名都不同。
所以给main.name
and取别名subject.name
,如下所示:
SELECT subject.id, main.name AS main_name, ..., subject.name AS subject_name, ...
并且当您检索列值时不要使用表前缀:
System.out.println(rs.getInt("id") + " - " + rs.getString("main_name") + " - " + rs.getString("link") + " - " + rs.getString("subject_name") + " - " + rs.getInt("weight"));
推荐阅读
- c# - 如何动态创建多个数组 [5,5] ,并用随机数填充它们
- ftp - 尝试镜像文件夹时出现 Lftp 550 错误。FileZilla 没问题
- javascript - Vue & Nuxt Js:为什么不能在字符串'bottom:30px;right:30px;'上创建属性'display'?
- scala - 如何更新数据框中的多个列
- javascript - How to test with the marble approach when the returm is a EMPTY observable in effect?
- python - 迭代行和列的更有效方法?
- c# - C#字典,一个键的值总和?
- css - Unable to center an element when Windows scaling is enabled (125%)
- bash - 从 CSV 文件中提取两列,将它们拆分为 while read 循环并对每个字符串执行不同的命令
- java - 打印密钥的 getGeneratedKeys() 不起作用。为什么?