SQLite查询在Java中无法运行

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

SQLite Query Not Running in Java

问题

我使用了DB Browser for SQLite创建了一个数据库。现在我正在尝试运行查询以从数据库中检索数据，但遇到了问题。当我在DB Browser中运行查询时，查询顺利运行。但当我尝试在Java中运行相同的查询时，我收到一个错误消息。我三次检查了查询字符串。我确认没有错误使用SQLite关键字。为什么我在Java中运行这个查询时会出错？

  1. SELECT
  2. 	subject.id, main.name, main.link, subject.name, main_subject.weight
  3. FROM 
  4. 	main_subject
  5. LEFT JOIN 
  6. 	main ON main_subject.mainId = main.id
  7. LEFT JOIN 
  8. 	subject ON main_subject.subjectId = subject.id

查询在DB Browser中运行正确。

MCVE:

Main

  1. import com.google.gson.Gson;
  2. import com.google.gson.reflect.TypeToken;
  3. import java.io.File;
  4. import java.io.IOException;
  5. import java.lang.reflect.Type;
  6. import java.nio.file.Files;
  7. import java.util.ArrayList;
  8. import java.util.LinkedHashSet;
  9. import java.util.List;
  10. import java.util.Set;
  11. import java.util.logging.Level;
  12. import java.util.logging.Logger;
  14. public class Main
  15. {
  16.     public static void main(String[] args)
  17.     {
  18.         try (DatabaseHandler databaseHandler = new DatabaseHandler()) {
  19.             List<BySubjectItemTwo> bySubjectItemTwos = databaseHandler.getBySubjectItems();
  20.             bySubjectItemTwos.forEach(System.out::println);
  21.         }
  22.         catch (Exception ex) {
  23.             Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
  24.         }
  25.     }
  26. }

DatabaseHandler

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.ArrayList;
  8. import java.util.List;
  9. import java.util.logging.Level;
  10. import java.util.logging.Logger;
  12. public class DatabaseHandler implements AutoCloseable
  13. {
  14.     String dbString = "jdbc:sqlite:qDatabase.sqlite3";
  15.     private Connection conn;
  17.     public DatabaseHandler()
  18.     {
  19.         try {
  20.             conn = DriverManager.getConnection(dbString);
  21.             System.out.println("Connected to qDatabase!");
  22.         }
  23.         catch (SQLException ex) {
  24.             Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);
  25.         }
  26.     }
  28.     @Override
  29.     public void close() throws Exception
  30.     {
  31.         conn.close();
  32.     }
  34.     public List<BySubjectItemTwo> getBySubjectItems()
  35.     {
  36.         List<BySubjectItemTwo> returnList = new ArrayList();
  38.         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";
  39.         try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sqlString)) {
  40.             while (rs.next()) {
  41.                 System.out.println(rs.getInt("subject.id") + " - " + rs.getString("main.name") + " - " + rs.getString("main.link") + " - " + rs.getString("subject.name") + " - " + rs.getInt("main_subject.weight"));
  42.             }
  43.         }
  44.         catch (SQLException e) {
  45.             System.out.println(e.getMessage());
  46.         }
  48.         return returnList;
  49.     }  
  50. }

