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)