尝试编写java代码，使用开关从sqlite数据库中选择数据

Question

我正在尝试使用JDBC从数据库查询数据，但是我不能让SELECT SUM()在第三个开关情况下工作。所有其他情况和第三种情况下的SELECT金额工作得很好，但是当我尝试使用SELECT SUM(金额)时，它给出了错误&;no such column: 'amount'&;

下面是我的代码:

package net.sqlitetutorial;
import java.util.Scanner;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLMain {

private Connection connect() {

String url = "jdbc:sqlite:C://sqlite/db/test.db";
Connection conn = null;
try {
conn = DriverManager.getConnection(url);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}



public void selectAll(){
String sql = "SELECT name, amount FROM names ORDER BY amount desc";

try (Connection conn = this.connect();
Statement stmt  = conn.createStatement();
ResultSet rs    = stmt.executeQuery(sql)){

while (rs.next()) {
System.out.println(rs.getString("name") +  "t" + 
rs.getInt("amount"));

}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
public void selectNames(){
String sql = "SELECT name FROM names ORDER BY name";

try (Connection conn = this.connect();
Statement stmt  = conn.createStatement();
ResultSet rs    = stmt.executeQuery(sql)){

while (rs.next()) {
System.out.println(rs.getString("name"));

}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}

public void selectAmount(){
String sql = "SELECT sum(amount) FROM names";

try (Connection conn = this.connect();
Statement stmt  = conn.createStatement();
ResultSet rs    = stmt.executeQuery(sql)){

while (rs.next()) {
System.out.println(rs.getInt("amount"));

}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}



public static void main(String[] args) {
SQLMain app = new SQLMain();

int number;

Scanner console = new Scanner(System.in);

System.out.println("Choose one by entering a number:n 1. List all ordered by most popular name firstn 2. List names in alphabetical order");
number = console.nextInt();

switch (number)
{
case 1 :
app.selectAll();
break;

case 2 :
app.selectNames();
break;
case 3 :
app.selectAmount();
break;
default:
System.out.println("Invalid input");

}

}
}

这是它给出的错误:no such column: 'amount'

Answer 1

在此语句中:

SELECT sum(amount) FROM names

没有名称amount

返回的列。必须为返回的列设置别名:

SELECT sum(amount) AS amount FROM names

或者，在不混叠列的情况下，您可以通过索引1:

获得它的值。

System.out.println(rs.getInt(1));

Answer 2

这是因为结果包含SUM(amount)而不是amount列。

例如，您可以通过将查询更改为 来修复此问题。

"SELECT sum(amount) AS amount FROM names"

或通过索引

获取结果

rs.getInt(1);

Answer 3

你的查询真的没有一个amount列-它有sum(amount)。您可以像forpas的回答建议的那样使用别名，或者仅使用索引(记住- JDBC中的列索引是基于一个的):

while (rs.next()) {
System.out.println(rs.getInt(1));
}