如何从Java中的sqlite中的多个表中进行选择？ [英] How do you select from multiple tables in sqlite in Java?

问题描述

我正在尝试学习如何在java程序中使用sqlite数据库。 （不是Android）。我去了这个链接，下载了jdbc库并复制了这个例子。该示例正常运行。然后我将另一个表添加到数据库中，并且想要连接两个表并从每个表中选择一列。我在从数据库进行查询的行上出现错误没有这样的列：'person.name'。我已尝试了许多不同的方法来加入表并选择，但每次我在 Table.columnName 上都会出现异常。有人能告诉我在sqlite中连接两个表的正确语法吗？这是我的代码：

I'm trying to learn how to use an sqlite database in a java program. (not Android). I went to this link, download the jdbc library and copied the example. The example worked without errors. I then added another table to the database and the wanted to join both tables and select once column from each. I get an error on the line that makes the query from the database no such column: 'person.name' . I've tried many different ways to join the tables and select, but every time I get an exception on Table.columnName. Can someone show me the correct syntax to join two tables in sqlite? Here's my code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Sample
{
  public static void main(String[] args) throws ClassNotFoundException
  {
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");

Connection connection = null;
try
{
  // create a database connection
  connection = DriverManager.getConnection("jdbc:sqlite:newSample.db");
  Statement statement = connection.createStatement();
  statement.setQueryTimeout(30);  // set timeout to 30 sec.

  statement.executeUpdate("drop table if exists person");
  statement.executeUpdate("drop table if exists purchase");
  statement.executeUpdate("create table person (id integer, name string)");
  statement.executeUpdate("create table purchase (id integer, name string)");
  statement.executeUpdate("insert into purchase values(1, 'shampoo')");
  statement.executeUpdate("insert into purchase values(1, 'cheese')");
  statement.executeUpdate("insert into purchase values (2, 'cherries')");
  statement.executeUpdate("insert into purchase values (3, 'yogurt')");
  statement.executeUpdate("insert into purchase values (3, 'butter')");
  statement.executeUpdate("insert into person values(1, 'leo')");
  statement.executeUpdate("insert into person values(2, 'yui')");
  statement.executeUpdate("insert into person values(3, 'Steve')");
  ResultSet rs = statement.executeQuery("select purchase.name, person.name from 
  purchase inner join person on person.id = purchase.id"); <-- This is where I get the error!!

  while(rs.next())
  {
    // read the result set
    System.out.println("name = " + rs.getString("person.name"));
    System.out.println("id = " + rs.getInt("person.id"));
    System.out.println("purchase = " + rs.getString("purchase.name"));
  }
}
catch(SQLException e)
{
  // if the error message is "out of memory", 
  // it probably means no database file is found
  System.err.println(e.getMessage());
}
finally
{
  try
  {
    if(connection != null)
      connection.close();
  }
  catch(SQLException e)
  {
    // connection close failed.
    System.err.println(e);
  }
}

}
}

推荐答案

SQLite文档说：

结果列的名称是该列的AS子句的值，如果有的话AS条款。如果没有AS子句，则列的名称未指定，可能会从SQLite的一个版本更改为下一个版本。

The name of a result column is the value of the "AS" clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next.

您应该使用SQLite碰巧使用的列名：

You should either use the column name that SQLite happens to use:

rs.getString("name")

或给结果列一个唯一的名称：

or give the result column a unique name:

executeQuery("select person.name AS person_name, ...")
rs.getString("person_name")

或只使用列索引：

rs.getString(1)

这篇关于如何从Java中的sqlite中的多个表中进行选择？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！