Diffusion Genome Privacy - SHCS App (master) Browse

DBconnector_mu.java
89c2e8c655e6master

DBconnector_mu.java
View Options

			package FrameWork;

			import java.math.BigInteger;
			import java.sql.Connection;
			import java.sql.Date;
			import java.sql.DriverManager;
			import java.sql.PreparedStatement;
			import java.sql.ResultSet;
			import java.sql.SQLException;
			import java.util.ArrayList;
			import java.util.HashMap;
			import java.util.LinkedList;

			import org.apache.http.conn.scheme.Scheme;
			import org.json.JSONException;
			import org.json.JSONObject;

			import android.media.MediaCodec.CryptoException;

			/**
			* @author Serrano Kevin, Weber Jeremy
			*
			*/
			public class DBconnector_mu {

			/**
			* The link from Java to the database
			*/
			static private Connection connect = null;
			/**
			* The SQL statement
			*/
			static private PreparedStatement preparedStatement = null;
			/**
			* The result of the SQL statement
			*/
			static private ResultSet resultSet = null;
			/**
			* Properties for accessing the server
			*/
			static private ReadProperties prop = new ReadProperties();
			/**
			* @return a list of all the patients
			* @throws SQLException
			*/
			static public ArrayList<Integer> getPatients() throws SQLException {
			openConnection();
			ArrayList<Integer> patients = new ArrayList<Integer>();
			preparedStatement = connect
			.prepareStatement("SELECT ID_p FROM mu.patients");
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			patients.add(resultSet.getInt("ID_p"));
			}
			closeConnection();
			return patients;
			}

			/**
			* @return return an HashMap mapping every disease to his ID
			* @throws SQLException
			*/
			static public HashMap<String, Integer> diseaseToIDdisease()
			throws SQLException {
			HashMap<String, Integer> diseaseToIDdisease = new HashMap<String, Integer>();
			openConnection();
			preparedStatement = connect
			.prepareStatement("SELECT ID_dis, scatname FROM mu.subdiseases");
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			diseaseToIDdisease.put(resultSet.getString("scatname"),
			resultSet.getInt("ID_dis"));
			}
			closeConnection();
			return diseaseToIDdisease;
			}

			/**
			* @return return an HashMap mapping every id_dis to his name
			* @throws SQLException
			*/
			static public HashMap<Integer, String> IDDisToDisease() throws SQLException {
			HashMap<Integer, String> IDdiseaseToID = new HashMap<Integer, String>();
			openConnection();
			preparedStatement = connect
			.prepareStatement("SELECT ID_dis, scatname FROM mu.subdiseases");
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			IDdiseaseToID.put(resultSet.getInt("ID_dis"),
			resultSet.getString("scatname"));
			}
			closeConnection();
			return IDdiseaseToID;
			}

			/**
			* @return return an HashMap mapping every id_dis to id category
			* @throws SQLException
			*/
			static public HashMap<Integer, Integer> IDDisToIDcat() throws SQLException {
			HashMap<Integer, Integer> IDdiseaseToIDcat = new HashMap<Integer, Integer>();
			openConnection();
			preparedStatement = connect
			.prepareStatement("SELECT ID_dis, catid FROM mu.subdiseases");
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			IDdiseaseToIDcat.put(resultSet.getInt("ID_dis"),
			resultSet.getInt("catid"));
			}
			closeConnection();
			return IDdiseaseToIDcat;
			}

			/**
			* Get ID of diseases depending the category ID
			*/
			static public ArrayList<Integer> getIDDis(int catId) throws SQLException {
			ArrayList<Integer> IDDisFromCatID = new ArrayList<Integer>();
			openConnection();
			preparedStatement = connect
			.prepareStatement("SELECT ID_dis FROM mu.subdiseases WHERE catid = ?");
			preparedStatement.setInt(0, catId);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			IDDisFromCatID.add(resultSet.getInt("ID_dis"));
			}
			closeConnection();
			return IDDisFromCatID;

			}

			/**
			* @return the different category of disease
			* @throws SQLException
			*/
			static public HashMap<Integer, LinkedList<String>> catIDtoDisease()
			throws SQLException {
			ResultSet resultCatName = null;
			HashMap<Integer, LinkedList<String>> catIDtoDisease = new HashMap<Integer, LinkedList<String>>();
			openConnection();
			preparedStatement = connect
			.prepareStatement("SELECT catid FROM mu.subdiseases");
			resultSet = preparedStatement.executeQuery();
			preparedStatement = connect
			.prepareStatement("SELECT catid, scatname FROM mu.subdiseases");
			resultCatName = preparedStatement.executeQuery();
			while (resultSet.next()) {
			LinkedList<String> diseaseCat = new LinkedList<String>();
			catIDtoDisease.put(resultSet.getInt("catid"), diseaseCat);
			}
			while (resultCatName.next()) {
			catIDtoDisease.get(resultCatName.getInt("catid")).add(
			resultCatName.getString("scatname"));
			}
			closeConnection();
			return catIDtoDisease;
			}

			/**
			* @return the different number of category of disease
			* @throws SQLException
			*/
			static public HashMap<Integer, String> catIDtocatName() throws SQLException {
			HashMap<Integer, String> catIDtoCatName = new HashMap<Integer, String>();
			openConnection();
			preparedStatement = connect
			.prepareStatement("SELECT catid,catname FROM mu.diseases2");
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			catIDtoCatName.put(resultSet.getInt("catid"),
			resultSet.getString("catname"));
			}
			closeConnection();
			return catIDtoCatName;
			}

			/**
			* @return number of disease
			* @throws SQLException
			*/
			static public int getNbDiseases() throws SQLException {
			openConnection();
			int nb = 0;
			ArrayList[] results = new ArrayList[2];

			preparedStatement = connect
			// deleted the name of the test because we don't have it.
			.prepareStatement("SELECT count(*) as a FROM mu.subdiseases");
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			nb = resultSet.getInt("a");
			}

			closeConnection();
			return nb - 2;
			}

			/**
			* Get the test's ID from his name
			*
			* @param description
			* of the test
			* @return ID of the test
			* @throws SQLException
			*/
			static public int getTestID(String desc) throws SQLException {
			openConnection();
			int ID_t;
			preparedStatement = connect
			.prepareStatement("SELECT ID_t FROM mu.tests where name = ?");
			preparedStatement.setString(1, desc);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			ID_t = resultSet.getInt("ID_t");

			closeConnection();
			return ID_t;
			}

			/**
			* @param ID
			* test
			* @return an array which contains two arrayList : the markers and the
			* weight
			* @throws SQLException
			*/
			static public ArrayList[] getMarkers(int ID_t) throws SQLException {
			openConnection();
			ArrayList<Integer> markers = new ArrayList<Integer>();
			ArrayList<Double> weights = new ArrayList<Double>();

			preparedStatement = connect
			.prepareStatement("SELECT ID_vcf, weight FROM mu.d_v WHERE ID_dis = ?");
			preparedStatement.setInt(1, ID_t);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			markers.add(resultSet.getInt("ID_vcf"));
			weights.add(resultSet.getDouble("weight"));
			}
			closeConnection();
			ArrayList[] result = { markers, weights };
			return result;

			}

			/**
			* @param ID
			* test
			* @return an array of two arrayList : id cf and weight
			* @throws SQLException
			*/
			static public ArrayList[] getCFs(int ID_t) throws SQLException {
			openConnection();
			ArrayList<Integer> id_cf = new ArrayList<Integer>();
			ArrayList<Double> weights = new ArrayList<Double>();

			preparedStatement = connect
			.prepareStatement("SELECT ID_cf, weight FROM mu.cf_d WHERE ID_dis = ?");
			preparedStatement.setInt(1, ID_t);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			id_cf.add(resultSet.getInt("ID_cf"));
			weights.add(resultSet.getDouble("weight"));
			}
			closeConnection();
			ArrayList[] result = { id_cf, weights };
			return result;

			}

			/**
			* @param ID
			* patient
			* @return an array with the public key which is split in three
			* @throws SQLException
			*/
			static public BigInteger[] getPublicKey(int ID_p) throws SQLException {
			openConnection();
			BigInteger[] plk = new BigInteger[3];

			preparedStatement = connect
			.prepareStatement("SELECT Pub_key FROM mu.crypto_keys WHERE ID_p = ?");
			preparedStatement.setInt(1, ID_p);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			String[] pub_key = resultSet.getString("Pub_key").split(",");
			plk[0] = new BigInteger(pub_key[0]);
			plk[1] = new BigInteger(pub_key[1]);
			plk[2] = new BigInteger(pub_key[2]);

			closeConnection();
			return plk;

			}

			/**
			* @param ID
			* patient
			* @return the MU half private key
			* @throws SQLException
			*/
			static public BigInteger getPrivateKey(int ID_p) throws SQLException {
			openConnection();
			BigInteger prk = null;
			preparedStatement = connect
			.prepareStatement("SELECT Priv_key_half FROM mu.crypto_keys WHERE ID_p = ?");
			preparedStatement.setInt(1, ID_p);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			prk = new BigInteger(resultSet.getString("Priv_key_half"));
			closeConnection();
			return prk;

			}

			/**
			* @param id
			* patient
			* @return the complete profile of the patient
			*/
			@SuppressWarnings("null")
			static public JSONObject getProfil(int id) {
			JSONObject jsonRequest = null;
			openConnection();
			try {
			preparedStatement = connect
			.prepareStatement("SELECT * FROM patients WHERE ID = ?");
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			String name = resultSet.getString("Name");
			String lastName = resultSet.getString("LastName");
			Date birthDate = resultSet.getDate("Birth_date");

			jsonRequest.put("type", Type.PATIENT);
			jsonRequest.put("Name", name);
			jsonRequest.put("LastName", lastName);
			jsonRequest.put("birth_date", birthDate);
			jsonRequest.put("id", id);
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			}

			closeConnection();
			return jsonRequest;
			}

			/**
			* @param letter
			* that are entered in the search box
			* @return the list of the matches patient
			*/
			@SuppressWarnings("null")
			static public ArrayList<String> getPatientsName(String letter) {
			ArrayList<String> setPeople = new ArrayList<String>();
			openConnection();
			try {
			preparedStatement = connect
			.prepareStatement("SELECT LastName, Name, ID_p FROM mu.patients WHERE LastName REGEXP '[[:<:]]"
			+ letter
			+ "' OR ID_p REGEXP '[[:<:]]"
			+ letter
			+ "'");

			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			setPeople.add(resultSet.getString("LastName") + " ; "
			+ resultSet.getString("Name") + " ; "
			+ resultSet.getInt("ID_p"));
			}
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			}

			closeConnection();
			return setPeople;
			}

			/**
			* @param id_patient
			* @param key
			* @param flag
			* @throws SQLException
			*/
			static public void insertKey(int id_p, String key, boolean flag)
			throws SQLException {
			ResultSet alreadyPatient;
			openConnection();

			// check if a patient p already has a key's pair
			preparedStatement = connect
			.prepareStatement("SELECT ID_key, ID_p, Priv_key_half, Pub_key FROM mu.crypto_keys WHERE ID_p = ?");
			preparedStatement.setInt(1, id_p);
			alreadyPatient = preparedStatement.executeQuery();
			// if we can't find any patient with his keys we can add him
			if (!alreadyPatient.next()) {
			// if the flag is set to one it means that it's the public key and
			// if there isn't already a public key we can add it
			if (flag) {
			preparedStatement = connect
			.prepareStatement("INSERT INTO mu.crypto_keys (ID_p,Pub_key) VALUES (?,?)");
			System.out.println("A new key has been added (pub)");
			} else {
			preparedStatement = connect
			.prepareStatement("INSERT INTO mu.crypto_keys (ID_p,Priv_key_half) VALUES (?,?)");
			}
			preparedStatement.setInt(1, id_p);
			preparedStatement.setString(2, key);
			preparedStatement.executeUpdate();
			// It's means that someone is already in the DB but we don't know if
			// his Pub or Priv key is inside the DB
			} else if (alreadyPatient.getString("Priv_key_half") == null && !flag) {
			preparedStatement = connect
			.prepareStatement("UPDATE mu.crypto_keys SET Priv_key_half = ? WHERE ID_key = ?");
			preparedStatement.setString(1, key);
			preparedStatement.setInt(2, alreadyPatient.getInt("ID_key"));
			preparedStatement.executeUpdate();

			} else if (alreadyPatient.getString("Pub_key") == null && flag) {

			preparedStatement = connect
			.prepareStatement("UPDATE mu.crypto_keys SET Pub_key = ? WHERE ID_key = ?");
			preparedStatement.setString(1, key);
			preparedStatement.setInt(2, alreadyPatient.getInt("ID_key"));
			preparedStatement.executeUpdate();
			}

			closeConnection();
			}

			/**
			* @param lastName
			* @param name
			* @param birthDate
			* @param id
			*/
			static public void insertNewPatient(String lastName, String name,
			Date birthDate, int id) {
			openConnection();

			try {
			preparedStatement = connect
			.prepareStatement("INSERT INTO mu.patients (ID_p, Name, LastName, Birth_date) VALUES (?,?,?,?)");
			preparedStatement.setInt(1, id);
			preparedStatement.setString(2, name);
			preparedStatement.setString(3, lastName);
			preparedStatement.setDate(4, birthDate);
			preparedStatement.executeUpdate();

			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			}

			closeConnection();

			}

			/**
			* This method set up the connection to the PharmacoGenomics database
			*/
			static private void openConnection() {
			try {

			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager.getConnection(prop
			.readPropertiesXML("database.url.mu"));

			} catch (Exception e) {
			e.printStackTrace();
			}
			}

			/**
			* This method close the connection to any database
			*/
			static private void closeConnection() {
			try {
			if (resultSet != null) {
			resultSet.close();
			}
			if (connect != null) {
			connect.close();
			}
			} catch (Exception e) {

			}
			}


			static public void putResult(int id_p, int id_t, double result) {
			openConnection();
			System.out.println("putting result");
			try {
			preparedStatement = connect
			.prepareStatement("INSERT INTO mu.tests (ID_dis, ID_p,result) VALUES (?,?,?)");
			preparedStatement.setInt(1, id_t);
			preparedStatement.setInt(2, id_p);
			preparedStatement.setDouble(3, result);
			preparedStatement.executeUpdate();
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			}
			closeConnection();
			}

			/**
			* Get the stats
			*
			* @param id_d
			* @return the statistics
			* @throws SQLException
			*/
			static public ArrayList[] getStats(int id_d) throws SQLException {
			openConnection();
			ArrayList<Double> results = new ArrayList<Double>();
			ArrayList<Integer> nbPatient = new ArrayList<Integer>();
			preparedStatement = connect
			.prepareStatement("SELECT result FROM mu.tests WHERE ID_dis = ? ");
			preparedStatement.setInt(1, id_d);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			results.add(resultSet.getDouble("result"));
			}

			for (int i = 0; i < results.size(); i++) {

			preparedStatement = connect
			.prepareStatement("SELECT COUNT(*) as total FROM mu.tests WHERE (ID_dis,result) = (?,?)");
			preparedStatement.setInt(1, id_d);
			preparedStatement.setDouble(2, results.get(i));
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			nbPatient.add(resultSet.getInt("total"));
			}

			}
			closeConnection();
			ArrayList[] a = new ArrayList[2];
			a[0] = results;
			a[1] = nbPatient;
			return a;
			}

			/**
			* Get the corresponding quartiles
			*
			* @param id_d
			* @return
			*/
			public static double[] getQuartiles(int id_d) {
			// TODO Auto-generated method stub
			openConnection();

			double second = 0;
			double median = 0;
			double third = 0;

			try {
			preparedStatement = connect
			.prepareStatement("SELECT 2nd,median,3rd FROM mu.genetic_score_dist WHERE ID_dis = ?");

			preparedStatement.setInt(1, id_d);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
			second = resultSet.getDouble("2nd");
			median = resultSet.getDouble("median");
			third = resultSet.getDouble("3rd");
			}
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			}

			closeConnection();
			double[] result = { second, median, third };
			return result;

			}
			}

genopri-shcs/project/GenomicPrivacy(android)/GenomicPrivacy/src/FrameWork/DBconnector_mu.java89c2e8c655e6master

DBconnector_mu.javaView Options

genopri-shcs/project/GenomicPrivacy(android)/GenomicPrivacy/src/FrameWork/DBconnector_mu.java
89c2e8c655e6master

DBconnector_mu.java
View Options