java - 如何使用bash或java将三个不同的sqlite3数据库D1、D2和D3附加到另一个数据库D4

Question

我在同一文件夹中有 3 个不同的数据库，如下所示

我想将其他 3 个数据库附加到 joined.db 数据库，然后创建一个名为 join1 的表，如下所示

public class attaching_DB {


public static void main(String args[]) {
    Connection c = null;
    Statement stmt = null;

    try {
        Class.forName("org.sqlite.JDBC");


        String database_path = "/Users/amar/Documents/ThesisCode/CEP_Architectures/databases/joined.db";

        c = DriverManager.getConnection("jdbc:sqlite:" + database_path);


        System.out.println("connection to sql is made");

        stmt = c.createStatement();




        // attaching all the tables in joined database
        String a1 = "ATTACH DATABASE 'mobile_data.db' as 'mobile';" ;
        String a2 = "ATTACH DATABASE 'server_data.db' as 'server';" ;
        String a3 = "ATTACH DATABASE 'flink_data.db' as 'flink';" ;


        stmt.execute(a1);
        stmt.execute(a2);
        stmt.execute(a3);


        String b1 =  "CREATE TABLE if not EXISTS  mobile_events as select * from mobile.mobile_events;"   ;
        String b2 =  "CREATE TABLE if not EXISTS  server_events  as select * from server.server_events;" ;
        String b3 =  "CREATE TABLE if not EXISTS  flink_events  as select * from flink.flink_events;"  ;

        stmt.executeUpdate(b1);
        stmt.executeUpdate(b2);
        stmt.executeUpdate(b3);


        // joining mobile and server data
        String join1 = "CREATE TABLE  if not EXISTS join1 as select M.patientid, M.sensorid , M.uid , M.egtl, M.egtg, S.eatg, M.valuez from mobile_events M inner join server_events S  on M.sensorid = S.sensorid and  M.uid = S.uid ; " ;
        stmt.executeUpdate(join1);



        stmt.close();
        c.commit();
        c.close();
    } catch (Exception e) {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
        System.exit(0);
    }
    System.out.println("attach is successful");
}

}

我收到以下错误

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: mobile.mobile_events)

问题是附加不起作用，我尝试使用可执行文件 bash 执行相同的操作，如下所示，但是当执行 sqlite3 join.db 时，会打开一个新选项卡并且脚本停止

    #! /bin/bash
clear
cd /Users/amar/Documents/ThesisCode/CEP_Architectures/databases/
#starting sqlite3
sqlite3 joined.db

# attaching other db's
ATTACH DATABASE 'mobile_data.db' as 'mobile';
ATTACH DATABASE 'server_data.db' as 'server';
ATTACH DATABASE 'flink_data.db' as 'flink';

# creating tables
CREATE TABLE mobile_events as select * from mobile.mobile_events;
CREATE TABLE server_events as select * from server.server_events;
CREATE TABLE server_events as select * from server.server_events;

# crate a joined table
CREATE TABLE join1 as select M.patientid, M.sensorid , M.uid , M.egtl, M.egtg, S.eatg, M.valuez from mobile_events M inner join server_events S  on M.sensorid = S.sensorid and  M.uid = S.uid ;
echo "program is complete"

P.S:我可以通过在终端中手动编写这些脚本来附加数据库并创建表 join1，但由于我正在做一些实验，所以我不想一次又一次地这样做

提前致谢!

Answer 1

您将 SQL 语句传递给 bash，而 bash 无法理解它们。将它们传递给 sqlite3，例如:

#! /bin/bash
clear
cd /Users/amar/Documents/ThesisCode/CEP_Architectures/databases/
#starting sqlite3
sqlite3 joined.db <<EOF

-- attaching other db's
ATTACH DATABASE "mobile_data.db" as mobile;
ATTACH DATABASE "server_data.db" as server;
ATTACH DATABASE "flink_data.db" as flink;

-- creating tables
CREATE TABLE mobile_events as select * from mobile.mobile_events;
CREATE TABLE server_events as select * from server.server_events;
CREATE TABLE flink_events as select * from flink.flink_events;

-- create a joined table
CREATE TABLE join1 as select M.patientid, M.sensorid , M.uid , M.egtl, M.egtg, S.eatg, M.valuez from mobile_events M inner join server_events S  on M.sensorid = S.sensorid and  M.uid = S.uid ;

EOF

echo "program is complete"