代码之家 › 专栏 › 技术社区 › Amarjit Dhillon

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

sqlite shell java

Amarjit Dhillon · 技术社区 · 7 年前

我在同一文件夹中有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)

问题是attach不起作用,我尝试使用可执行bash执行相同的操作,如下所示,但当 sqlite3 joined.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,但由于我正在做一些实验,所以我不想重复这么做

提前感谢!

1 回复 | 直到 7 年前

varro 7 年前

您正在将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"