Select values from two different Java.Sql tables?

Question

Select values from two different Java.Sql tables?

Navigation

#1 by (4 votes)
#2 by (1 votes)

1

Hello, I am trying to create a method that returns me% String[] with the data from 2 different tables in this case is the table user and the table geolocation , the data of the user with its respective geolocation (city , neighborhood, latitude, longitude.

But I can not create a "correct" query.

static String[] getUserDataFromDb(JTextField id, JPasswordField pass) throws SQLException {

    String[] data = new String[9];

    Connection c = connectDB();

    Statement stmt = null;

    String query1 = "SELECT * FROM users INNER JOIN geolocation ON user.userid = geolocation.userid" ;

    stmt = c.createStatement();

    ResultSet rs = stmt.executeQuery(query1);

    while (rs.next()) {

        data[0] = rs.getString("userid").trim();
        data[1] = rs.getString("name").trim();
        data[2] = rs.getString("surname").trim();
        data[3] = rs.getString("email").trim();
        data[4] = rs.getString("password").trim();          
        data[5] = rs.getString("city").trim();
        data[6] = rs.getString("district").trim();
        data[7] = rs.getString("latitude").trim();
        data[8] = rs.getString("longitudes").trim();    

    }

    stmt.close();
    c.close();

    return data;

}

It always returns a error since the query is not well written.

The tables are:

users:

+-------+-------------+------+-----+---------------------+
| name  | surname     |     email        |password|userid|   
+-------+-------------+------+-----+---------------------+
| jose  | aguilar     | [email protected] | 123    |jose23|
+-------+-------------+------+-----+---------------------+

geolocation:

    +-----------+-------------+-----------+---------+----------+
    | city      | district    | latitude  |longitude|   userid |
    +-------+-------------+-------------------------+----------+
    | su ciudad | su barrio   | 1.2       | -1.3    |   jose23 |
    +-----------+-------------+-----------+---------+----------+

sql java

asked by Bryan Romero 11.10.2016 в 17:18

source

2 answers

1

I think you have miswritten the name of the table in the join:

ON user.userid = geolocation.userid

It should be:

ON users.userid=...

answered by 11.10.2016 в 17:32

How to subtract hours from a DateTime? What is the best way to include classes in php?

score 4 · Accepted Answer

Maybe it's because there are duplicate columns (userid) in the result. This error always appeared to me when there were two columns with the same name in the result of the query.

I imagine that when you want to do the

data[0] = rs.getString("userid").trim();

Java does not know which column you are referring to, since there are two

Can you put the complete error?

EDITION:

You have put a user after the WHERE. It should be users. To avoid this error through carelessness and as a good practice, you should use aliases

SELECT * FROM users u INNER JOIN geolocation g ON u.userid = g.userid