如何将表值参数从java传递到sql server存储过程?

时间:2021-05-06 18:02:21

I have a Student class with the following attributes:

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

Name, Department, Address, Grade. 

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

现在我有一个ArrayList,其中包含一些像这样的Student对象,

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"));

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.

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

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

Java版本8,Sql Server 2014如果有任何用处。

1 个解决方案

#1


10  

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.

通过Mark Rotteveel提供的输入,我能够做到这一点。谢谢Mark,Sean感谢您的投入。以下是任何可能发现它有用的工作代码。

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();

#1


10  

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.

通过Mark Rotteveel提供的输入,我能够做到这一点。谢谢Mark,Sean感谢您的投入。以下是任何可能发现它有用的工作代码。

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();