CODEWITHSUNDEEP
sql-server-2008playframework
Saquibul Islam Waheed
Saquibul Islam Waheed

Reputation: 470

How to Insert primary key in a table while using Play Framework, Ebean, sql server 2008

I want to insert a primary key in a table. How do I set the "Identity Insert" property ON from my Controller Class? I've tried this with no luck:

String sql = "SET IDENTITY_INSERT t_Student ON";

        Connection conn = play.db.DB.getConnection();
        try {
            Statement stmt = conn.createStatement();
            try {
                stmt.execute(sql);
                student.save();
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }

Here, I want to save the "student" object in to the DataBase, but getting the error: "Cannot insert explicit value for identity column in table 't_Student' when IDENTITY_INSERT is set to OFF." Thanks in advance.

Upvotes: 0

Views: 609

Answers (1)

r.piesnikowski
r.piesnikowski

Reputation: 2951

One important thing.

IDENTITY_INSERT has nothing to primary key in database table. Moreover it has nothing to do with playframework in your situation. Primary key in shortcut is constraint that won't allow adding the same value to column(or columns). It's obvious. IDENTITY_INSERT mean that you take control explicitly of value for PK constraint. You're the boss.
For example:

CREATE TABLE student (id int PRIMARY KEY, name varchar(40))

This situation simulates SET IDENTITY_INSERT ON and is very rare in database schema. Nevertheless it's rare, you need to expliclitly put id and name. You must be aware that current id won't interfere with existing primary keys in table:

INSERT INTO student VALUES(1,'John')

For first time this code will work but when you try insert it again it will raise exception because you want add the same value as PK.

SET IDENTITY_INSERT OFF in other hand allow you to forget inserting id each time because database engine will do it for you. It will also keep PK constraint.

CREATE TABLE student (id int IDENTITY PRIMARY KEY, name varchar(40))

And if you want insert row into table you can do this:

INSERT INTO student('Johny Paul')

And if you will force ID:

INSERT INTO student VALUES(666,'Johnny Rambo')

You will have exception: An explicit value for the identity column in table 'student' can only be specified when a column list is used and IDENTITY_INSERT is ON. There are some situation when you need have full control of database values: dictionary tables across many environments or when your PK is corrupted and you need to "surgery" on PK. Otherwise don't use IDENTITY_INSERT ON. Perhaps under the hood EBean can't recognize whether insert statement should use id or not.

Answer

I strongly suggest using table with IDENTITY (IDENTITY_INSERT OFF). I have used Ebean only few times (I don't like this database layer framework) but I think that code should help:

@Entity  
@Table(name="student")  
public class Student {  
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long id;
    public String name;
}

Student student = new Student();
student.name = "Jonny JDK";
Ebean.save(student);

Or you can write raw SQL:

SqlUpdate insert = Ebean.createSqlUpdate("INSERT INTO student VALUES ('Jonny Bravo')");
insert.execute();

Upvotes: 2

Related Questions