Homec4science
Diffusion Genome Privacy - SHCS App (ms_access_migration) Browse

genopri-shcs/project/PPPServer/src/utils/DBconnector_server.java
a849cce6c01ams_access_migration

Last Change
Raw File

DBconnector_server.java
View Options

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package utils;
import com.mysql.jdbc.Blob;
import java.math.BigInteger;
import java.sql.Connection;
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.Iterator;
import java.util.Map;
/**
*
* @author raisaro
*/
public class DBconnector_server {
private Connection connect = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;
private final String database = "pharmacogenomics_server_test";
public DBconnector_server() {
}
public String[] getAlleles(String rs_code) throws SQLException{
String[] result = new String[3];
openConnection();
preparedStatement = connect.prepareStatement("SELECT ID_m, risk_allele, ref_allele FROM " + database + ".markers WHERE rs = ?");
preparedStatement.setString(1, rs_code);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
result[0] = resultSet.getString("risk_allele");
result[1] = resultSet.getString("ref_allele");
Integer id_m = resultSet.getInt("ID_m");
result[2] = id_m.toString();
}
closeConnection();
return result;
}
public BigInteger[] getEncryptedMarker(int ID_p, int ID_m) throws SQLException{
BigInteger[] encMarker = new BigInteger[2];
openConnection();
preparedStatement = connect.prepareStatement("SELECT enc_genotype_1, enc_genotype_2, enc_genotype2_1, enc_genotype2_2 FROM " + database + ".m_p WHERE ID_p = ? AND ID_m = ?");
preparedStatement.setInt(1, ID_p);
preparedStatement.setInt(2, ID_m);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
encMarker[0] = new BigInteger(resultSet.getString("enc_genotype_1"));
encMarker[1] = new BigInteger(resultSet.getString("enc_genotype_2"));
//encMarker[2] = new BigInteger(resultSet.getBytes("enc_genotype2_1"));
//encMarker[3] = new BigInteger(resultSet.getBytes("enc_genotype2_2"));
}
closeConnection();
return encMarker;
}
public BigInteger[] getHLA(int ID_p) throws SQLException {
openConnection();
BigInteger[] hla = new BigInteger[6];
preparedStatement = connect.prepareStatement("SELECT * FROM " + database + ".hla_p WHERE ID_p = ?");
preparedStatement.setInt(1, ID_p);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
hla[0] = new BigInteger(resultSet.getString("enc_hla_A_1"));
hla[1] = new BigInteger(resultSet.getString("enc_hla_A_2"));
hla[2] = new BigInteger(resultSet.getString("enc_hla_B_1"));
hla[3] = new BigInteger(resultSet.getString("enc_hla_B_2"));
hla[4] = new BigInteger(resultSet.getString("enc_hla_C_1"));
hla[5] = new BigInteger(resultSet.getString("enc_hla_C_2"));
}
closeConnection();
return hla;
}
public BigInteger[] getPublicKey(int ID_p) throws SQLException{
openConnection();
BigInteger[] plk = new BigInteger[3];
preparedStatement = connect.prepareStatement("SELECT value FROM " + database + ".cryptokeys WHERE ID_p = ? AND isPublic = 1");
preparedStatement.setInt(1, ID_p);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
String[] pub_key = resultSet.getString("value").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;
}
public BigInteger getPrivateKey(int ID_p) throws SQLException{
openConnection();
BigInteger prk = null;
preparedStatement = connect.prepareStatement("SELECT value FROM " + database + ".cryptokeys WHERE ID_p = ? AND isPublic = 0");
preparedStatement.setInt(1, ID_p);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
prk = new BigInteger(resultSet.getString("value"));
}
closeConnection();
return prk;
}
public BigInteger[] getEncryptedCF(int id_p, int id_cf){
openConnection();
ResultSet setCF = null;
BigInteger[] response = new BigInteger[4];
try{
preparedStatement = connect.prepareStatement("SELECT * FROM " + database + ".cf_p WHERE ID_p = ? AND ID_cf = ?");
preparedStatement.setInt(1, id_p);
preparedStatement.setInt(2,id_cf);
setCF = preparedStatement.executeQuery();
if (setCF.next()) {
response[0] = new BigInteger(setCF.getString("enc_cf_1"));
response[1] = new BigInteger(setCF.getString("enc_cf_2"));
// response[2] = new BigInteger(setCF.getString("enc_cf2_1"));
// response[3] = new BigInteger(setCF.getString("enc_cf2_2"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
closeConnection();
return response;
}
public void insertEncryptedMarkers(int ID_p, String rs_code, BigInteger enc_gen_1, BigInteger enc_gen_2, BigInteger enc_gen2_1, BigInteger enc_gen2_2) throws SQLException{
openConnection();
preparedStatement = connect.prepareStatement("SELECT ID_m FROM " + database + ".markers WHERE rs = ?");
preparedStatement.setString(1, rs_code);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
int ID_m = resultSet.getInt("ID_m");
if(enc_gen2_1 == null){
preparedStatement = connect.prepareStatement("INSERT INTO " + database + ".m_p (ID_p, ID_m, enc_genotype_1, enc_genotype_2) VALUES (?,?,?,?)");
preparedStatement.setInt(1, ID_p);
preparedStatement.setInt(2, ID_m);
preparedStatement.setString(3, enc_gen_1.toString());
preparedStatement.setString(4, enc_gen_2.toString());
}else{
preparedStatement = connect.prepareStatement("INSERT INTO " + database + ".m_p VALUES (?,?,?,?,?,?)");
preparedStatement.setInt(1, ID_p);
preparedStatement.setInt(2, ID_m);
preparedStatement.setString(3, enc_gen_1.toString());
preparedStatement.setString(4, enc_gen_2.toString());
preparedStatement.setString(5, enc_gen2_1.toString());
preparedStatement.setString(6, enc_gen2_2.toString());
}
preparedStatement.executeUpdate();
}
closeConnection();
}
public void insertEncryptedHLA(int ID_p, BigInteger a1, BigInteger a2, BigInteger b1, BigInteger b2, BigInteger c1, BigInteger c2 ) throws SQLException{
openConnection();
preparedStatement = connect.prepareStatement("INSERT INTO " + database + ".hla_p VALUES (?,?,?,?,?,?,?)");
preparedStatement.setInt(1, ID_p);
preparedStatement.setString(2, a1.toString());
preparedStatement.setString(3, a2.toString());
preparedStatement.setString(4, b1.toString());
preparedStatement.setString(5, b2.toString());
preparedStatement.setString(6, c1.toString());
preparedStatement.setString(7, c2.toString());
preparedStatement.executeUpdate();
closeConnection();
}
public void insertKey(int ID_p, String key, boolean flag ) throws SQLException{
openConnection();
preparedStatement = connect.prepareStatement("INSERT INTO " + database + ".cryptokeys (ID_p,value,isPublic) VALUES (?,?,?)");
preparedStatement.setInt(1, ID_p);
preparedStatement.setString(2, key);
preparedStatement.setBoolean(3, flag);
preparedStatement.executeUpdate();
closeConnection();
}
public void insertNewCF(int id_p, ArrayList[] encCF) {
openConnection();
ArrayList<Integer> id_cfs = encCF[0];
ArrayList<BigInteger[]> encVal = encCF[1];
for (int i = 0; i < id_cfs.size(); i++) { // for each encrypted CF =>
// store in the DB
String encryp_values1_1 = encVal.get(i)[0].toString();
String encryp_values2_1 = encVal.get(i)[1].toString();
String encryp_values1_2 = encVal.get(i)[0].pow(2).toString();
String encryp_values2_2 = encVal.get(i)[1].pow(2).toString();
int id_cf = id_cfs.get(i);
try {
preparedStatement = connect
.prepareStatement("SELECT * FROM " + database + ".cf_p WHERE (ID_p,ID_cf) = (?,?)");
preparedStatement.setInt(1, id_p);
preparedStatement.setInt(2, id_cf);
;
if(preparedStatement.executeQuery().next()){
preparedStatement = connect
.prepareStatement("UPDATE " + database + ".cf_p SET enc_cf_1 = ? , enc_cf_2 = ?, enc_cf2_1=?, enc_cf2_2=? WHERE ID_p =? AND ID_cf = ? ");
preparedStatement.setString(1, encryp_values1_1);
preparedStatement.setString(2, encryp_values2_1);
preparedStatement.setString(3, encryp_values1_2);
preparedStatement.setString(4, encryp_values2_2);
preparedStatement.setInt(5, id_p);
preparedStatement.setInt(6,id_cf);
preparedStatement.executeUpdate();
}else{
preparedStatement = connect.prepareStatement("INSERT INTO " + database + ".cf_p (ID_p,ID_cf,enc_cf_1, enc_cf_2,enc_cf2_1, enc_cf2_2) VALUES (?,?,?,?,?,?)");
preparedStatement.setInt(1, id_p);
preparedStatement.setInt(2, id_cf);
preparedStatement.setString(3, encryp_values1_1);
preparedStatement.setString(4, encryp_values2_1);
preparedStatement.setString(5, encryp_values1_2);
preparedStatement.setString(6, encryp_values2_2);
preparedStatement.executeUpdate();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
closeConnection();
}
/**
* This method set up the connection to the PharmacoGenomics database
*/
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("jdbc:mysql://128.178.151.209/" + database + "?"
+ "user=root&password=admin");
} catch (SQLException e) {
System.out.println("Connection Failed");
} catch (ClassNotFoundException e) {
System.out.println("Connection Failed");
}
}
/**
* This method close the connection to any database
*/
private void closeConnection() {
try {
if (resultSet != null) {
resultSet.close();
}
if (connect != null) {
connect.close();
}
} catch (Exception e) {
}
}
}
c4science · Help