Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to pass Table-Valued parameters from java to sql server stored procedure?

I have a Student class with the following attributes:

Name, Department, Address, Grade. 

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

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 version 8, Sql Server 2014 if its of any use.

like image 279
Umakanth Avatar asked Apr 12 '16 13:04

Umakanth


People also ask

How do you pass a list of values into a stored procedure in Java?

CREATE FUNCTION dbo. SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').

How do you execute a stored procedure with table valued parameters in SQL?

First a Table Variable of User Defined Table Type has to be created of the same schema as that of the Table Valued parameter. Then it is passed as Parameter to the Stored Procedure and the Stored Procedure is executed using the EXEC command in SQL Server.

Can you pass a table variable to a stored procedure?

Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. You can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.


1 Answers

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();
like image 74
Umakanth Avatar answered Oct 14 '22 16:10

Umakanth