Java executeQuery 返回空值

Question

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

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

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

    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;

  }

我还检查了我是否传递了正确的用户名和方法名，这似乎没问题。

数据库结构:

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 中执行查询时，它确实按预期工作。

Answer 1

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的参数。