· 9 years ago · Apr 09, 2017, 02:18 AM
1/*
2 * To change this template, choose Tools | Templates
3 * and open the template in the editor.
4 */
5import java.sql.Connection;
6import java.sql.DriverManager;
7import java.sql.ResultSet;
8import java.sql.ResultSetMetaData;
9import java.sql.SQLException;
10import java.sql.Statement;
11import java.util.ArrayList;
12import java.util.logging.Level;
13import java.util.logging.Logger;
14
15public class Main
16{
17 public static void main(String args[])
18 {
19 /* Database credentials */
20 String sql;
21 Statement stmt;
22 Connection con;
23 ResultSet rs;
24 ResultSetMetaData rsmd;
25
26 // list of genes to look for
27 ArrayList<String> genes = new ArrayList<String>();
28 genes.add("APC");
29 genes.add("TP53");
30 genes.add("KRAS");
31 genes.add("PIK3CA");
32 genes.add("PTEN");
33 genes.add("ATM");
34 genes.add("MUC4");
35 genes.add("SMAD4");
36 genes.add("SYNE1");
37 genes.add("FBXW7");
38
39 // server credentials
40 String user_s = "cse4701";
41 String pass_s = "datamine";
42 String host_s = "query.engr.uconn.edu";
43 String port_s = "1521";
44 String sid_s = "BIBCI";
45 String url_s = "jdbc:oracle:thin:@" + host_s + ":" + port_s + ":" + sid_s;
46
47 // local credentials
48 String user_l = "root";
49 String pass_l = "password";
50 String url_l = "jdbc:mysql://localhost:3306/project2_part1?useSSL=false";
51
52 Object[][] info = new Object[627][12]; // will hold the info that gets added to IG_READY
53
54 try
55 {
56 // fill in zeros for the genes until they are found
57 for(int i = 0; i < 627; i++)
58 for(int j = 1; j < 11; j++)
59 info[i][j] = 0;
60
61 // connect to oracle database
62 DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
63 con = DriverManager.getConnection(url_s, user_s, pass_s);
64 sql = "SELECT * FROM CLINICAL";
65 stmt = con.createStatement();
66 rs = stmt.executeQuery(sql);
67 // grab IDs from clinical and fill in living/deceased status
68 int row = 0;
69 while (rs.next())
70 {
71 info[row][0] = rs.getString(1);
72 if (rs.getString(6).equals("LIVING"))
73 info[row][11] = 1;
74 else
75 info[row][11] = 0;
76 row++;
77 }
78
79 // get info from mutation
80 sql = "SELECT * FROM MUTATION";
81 stmt = con.createStatement();
82 rs = stmt.executeQuery(sql);
83 rsmd = rs.getMetaData();
84 String lastID = null;
85 String thisID = null;
86 while (rs.next())
87 {
88 if (lastID == null)
89 {
90 lastID = rs.getString(2);
91 }
92 // avoid adding duplicate IDs to the info list by checking equivalence of ID from previous row
93 lastID = thisID;
94 System.out.println("current ID: " + thisID);
95 thisID = rs.getString(2);
96 thisID = thisID.substring(0, thisID.length()-3);
97 for (int i = 0; i < 627; i++)
98 {
99 // if the ID matches an ID from clinical, look for its genes
100 if (info[i][0].equals(thisID))
101 {
102 System.out.println("matched ID: " + info[i][0] + " " + thisID + " looking for genes");
103 String thisGene = rs.getString(3);
104 System.out.println("current gene: " + thisGene);
105 // if the gene matches, check whether it's silent and add it
106 for (int n = 0; n < 10; n++)
107 {
108 if ((thisGene.equals(genes.get(n))) && (!rs.getString(4).equals("Silent")))
109 {
110 System.out.println("matched gene: " + thisGene + " " + genes.get(n));
111 info[i][n+1] = 1;
112 }
113 }
114 }
115 }
116 }
117 // connect to local MySQL server db
118 Class.forName("com.mysql.jdbc.Driver");
119 con = DriverManager.getConnection(url_l, user_l, pass_l);
120 // create the table in MySQL Server
121 sql = "CREATE TABLE IF NOT EXISTS IG_READY(Patient_ID VARCHAR(20) NOT NULL, APC INT, TP53 INT, KRAS INT, PIK3CA INT, PTEN INT, ATM INT, MUC4 INT, SMAD4 INT, SYNE1 INT, FBXW7 INT, Status INT, PRIMARY KEY (Patient_ID))";
122 stmt = con.createStatement();
123 stmt.executeUpdate(sql);
124 // iteratively construct and execute queries based on gathered info
125 // include counts of each gene
126 int[] counts = new int[11];
127 for(int i = 0; i < 627; i++)
128 {
129 String id = (String) info[i][0];
130 int g1 = (int) info[i][1];
131 if(g1 == 1)
132 counts[0]++;
133 int g2 = (int) info[i][2];
134 if(g2 == 1)
135 counts[1]++;
136 int g3 = (int) info[i][3];
137 if(g3 == 1)
138 counts[2]++;
139 int g4 = (int) info[i][4];
140 if(g4 == 1)
141 counts[3]++;
142 int g5 = (int) info[i][5];
143 if(g5 == 1)
144 counts[4]++;
145 int g6 = (int) info[i][6];
146 if(g6 == 1)
147 counts[5]++;
148 int g7 = (int) info[i][7];
149 if(g7 == 1)
150 counts[6]++;
151 int g8 = (int) info[i][8];
152 if(g8 == 1)
153 counts[7]++;
154 int g9 = (int) info[i][9];
155 if(g9 == 1)
156 counts[8]++;
157 int g10 = (int) info[i][10];
158 if(g10 == 1)
159 counts[9]++;
160 int status = (int) info[i][11];
161 if(status == 1)
162 counts[10]++;
163 sql = "INSERT INTO IG_READY(Patient_ID, APC, TP53, KRAS, PIK3CA, PTEN, ATM, MUC4, SMAD4, SYNE1, FBXW7, Status) VALUES ('" + id + "', " + g1 + ", " + g2 + ", " + g3 + ", " + g4 + ", " + g5 + ", " + g6 + ", " + g7 + ", " + g8 + ", " + g9 + ", " + g10 + ", " + status + ")";
164 System.out.println(sql);
165 stmt = con.createStatement();
166 stmt.executeUpdate(sql);
167 }
168 // display final report of counts
169 System.out.println("APC: " + counts[0]);
170 System.out.println("TP53: " + counts[1]);
171 System.out.println("KRAS: " + counts[2]);
172 System.out.println("PIK3CA: " + counts[3]);
173 System.out.println("PTEN: " + counts[4]);
174 System.out.println("ATM: " + counts[5]);
175 System.out.println("MUC4: " + counts[6]);
176 System.out.println("SMAD4: " + counts[7]);
177 System.out.println("SYNE1: " + counts[8]);
178 System.out.println("FBXW7: " + counts[9]);
179 System.out.println("Status: " + counts[10]);
180 }
181 catch (SQLException | ClassNotFoundException ex)
182 {
183 Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
184 }
185 }
186}