RSS-Feed

David Hadizadeh

E-Mail

Xing-Profil

LinkedIn

Impressum

 




SQLite Persistence-Manager (Java)

06.05.2015

Here you can see an alternative implementation of the persistence manager for the positioning library.
This implementation is not for Android. If you want to use SQLite for Android you should adapt it for the SQLite database in Android.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154

import de.hadizadeh.positioning.exceptions.PositioningPersistenceException;
import de.hadizadeh.positioning.model.PositionInformation;
import de.hadizadeh.positioning.model.SignalInformation;
 
import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
 
public class SQLitePersistenceManager implements PersistenceManager {
    private Connection c = null;
    protected File persistenceFile;
 
    public SQLitePersistenceManager(File persistenceFile) throws PositioningPersistenceException {
        try {
            this.persistenceFile = persistenceFile;
            createStructure();
        } catch (SQLException e) {
            throw new PositioningPersistenceException(e.getMessage());
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new PositioningPersistenceException(e.getMessage());
        }
    }
 
    private void connect() throws ClassNotFoundException, SQLException {
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:" + persistenceFile);
    }
 
    private void createStructure() throws SQLException, ClassNotFoundException {
        connect();
        Statement stmt = c.createStatement();
        stmt.executeUpdate("CREATE TABLE IF NOT EXISTS Positions " +
                           "(ID INTEGER PRIMARY KEY AUTOINCREMENT," +
                           " name CHAR(100) NOT NULL, " +
                           " technology CHAR(100) NOT NULL)");
        stmt.executeUpdate("CREATE TABLE IF NOT EXISTS Signalinformation " +
                           "(ID INTEGER PRIMARY KEY AUTOINCREMENT," +
                           " name CHAR(100) NOT NULL, " +
                           " strength REAL NOT NULL, " +
                           " position INTEGER NOT NULL)");
        stmt.close();
        c.close();
    }
 
    @Override
    public Map<String, List> getPersistedPositions() throws PositioningPersistenceException {
        Map<String, List> positionInformation = new HashMap<String, List>();
        try {
            connect();
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT ID, name, technology FROM Positions;");
            while (rs.next()) {
                String technology = rs.getString("technology");
                if (!positionInformation.containsKey(technology)) {
                    positionInformation.put(technology, new ArrayList());
                }
 
                Map<String, SignalInformation> signalInformationData = new HashMap<String, SignalInformation>();
                Statement signalStmt = c.createStatement();
                ResultSet signalRs = signalStmt.executeQuery("SELECT name, strength FROM Signalinformation;");
                while (signalRs.next()) {
                    signalInformationData.put(signalRs.getString("name"), new SignalInformation(signalRs.getDouble("strength")));
                }
                signalRs.close();
                signalStmt.close();
                positionInformation.get(technology).add(new PositionInformation(rs.getString("name"), signalInformationData));
            }
            rs.close();
            stmt.close();
            c.close();
        } catch (SQLException e) {
            throw new PositioningPersistenceException(e.getMessage());
        } catch (ClassNotFoundException e) {
            throw new PositioningPersistenceException(e.getMessage());
        }
        return positionInformation;
    }
 
    @Override
    public void persistPosition(String technologyName, PositionInformation positionInformation) throws PositioningPersistenceException {
        try {
            connect();
            Statement stmt = c.createStatement();
            removeMappedPosition(positionInformation.getName(), technologyName);
            long id = stmt.executeUpdate("INSERT INTO Positions (name, technology) " + "VALUES ('" + positionInformation.getName() + "', '" +
                                         technologyName + "');", Statement.RETURN_GENERATED_KEYS);
            for (Map.Entry<String, SignalInformation> signalInformation : positionInformation.getSignalInformation().entrySet()) {
                stmt.executeUpdate("INSERT INTO Signalinformation (name, strength, position) " + "VALUES ('" + signalInformation.getKey() + "', " +
                                   signalInformation.getValue() + ", " + id + ");");
            }
            stmt.close();
            c.close();
        } catch (SQLException e) {
            throw new PositioningPersistenceException(e.getMessage());
        } catch (ClassNotFoundException e) {
            throw new PositioningPersistenceException(e.getMessage());
        }
    }
 
    @Override
    public void removeMappedPosition(String name) throws PositioningPersistenceException {
        try {
            connect();
            Statement stmt = c.createStatement();
            String whereClause = "WHERE name='" + name + "'";
            stmt.executeUpdate("DELETE FROM Signalinformation WHERE position IN (SELECT ID FROM Position " + whereClause + ")");
            stmt.executeUpdate("DELETE FROM Positions " + whereClause);
            stmt.close();
            c.close();
        } catch (SQLException e) {
            throw new PositioningPersistenceException(e.getMessage());
        } catch (ClassNotFoundException e) {
            throw new PositioningPersistenceException(e.getMessage());
        }
    }
 
    @Override
    public void removeMappedPosition(String name, String technology) throws PositioningPersistenceException {
        try {
            connect();
            Statement stmt = c.createStatement();
            String whereClause = "WHERE name='" + name + "', technology='" + technology + "'";
            stmt.executeUpdate("DELETE FROM Signalinformation WHERE position IN (SELECT ID FROM Position " + whereClause + ")");
            stmt.executeUpdate("DELETE FROM Positions " + whereClause);
            stmt.close();
            c.close();
        } catch (SQLException e) {
            throw new PositioningPersistenceException(e.getMessage());
        } catch (ClassNotFoundException e) {
            throw new PositioningPersistenceException(e.getMessage());
        }
    }
 
    @Override
    public void removeAllMappedPositions() throws PositioningPersistenceException {
        try {
            connect();
            Statement stmt = c.createStatement();
            stmt.executeUpdate("DROP TABLE Signalinformation");
            stmt.executeUpdate("DROP TABLE Positions");
            stmt.close();
            c.close();
        } catch (SQLException e) {
            throw new PositioningPersistenceException(e.getMessage());
        } catch (ClassNotFoundException e) {
            throw new PositioningPersistenceException(e.getMessage());
        }
    }
}

Kategorie: Indoor-Positioning Addons |


Impressum