我创建了名为"customer1"的模式,其中包含名为"user"的表格,并尝试使用Connection.setSchema()函数从JDBC连接到该模式:
这段代码会输出:
如果我在SELECT查询中限定“用户”表:
然后它打印输出:
String url = "jdbc:postgresql://localhost/project";
Properties props = new Properties();
props.setProperty("user", "postgres");
props.setProperty("password", "postgres");
try (Connection conn = DriverManager.getConnection(url, props)) {
conn.setSchema("customer1");
try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SHOW search_path")) {
rs.next();
System.out.println("search_path: " + rs.getString(1));
}
try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT name FROM user LIMIT 1")) {
if (rs.next()) {
System.out.println("user name: " + rs.getString("name"));
}
}
}
这段代码会输出:
search_path: customer1
然后它抛出带有以下信息的PSQLException:
ERROR: column "name" does not exist
如果我在SELECT查询中限定“用户”表:
try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT name FROM customer1.user LIMIT 1")) {
if (rs.next()) {
System.out.println("user name: " + rs.getString("name"));
}
}
然后它打印输出:
search_path: customer1
user name: name1
并且没有错误发生。我正在使用JDBC驱动程序42.2.2和PostgreSQL服务器10.4。为什么设置模式无效?