Homec4science
Diffusion Genome Privacy - SHCS App (ms_access_migration) Browse

genopri-shcs/project/PPPCertifiedInstitution/src/utils/DBconnector_client.java
750375b50742ms_access_migration

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 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("SELECT ID_p FROM " + database + ".cryptokeys WHERE ID_p = ?");
preparedStatement.setInt(1, ID_p);
resultSet = preparedStatement.executeQuery();
if(!resultSet.next()){
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[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 int getCF(String name) throws SQLException{
int result = -1;
// openConnection();
preparedStatement = connect.prepareStatement("SELECT ID_cf FROM " + database + ".clinical_factors WHERE description = ?");
preparedStatement.setString(1, name);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
result = resultSet.getInt(1);
}
// closeConnection();
return result;
}
public ArrayList<String> getCFnames(String name) throws SQLException{
ArrayList<String> result = new ArrayList<>();
openConnection();
preparedStatement = connect.prepareStatement("SELECT cf.description FROM " + database + ".clinical_factors as cf, " + database + ".cf_t as cft, " +
database + ".tests as t WHERE cft.ID_t = t.ID_t AND cft.ID_cf = cf.ID_cf AND t.name = ?");
preparedStatement.setString(1, name);
resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
result.add(resultSet.getString(1));
}
closeConnection();
return result;
}
/**
* This method set up the connection to the PharmacoGenomics database
*/
public 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
*/
public void closeConnection() {
try {
if (resultSet != null) {
resultSet.close();
}
if (connect != null) {
connect.close();
}
} catch (Exception e) {
}
}
}
c4science · Help