I use embedded Apache derby for my application. I have a SQL script called createdb.sql that creates all tables in a database and populates it with initial data, e.g.:
SET SCHEMA APP;
CREATE TABLE study (
study_id bigint not null GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
name varchar(50) not null,
note varchar(1000) DEFAULT '',
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
deleted boolean DEFAULT false,
UNIQUE(name),
CONSTRAINT primary_key PRIMARY KEY (study_id)
);
INSERT INTO "APP"."STUDY" (NAME) VALUES ('default');
CREATE TABLE img (
img_id bigint not null GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
filename varchar(200) not null,
path varchar(300) not null,
flipped boolean DEFAULT false,
type smallint not null,
note varchar(1000) DEFAULT '',
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (img_id)
);
ALTER TABLE img ADD COLUMN dpix integer DEFAULT -1;
ALTER TABLE img ADD COLUMN dpiy integer DEFAULT -1;
The question is how do I load this file and execute all the statements using java? I'm trying different function but they all don't work. For example,
Statement s = conn.createStatement();
s.execute(sqlStr);
or
Statement s = conn.createStatement();
s.executeUpdate(sqlStr);
where sqlStr is a String variable containing the contents of the createdb.sql file. How do I execute all the SQL commands contained in the script so that I can create all the tables and initialize them? Btw, the SQL script works, as I use it in SQuirreL SQL Client to manualy create and initialize the database. Now I would like to do it from within my application.
The below tutorial give how to run a mysql script(.sql file) . What you have to do is that Change the mysql db connection to derby db and run. It will work.
http://www.mkyong.com/jdbc/how-to-run-a-mysql-script-using-java/
Here is an alternative way to run a MySQL script without using any third party library.
http://coreyhulen.wordpress.com/2010/04/07/run-a-sql-script-for-mysql-using-java/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With