|
From: <all...@su...> - 2006-02-17 01:45:39
|
Update of /cvsroot/libnelson/java In directory sumo.genetics.ucla.edu:/tmp/cvs-serv6451 Modified Files: gecCel.java Log Message: now exports annotations as well Index: gecCel.java =================================================================== RCS file: /cvsroot/libnelson/java/gecCel.java,v retrieving revision 1.1 retrieving revision 1.2 diff -C2 -d -r1.1 -r1.2 *** gecCel.java 31 Jan 2006 00:50:02 -0000 1.1 --- gecCel.java 17 Feb 2006 01:45:32 -0000 1.2 *************** *** 19,40 **** String SEL_EXP_RUN = "SELECT sample.sample_id, chip.chip_id, chip.file_id || '.file' file_id FROM gen_experiment_chips sample, gen_chip_files chip, gen_file_types type WHERE type.file_type_id = chip.file_type_id AND sample.chip_id = chip.chip_id AND type.file_extension = 'cel'"; Connection conn = null; ! PreparedStatement pstmt = null; ! ResultSet rs = null; ! try { conn = getConnection(); ! pstmt = conn.prepareStatement(SEL_EXP_RUN); ! rs = pstmt.executeQuery(); System.out.println("<files>"); ! while(rs.next()) { ! int chip_id = rs.getInt("CHIP_ID"); ! String file_id = rs.getString("FILE_ID"); ! int sample_id = rs.getInt("SAMPLE_ID"); ! System.out.println(" <file file_id=\"" + file_id + "\" chip_id=\"" + chip_id + "\" sample_id=\"" + sample_id + "\"/>"); } System.out.println("</files>"); ! cleanup(conn, pstmt, rs); } catch(Exception e){ --- 19,68 ---- String SEL_EXP_RUN = "SELECT sample.sample_id, chip.chip_id, chip.file_id || '.file' file_id FROM gen_experiment_chips sample, gen_chip_files chip, gen_file_types type WHERE type.file_type_id = chip.file_type_id AND sample.chip_id = chip.chip_id AND type.file_extension = 'cel'"; + // String SEL_ANNOT = "SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.cell_type_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND sample_id = ? UNION SELECT d.sample_id, 'null:' || c.name FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c WHERE d.dev_stage_level_id = c.cvterm_id AND sample_id = ? UNION SELECT d.sample_id, 'null:' || c.name FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c WHERE d.cell_growth_level_id = c.cvterm_id AND sample_id = ? UNION SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_ds d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.disease_state_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND d.sample_id = ? UNION SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_ed d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.experiment_design_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref! _id AND d.sample_id = ? UNION SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_pt d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.phenotype_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND d.sample_id = ? UNION SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.rna_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND sample_id = ?"; + Connection conn = null; ! PreparedStatement stmt1 = null; ! PreparedStatement stmt2 = null; ! ResultSet rs1 = null; ! ResultSet rs2 = null; ! try { conn = getConnection(); ! stmt1 = conn.prepareStatement(SEL_EXP_RUN); ! rs1 = stmt1.executeQuery(); System.out.println("<files>"); ! while(rs1.next()) { ! int chip_id = rs1.getInt("CHIP_ID"); ! String file_id = rs1.getString("FILE_ID"); ! int sample_id = rs1.getInt("SAMPLE_ID"); ! ! System.out.println(" <file file_id=\"" + file_id + "\" chip_id=\"" + chip_id + "\" sample_id=\"" + sample_id + "\">"); ! ! stmt2 = conn.prepareStatement( ! "SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.cell_type_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND sample_id = "+sample_id+" UNION "+ ! "SELECT d.sample_id, 'null:' || c.name FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c WHERE d.dev_stage_level_id = c.cvterm_id AND sample_id = "+sample_id+" UNION "+ ! "SELECT d.sample_id, 'null:' || c.name FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c WHERE d.cell_growth_level_id = c.cvterm_id AND sample_id = "+sample_id+" UNION "+ ! "SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_ds d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.disease_state_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND d.sample_id = "+sample_id+" UNION "+ ! "SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_ed d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.experiment_design_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND d.sample_id = "+sample_id+" UNION "+ ! "SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_pt d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.phenotype_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND d.sample_id = "+sample_id+" UNION "+ ! "SELECT d.sample_id, x.accession FROM gen_annotation_exp_sample_data d, gen_annotation_cvterm c, gen_annotation_dbxref x WHERE d.rna_level_id = c.cvterm_id AND c.dbxref_id = x.dbxref_id AND sample_id = "+sample_id ! ); ! ! // rs2 = stmt2.executeQuery( sample_id, sample_id, sample_id, sample_id, sample_id, sample_id, sample_id ); ! rs2 = stmt2.executeQuery(); ! ! while(rs2.next()) { ! String accession = rs2.getString("ACCESSION"); ! System.out.println(" <annotation accession=\""+ accession +"\"/>"); ! } + rs2.close(); + rs2 = null; + stmt2.close(); + + System.out.println(" </file>"); } System.out.println("</files>"); ! cleanup(conn, stmt1, rs1); } catch(Exception e){ *************** *** 53,65 **** } ! private static void cleanup(Connection conn, PreparedStatement pstmt, ResultSet rs) throws SQLException { ! if (rs != null) { ! rs.close(); ! rs = null; } ! if (pstmt != null) { ! pstmt.close(); ! pstmt = null; } if (conn != null) { conn.close(); --- 81,97 ---- } ! private static void cleanup(Connection conn, PreparedStatement stmt1, ResultSet rs1) throws SQLException { ! if (rs1 != null) { ! rs1.close(); ! rs1 = null; } ! if (stmt1 != null) { ! stmt1.close(); ! stmt1 = null; } + // if (stmt2 != null) { + // stmt2.close(); + // stmt2 = null; + // } if (conn != null) { conn.close(); |