BySubjectItemTwo

  1. public class BySubjectItemTwo
  2. {
  3.     private int subjectId;
  4.     private String mainName;
  5.     private String mainLink;
  6.     private String subjectName;
  7.     private int mainSubjectWeight;
  9.     public BySubjectItemTwo(int subjectId, String mainName, String mainLink, String subjectName, int mainSubjectWeight)
  10.     {
  11.         this.subjectId = subjectId;
  12.         this.mainName = mainName;
  13.         this.mainLink = mainLink;
  14.         this.subjectName = subjectName;
  15.         this.mainSubjectWeight = mainSubjectWeight;
  16.     }
  18.     public int getSubjectWeight()
  19.     {
  20.         return mainSubjectWeight;
  21.     }
  23.     public void setSubjectWeight(int mainSubjectWeight)
  24.     {
  25.         this.mainSubjectWeight = mainSubjectWeight;
  26.     }
  28.     public int getSubjectId()
  29.     {
  30.         return subjectId;
  31.     }
  33.     public void setSubjectId(int subjectId)
  34.     {
  35.         this.subjectId = subjectId;
  36.     }
  38.     public String getMainName()
  39.     {
  40.         return mainName;
  41.     }
  43.     public void setMainName(String mainName)
  44.     {
  45.         this.mainName = mainName;
  46.     }
  48.     public String getMainLink()
  49.     {
  50.         return mainLink;
  51.     }
  53.     public void setMainLink(String mainLink)
  54.     {
  55.         this.mainLink = mainLink;
  56.     }
  58.     public String getSubjectName()
  59.     {
  60.         return subjectName;
  61.     }
  63.     public void setSubjectName(String subjectName)
  64.     {
  65.         this.subjectName = subjectName;
  66.     }
  68.     @Override
  69.     public String toString()
  70.     {
  71.         StringBuilder sb = new StringBuilder();
  72.         sb.append("BySubjectItemTwo{subjectId=").append(subjectId);
  73.         sb.append(", mainName=").append(mainName);
  74.         sb.append(", mainLink=").append(mainLink);
  75.         sb.append(", subjectName=").append(subjectName);
  76.         sb.append(", subjectWeight=").append(mainSubjectWeight);
  77.         sb.append('}');
  78.         return sb.toString();
  79.     }
  80. }

错误

  1. ----------< sed.work:CreateDatabaseByTitleAndSubjectDatabase >----------
  2. 构建 CreateDatabaseByTitleAndSubjectDatabase 1.0-SNAPSHOT
  3. -------------------------------[ jar ]---------------------------------
  4. --- exec-maven-plugin:1.5.0:exec (default-cli) @ CreateDatabaseByTitleAndSubjectDatabase ---
  5. Connected to qDatabase!
  6. [SQLITE_ERROR] SQL error or missing database (ambiguous column name: subject.id)
  7. ------------------------------------------------------------------------
  8. 构建成功
  9. ------------------------------------------------------------------------
  10. 总时间: 1.484 s
  11. 完成于: 2020-10-16T12:42:35-05:00
  12. ------------------------------------------------------------------------

Netbean's 信息

  1. 产品版本: Apache NetBeans IDE 12.1
  2. Java: 15; Java HotSpot(TM) 64-Bit Server VM 15+36-1562
  3. 运行时: Java(TM) SE Runtime Environment 15+36-1562
  4. 系统: Windows 10 version 10.0 running on amd64; Cp1252; en_US (nb)
  5. Maven 项目
英文:

I created a database using DB Browser for SQLite. Now I am trying to run queries to retrieve data from the database, and I have run into an issue. When I run the query in DB Browser, the query runs smoothly. When I attempt to run the same query in Java, I get an error message. I triple-checked the query string. I looked to make sure I was not using SQLite keywords incorrectly. Why am I getting an error when I run this query Java?

  1. SELECT
  2. subject.id, main.name, main.link, subject.name, main_subject.weight
  3. FROM 
  4. main_subject
  5. LEFT JOIN 
  6. main ON main_subject.mainId = main.id
  7. LEFT JOIN 
  8. subject ON main_subject.subjectId = subject.id

The query running correctly in DB Browser.
SQLite查询在Java中无法运行

MCVE:

Main

  1. import com.google.gson.Gson;
  2. import com.google.gson.reflect.TypeToken;
  3. import java.io.File;
  4. import java.io.IOException;
  5. import java.lang.reflect.Type;
  6. import java.nio.file.Files;
  7. import java.util.ArrayList;
  8. import java.util.LinkedHashSet;
  9. import java.util.List;
  10. import java.util.Set;
  11. import java.util.logging.Level;
  12. import java.util.logging.Logger;
  13. /**
  14. * @author sedrick
  15. */
  16. public class Main
  17. {
  18. public static void main(String[] args)
  19. {
  20. try (DatabaseHandler databaseHandler = new DatabaseHandler()) {
  21. List&lt;BySubjectItemTwo&gt; bySubjectItemTwos = databaseHandler.getBySubjectItems();
  22. bySubjectItemTwos.forEach(System.out::println);
  23. //databaseHandler.getByTitleItems().forEach(System.out::println);
  24. }
  25. catch (Exception ex) {
  26. Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
  27. }
  28. }
  29. }

