11

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Umakanth
  • 686
  • 1
  • 5
  • 17
  • https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f7377f1c-f235-4870-b4a9-eab041fbd7b5/is-tablevalued-parameters-available-in-java-jdbc?forum=sqldatabaseengine – Sean Lange Apr 12 '16 at 14:15
  • 1
    See also https://blogs.technet.microsoft.com/dataplatforminsider/2016/04/04/preview-the-microsoft-jdbc-driver-6-0-for-sql-server/ and https://msdn.microsoft.com/en-us/library/mt651781.aspx Note that this requires the SQL Server JDBC 6.0 Preview(!) driver. – Mark Rotteveel Apr 13 '16 at 08:11

1 Answers1

17

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();
Community
  • 1
  • 1
Umakanth
  • 686
  • 1
  • 5
  • 17
  • MS added this feature in the Apr-1 release of their 6.0-preview available here: https://www.microsoft.com/en-us/download/details.aspx?id=11774 – nirmal Jun 22 '16 at 18:11
  • 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. – allenru Jul 15 '16 at 19:18
  • The SQLServerDataTable is no longer available as part of the driver download. – Finni McFinger Sep 12 '16 at 12:36
  • 1
    @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. – allenru Oct 03 '16 at 20:27
  • @allenru You're correct. It is in the sqljdbc42.jar. I was looking in sqljdbc6.jar. Cheers! – Finni McFinger Oct 03 '16 at 20:32
  • com.microsoft.sqlserver.jdbc.SQLServerException: INSERT into an identity column not allowed on table variables. – srinivas gowda Sep 18 '18 at 07:37
  • Same goes for Oracle? – Faizan Mubasher Mar 02 '21 at 10:00