java使用JDBC从SQL INSERT获取序列
所以我尝试使用文件中的JDBC
添加到数据库中。现在当INSERTING
我需要在插入之前获取ID
,如果数据库仍然使用sequence
会更好
在文件夹resources
中,我有一个名为insert_plant.sql
的文件,其中包含以下查询:
INSERT INTO PLANTS (id, plantname, species)
VALUES (NEXT VALUE FOR sequence, ?, null);
该表由以下内容生成:
DROP SCHEMA public CASCADE;
CREATE SEQUENCE sequence START WITH 1;
CREATE TABLE PLANTS (
id BIGINT NOT NULL PRIMARY KEY,
plantname VARCHAR(255) NOT NULL,
species VARCHAR(255) NULL,
);
现在在Java中,我称之为:
public static void insertIntoOrderTable(BasicDataSource basicDataSource, String plantname) throws SQLException{
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = basicDataSource.getConnection();
stmt = conn.prepareStatement(Util.readFileFromClasspath("insert_plant.sql"));
stmt.setString(1, plantname);
stmt.executeUpdate();
//Below is the line 57 (in error)
ResultSet rs = stmt.executeQuery("SELECT sequence.NEXTVAL FROM PLANTS");
if(rs.next()){
System.out.println("ID" + rs.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}
我得到的错误是:
java.sql.SQLFeatureNotSupportedException: feature not supported
at org.hsqldb.jdbc.JDBCUtil.notSupported(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at database.PlantDao.insertIntoOrderTable(PlantDao.java:57)
at database.PlantDao.main(PlantDao.java:19)
所以问题来了
# 1 楼答案
正如“穿刺者”已经评论的那样,不需要执行第二条语句,您可以使用JDBC
要从中获得价值:
因此,您的方法如下所示: