Java 检查表是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2942788/
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
Check if table exists
提问by Dmitry
I have a desktop application with a database embedded in it. When I execute my program I need to check that specific table exists, or create it if not.
我有一个桌面应用程序，其中嵌入了一个数据库。当我执行我的程序时，我需要检查特定的表是否存在，如果不存在则创建它。
Given a Connection object named conn for my database, how could I check this?
给定一个名为 conn 的 Connection 对象用于我的数据库，我该如何检查？
采纳答案by Brian Agnew
You can use the available meta data:
您可以使用可用的元数据：
DatabaseMetaData meta = con.getMetaData();
ResultSet res = meta.getTables(null, null, "My_Table_Name",
new String[] {"TABLE"});
while (res.next()) {
System.out.println(
" "+res.getString("TABLE_CAT")
+ ", "+res.getString("TABLE_SCHEM")
+ ", "+res.getString("TABLE_NAME")
+ ", "+res.getString("TABLE_TYPE")
+ ", "+res.getString("REMARKS"));
}
See herefor more details. Note also the caveats in the JavaDoc.
回答by RealHowTo
DatabaseMetaData dbm = con.getMetaData();
// check if "employee" table is there
ResultSet tables = dbm.getTables(null, null, "employee", null);
if (tables.next()) {
// Table exists
}
else {
// Table does not exist
}
回答by wawiwa
Adding to Gaby's post, my jdbc getTables() for Oracle 10g requires all caps to work:
添加到 Gaby 的帖子中，我的用于 Oracle 10g 的 jdbc getTables() 需要全部大写才能工作：
"employee" -> "EMPLOYEE"
"employee" -> "EMPLOYEE"
Otherwise I would get an exception:
否则我会得到一个例外：
java.sql.SqlExcepcion exhausted resultset
java.sql.SqlExcepcion 耗尽结果集
(even though "employee" is in the schema)
（即使“员工”在架构中）
回答by peterh
I don't actually find any of the presented solutions here to be fully complete so I'll add my own. Nothing new here. You can stitch this together from the other presented solutions plus various comments.
我实际上没有发现这里提供的任何解决方案是完全完整的，因此我将添加我自己的解决方案。这里没有什么新鲜事。您可以将其与其他提供的解决方案以及各种评论拼接在一起。
There are at least two things you'll have to make sure:
至少有两件事你必须确保：
Make sure you pass the table name to the
getTables()method, rather than passing a null value. In the first case you let the database server filter the result for you, in the second you request a list of alltables from the server and then filter the list locally. The former is much faster if you are only searching for a single table.Make sure to check the table name from the resultset with an equals match. The reason is that the
getTables()does pattern matching on the query for the table and the_character is a wildcard in SQL. Suppose you are checking for the existence of a table namedEMPLOYEE_SALARY. You'll then get a match onEMPLOYEESSALARYtoo which is not what you want.
确保将表名传递给
getTables()方法，而不是传递空值。在第一种情况下，您让数据库服务器为您过滤结果，在第二种情况下，您从服务器请求所有表的列表，然后在本地过滤该列表。如果您只搜索单个表，前者要快得多。确保使用等于匹配检查结果集中的表名。原因是
getTables()查询表和_字符的does模式匹配是SQL中的通配符。假设您正在检查名为 的表是否存在EMPLOYEE_SALARY。然后你也会得到一个匹配，EMPLOYEESSALARY这不是你想要的。
Ohh, and do remember to close those resultsets. Since Java 7 you would want to use a try-with-resources statementfor that.
哦，记得关闭这些结果集。从 Java 7 开始，您可能希望为此使用try-with-resources 语句。
Here's a complete solution:
这是一个完整的解决方案：
public static boolean tableExist(Connection conn, String tableName) throws SQLException {
boolean tExists = false;
try (ResultSet rs = conn.getMetaData().getTables(null, null, tableName, null)) {
while (rs.next()) {
String tName = rs.getString("TABLE_NAME");
if (tName != null && tName.equals(tableName)) {
tExists = true;
break;
}
}
}
return tExists;
}
You may want to consider what you pass as the typesparameter (4th parameter) on your getTables()call. Normally I would just leave at nullbecause you don't want to restrict yourself. A VIEW is as good as a TABLE, right? These days many databases allow you to update through a VIEW so restricting yourself to only TABLE type is in most cases not the way to go. YMMV.
您可能需要考虑types在getTables()调用时作为参数（第 4 个参数）传递的内容。通常我会离开，null因为你不想限制自己。VIEW 和 TABLE 一样好，对吧？如今，许多数据库允许您通过 VIEW 进行更新，因此将自己限制为仅使用 TABLE 类型在大多数情况下不是可行的方法。天啊。
回答by Konstantin F
/**
* Method that checks if all tables exist
* If a table doesnt exist it creates the table
*/
public void checkTables() {
try {
startConn();// method that connects with mysql database
String useDatabase = "USE " + getDatabase() + ";";
stmt.executeUpdate(useDatabase);
String[] tables = {"Patients", "Procedures", "Payments", "Procedurables"};//thats table names that I need to create if not exists
DatabaseMetaData metadata = conn.getMetaData();
for(int i=0; i< tables.length; i++) {
ResultSet rs = metadata.getTables(null, null, tables[i], null);
if(!rs.next()) {
createTable(tables[i]);
System.out.println("Table " + tables[i] + " created");
}
}
} catch(SQLException e) {
System.out.println("checkTables() " + e.getMessage());
}
closeConn();// Close connection with mysql database
}
回答by DBulgatz
If using jruby, here is a code snippet to return an array of all tables in a db.
如果使用 jruby，这里是一个代码片段，用于返回数据库中所有表的数组。
require "rubygems"
require "jdbc/mysql"
Jdbc::MySQL.load_driver
require "java"
def get_database_tables(connection, db_name)
md = connection.get_meta_data
rs = md.get_tables(db_name, nil, '%',["TABLE"])
tables = []
count = 0
while rs.next
tables << rs.get_string(3)
end #while
return tables
end
