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

Question

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.

See also blogs.technet.microsoft.com/dataplatforminsider/2016/04/04/… and msdn.microsoft.com/en-us/library/mt651781.aspx Note that this requires the SQL Server JDBC 6.0 Preview(!) driver. — Mark Rotteveel
– Mark Rotteveel, Commented Apr 13, 2016 at 8:11

Community · Accepted Answer · 2017-05-23 12:02:51Z

18

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

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Apr 14, 2016 at 9:17

Umakanth

8021 gold badge6 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

nirmal Over a year ago

MS added this feature in the Apr-1 release of their 6.0-preview available here: microsoft.com/en-us/download/details.aspx?id=11774

allenru Over a year ago

As an alternative to casting the PreparedStatement, you can pass the SQLServerDataTable instance to the PreparedStatement.setObject(int,Object) method. This worked for a TVP type that was defined in the dbo schema.

Finni McFinger Over a year ago

The SQLServerDataTable is no longer available as part of the driver download.

allenru Over a year ago

@FinniMcFinger There's several jars in the MS driver package. Are you sure you're looking in the right place? I just re-downloaded it from the MS site and verified that the class is in the jar. You must choose the MS JDBC driver 6.0 version. The site then asks you to choose a file, and I picked sqljdbc_6.0.7507.100_enu.tar.gz. Once downloaded, within that archive I used the sqljdbc42.jar only.

Finni McFinger Over a year ago

@allenru You're correct. It is in the sqljdbc42.jar. I was looking in sqljdbc6.jar. Cheers!

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related