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)
