Appelez une procédure stockée avec un paramètre table de Java

Dans mon application, je veux exécuter une requête comme SELECT * FROM tbl WHERE col IN (@list) où, @ list peut avoir un nombre variable de valeurs. J'utilise la database du server MS SQL. Quand je google ce problème, j'ai trouvé ce lien

http://www.sommarskog.se/arrays-in-sql-2008.html

Ce lien dit d'utiliser un paramètre table-évalué. J'ai donc créé un type de données défini par l'user à l'aide de Microsoft SQL Server Management Studio.

CREATE TYPE integer_list_tbltype COMME TABLE (n int NOT NULL PRIMARY KEY)

Ensuite, j'ai écrit la procédure stockée

CREATE PROCEDURE get_product_names @prodids integer_list_tbltype READONLY AS SELECT p.ProductID, p.ProductName FROM Northwind.dbo.Products p WHERE p.ProductID IN (SELECT n FROM @prodids)

puis en utilisant le studio de gestion seulement j'ai exécuté cette procédure

 DECLARE @mylist integer_list_tbltype INSERT @mylist(n) VALUES(9),(12),(27),(37) EXEC get_product_names @mylist

et il me donne une sortie correcte. Mais je me request comment appeler cette procédure stockée à partir du code source java. Je sais comment appeler une procédure stockée simple avec un nombre constant d'arguments

 CallableStatement proc_stmt = null; proc_stmt = con.prepareCall("{call test(?)}"); proc_stmt.setSsortingng(1,someValue);

mais comment appeler la procédure stockée dans le cas de paramètre table-valeur?

On dirait que c'est un ajout prévu à JDBC mais n'a pas encore été implémenté:

http://blogs.msdn.com/b/jdbcteam/archive/2012/04/03/how-would-you-use-table-valued-parameters-tvp.aspx

Transmettez le paramètre sous la forme d'une string délimitée ("9,12,27,37"), puis créez une fonction table dans SQL Server appelée "fnSplit" ou quoi que ce soit qui renvoie les valeurs entières dans une table (searchz "sql" fonction de partage de server, "il y a des millions d'entre eux).

Ceci est documenté ici dans le manuel du pilote JDBC . Dans votre cas, vous devez faire ceci:

 try (SQLServerCallableStatement stmt = (SQLServerCallableStatement) con.prepareCall("{call test(?)}")) { SQLServerDataTable table = new SQLServerDataTable(); sourceDataTable.addColumnMetadata("n", java.sql.Types.INTEGER); sourceDataTable.addRow(9); sourceDataTable.addRow(12); sourceDataTable.addRow(27); sourceDataTable.addRow(37); stmt.setStructured(1, "dbo.integer_list_tbltype", table); }

J'ai aussi récemment documenté cela dans un article .

Les réponses typiques (délimitées par des virgules ou XML) ont toutes des problèmes avec SQL Injection. J'avais besoin d'une réponse qui me permette d'utiliser un PreparedStatement. Donc je suis arrivé avec ceci:

 SsortingngBuilder query = new SsortingngBuilder(); query.append( "DECLARE @mylist integer_list_tbltype;" + "INSERT @mylist(n) VALUES(?)"); for (int i = 0; i < values.size() - 1; ++i) { query.append(",(?) "); } query.append("; EXEC get_product_names @mylist "); PreparedStatement preparedStmt = conn.prepareStatement(query.toSsortingng()); for (int i = 0; i < values.size(); ++i) { preparedStmt.setObject(i + 1, itemLookupValues.get(i)); }

Maintenant, il a été ajouté à JDBC Driver 6.0. Il CTP2 encore.

"Ce nouveau pilote prend désormais en charge les parameters de table et Azure Active Directory.En plus de ces nouvelles fonctionnalités, nous avons également ajouté des fonctionnalités pour Always Encrypted.Le pilote prend également en charge les noms de domaine internationalisés et paramétrés."

https://blogs.msdn.microsoft.com/jdbcteam/2016/04/04/get-the-new-microsoft-jdbc-driver-6-0-preview/

Lien de téléchargement: https://www.microsoft.com/fr-fr/download/details.aspx?displaylang=fr&id=11774

Voici la documentation comment l'utiliser https://msdn.microsoft.com/en-us/library/mt651781(v=sql.110).aspx

Après avoir cherché pendant un moment, j'ai trouvé la réponse à ce problème. En particulier lorsque vous utilisez la clause IN et qu'aucun opérande n'est variable, vous pouvez utiliser des valeurs délimitées par des virgules comme input dans la clause IN.

Voici l'exemple comment la procédure stockée qui récupérera tous les avocats du type d'avocat donné dans le code postal fourni ressemblera à l'utilisation de SQL dynamic.

 CREATE PROCEDURE [dbo].[GetLawyers] ( @ZIP CHAR(5), @LawyerTypeIDs VARCHAR(100) ) AS DECLARE @SQL VARCHAR(2000) SET @SQL = 'SELECT * FROM [dbo].[Lawyers] WHERE [ZIP] = ' + @ZIP + ' AND [LawyerTypeID] IN (' + @LawyerTypeIDs + ')' EXECUTE (@SQL) GO

Pour exécuter la procédure stockée en transmettant le code postal entré par l'user et les types d'avocat sélectionnés dans une valeur séparée par des virgules:

 EXECUTE [dbo].[GetLawyers] '12345', '1,4'

Donc, conclusion, vous n'avez pas besoin d'utiliser TVP. Quelle que soit la langue [Java, PHP] que vous utilisez, il suffit de passer les parameters en tant que string délimitée par des virgules à la procédure stockée et cela fonctionnera parfaitement.

Donc, en Java, vous pouvez appeler au-dessus de la procédure stockée: –

 proc_stmt = con.prepareCall("{call GetLawyers(?,?)}"); proc_stmt.setSsortingng(1,"12345"); proc_stmt.setSsortingng(2,"'1,4'");