CODEWITHSUNDEEP
javasql-server
user3606762
user3606762

Reputation: 11

How to convert a Java object to a SQL Server table valued parameter from java code

How to convert a Java object to a SQL Server table-valued-parameter?

public class User {
    private int userId; 
    private String firstName; 
    private String lastName; 
    private String gender; 
    private String city;
}

CREATE TABLE TEST_USER]( 
    [USER_ID] [int] NOT NULL, 
    [FIRST_NAME] [varchar](100) NULL, 
    [LAST_NAME] [varchar](100) NULL,
    [GENDER] [varchar](10) NULL, 
    [CITY] [varchar](30) NULL, 
    PRIMARY KEY ( [USER_ID] )
); 

CREATE TYPE USERTYPE AS TABLE ( 
    USER_ID INTEGER NOT NULL , 
    FIRST_NAME VARCHAR(100), 
    LAST_NAME VARCHAR(100), 
    GENDER VARCHAR(10), 
    CITY VARCHAR(30) 
); 

CREATE PROCEDURE SP_InsertUser @UserDetail USERTYPE READONLY  
AS 
BEGIN
   SET NOCOUNT ON
  INSERT INTO TEST_USER (USER_ID, FIRST_NAME, LAST_NAME, GENDER,CITY) 
   SELECT [USER_ID], [FIRST_NAME], [LAST_NAME],[GENDER],[CITY] FROM @UserDetail 
END

I want to convert an object of User class to a (SQL Server)USERTYPE Table Type

Upvotes: 0

Views: 1947

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220952

Use the following logic in JDBC:

SQLServerDataTable table = new SQLServerDataTable();
table.addColumnMetadata("USER_ID", Types.INTEGER);
table.addColumnMetadata("FIRST_NAME", Types.VARCHAR);
table.addColumnMetadata("LAST_NAME", Types.VARCHAR);
table.addColumnMetadata("GENDER", Types.VARCHAR);
table.addColumnMetadata("CITY", Types.VARCHAR);
table.addRow(
    user.getUserId(),
    user.getFirstName(),
    user.getLastName(),
    user.getGender(),
    user.getCity()
);

try (SQLServerPreparedStatement stmt=
    (SQLServerPreparedStatement) connection.prepareStatement(
       "{ call SP_InsertUser (?) }")) {

    stmt.setStructured(1, "dbo.usertype", table);  
    stmt.executeUpdate();
}

I have described this also in this blog post here.

Upvotes: 1

Related Questions