如何将表值参数从 java 传递到 sql server 存储过程? [英] How to pass Table-Valued parameters from java to sql server stored procedure?

问题描述

我有一个具有以下属性的 Student 类:

I have a Student class with the following attributes:

Name, Department, Address, Grade. 

现在我有一个 ArrayList 包含一些这样的 Student 对象，

Now I have an ArrayList that contains some Student objects like this,

List<Student> stuList = new ArrayList<Student>();
stuList.add(new Student("Tom","Comp", "123 street", "A"));
stuList.add(new Student("Jery","Comp", "456 street", "A+"));
stuList.add(new Student("Mac","Maths", "Dum Street", "B"));

我需要将这个arraylist传递给sql server存储过程，并将student对象数据插入到表中.如何在 Java 中最好地实现这一目标?我需要有一个存储过程.

I need to pass this arraylist to the sql server stored procedure and insert the student object data into the table. How to best achieve this in Java? I am required to have a stored procedure.

Java 版本 8、Sql Server 2014(如果有任何用途).

Java version 8, Sql Server 2014 if its of any use.

推荐答案

使用 Mark Rotteveel 提供的输入我能够做到.感谢 Mark，Sean 也感谢您的投入.这是您可能觉得有用的任何人的工作代码.

With the inputs provided by Mark Rotteveel I was able to do it. Thanks Mark, Sean thanks for your input as well. Here is the working code for any of you that may find it useful.

String jdbcurl = "jdbc:sqlserver://TestServer:1433;DatabaseName=Student";
connection = DriverManager.getConnection(jdbcurl,"username","password");

SQLServerDataTable stuTypeDT = new SQLServerDataTable(); 
stuTypeDT.addColumnMetadata("StudentId", java.sql.Types.NUMERIC);
stuTypeDT.addColumnMetadata("Name", java.sql.Types.VARCHAR);
stuTypeDT.addColumnMetadata("Department", java.sql.Types.VARCHAR);
stuTypeDT.addColumnMetadata("Address", java.sql.Types.VARCHAR);

stuTypeDT.addRow("1","Tom", "A", "123 Street");
stuTypeDT.addRow("2","Jery", "B", "456 Street");
stuTypeDT.addRow("3","Mac", "C", "Vancour");

String ececStoredProc = "EXEC InsertStudentInfo ?";
SQLServerPreparedStatement pStmt = (SQLServerPreparedStatement)connection.prepareStatement(ececStoredProc);
pStmt.setStructured(1, "dbo.StudentInfoType", stuTypeDT);
pStmt.execute();

这篇关于如何将表值参数从 java 传递到 sql server 存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！