CODEWITHSUNDEEP
javasqliteinner-join
Craig Smith
Craig Smith

Reputation: 66

How do you select from multiple tables in sqlite in Java?

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);
  }
}

} }

Upvotes: 0

Views: 2601

Answers (2)

Shurado
Shurado

Reputation: 1

You should define alias for table name as below 

select pc.name, ps.name from purchase as pc inner join person as ps on ps.id = pc.id

Upvotes: 0

CL.
CL.

Reputation: 180060

The SQLite documentation says:

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.

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")

or just use the column index:

rs.getString(1)

Upvotes: 1

Related Questions