DatabaseHandler

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.ArrayList;
  8. import java.util.List;
  9. import java.util.logging.Level;
  10. import java.util.logging.Logger;
  11. /**
  12. *
  13. * @author sedrick
  14. */
  15. public class DatabaseHandler implements AutoCloseable
  16. {
  17. String dbString = &quot;jdbc:sqlite:qDatabase.sqlite3&quot;;
  18. private Connection conn;
  19. public DatabaseHandler()
  20. {
  21. try {
  22. conn = DriverManager.getConnection(dbString);
  23. System.out.println(&quot;Connected to qDatabase!&quot;);
  24. }
  25. catch (SQLException ex) {
  26. Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);
  27. }
  28. }
  29. @Override
  30. public void close() throws Exception
  31. {
  32. conn.close();
  33. }
  34. public List&lt;BySubjectItemTwo&gt; getBySubjectItems()
  35. {
  36. List&lt;BySubjectItemTwo&gt; returnList = new ArrayList();
  37. String sqlString = &quot;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&quot;;
  38. try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sqlString)) {
  39. while (rs.next()) {
  40. System.out.println(rs.getInt(&quot;subject.id&quot;) + &quot; - &quot; + rs.getString(&quot;main.name&quot;) + &quot; - &quot; + rs.getString(&quot;main.link&quot;) + &quot; - &quot; + rs.getString(&quot;subject.name&quot;) + &quot; - &quot; + rs.getInt(&quot;main_subject.weight&quot;));
  41. //returnList.add(new BySubjectItemTwo(rs.getInt(&quot;subject.id&quot;), rs.getString(&quot;main.name&quot;), rs.getString(&quot;main.link&quot;), rs.getString(&quot;subject.name&quot;), rs.getInt(&quot;main_subject.weight&quot;)));
  42. }
  43. }
  44. catch (SQLException e) {
  45. System.out.println(e.getMessage());
  46. }
  47. return returnList;
  48. }  
  49. }

