Executing SQL in a Gradle task?

How can I exec开发者_运维百科ute SQL in a Gradle task?

configurations {
    compile
}
repositories {
    mavenCentral()
}
dependencies {
    compile 'postgresql:postgresql:9.0-801.jdbc4'
}
task sql << {
    driverName = 'org.postgresql.Driver'
    Class.forName(driverName)
    groovy.sql.Sql sql = Sql.newInstance(
        'jdbc:postgresql://localhost:5432/postgres', 
        'username', 
        'password', 
        driverName
    )
    sql.execute 'create table test (id int not null)'
    sql.execute 'insert into test (id) values(1)'
    sql.eachRow 'select * from test' {
        println it
    }
}

I get a java.lang.ClassNotFoundException: org.postgresql.Driver exception when executing the sql task.

To define external dependencies for the build script itself you got to put it into the build scripts' classpath. You can do that by defining it within the buildscript closure.

buildscript {
    repositories {
        mavenCentral()
    }

    dependencies {
        classpath 'postgresql:postgresql:9.0-801.jdbc4'
    }
}

If you don't mind depending on yet another tool, you can leverage dbdeploy in your project. There is also a gradle plugin that let you import SQL scripts as is.

buildscript {
    dependencies {
        classpath 'com.oracle:ojdbc6:11.2.0.3'
    }
}

task tmp() {
    dependsOn configurations.batch
    doLast {
        ant.sql(classpath: buildscript.configurations.classpath.asPath,
            driver: "oracle.jdbc.OracleDriver",
            url: "${dbConn}", userid: "${dbUser}", password: "${dbPass}",
            "select 1 from dual")
    }
}

Alternatively:

ant.sql(classpath: buildscript.configurations.classpath.asPath,
        driver: "oracle.jdbc.OracleDriver",
        url: "${dbConn}", userid: "${dbUser}", password: "${dbPass}") {
    fileset(dir: dir) {
        include(name: "**/*.sql")
    }
}

Found runsql-gradle-plugin that allows executing SQL script files defined in custom Gradle task.

After adding these strings to my build.gradle.kts:

plugins {
    id("com.nocwriter.runsql") version ("1.0.3")
}

task<RunSQL>("initData") {
    dependencies {
        implementation("org.postgresql:postgresql")
    }
    config {
        url = "jdbc:postgresql://localhost:5432/test"
        driverClassName = "org.postgresql.Driver"
        username = "test"
        password = "test"
        scriptFile = "data.sql"
    }
}

I could execute data.sql by:

./gradlew initData

Here is one way:

gradle.class.classLoader.addURL(new File('../../../../lib/server/mssql/sqljdbc4.jar').toURI().toURL())
def Sql sql = Sql.newInstance(dbConnectionURL, dbUserName, dbPassword, dbDriverName)
String sqlString = new File(dbSchemaFile as String).text

sql.execute(sqlString)