bda-datastore/src/main/java/gr/ntua/ece/cslab/selis/bda/datastore/connectors/DatastorePostgresqlConnector.java
public void put(MasterData masterData) throws Exception {
        try {
            Statement st = conn.getConnection().createStatement();
            for (DimensionTable table: masterData.getTables()) {
                List<KeyValue> columns = table.getSchema().getColumnTypes();
                String primaryKey = table.getSchema().getPrimaryKey();
                st.addBatch("DROP TABLE IF EXISTS "+table.getName()+";");

                String q="CREATE TABLE " + table.getName() + " (";
                for (KeyValue element : columns){
                    q+=element.getKey()+" "+element.getValue();
                    if (element.getKey().equals(primaryKey))
                        q+=" PRIMARY KEY";
                    q+=",";
                }
                q=q.substring(0, q.length() - 1)+");";
                LOGGER.log(Level.INFO, q);
                st.addBatch(q);
                st.addBatch("ALTER TABLE " + table.getName() + " OWNER TO "+ conn.getUsername()+";");
                st.executeBatch();

                // fill-in column values
                List<Tuple> data = table.getData();
                if (data.size()>0) {
                    String values = "";
                    String insertTableSQL = "INSERT INTO " + table.getName() + " (";
                    for (KeyValue element : data.get(0).getTuple()) {
                        insertTableSQL += element.getKey() + ",";
                        values += "?,";
                    }
                    insertTableSQL = insertTableSQL.substring(0, insertTableSQL.length() - 1) + ") VALUES (" + values.substring(0, values.length() - 1) + ");";

                    PreparedStatement prepst = conn.getConnection().prepareStatement(insertTableSQL);
                    for (Tuple tuple : data) {
                        int i = 1;
                        for (KeyValue element : tuple.getTuple()) {
                            for (KeyValue field : columns) {
                                if (field.getKey().equals(element.getKey())) {
                                    if (field.getValue().contains("integer"))
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.INTEGER);
                                        else
                                            prepst.setInt(i, Integer.valueOf(element.getValue()));
                                    else if (field.getValue().contains("numeric"))
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.NUMERIC);
                                        else
                                            prepst.setFloat(i, Float.valueOf(element.getValue()));
                                    else if (field.getValue().contains("timestamp"))
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.TIMESTAMP);
                                        else
                                            prepst.setTimestamp(i, Timestamp.valueOf(element.getValue()));
                                    else if (field.getValue().contains("bytea"))
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.BINARY);
                                        else
                                            prepst.setBytes(i, element.getValue().getBytes());
                                    else if (field.getValue().contains("boolean"))
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.BOOLEAN);
                                        else
                                            prepst.setBoolean(i, Boolean.parseBoolean(element.getValue()));
                                    else if (field.getValue().contains("character varying"))
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.VARCHAR);
                                        else
                                            prepst.setString(i, element.getValue());
                                    else
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.NULL);
                                        else
                                            prepst.setString(i, element.getValue());
                                }
                            }
                            i++;
                        }
                        prepst.addBatch();
                    }
                    prepst.executeBatch();
                }
                conn.getConnection().commit();
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Failed creation");
            e.printStackTrace();
            conn.getConnection().rollback();
        }
    }
// Create dimension table and populate it
Create dimension table and populate it
Create dimension table and populate it
public void put(MasterData masterData) throws Exception {
        try {
            Statement st = conn.getConnection().createStatement();
            for (DimensionTable table: masterData.getTables()) {
                List<KeyValue> columns = table.getSchema().getColumnTypes();
                String primaryKey = table.getSchema().getPrimaryKey();
                st.addBatch("DROP TABLE IF EXISTS "+table.getName()+";");
                String q="CREATE TABLE " + table.getName() + " (";
                for (KeyValue element : columns){
                    q+=element.getKey()+" "+element.getValue();
                    if (element.getKey().equals(primaryKey))
                        q+=" PRIMARY KEY";
                    q+=",";
                }
                q=q.substring(0, q.length() - 1)+");";
                LOGGER.log(Level.INFO, q);
                st.addBatch(q);
                st.addBatch("ALTER TABLE " + table.getName() + " OWNER TO "+ conn.getUsername()+";");
                st.executeBatch();
                List<Tuple> data = table.getData();
                if (data.size()>0) {
                    String values = "";
                    String insertTableSQL = "INSERT INTO " + table.getName() + " (";
                    for (KeyValue element : data.get(0).getTuple()) {
                        insertTableSQL += element.getKey() + ",";
                        values += "?,";
                    }
                    insertTableSQL = insertTableSQL.substring(0, insertTableSQL.length() - 1) + ") VALUES (" + values.substring(0, values.length() - 1) + ");";
                    PreparedStatement prepst = conn.getConnection().prepareStatement(insertTableSQL);
                    for (Tuple tuple : data) {
                        int i = 1;
                        for (KeyValue element : tuple.getTuple()) {
                            for (KeyValue field : columns) {
                                if (field.getKey().equals(element.getKey())) {
                                    if (field.getValue().contains("integer"))
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.INTEGER);
                                        else
                                            prepst.setInt(i, Integer.valueOf(element.getValue()));
                                    else if (field.getValue().contains("numeric"))
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.NUMERIC);
                                        else
                                            prepst.setFloat(i, Float.valueOf(element.getValue()));
                                    else if (field.getValue().contains("timestamp"))
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.TIMESTAMP);
                                        else
                                            prepst.setTimestamp(i, Timestamp.valueOf(element.getValue()));
                                    else if (field.getValue().contains("bytea"))
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.BINARY);
                                        else
                                            prepst.setBytes(i, element.getValue().getBytes());
                                    else if (field.getValue().contains("boolean"))
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.BOOLEAN);
                                        else
                                            prepst.setBoolean(i, Boolean.parseBoolean(element.getValue()));
                                    else if (field.getValue().contains("character varying"))
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.VARCHAR);
                                        else
                                            prepst.setString(i, element.getValue());
                                    else
                                        if (element.getValue().equalsIgnoreCase("null"))
                                            prepst.setNull(i,Types.NULL);
                                        else
                                            prepst.setString(i, element.getValue());
                                }
                            }
                            i++;
                        }
                        prepst.addBatch();
                    }
                    prepst.executeBatch();
                }
                conn.getConnection().commit();
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "Failed creation");
            e.printStackTrace();
            conn.getConnection().rollback();
        }
    }
