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.
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>').
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.
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.
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With