Rewrite the title to: By getting the id of the ResultSet and returning it as a java.sql.Array-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

Rewrite the title to: By getting the id of the ResultSet and returning it as a java.sql.Array

P粉905144514 2023-09-03 18:10:50

441

I have the following:

 def getIds(name: String): java.sql.Array = { val ids: Array[Integer] = Array() val ps: PreparedStatement = connection.prepareStatement("SELECT id FROM table WHERE name = ?") ps.setString(1, name) val resultSet = ps.executeQuery() while(resultSet.next()) { val currentId = resultSet.getInt(1) ids: currentId } return connection.createArrayOf("INTEGER", ids.toArray) }

My intention is to use the output of this method into another PreparedStatement, using .setArray(1, )

But I get the following error: java.sql.SQLFeatureNotSupportedException

I'm using MySQL. Already tried INTEGER, INT, BIGINT. But all failed.

P粉905144514

reply all (1)

P粉2657249302023-09-04 14:49:07 1 floor

The translated content is:

After research, we found:

So my solution is to create a temporary table containing only the ids:

val idsStatement = connection.prepareStatement( "CREATE TEMPORARY TABLE to_delete_ids SELECT id FROM table WHERE name = ?") idsStatement.setString(1, name) idsStatement.executeUpdate()

Then do an inner join with other statements/queries to achieve the same result:

val statementDeleteUsingIds = connection.prepareStatement( "DELETE to_delete_rows FROM table2 to_delete_rows INNER JOIN to_delete_ids tdi ON tdi.id = to_delete_rows.other_tables_id") statementDeleteUsingIds.executeUpdate()