java 从数据库的两个表执行 INNER JOIN 的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40943961/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to do INNER JOIN from two tables of a data base
提问by
I have two tables:
我有两个表：
- usuario (user in english): nombre, apellido, usuario, contrasena, id_perfil
- perfil (profile in english): id_perfil, nombre
- usuario（英语用户）：nombre、apellido、usuario、contrasena、id_perfil
- perfil（英文配置文件）：id_perfil，nombre
I have a login on my program using Java and MySQL and I want to know if the username and password entered by the user is correct, to send him to another Jframe.
我使用 Java 和 MySQL 登录了我的程序，我想知道用户输入的用户名和密码是否正确，以便将他发送到另一个 Jframe。
I don't know much about SQL query but, I did my best here (I'm passing the username and password directly to the function):
我对 SQL 查询不太了解，但是，我在这里尽力了（我将用户名和密码直接传递给函数）：
public boolean login(String usuario, String contrasena) {
Dato_login d_lgn = new Dato_login();
boolean resultado = false;
sSQL = "SELECT u.nombre, u.apellido, u.usuario, u.contrasena, u.id_perfil, p.nombre AS perfil_nombre FROM "
+ "usuario U INNER JOIN perfil P u.id_perfil = p.id "
+ "WHERE u.usuario='" + usuario + "' AND u.contrasena='" + contrasena + "'";
// Java 7 try-with-resources
try (PreparedStatement pstm = con.prepareStatement(sSQL);
ResultSet rs = pstm.executeQuery(sSQL)) {
while (rs.next()) {
if (d_lgn.getContrasena().equals(contrasena)) {
resultado = true;
} else {
resultado = false;
}
d_lgn.setPerfil(rs.getString("perfil_nombre"));
d_lgn.setUsuario(rs.getString("usuario"));
d_lgn.setNombre(rs.getString("nombre"));
d_lgn.setApellido(rs.getString("apellido"));
d_lgn.setContrasena(rs.getString("contrasena"));
}
} catch (SQLException e) {
JOptionPane.showMessageDialog(null, "SQLException:\n" + e, "Error: login(String usuario, String contrasena)", JOptionPane.ERROR_MESSAGE);
}
return resultado;
}
Is not working, it keeps telling me that there is not any user to log in. What I need to change?
不工作，它一直告诉我没有任何用户登录。我需要更改什么？
Also I would like to receive the name of the profile instead of the id of the user, I tried with INNER JOIN but I don't understand how it works correctly yet.
此外，我想接收配置文件的名称而不是用户的 ID，我尝试使用 INNER JOIN，但我还不明白它是如何正常工作的。
Table:
桌子：
Error received:
收到错误：
Error in SQL Syntax
SQL 语法错误
采纳答案by C. Smith
In this part:
在这部分：
sSQL = "SELECT u.nombre, u.apellido, u.usuario, u.contrasena, u.id_perfil FROM usuario U INNER JOIN perfil P ON p.nombre=u.nombre WHERE u.usuario='"
+ usuario + "' AND u.contrasena='" + contrasena + "'";
I don't see where a variables contrasenaor usariois defined. Should that be ...AND u.usario='" + username + "' AND u.contrasena='" + password + "'";instead? (putting aside a moment that this exposes a SQL Injection vulnerability). Also, it seems suspect that you're joining your usarioand perfiltables on nombre. Is it the case that a User's name would be the same as their Profile name? Without understanding your domain and data model better, I can't really say.
我没有看到变量contrasena或usario定义的位置。应该是这样...AND u.usario='" + username + "' AND u.contrasena='" + password + "'";吗？（暂且不说这会暴露 SQL 注入漏洞）。此外，似乎怀疑您正在加入您的usario和perfil表nombre。用户名是否会与他们的个人资料名称相同？如果没有更好地了解您的领域和数据模型，我真的不能说。
If you also wanted to retrieve the profile name as well, your query could be this:
如果您还想检索配置文件名称，您的查询可能是这样的：
SELECT u.nombre, u.apellido, u.usuario, u.contrasena, p.nombre as perfil_nombre
FROM usario u
JOIN perfil p ON u.id_perfil = p.id_perfil
WHERE u.usuario = ? and u.contrasena = ?
Notice I'm joining usarioand perfilon the id columns instead of nombre. I think you want the usario.perfil_idto match the perfil.id_perfilcolumn.
通知我参加usario，并perfil在ID列，而不是nombre。我认为您希望usario.perfil_id与perfil.id_perfil列匹配。
Instead of con.createStatement()use con.createPreparedStatement(). See Using Prepared Statementsfor more information on that.
而不是con.createStatement()使用con.createPreparedStatement()。有关更多信息，请参阅使用准备好的语句。
Lastly, to access the perfil.nombrefrom the ResultSetdo this: rs.getString("perfil_nombre");
最后，要访问perfil.nombrefromResultSet执行以下操作：rs.getString("perfil_nombre");
Also I am returning perfil.nombreinstead of usario.nombrebecause you mentioned your want the profile name instead of the user name.
我也回来了，perfil.nombre而不是usario.nombre因为你提到你想要配置文件名而不是用户名。
