java - Java executeQuery返回null

我正在尝试编写一种方法来检查我的数据库，如果用户有权执行某种方法。

执行查询后得到的响应为null。

我有以下方法可以做到这一点：

    public boolean checkRole(String encToken,String methodName)throws Exception, RuntimeException{
     CryptoHelper crypto = new CryptoHelper();
     SecretKeySpec key= new SecretKeySpec(keyString.getBytes("UTF-8"),"AES");
    try{
    String token = crypto.decrypt(encToken.toString(), key);
      String [] parts = token.split( ":" );
      String user = parts[0];

      String query ="SELECT EXISTS(SELECT * FROM data INNER JOIN Permissions "
            + "ON data.Role = Permissions.Permission "
            + "where Username = '" + user + "' AND Function ='" + methodName + "')";
      res = stm.executeQuery(query);

      if(res.equals(true)){
            System.out.println("Welcome " + user );
            return true;
          }
  }
       catch(Exception e){
        System.out.println(e);
       }
    return false;

  }

我还检查了我是否传递了正确的用户名和methodName，这似乎很好。

数据库结构：

USE DB;
DROP TABLE IF EXISTS `data`;
CREATE TABLE IF NOT EXISTS `data` (
  `id_u` int(11) NOT NULL AUTO_INCREMENT,
  `Username` varchar(30) NOT NULL,
  `Password` varchar(64) NOT NULL,
  `Salt` varchar(64) NOT NULL,
  `Role` int(30)  NOT NULL,
);

--
-- Dumping data for table `data`
--

INSERT INTO `data` (`id_u`,`Username`, `Password`, `Salt`,`Role`) VALUES
(1,'Mike', 'TzBql1WR9wZjN0LoKr2OBk2majc=', '4NWwJULan8U=','1'), --- password : ThisTheFirstPassword
(2,'Cecilia', 'TzBql1WR9wZjN0LoKr2OBk2majc=', '4NWwJULan8U=','1'),
(3,'Erika', 'iubXIju+Hd+EOgIuivTx3RbRDoU=', '2MWwJULan8U=','2'),
(4,'Alice', 'mWrE8czs6KkOeP1WiMyn0NEnKGw=', '4NWSJULgn8U=','2'),
(5,'Bob', 'YNvbZBcchzXYRyRJBx5WkPmwxfo=', '4NWwJILan9U=','3'),
(6,'David', 'OPhte5nto3U+rJucbb3GUTGCSiI=', '4NWwFULan8X=','3');
COMMIT;

--
-- Table structure for table `Permissions`
--

DROP TABLE IF EXISTS `Permissions`;
CREATE TABLE IF NOT EXISTS `Permissions` (
  `id_p` int(11) NOT NULL AUTO_INCREMENT,
  `Function` varchar(30) NOT NULL,
  `Permission` int(30) NOT NULL,
) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Permissions`
--

INSERT INTO `Permissions` (`id_p`, `Function`, `Permission`) VALUES
(1, 'print', 1),
(2, 'print', 2),
(3, 'print', 3),
(4, 'queue', 1),
(5, 'queue', 2),
(6, 'queue', 3),
(7, 'topQueue', 1),
(8, 'topQueue', 2),
(9, 'restart', 1),
(10, 'restart', 2);
(11, 'restart', 4);
(12, 'start', 1);
(13, 'start', 4);
(14, 'stop', 1);
(15, 'stop', 4);
(16, 'status', 1);
(17, 'status', 4);
(18, 'readConfig', 1);
(19, 'readConfig', 4);
(20, 'setConfig', 1);
(21, 'setConfig', 4);

另外，当我在Mysql中执行查询时，它确实可以按预期工作。

最佳答案

SELECT EXISTS恰好提供一条记录。因此，需要：

  String query ="SELECT EXISTS(SELECT * FROM data INNER JOIN Permissions "
        + "ON data.Role = Permissions.Permission "
        + "WHERE Username = ? AND Function = ?)";
  try (PreparedStatement stm = con.prepareStatement(query)) {
      stm.setString(1, user);
      stm.setString(2, methodName);
      try (ResultSet res = stm.executeQuery()) {
          if (res.next()) {
              boolean exists = res.getBoolean(1);
              if (exists) {
                  System.out.println("Welcome " + user);
              }
              return exists;
          }
          return false;
      }
  }

您可以使用更简单的SQL解决方案

  String query ="SELECT Username FROM data INNER JOIN Permissions "
        + "ON data.Role = Permissions.Permission "
        + "WHERE Username = ? AND Function = ? "
        + "LIMIT 1"; // One record only
  try (PreparedStatement stm = con.prepareStatement(query)) {
      stm.setString(1, user);
      stm.setString(2, methodName);
      try (ResultSet res = stm.executeQuery()) {
          if (res.next()) {
              System.out.println("Welcome " + user);
              return true;
          }
          return false;
      }
  }

注意：在第一个版本中，我忘记删除了executeQuery的参数。

关于java - Java executeQuery返回null，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59087858/