Homec4science
Diffusion Genome Privacy - SHCS App (downgrade_java6) Browse

genopri-shcs/project/PPPClient/src/utils/DBconnector_client.java
750375b50742downgrade_java6

Last Change
Raw File

DBconnector_client.java
View Options

package utils;
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;
public class DBconnector_client {
private Connection connect = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;
private final String database = "pharmacogenomics_mu_test";
public DBconnector_client() {
}
public ArrayList<Integer> getPatients() throws SQLException{
openConnection();
ArrayList<Integer> patients = new ArrayList<>();
preparedStatement = connect.prepareStatement("SELECT ID_p FROM " + database + ".patients");
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
patients.add(resultSet.getInt("ID_p"));
}
closeConnection();
return patients;
}
public ArrayList[] getTests() throws SQLException{
openConnection();
ArrayList<Integer> test_ids = new ArrayList<>();
ArrayList<String> test_names = new ArrayList<>();
ArrayList<String> test_groups = new ArrayList<>();
ArrayList[] results = new ArrayList[3];
preparedStatement = connect.prepareStatement("SELECT ID_t, name, grp FROM " + database + ".tests");
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
test_ids.add(resultSet.getInt("ID_t"));
test_names.add(resultSet.getString("name"));
test_groups.add(resultSet.getString("grp"));
}
results[0] = test_ids;
results[1] = test_names;
results[2] = test_groups;
closeConnection();
return results;
}
public int getTestID(String desc) throws SQLException{
openConnection();
int ID_t = 0;
preparedStatement = connect.prepareStatement("SELECT ID_t FROM " + database + ".tests where name = ?");
preparedStatement.setString(1, desc);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
ID_t = resultSet.getInt("ID_t");
}
closeConnection();
return ID_t;
}
public ArrayList<String> getTestGroup() throws SQLException{
openConnection();
ArrayList<String> groups = new ArrayList<String>();
preparedStatement = connect.prepareStatement("SELECT distinct(grp) as grp FROM " + database + ".tests");
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
groups.add(resultSet.getString("grp"));
}
closeConnection();
return groups;
}
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_m, weight FROM " + database + ".m_t WHERE ID_t = ?");
preparedStatement.setInt(1, ID_t);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
markers.add(resultSet.getInt("ID_m"));
weights.add(resultSet.getDouble("weight"));
}
closeConnection();
ArrayList[] result = {markers,weights};
return result;
}
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 void insertMarker(String rs_code, String risk_allele, String ref_allele, int position, double maf, String chr) throws SQLException{
openConnection();
preparedStatement = connect.prepareStatement("SELECT * FROM " + database + ".markers WHERE rs = ?");
preparedStatement.setString(1, rs_code);
resultSet = preparedStatement.executeQuery();
if(!resultSet.next()){
preparedStatement = connect.prepareStatement("INSERT INTO " + database + ".markers (rs,chr,position,risk_allele,ref_allele,maf) VALUES (?,?,?,?,?,?)");
preparedStatement.setString(1, rs_code);
preparedStatement.setString(2, chr);
preparedStatement.setInt(3, position);
preparedStatement.setString(4, risk_allele);
preparedStatement.setString(5, ref_allele);
preparedStatement.setDouble(6, maf);
preparedStatement.executeUpdate();
closeConnection();
}
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 insertPatient(int ID_p, String name, String last_name, String sex, Date birthdate) throws SQLException{
openConnection();
preparedStatement = connect.prepareStatement("SELECT * FROM " + database + ".patients WHERE ID_p = ?");
preparedStatement.setInt(1, ID_p);
resultSet = preparedStatement.executeQuery();
if(!resultSet.next()){
preparedStatement = connect.prepareStatement("INSERT INTO " + database + ".patients VALUES (?,?,?,?,?)");
preparedStatement.setInt(1, ID_p);
preparedStatement.setString(2, name);
preparedStatement.setString(3, last_name);
preparedStatement.setString(4, sex);
preparedStatement.setDate(5, birthdate);
preparedStatement.executeUpdate();
}
// closeConnection();
}
public String[] getAlleles(String rs_code) throws SQLException{
String[] result = new String[2];
// openConnection();
preparedStatement = connect.prepareStatement("SELECT 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");
}
return result;
}
/**
* 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 (Exception 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) {
}
}
public double[] getQuartiles(int id_t) {
openConnection();
double second = 0;
double median = 0;
double third = 0;
try {
preparedStatement = connect
.prepareStatement("SELECT 2nd,median,3rd FROM " + database + ".quartile_dist WHERE ID_t = ?");
preparedStatement.setInt(1, id_t);
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;
}
public void insertResult(int id_p, int id_t, double result) {
openConnection();
System.out.println("putting result");
try {
preparedStatement = connect
.prepareStatement("INSERT INTO " + database + ".results (ID_t, ID_p, result, date) VALUES (?,?,?,?)");
preparedStatement.setInt(1, id_t);
preparedStatement.setInt(2, id_p);
preparedStatement.setDouble(3, result);
preparedStatement.setDate(4, new Date(System.currentTimeMillis()));
preparedStatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
closeConnection();
}
public ArrayList[] getCFs(int ID_t) {
openConnection();
ArrayList<Integer> id_cf = new ArrayList<Integer>();
ArrayList<Double> weights = new ArrayList<Double>();
try{
preparedStatement = connect
.prepareStatement("SELECT ID_cf, weight FROM " + database + ".cf_t WHERE ID_t = ?");
preparedStatement.setInt(1, ID_t);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
id_cf.add(resultSet.getInt("ID_cf"));
weights.add(resultSet.getDouble("weight"));
}
}catch(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
closeConnection();
ArrayList[] result = { id_cf, weights };
return result;
}
}
c4science · Help