oracle 使用JDBC连接从插入语句返回ROWID参数到oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/682539/
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
Return ROWID Parameter from insert statement using JDBC connection to oracle
提问by Goyuix
I can't seem to get the right magic combination to make this work:
我似乎无法获得正确的魔法组合来完成这项工作：
OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
ods.setURL("jdbc:oracle:thin:app_user/pass@server:1521:sid");
DefaultContext conn = ods.getConnection();
CallableStatement st = conn.prepareCall("INSERT INTO tableA (some_id) VALUES (1) RETURNING ROWID INTO :rowid0");
st.registerReturnParameter(1, OracleTypes.ROWID);
st.execute();
The error I get is "Protocol Violation". If I change to registerOutParameter(), I get notified that I haven't registered all return variables. If I wrap the statement in a PL/SQL begin; end; block then I get the parameter just fine using a regular registerOutParameter() call. I would really prefer to avoid wrapping all my inserts statements in PL/SQL - so what is missing from above?
我得到的错误是“违反协议”。如果我更改为 registerOutParameter()，我会收到通知，我尚未注册所有返回变量。如果我将语句包装在 PL/SQL 开头；结尾; 阻止然后我使用常规的 registerOutParameter() 调用获取参数就好了。我真的希望避免将所有插入语句都包装在 PL/SQL 中 - 那么上面缺少什么？
回答by
Usually you don't want to make code database dependent. Instead of OraclePreparedStatement, you should use CallableStatement.
通常你不想让代码数据库依赖。您应该使用 CallableStatement 而不是 OraclePreparedStatement。
CallableStatement statement = connection.prepareCall("{call INSERT INTO tableA (some_id) VALUES (1) RETURNING ROWID INTO ? }");
statement.registerOutParameter( 1, Types.VARCHAR );
int updateCount = statement.executeUpdate();
if (updateCount > 0) {
return statement.getString(1);
}
回答by Achille
A few things you'll need to do
你需要做的几件事
- Change CallableStatement to OracleCallableStatement
- Try and return into a NUMBER, ie: OracleTypes.Number
- 将 CallableStatement 更改为 OracleCallableStatement
- 尝试返回一个 NUMBER，即：OracleTypes.Number
Sample code for returning info from a query:
从查询返回信息的示例代码：
OraclePreparedStatement pstmt = (OraclePreparedStatement)conn.prepareStatement(
"delete from tab1 where age < ? returning name into ?");
pstmt.setInt(1,18);
/** register returned parameter
* in this case the maximum size of name is 100 chars
*/
pstmt.registerReturnParameter(2, OracleTypes.VARCHAR, 100);
// process the DML returning statement
count = pstmt.executeUpdate();
if (count>0)
{
ResultSet rset = pstmt.getReturnResultSet(); //rest is not null and not empty
while(rset.next())
{
String name = rset.getString(1);
...
}
}
More info on Oracle's JDBC extensions:
有关 Oracle JDBC 扩展的更多信息：
回答by DCookie
Don't know if this applies or not since you don't specify what version you're using.
不知道这是否适用，因为您没有指定您使用的版本。
From Oracle Metalink:
来自 Oracle Metalink：
Cause
原因
In the 10.1.0.x JDBC driver, returning DML is not supported:
在 10.1.0.x JDBC 驱动程序中，不支持返回 DML：
Per the JDBC FAQ: "10.1.0 (10g r1) Is DML Returning Supported ? Not in the current drivers. However, we do have plans to support it in post 10.1.0 drivers. We really mean it this time."
根据 JDBC 常见问题解答：“10.1.0 (10g r1) 是否支持 DML 返回？不在当前驱动程序中。但是，我们确实计划在 10.1.0 后的驱动程序中支持它。这次我们是认真的。”
As the application code is trying to use unsupported JDBC features, errors are raised.
由于应用程序代码试图使用不受支持的 JDBC 功能，因此会引发错误。
Solution
解决方案
Upgrade the JDBC driver to 10.2.0.x, because per the FAQ the 10.2.0.x JDBC drivers do support returning clause:
将 JDBC 驱动程序升级到 10.2.0.x，因为根据 FAQ 10.2.0.x JDBC 驱动程序确实支持返回子句：
"10.2.0 (10g r2) Is DML Returning Supported ? YES! And it's about time. See the Developer's Guide for details. "
“10.2.0 (10g r2) 是否支持 DML 返回？是的！现在是时候了。有关详细信息，请参阅开发人员指南。”
EDITJust for grins, you can check the version of JDBC Oracle thinks it's using with:
编辑只是为了咧嘴笑，您可以检查 JDBC Oracle 认为它使用的版本：
// Create Oracle DatabaseMetaData object
DatabaseMetaData meta = conn.getMetaData();
// gets driver info:
System.out.println("JDBC driver version is " + meta.getDriverVersion());
If that shows a JDBC driver 10.2.0.x or later, then I'm out of ideas and perhaps a support request to oracle is in order...
如果这显示 JDBC 驱动程序 10.2.0.x 或更高版本，那么我没有想法，也许对 oracle 的支持请求是有序的......
回答by Süniúr
PreparedStatement prepareStatement = connection.prepareStatement("insert...",
new String[] { "your_primary_key_column_name" });
prepareStatement.executeUpdate();
ResultSet generatedKeys = prepareStatement.getGeneratedKeys();
if (null != generatedKeys && generatedKeys.next()) {
Long primaryKey = generatedKeys.getLong(1);
}
I have found the answer this is perfectly works. I can insert from JAVA and its return with the key.
我找到了答案，这是完美的作品。我可以从 JAVA 插入并用键返回。
Full version:
完整版本：
CREATE TABLE STUDENTS
(
STUDENT_ID NUMBER NOT NULL PRIMARY KEY,
NAME VARCHAR2 (50 BYTE),
EMAIL VARCHAR2 (50 BYTE),
BIRTH_DATE DATE
);
CREATE SEQUENCE STUDENT_SEQ
START WITH 0
MAXVALUE 9999999999999999999999999999
MINVALUE 0;
And the Java code
和Java代码
String QUERY = "INSERT INTO students "+
" VALUES (student_seq.NEXTVAL,"+
" 'Harry', '[email protected]', '31-July-1980')";
// load oracle driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// get database connection from connection string
Connection connection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:sample", "scott", "tiger");
// prepare statement to execute insert query
// note the 2nd argument passed to prepareStatement() method
// pass name of primary key column, in this case student_id is
// generated from sequence
PreparedStatement ps = connection.prepareStatement(QUERY,
new String[] { "student_id" });
// local variable to hold auto generated student id
Long studentId = null;
// execute the insert statement, if success get the primary key value
if (ps.executeUpdate() > 0) {
// getGeneratedKeys() returns result set of keys that were auto
// generated
// in our case student_id column
ResultSet generatedKeys = ps.getGeneratedKeys();
// if resultset has data, get the primary key value
// of last inserted record
if (null != generatedKeys && generatedKeys.next()) {
// voila! we got student id which was generated from sequence
studentId = generatedKeys.getLong(1);
}
}
source : http://viralpatel.net/blogs/oracle-java-jdbc-get-primary-key-insert-sql/
来源：http: //viralpatel.net/blogs/oracle-java-jdbc-get-primary-key-insert-sql/
回答by Pablo Santa Cruz
Try using ?instead of :rowid0on your SQL string. I have had problems before with named parameters and Oracle.
尝试使用? 而不是:rowid0在您的 SQL 字符串上。我以前遇到过命名参数和 Oracle 的问题。
