从文件执行db语句 [英] Execute db statements from file

问题描述

我为我的应用程序使用嵌入式Apache derby。我有一个名为 createdb.sql 的SQL脚本，用于创建数据库中的所有表，并用初始数据填充它，例如：

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);

或

Statement s = conn.createStatement();
s.executeUpdate(sqlStr);

其中 sqlStr 是一个String变量， createdb.sql 文件。如何执行脚本中包含的所有SQL命令，以便我可以创建所有表并初始化它们？ Btw，SQL脚本工作，因为我在SQuirreL SQL Client中使用它来手动创建和初始化数据库。

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.

推荐答案

下面的教程介绍如何运行一个mysql脚本文件）。你必须做的是将mysql数据库连接更改为derby db并运行。它会工作。

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/

这是一种运行MySQL脚本而不使用任何第三方库的替代方法。

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 /

这篇关于从文件执行db语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！