Java 如何确定表中是否包含 SQL 中的值?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/20816947/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-08-13 04:33:31  来源:igfitidea点击:

How to determine if a table contains a value in SQL?

javasqlms-accessjdbcms-access-2013

提问by TacoManStan

I feel like I'm missing something very obvious here, but it seems that the only way to go about doing this is to get the value, and then see if it returns a null (empty) value, which I would rather not do.

我觉得我在这里遗漏了一些非常明显的东西,但似乎唯一的方法是获取值,然后查看它是否返回 null(空)值,我宁愿不这样做。

Is there an equivalent to List.contains(Object o) in SQL? Or perhaps the JDBC has something of that nature? If so, what is it? Thank you.

SQL 中是否有等效于 List.contains(Object o) 的内容?或者也许 JDBC 具有这种性质?如果是,那是什么?谢谢你。

EDIT: I am using Microsoft Access 2013.

编辑:我正在使用 Microsoft Access 2013。

EDIT 2: Unfortunately I don't have any useful code to show, but here is the gist of what I am trying to do. It isn't anything unique at all. I want to have a method (Java) that returns the values of a user that are stored in the database. If the user has not previously been added to the database, the user should be added, and the default values of the user should be set. Then those newly created values will be returned. If a player has already been added to the database (with the username as the primary key), I don't want to overwrite the data that is already there.

编辑 2:不幸的是,我没有任何有用的代码可以显示,但这是我想要做的事情的要点。它根本不是什么独特的东西。我想要一个方法(Java)来返回存储在数据库中的用户的值。如果之前没有将用户添加到数据库中,则应添加该用户,并设置该用户的默认值。然后将返回那些新创建的值。如果一个玩家已经被添加到数据库中(以用户名作为主键),我不想覆盖已经存在的数据。

EDIT 3: I have solved the problem! Refer to my answer below.

编辑3:我已经解决了这个问题!参考我下面的回答。

回答by Darin Dimitrov

The only way to see if an SQL table contains a row with some condition on a column is to actually make an SQL query. I don't see why you wouldn't do that. Just make sure that you have an index on the column that you will be constraining the results on. Also for better speed use countto prevent from retrieving all the data from the rows.

查看 SQL 表是否包含在列上具有某些条件的行的唯一方法是实际进行 SQL 查询。我不明白你为什么不这样做。只需确保您将限制结果的列上有一个索引。也为了更好的速度使用,count以防止从行中检索所有数据。

SELECT count(*) FROM foos WHERE bar = 'baz'

Assuming you have an index on the barcolumn this query should be pretty fast and all you have to do is check whether it returns > 0. If it does then you have rows matching your criteria.

假设您在该bar列上有一个索引,这个查询应该非常快,您所要做的就是检查它是否返回 > 0。如果是,那么您有与您的条件匹配的行。

回答by Haji

You can simply use Query with condition.

您可以简单地使用带条件的查询。

For example if you have to check records with particular coloumn, you can use wherecondition

例如,如果您必须检查具有特定列的记录,则可以使用where条件

select * from table where column1 = 'checkvalue'

You can use countproperty to check the no. of records existing with your specified conditon

您可以使用count属性来检查编号。以您指定的条件存在的记录数

select count(*) from table where column1 = 'checkvalue'

回答by Ravinder Reddy

You can use "IF EXISTS" which returns a booleanvalue of 1or 0.

您可以使用 " IF EXISTS" 返回boolean1or 0

select
  if(
    exists( select * from date1 where current_date()>now() ),
    'today > now',
    'today is not > now' 
  ) as 'today > now ?' ;

+--------------------+
| today > now?       |
+--------------------+
| today is not > now |
+--------------------+
1 row in set (0.00 sec)

Another Example:

另一个例子

SELECT IF( 
         EXISTS( SELECT col from tbl where id='n' ),
         colX, colY 
       ) AS 'result'
FROM TBL;

回答by TacoManStan

I have created the following method, which to my knowledge works perfectly. (Using the java.sql package)

我创建了以下方法,据我所知,它非常有效。(使用 java.sql 包)

public static containsUser(String username)
{
    //connection is the Connection object used to connect to my Access database.
    Statement statement = this.connection.createStatement();
    //"Users" is the name of the table, "Username" is the primary key.
    String sql = "SELECT * FROM Users WHERE Username = '" + username + "'";
    Result result = statement.executeQuery(sql);
    //There is no need for a loop because the primary key is unique.
    return result.next();
}

It's an extremely simple and extremely basic method, but hopefully it might help someone in the future. If there is anything wrong with it, please let me know. I don't want anyone learning from or using poorly written code.

这是一种非常简单且非常基本的方法,但希望它可以在将来对某人有所帮助。如果有什么问题,请告诉我。我不希望任何人学习或使用写得不好的代码。

回答by Peng

I'm also new to sql and I'm using Oracle.

我也是 sql 的新手,我正在使用 Oracle。

In Oracle, suppose we have: TYPE: value.

在 Oracle 中,假设我们有: TYPE: 值。

We can use:

我们可以用:

where value not in (select TYPE from table)

to make sure valuenot exist in the column TYPE of the table.

确保表的 TYPE 列中不存在

Don't know if it helps.

不知道有没有帮助。