Sql Query for Insert in Grails

HQL Insert Query in Grails

Execute a native query:

def sql = new Sql(sessionFactory.currentSession.connection())
sql.execute("insert into person values(?,?)", ["foo", "bar"])

note that person is the actual table name.

Not able to insert into MSSQL through grails application

I didnt look carefully enough at the permissions. I had "db_denydatareader" and "db_denydatawriter" checked. Very simple...

Executing SQL inserts during Grails application startup

You can do this in BootStrap.groovy. If you add a dependency injection for the dataSource bean you can use it with a groovy.sql.Sql instance to do inserts:

import groovy.sql.Sql

class BootStrap {

   def dataSource

   def init = { servletContext ->
      def sql = new Sql(dataSource)
      sql.executeUpdate(
         'insert into some_table(foo, bar) values(?, ?)',
         ['x', 'y'])
   }
}

You would probably be better off using GORM though, assuming these are tables that are managed with domain classes. E.g. run something like new Book(author: 'me', title: 'some title').save()

Insert 10,000,000+ rows in grails

Try doing batch inserts, it's much more efficient

def updateCounts = sql.withBatch { stmt ->
     stmt.addBatch("insert into TABLENAME ...")
     stmt.addBatch("insert into TABLENAME ...")
     stmt.addBatch("insert into TABLENAME ...")
     ...
 }

How to set up an insert to a grails created file with next sequence number?

Problem solved.

It turns out that when grails creates a table, it doesn't assign a specific sequence generator to it.

Instead, grails uses a single sequence generator for all tables. This is called "hibernate_sequence".

So, to get around the problem, I included the "nextval" for that in my SQL statement:

ps = c.prepareStatement("INSERT INTO xml_test (id, version, text_field) VALUES (nextval('hibernate_sequence'), 0, ?)");

---

Related Topics