How to insert primary key value in database in scala with play framework?

Question

I am working with play framework with scala and i am storing values into database but i am getting error how to insert values in database

what am i doing:

i am taking values from form and then performing some operation on data and then storing values in database .

i went through solutions but the solution they are providing is not working in my application

app evolution file:

# --- !Ups
create table keyword (
  id                        bigint  not null AUTO_INCREMENT primary key,
  word                       varchar(255) not null ,
  blog                       varchar(255) not null ,
  cat                       varchar(255) not null ,
  score                       BIGINT not null ,
  summaryId                       varchar(255) not null,
  dates                             datetime 

);

# --- !Downs

drop table keyword;

and models/keyword.scala file

case class Keyword(id: Pk[Long] = NotAssigned,word: String,blog: String,cat: String,score: Long, summaryId: String)

object Keyword {


  val keyw = {
     get[Pk[Long]]("keyword.id") ~
     get[String]("keyword.word")~
    get[String]("keyword.blog")~
    get[String]("keyword.cat")~
    get[Long]("keyword.score") ~ 
    get[String]("keyword.summaryId")map {
    case id~word~blog~cat~score~summaryId => Keyword(id,word,blog,cat,score, summaryId)
  }
}

  def all(): List[Keyword] = DB.withConnection { implicit c =>
  SQL("select * from keyword").as(Keyword.keyw *)
}

def ad(word: String, count: Long, summaryId: String, blog: String, cat: String)={DB.withConnection{implicit c=> 
  val id=SQL("select id from keyword").apply.head

  SQL("insert into keyword values({id},{word},{blog}, {cat}, {score},{summaryId},{dates})").on('id->Id(id),
      'word-> word,'blog->blog,
      'cat -> cat,
      'score-> count,
      'summaryId -> summaryId,
      'dates-> new DateTime().toDate()).executeUpdate}}

contoller file:

i am calling ad method to insert into database

Keyword.ad(k.word, k.count, link, blog, category)

but i am not getting how to insert primary value id into database that is primary key.

i set id as a primary key that is auto- increment

but when i am calling this method that time how to give value to id i am not getting

can any one please give me some idea to solve this ?

score 2 · Answer 1 · edited May 23 '17 at 12:29

2

I think you should not insert PK Id value directly from your code, - the db does it for you and will provide you with new autoincremented value back.

Just remove id-related statement from your query:

SQL("insert into keyword values({word},{blog}, {cat}, {score},{summaryId},{dates})").on(
      'word-> word,'blog->blog,
      'cat -> cat,
      'score-> count,
      'summaryId -> summaryId,
      'dates-> new DateTime().toDate()).executeInsert()}}

Hope this should help.

UPD: Also, please use executeInsert method. Here you can find how to get you id back brom the database: How to Retrieve the Primary Key When Saving a New Object in Anorm

edited May 23 '17 at 12:29

Community

1
1

answered Nov 04 '13 at 09:31

gyromonotron

1,101
9
13

it is not working it's giving exception that is `[SQLException: Column count doesn't match value count at row 1] ` – Rahul Kulhari Nov 04 '13 at 09:55
try to specify column names in your insert statement like 'insert into keyword (word, blog, cat, score, summaryId, dates) ...'. – gyromonotron Nov 04 '13 at 10:58
i tried it but when i delete id from table that time it is working – Rahul Kulhari Nov 04 '13 at 11:00
it is strange. Be sure that you really have auto_increment id, number of columns in the table is the same as in you query, you are using executeInsert method and you don't pass 'null' values to NOT NULL columns. Also, check that you didn't miss a comma in the columns list – gyromonotron Nov 04 '13 at 11:24
@RahulKulhari Is this the query string you're using? `"insert into keyword (word, blog, cat, score, summaryId, dates) values ({word},{blog}, {cat}, {score},{summaryId},{dates})"` – Michael Zajac Nov 06 '13 at 13:59

How to insert primary key value in database in scala with play framework?

1 Answers1