public void put ( MasterData masterData ) throws Exception { try { Statement st = conn . getConnection ( ) . createStatement ( ) ; for ( DimensionTable table : masterData . getTables ( ) ) { List < KeyValue > columns = table . getSchema ( ) . getColumnTypes ( ) ; String primaryKey = table . getSchema ( ) . getPrimaryKey ( ) ; st . addBatch ( "DROP TABLE IF EXISTS " + table . getName ( ) + ";" ) ; String q = "CREATE TABLE " + table . getName ( ) + " (" ; for ( KeyValue element : columns ) { q += element . getKey ( ) + " " + element . getValue ( ) ; if ( element . getKey ( ) . equals ( primaryKey ) ) q += " PRIMARY KEY" ; q += "," ; } q = q . substring ( 0 , q . length ( ) - 1 ) + ");" ; LOGGER . log ( Level . INFO , q ) ; st . addBatch ( q ) ; st . addBatch ( "ALTER TABLE " + table . getName ( ) + " OWNER TO " + conn . getUsername ( ) + ";" ) ; st . executeBatch ( ) ; List < Tuple > data = table . getData ( ) ; if ( data . size ( ) > 0 ) { String values = "" ; String insertTableSQL = "INSERT INTO " + table . getName ( ) + " (" ; for ( KeyValue element : data . get ( 0 ) . getTuple ( ) ) { insertTableSQL += element . getKey ( ) + "," ; values += "?," ; } insertTableSQL = insertTableSQL . substring ( 0 , insertTableSQL . length ( ) - 1 ) + ") VALUES (" + values . substring ( 0 , values . length ( ) - 1 ) + ");" ; PreparedStatement prepst = conn . getConnection ( ) . prepareStatement ( insertTableSQL ) ; for ( Tuple tuple : data ) { int i = 1 ; for ( KeyValue element : tuple . getTuple ( ) ) { for ( KeyValue field : columns ) { if ( field . getKey ( ) . equals ( element . getKey ( ) ) ) { if ( field . getValue ( ) . contains ( "integer" ) ) if ( element . getValue ( ) . equalsIgnoreCase ( "null" ) ) prepst . setNull ( i , Types . INTEGER ) ; else prepst . setInt ( i , Integer . valueOf ( element . getValue ( ) ) ) ; else if ( field . getValue ( ) . contains ( "numeric" ) ) if ( element . getValue ( ) . equalsIgnoreCase ( "null" ) ) prepst . setNull ( i , Types . NUMERIC ) ; else prepst . setFloat ( i , Float . valueOf ( element . getValue ( ) ) ) ; else if ( field . getValue ( ) . contains ( "timestamp" ) ) if ( element . getValue ( ) . equalsIgnoreCase ( "null" ) ) prepst . setNull ( i , Types . TIMESTAMP ) ; else prepst . setTimestamp ( i , Timestamp . valueOf ( element . getValue ( ) ) ) ; else if ( field . getValue ( ) . contains ( "bytea" ) ) if ( element . getValue ( ) . equalsIgnoreCase ( "null" ) ) prepst . setNull ( i , Types . BINARY ) ; else prepst . setBytes ( i , element . getValue ( ) . getBytes ( ) ) ; else if ( field . getValue ( ) . contains ( "boolean" ) ) if ( element . getValue ( ) . equalsIgnoreCase ( "null" ) ) prepst . setNull ( i , Types . BOOLEAN ) ; else prepst . setBoolean ( i , Boolean . parseBoolean ( element . getValue ( ) ) ) ; else if ( field . getValue ( ) . contains ( "character varying" ) ) if ( element . getValue ( ) . equalsIgnoreCase ( "null" ) ) prepst . setNull ( i , Types . VARCHAR ) ; else prepst . setString ( i , element . getValue ( ) ) ; else if ( element . getValue ( ) . equalsIgnoreCase ( "null" ) ) prepst . setNull ( i , Types . NULL ) ; else prepst . setString ( i , element . getValue ( ) ) ; } } i ++ ; } prepst . addBatch ( ) ; } prepst . executeBatch ( ) ; } conn . getConnection ( ) . commit ( ) ; } } catch ( SQLException e ) { LOGGER . log ( Level . SEVERE , "Failed creation" ) ; e . printStackTrace ( ) ; conn . getConnection ( ) . rollback ( ) ; } }
Create dimension table and populate it
Create dimension table and populate it
// fill-in column values