JDBC-查询不返回任何值 [英] JDBC - Query returns no values

问题描述

我正在尝试使用Java类上的JDBC进行查询，以返回一系列简单的结果.查询只需要1个联接就可以工作，由于某种原因，它不返回任何值.但是，在Oracle SQL Developer上运行此查询时，将显示正确的结果.下面是我当前正在使用的代码.

I am trying to make a query that returns a simple series of results using JDBC on a java class. The Query only needs 1 join for it to work yet, for some reason, it does not return any values. However, when this query is ran on the Oracle SQL Developer, the correct results are shown. below is the code i am currently using.

query = "select h.house_id, h.house_address, h.house_type, h.status, l.firstname, l.surname, h.price_per_month "
                  + "from houses_tbl h join landlord_tbl l on l.landlord_id = h.landlord_id";

conn = connectToDatabase();
Statement stmt = null;
ResultSet rs = null;

try {
    stmt = conn.createStatement();
    rs = stmt.executeQuery(query);
    System.out.println(query);
} catch (SQLException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
}

要检索数据

response.setContentType("application/json");

fullJsonString = "{";
fullJsonString += "\"houses\":[";
ArrayList<HouseObj> allHouses = new ArrayList<HouseObj>();
try {
    while (rs.next()) {
        int houseID = rs.getInt(1);
        Struct address = (Struct) rs.getObject(2);
        Object[] taskAddress = address.getAttributes();
        String houseAddressStreet = taskAddress[0].toString();
        String houseAddressTown = taskAddress[1].toString();
        String houseAddressCounty = taskAddress[2].toString();
        String houseAddressCountry = taskAddress[3].toString();
        String houseAddressPostcode = taskAddress[4].toString();
        String houseFullAddress = houseAddressStreet + ", "
                + houseAddressTown + ", " + houseAddressCounty
                + ", " + houseAddressCountry + ", "
                + houseAddressPostcode;
        String type = rs.getString(3);
        String status = rs.getString(4);
        String landlord = rs.getString(5)+" "+rs.getString(6);
        int price = rs.getInt(7);

        HouseObj newClient = new HouseObj(houseID,
        houseFullAddress, type, status, landlord, price);
        allHouses.add(newClient);
    }

    System.out.println("Number Of Houses : "+allHouses.size());
    for (int i = 0; i < allHouses.size(); i++) {
        if (i == allHouses.size() - 1) {
            fullJsonString += "{\"id\":\""
                    + allHouses.get(i).getHouseId() + "\","
                    + "\"address\":\""
                    + allHouses.get(i).getAddress() + "\","
                    + "\"type\":\""
                    + allHouses.get(i).getType() + "\","
                    + "\"status\":\""
                    + allHouses.get(i).getStatus() + "\","
                    + "\"landlord\":\""
                    + allHouses.get(i).getLandlord() + "\","
                    + "\"price\":\""
                    + allHouses.get(i).getPrice() + "\"}";
        } else {
            fullJsonString += "{\"id\":\""
                    + allHouses.get(i).getHouseId() + "\","
                    + "\"address\":\""
                    + allHouses.get(i).getAddress() + "\","
                    + "\"type\":\""
                    + allHouses.get(i).getType() + "\","
                    + "\"status\":\""
                    + allHouses.get(i).getStatus() + "\","
                    + "\"landlord\":\""
                    + allHouses.get(i).getLandlord() + "\","
                    + "\"price\":\""
                    + allHouses.get(i).getPrice() + "\"},";
        }

    }
    fullJsonString += "]}";

} //Catch Exception Below

输出

Number Of Houses : 0
{"houses":[]}

对于解决此问题的任何帮助，我们深表感谢.

Any help to resolve this is greatly appreciated.

推荐答案

从您发布的代码中，我看不到结果集的任何打印内容. 您怎么知道它什么也不返回? 您可以在while(rs.next)周期(if，计数器)之前/之后放置一个打印语句，以查看其是否为空吗? 如果为空，请尝试从查询中删除表别名

From your posted code, I don't see any printing of the resultset. How do you know it's not returning anything? Can you put a print statement before/after the while (rs.next) cycle (an if, a counter) to see if it' s actually empty? If it is empty, try removing table alias from your query

修改从houses_tbl中选择house_id"中的查询，并在

Modify the query in "select house_id from houses_tbl", execute the query and exactly after

stmt = conn.createStatement(); 
rs = stmt.executeQuery(query); 

放入

if (rs.next()){ 
    System.out.println("Got house!"); 
}else{ 
    System.out.println("No houses here!"); 
} 

，并暂时注释掉/跳过打印代码.这是为了检查您的环境在这种情况下的基线可操作性.如果这不起作用，对我来说一定是数据库/驱动程序问题

and momentarily comment out/bypass the printing code. This is to check baseline operativity of you env in that context. If this doesn't work, must be a database/driver issue, to me

这篇关于JDBC-查询不返回任何值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！