首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >检查用户是否存在,Java、JDBC和MySQL

问检查用户是否存在,Java、JDBC和MySQL
EN

Stack Overflow用户
提问于 2015-12-22 14:04:15
回答 3查看 4.9K关注 0票数 2

我正在尝试检查数据库中是否存在某个用户,然后对该用户执行一些验证过程,如果不存在,则返回入侵者消息。为此,我使用了Java和MySQL。这是我的代码。

代码语言:javascript
复制
public class Check{

    public boolean isPresent(){
        connect();// private method that connects to db
        boolean isAvailable = false;
        String query = "SELECT EXISTS(SELECT 1 FROM students WHERE matric =" + this.myId + ")"; // this.myId has been passed into the constructor

        try{
            Statement statement;
            ResultSet resultSet;
            statement = connection.createStatement();
            resultSet = statement.executeQuery(query);
            if(resultSet.absolute(1)){
                isAvailable = true;
            }else{
                isAvailable = false;
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
         closeConnection();// private method that closes connection
         return isAvailable;
    }

}

I have checked for a solution like this earlier but it does not seem to解决了这个问题。我得到的错误是:“where子句中的未知列CCE”,还有其他来自JDBC API的错误。在我看来,我从构造器传入的值似乎被截断了。

代码语言:javascript
复制
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'CCE' in 'where clause'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
        at com.mysql.jdbc.Util.getInstance(Util.java:383)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1432) 
EN

回答 3

Stack Overflow用户

发布于 2015-12-22 14:26:21

从简单修复到正确性的

1-引用您的ID将修复您的SQL错误(添加‘around id)

代码语言:javascript
复制
String query = "SELECT EXISTS(SELECT 1 FROM students WHERE matric = '" + this.myId + "')"; // this.myId has been passed into the constructor

但是,使用subselect表示存在吗?为什么不直接获取学生呢?

2-移除子选择

代码语言:javascript
复制
String query = "SELECT * FROM students WHERE matric = '" + this.myId + "'"; 
try{
        Statement statement;
        ResultSet resultSet;
        statement = connection.createStatement();
        resultSet = statement.executeQuery(query);
        if(resultSet.next()){
            isAvailable = true;
        }else{
            isAvailable = false;
        } //Missing in your code

  1. 使用预准备语句防止SQL注入

把myId想象成..。myId = "killer'); DROP TABLE STUDENTS; --" -你不想以这种方式执行它。

代码语言:javascript
复制
String query = "SELECT * FROM students WHERE matric = ?"; 
try{
        PreparedStatement statement;
        ResultSet resultSet;
        statement = connection.prepareStatement(query);
        statement.setString(1, myId);
        resultSet = statement.executeQuery();

  1. Try-With-Ressource和改进的

可读性

通过使用try-with-resource,您可以摆脱大多数捕获:

代码语言:javascript
复制
String query = "SELECT * FROM students WHERE matric = ?"; 
try (PreparedStatement statement = connection.prepareStatement(query)) {
        statement.setString(1, myId);
        try(ResultSet resultSet = statement.executeQuery()) {
          return rs.next();
        }
} catch(SQLException se) {
   se.printStackTrace();
   return false;
}
票数 6
EN

Stack Overflow用户

发布于 2015-12-22 14:14:29

SQL查询不是Java语句/表达式。

在变量的位置

this.myId

使用占位符,即

?

示例:"SELECT EXISTS(SELECT 1 FROM students WHERE matric =?"

并将其包含在双引号内。

请记住使用PreparedStatement而不是语句

代码语言:javascript
复制
PreparedStatement pst=connection.createStatement();

使用statement = connection.createStatement();创建查询后,只需将myId变量设置为查询即可,如下所示:

代码语言:javascript
复制
pst.setString(1,this.myID);

这应该会对你有帮助

票数 1
EN

Stack Overflow用户

发布于 2015-12-22 14:39:30

尝试将id作为参数传递,并使用预准备语句使您的代码更有效

代码语言:javascript
复制
 public boolean isUserExsit(String id) {

   boolean isDuplicated = false;
   Connection connection = lockConnection();
   String sql = "SELECT 1  FROM students WHERE matric = ? ";



    try {
        PreparedStatement statement = connection.prepareStatement(sql);
        if (statement != null) {
            statement.setString(1, id);




            try {
                ResultSet results = statement.executeQuery();
                if (results != null) {
                    try {
                        if (results.next()) {
                            isDuplicated = true;

                        }
                    } catch (Exception resultSetException) {
                        resultSetException.printStackTrace();
                    }
                    results.close();
                }
            } catch (Exception statmentExcption) {
                statmentExcption.printStackTrace();
            }
            statement.close();
        }
    } catch (Exception generalException) {
        generalException.printStackTrace();
    }

    releaseConnection(connection);



    return isDuplicated;
}
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/34409421

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档