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

Question

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

I want to pass a java object as a SQL Server table valued parameter to a sql server stored procedure from java code — user3606762
– user3606762, Commented Aug 13, 2015 at 7:22
You need to write a util method to do so. No inbuilt methods. — Suresh Atta
– Suresh Atta, Commented Aug 13, 2015 at 7:23
Show your code first - i.e. your Java class and the table structure (ideally the CREATE TABLE statement). We need to know what are your trying to convert into what to give you a relewant advice. — Jozef Chocholacek
– Jozef Chocholacek, Commented Aug 13, 2015 at 7:57

Lukas Eder · Accepted Answer · 2017-10-30 12:29:11Z

1

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.

answered Oct 30, 2017 at 12:29

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related