Java tutorial
/* * Copyright (C) 2014 TU Darmstadt, Hessen, Germany. * Department of Computer Science Databases and Distributed Systems * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. */ package de.tudarmstadt.dvs.myhealthassistant.myhealthhub.services.transformationmanager.database; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Collections; import java.util.Comparator; import java.util.Date; import java.util.List; import org.json.JSONArray; import org.json.JSONException; import org.json.JSONObject; import de.tudarmstadt.dvs.myhealthassistant.myhealthhub.events.management.JSONDataExchange; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.util.Log; public class LocalTransformationDBMS { private static String TAG = "LocalTransformationDBMS"; private static boolean D = false; private SQLiteDatabase database; private LocalTransformationDB dbHelper; public LocalTransformationDBMS(Context context) { dbHelper = new LocalTransformationDB(context); } public void open() { database = dbHelper.getWritableDatabase(); } public void close() { dbHelper.close(); } public boolean addTransformation(long bundleId, String transformationName, String producedEventType, List<String> requiredEventTypes, int costs) { // generate semicolon separated list of required event types String requiredEvents = ""; for (String type : requiredEventTypes) { requiredEvents += type + ";"; } ContentValues values = new ContentValues(); values.put(LocalTransformationDB.COLUMN_BUNDLE_ID, bundleId); values.put(LocalTransformationDB.COLUMN_TRANSFORMATION_NAME, transformationName); values.put(LocalTransformationDB.COLUMN_PRODUCED_EVENT_TYPE, producedEventType); values.put(LocalTransformationDB.COLUMN_REQUIRED_EVENT_TYPES, requiredEvents); values.put(LocalTransformationDB.COLUMN_TRANSFORMATION_COSTS, costs); long insertId = database.insert(LocalTransformationDB.TABLE_LOCAL_TRANSFORMATIONS, null, values); return insertId != -1; } private Transformation cursorToTransformation(Cursor cursor, int position) { if (cursor.moveToPosition(position)) { Transformation transformation = new Transformation( cursor.getLong(cursor.getColumnIndex(LocalTransformationDB.COLUMN_BUNDLE_ID)), cursor.getString(cursor.getColumnIndex(LocalTransformationDB.COLUMN_TRANSFORMATION_NAME)), cursor.getString(cursor.getColumnIndex(LocalTransformationDB.COLUMN_PRODUCED_EVENT_TYPE)), cursor.getInt(cursor.getColumnIndex(LocalTransformationDB.COLUMN_TRANSFORMATION_COSTS))); String requiredEventTypes = cursor .getString(cursor.getColumnIndex(LocalTransformationDB.COLUMN_REQUIRED_EVENT_TYPES)); String[] types = requiredEventTypes.split(";"); for (String type : types) { if (D) Log.d(TAG, "required event type: " + type); transformation.addRequiredEvent(type); } return transformation; } else return null; } public ArrayList<Transformation> getAvailableTransformations() { ArrayList<Transformation> transformations = new ArrayList<Transformation>(); if (D) Log.i(TAG, " quering for all transformations"); Cursor cursor = database.query(LocalTransformationDB.TABLE_LOCAL_TRANSFORMATIONS, null, null, null, null, null, null); Transformation tempTrans; for (int i = 0; i < cursor.getCount(); i++) { tempTrans = cursorToTransformation(cursor, i); if (tempTrans != null) transformations.add(tempTrans); } cursor.close(); return transformations; } /** * @param name */ public int deleteTransformation(long name) { return database.delete(LocalTransformationDB.TABLE_LOCAL_TRANSFORMATIONS, LocalTransformationDB.COLUMN_BUNDLE_ID + " = ?", new String[] { name + "" }); } public boolean addTraffic(String date, int trafficType, double xValue, double yValue) { ContentValues values = new ContentValues(); values.put(LocalTransformationDB.COLUMN_DATE_TEXT, date); values.put(LocalTransformationDB.COLUMN_TYPE, trafficType); values.put(LocalTransformationDB.COLUMN_X_AXIS, xValue); values.put(LocalTransformationDB.COLUMN_Y_AXIS, yValue); long insertId = database.insert(LocalTransformationDB.TABLE_TRAFFIC_MON, null, values); if (insertId == -1) { Log.e(TAG, "addTraffic failed at:[" + date + "; type:" + trafficType + "; " + xValue + "; " + yValue + "]"); } return insertId != -1; } public boolean addDateOfTraffic(String date, int trafficId) { ContentValues values = new ContentValues(); values.put(LocalTransformationDB.COLUMN_TRAFFIC_ID, trafficId); values.put(LocalTransformationDB.COLUMN_DATE_TEXT, date); long insertId = database.insert(LocalTransformationDB.TABLE_DATE_TO_TRAFFIC, null, values); if (insertId == -1) { Log.e(TAG, "addTraffic failed at:[" + date + "]"); } return insertId != -1; } public ArrayList<TrafficData> getAllTrafficFromDate(String date, int typeID) { ArrayList<TrafficData> list = new ArrayList<TrafficData>(); String q = "SELECT * FROM " + LocalTransformationDB.TABLE_TRAFFIC_MON // + ";"; + " where( " + LocalTransformationDB.COLUMN_DATE_TEXT + " like '" + date + "%' AND " + LocalTransformationDB.COLUMN_TYPE + " = " + typeID + ")" + " ORDER BY " + LocalTransformationDB.COLUMN_ID + ";"; Cursor cursor = database.rawQuery(q, null); if (cursor.moveToFirst()) { do { TrafficData trafficData = new TrafficData( cursor.getString(cursor.getColumnIndex(LocalTransformationDB.COLUMN_DATE_TEXT)), cursor.getInt(cursor.getColumnIndex(LocalTransformationDB.COLUMN_TYPE)), cursor.getDouble(cursor.getColumnIndex(LocalTransformationDB.COLUMN_X_AXIS)), cursor.getDouble(cursor.getColumnIndex(LocalTransformationDB.COLUMN_Y_AXIS))); list.add(trafficData); } while (cursor.moveToNext()); } cursor.close(); return list; } public ArrayList<String> getAllAvalDate() { ArrayList<String> list = new ArrayList<String>(); String q = "SELECT * FROM " + LocalTransformationDB.TABLE_DATE_TO_TRAFFIC + " ORDER BY " + LocalTransformationDB.COLUMN_DATE_ID + ";"; Cursor cursor = database.rawQuery(q, null); if (cursor.moveToFirst()) { do { String date = cursor.getString(cursor.getColumnIndex(LocalTransformationDB.COLUMN_DATE_TEXT)); if (!list.contains(date)) list.add(date); } while (cursor.moveToNext()); } cursor.close(); Collections.sort(list, new Comparator<String>() { @Override public int compare(String arg0, String arg1) { SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy"); int compareResult = 0; try { Date arg0Date = format.parse(arg0); Date arg1Date = format.parse(arg1); compareResult = arg0Date.compareTo(arg1Date); } catch (ParseException e) { e.printStackTrace(); compareResult = arg0.compareTo(arg1); } return compareResult; } }); for (String s : list) { Log.e(TAG, "AvalDate:" + s); } return list; } public void storeJsonData(ArrayList<ContentValues> vArray) { for (ContentValues values : vArray) { long insertId = database.insert(LocalTransformationDB.TABLE_JSON_DATA_EXCHANGE, null, values); if (insertId == -1) { Log.e(TAG, "addJsonData failed at:[" + values.toString() + "]"); } } } public void editJsonData(int id, ContentValues content) { int nrRows = database.update(LocalTransformationDB.TABLE_JSON_DATA_EXCHANGE, content, LocalTransformationDB.COLUMN_JSON_ID + "=?", new String[] { String.valueOf(id) }); Log.e(TAG, "editJsonData, nrOfRowsEffect=" + nrRows); } public void deleteJsonData(int id) { int nrRows = database.delete(LocalTransformationDB.TABLE_JSON_DATA_EXCHANGE, LocalTransformationDB.COLUMN_JSON_ID + "=?", new String[] { String.valueOf(id) }); Log.e(TAG, "deleteJsonData, nrOfRowsEffect=" + nrRows); } public JSONArray getAlljsonData() { JSONArray jArray = new JSONArray(); String q = "SELECT * FROM " + LocalTransformationDB.TABLE_JSON_DATA_EXCHANGE + " ORDER BY " + LocalTransformationDB.COLUMN_JSON_ID + ";"; Cursor cursor = database.rawQuery(q, null); if (cursor.moveToFirst()) { do { String contents = cursor.getString(cursor.getColumnIndex(LocalTransformationDB.COUMN_JSON_CONTENT)); int id = cursor.getInt(cursor.getColumnIndex(LocalTransformationDB.COLUMN_JSON_ID)); try { JSONObject jObj = new JSONObject(contents); jObj.put(JSONDataExchange.JSON_CONTENT_ID, id); jArray.put(jObj); } catch (JSONException e) { e.printStackTrace(); } } while (cursor.moveToNext()); } cursor.close(); return jArray; } public void deleteAllTrafficRecords() { // drop and recreate table database.execSQL("DROP TABLE IF EXISTS " + LocalTransformationDB.TABLE_TRAFFIC_MON); database.execSQL(LocalTransformationDB.TRAFFIC_MON_CREATE); database.execSQL("DROP TABLE IF EXISTS " + LocalTransformationDB.TABLE_DATE_TO_TRAFFIC); database.execSQL(LocalTransformationDB.DATE_TO_TRAFFIC); } public int deleteAllTrafficFromDate(String date) { database.delete(LocalTransformationDB.TABLE_DATE_TO_TRAFFIC, LocalTransformationDB.COLUMN_DATE_TEXT + " like '" + date + "%'", null); return database.delete(LocalTransformationDB.TABLE_TRAFFIC_MON, LocalTransformationDB.COLUMN_DATE_TEXT + " like '" + date + "%'", null); } }