BySubjectItemTwo

  1. /**
  2. *
  3. * @author sedrick
  4. */
  5. public class BySubjectItemTwo
  6. {
  7. private int subjectId;
  8. private String mainName;
  9. private String mainLink;
  10. private String subjectName;
  11. private int mainSubjectWeight;
  12. public BySubjectItemTwo(int subjectId, String mainName, String mainLink, String subjectName, int mainSubjectWeight)
  13. {
  14. this.subjectId = subjectId;
  15. this.mainName = mainName;
  16. this.mainLink = mainLink;
  17. this.subjectName = subjectName;
  18. this.mainSubjectWeight = mainSubjectWeight;
  19. }
  20. public int getSubjectWeight()
  21. {
  22. return mainSubjectWeight;
  23. }
  24. public void setSubjectWeight(int mainSubjectWeight)
  25. {
  26. this.mainSubjectWeight = mainSubjectWeight;
  27. }
  28. public int getSubjectId()
  29. {
  30. return subjectId;
  31. }
  32. public void setSubjectId(int subjectId)
  33. {
  34. this.subjectId = subjectId;
  35. }
  36. public String getMainName()
  37. {
  38. return mainName;
  39. }
  40. public void setMainName(String mainName)
  41. {
  42. this.mainName = mainName;
  43. }
  44. public String getMainLink()
  45. {
  46. return mainLink;
  47. }
  48. public void setMainLink(String mainLink)
  49. {
  50. this.mainLink = mainLink;
  51. }
  52. public String getSubjectName()
  53. {
  54. return subjectName;
  55. }
  56. public void setSubjectName(String subjectName)
  57. {
  58. this.subjectName = subjectName;
  59. }
  60. @Override
  61. public String toString()
  62. {
  63. StringBuilder sb = new StringBuilder();
  64. sb.append(&quot;BySubjectItemTwo{subjectId=&quot;).append(subjectId);
  65. sb.append(&quot;, mainName=&quot;).append(mainName);
  66. sb.append(&quot;, mainLink=&quot;).append(mainLink);
  67. sb.append(&quot;, subjectName=&quot;).append(subjectName);
  68. sb.append(&quot;, subjectWeight=&quot;).append(mainSubjectWeight);
  69. sb.append(&#39;}&#39;);
  70. return sb.toString();
  71. }
  72. }

Error

  1. ----------&lt; sed.work:CreateDatabaseByTitleAndSubjectDatabase &gt;----------
  2. Building CreateDatabaseByTitleAndSubjectDatabase 1.0-SNAPSHOT
  3. --------------------------------[ jar ]---------------------------------
  4. --- exec-maven-plugin:1.5.0:exec (default-cli) @ CreateDatabaseByTitleAndSubjectDatabase ---
  5. Connected to qDatabase!
  6. [SQLITE_ERROR] SQL error or missing database (ambiguous column name: subject.id)
  7. ------------------------------------------------------------------------
  8. BUILD SUCCESS
  9. ------------------------------------------------------------------------
  10. Total time:  1.484 s
  11. Finished at: 2020-10-16T12:42:35-05:00
  12. ------------------------------------------------------------------------

Netbean's Info

  1. Product Version: Apache NetBeans IDE 12.1
  2. Java: 15; Java HotSpot(TM) 64-Bit Server VM 15+36-1562
  3. Runtime: Java(TM) SE Runtime Environment 15+36-1562
  4. System: Windows 10 version 10.0 running on amd64; Cp1252; en_US (nb)
  5. Maven Project

答案1

得分: 1

这是你查询的问题：

  1. FROM main_subject, main, subject

从你的语句中移除“，main, subject”。

表格main和subject在LEFT连接之后已经正确连接。

当你在FROM子句中也使用它们时，这会创建额外的引用并导致与相同表格的CROSS连接。

另外，虽然SQLite允许查询返回多个相同名称的列，但最好的做法是给列取别名，以便在结果集中所有列名都是不同的。

所以给main.name和subject.name取别名，就像这样：

  1. SELECT subject.id, main.name AS main_name, ..., subject.name AS subject_name, ...

当检索列值时不要使用表格前缀：

  1. System.out.println(rs.getInt("id") + " - " + rs.getString("main_name") + " - " + rs.getString("link") + " - " + rs.getString("subject_name") + " - " + rs.getInt("weight"));
英文:

This:

  1. FROM main_subject, main, subject

is the problem with your query.<br/>

Remove , main, subject from your statement.<br/>
The tables main and subject are correctly joined after the LEFT joins.<br/>
When you use them also in the FROM clause this creates additional references and CROSS joins to the same tables.<br/>

Also, although SQLite allows a query to return more than 1 columns with the same name, it is a good practice to alias columns so that in the resultset all column names are different.<br/>
So give aliases to main.name and subject.name, something like this:

  1. SELECT subject.id, main.name AS main_name, ..., subject.name AS subject_name, ...

and when you retrieve the column values don't use the table prefixes:

  1. System.out.println(rs.getInt(&quot;id&quot;) + &quot; - &quot; + rs.getString(&quot;main_name&quot;) + &quot; - &quot; + rs.getString(&quot;link&quot;) + &quot; - &quot; + rs.getString(&quot;subject_name&quot;) + &quot; - &quot; + rs.getInt(&quot;weight&quot;));

huangapple
  • 本文由 发表于 2020年10月17日 04:19:49
  • 转载请务必保留本文链接：https://go.coder-hub.com/64395900.html
  • java
  • sqlite
匿名

发表评论

匿名网友

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

确定