Groovy SQL Create Table Examples

Nov 10, 2016 SQL comments

One of the first things we do to a database is to create tables. SQL supports data definition language or data description language (DDL). Using SQL "Create" statements, we can create tables in our database. Below is a simple example on how to use Groovy SQL to create a table in the database.

Create Table Example

In SQL, The CREATE command is typically used to create a new table, index, or stored procedure. When creating a table, the syntax should look like:

CREATE TABLE [table name] ( [column definitions] )

We can use the DDL functionality of SQL to create tables in Groovy SQL. Here is a simple example on how to do that:

package sample
import groovy.sql.Sql
import java.sql.*
/**
 * A simple Groovy example program that connects to a MySQL database, creates a table, 
 * and then queries the database about the fields of the table for console output.
 */
class Sample {
    static void main(String[] args) {
        // connect to db
        def sql = Sql.newInstance("jdbc:mysql://localhost:3306/test",
                "root", "root", "com.mysql.jdbc.Driver")
        // the sql script that creates a table
        def createTableScript = """
            CREATE TABLE PERSON (
                 FIRST_NAME VARCHAR(50),
                 LAST_NAME VARCHAR(50)
            )"""
        // execute the create table script
        sql.execute(createTableScript);
        // query MySQL for the details of the created table
        sql.eachRow('DESCRIBE PERSON'){ row ->
            println "Fielld = ${row[0]}, type = ${row[1]}"
         }
        // close connection
        sql.close()
    }
}

The example creates a table person with two columns: first_name and last_name. Both columns are Strings with maximum length of 50. MySQL also supports the command "DESCRIBE" to query the details of a table definition. Below is the output of the code when executed:

Fielld = FIRST_NAME, type = varchar(50)
Fielld = LAST_NAME, type = varchar(50)