Java tutorial
import org.apache.commons.lang3.builder.HashCodeBuilder; import java.io.File; import java.io.IOException; import java.io.PrintWriter; import java.sql.*; import java.util.*; import java.util.stream.Collectors; /** Copyright (C) 2016 Thomas Gregory This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>. * * Created by Thomas on 3/1/2016. * Manages HSQL Database */ @SuppressWarnings("Duplicates") public class HSqlManager { static final String JDBC_DRIVER_HSQL = "org.hsqldb.jdbc.JDBCDriver"; static final String DB_SERVER_URL = "jdbc:hsqldb:hsql://localhost/primerdb;ifexists=true"; static final String DB_URL_HSQL_C = "jdbc:hsqldb:file:database/primerdb"; private static ImportPhagelist INSTANCE; private static HSqlManager instance; public final Connection conn; public static List<String[]> newPhages; private static final String USER = "SA"; private static final String PASS = ""; private static boolean written = false; //Initial Connection Constructor for Database initial build private HSqlManager() throws IOException, ClassNotFoundException, SQLException, IllegalAccessException, InstantiationException { INSTANCE = ImportPhagelist.getInstance(); Class.forName(JDBC_DRIVER_HSQL).newInstance(); conn = DriverManager.getConnection(DB_SERVER_URL, USER, PASS); System.out.println("PrimerDB connected"); } public HSqlManager(String url) throws IOException, ClassNotFoundException, SQLException, IllegalAccessException, InstantiationException { INSTANCE = ImportPhagelist.getInstance(); Class.forName(JDBC_DRIVER_HSQL).newInstance(); conn = DriverManager.getConnection(url, USER, PASS); System.out.println("PrimerDB connected"); } //Returns the Instance of the manager public static HSqlManager getInstance() throws IOException, SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException { if (instance == null) { instance = new HSqlManager(); } return instance; } //Main for Initial DB Build public static void main(String[] args) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, IOException { HSqlManager db = new HSqlManager(DB_SERVER_URL); db.dbBuild(); } //runs Initial build and common clustering public void dbBuild() throws SQLException, ClassNotFoundException, IOException, InstantiationException, IllegalAccessException { dbInit(); primerDBsetup(); } public static void runNewBP(Connection connection, int bps) throws IOException, SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException { commonInitialize(bps, connection); uniqueDB(connection, bps); // HSqlPrimerDesign.primerPicks(connection,bps); // connection.createStatement().execute("SHUTDOWN"); } public static void runNewBPCommon(Connection connection, int bps) throws IOException, SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException { commonInitialize(bps, connection); } public static void runNewBPUnique(Connection connection, int bps) throws IOException, SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException { written = true; uniqueDB(connection, bps); } //Sets up DB and tables private void dbInit() throws SQLException { Statement init = conn.createStatement(); String start2 = "CREATE SCHEMA Primerdb AUTHORIZATION DBA" + "\tCREATE CACHED TABLE Primers(\n" + "\tid INTEGER GENERATED BY DEFAULT AS IDENTITY,\n" + "\tBp INTEGER NOT NULL,\n" + "\tSequence VARCHAR(45) NOT NULL,\n" + "\tStrain VARCHAR(45) NOT NULL,\n" + "\tCluster VARCHAR(45) NOT NULL,\n" + "\tTm FLOAT NULL,\n" + "\tGC FLOAT NULL,\n" + "\tCommonP BOOLEAN NULL,\n" + "\tUniqueP BOOLEAN NULL,\n" + "\tPicked BOOLEAN NULL,\n" + "\tHairpin BOOLEAN NULL,\n" + "\tPRIMARY KEY (id))\n" + "\n" + "\tCREATE CACHED TABLE MatchedPrimers(\n" + "\tid INTEGER GENERATED BY DEFAULT AS IDENTITY,\n" + "\tPrimer VARCHAR(45) NOT NULL,\n" + "\tPrimerMatch VARCHAR(45) NOT NULL,\n" + "\tComp FLOAT NULL,\n" + "\tFragAVG FLOAT NULL,\n" + "\tFragVAR FLOAT NULL,\n" + "\tH2SD FLOAT NULL,\n" + "\tL2SD FLOAT NULL,\n" + "\tStrain VARCHAR(45) NOT NULL,\n" + "\tCluster VARCHAR(45) NOT NULL,\n" + "\tPRIMARY KEY (id))\n" + "\n" + "\tCREATE CACHED TABLE Phages(\n" + "\tName VARCHAR(45) NOT NULL,\n" + "\tCluster VARCHAR(45) NOT NULL,\n" + "\tStrain VARCHAR(45) NOT NULL,\n" + "\tPRIMARY KEY (Name))\n"; init.executeUpdate(start2); init.executeUpdate("SET FILES NIO SIZE 8192 "); init.executeUpdate("CREATE INDEX a on primerdb.primers(Cluster)"); init.executeUpdate("CREATE INDEX b on primerdb.primers(Strain)"); init.executeUpdate("CREATE INDEX c on primerdb.primers(Sequence)"); init.executeUpdate("CREATE INDEX d on primerdb.matchedprimers(Cluster)"); init.executeUpdate("CREATE INDEX e on primerdb.matchedprimers(Strain)"); init.executeUpdate("CREATE INDEX f on primerdb.matchedprimers(Primer)"); init.executeUpdate("CREATE INDEX g on primerdb.matchedprimers(PrimerMatch)"); init.executeUpdate("CREATE INDEX h on primerdb.matchedprimers(Comp)"); init.executeUpdate("CREATE INDEX i on primerdb.matchedprimers(FragAVG)"); init.executeUpdate("CREATE INDEX j on primerdb.matchedprimers(FragVar)"); init.executeUpdate("CREATE INDEX k on primerdb.matchedprimers(H2SD)"); init.executeUpdate("CREATE INDEX l on primerdb.matchedprimers(L2SD)"); init.executeUpdate("CREATE INDEX m on primerdb.primers(Bp)"); init.executeUpdate("CREATE INDEX n on primerdb.primers(Tm)"); init.executeUpdate("CREATE INDEX o on primerdb.primers(Gc)"); init.executeUpdate("CREATE INDEX p on primerdb.primers(UniqueP)"); init.executeUpdate("CREATE INDEX q on primerdb.primers(Hairpin)"); init.executeUpdate("CREATE INDEX r on primerdb.phages(Cluster)"); init.executeUpdate("CREATE INDEX s on primerdb.phages(Strain)"); init.close(); System.out.println("Database Initialized"); } //Inserts current strains, clusters and phages private void primerDBsetup() throws SQLException, IOException { System.out.println("Building DB"); PreparedStatement insertPhage = conn .prepareStatement("INSERT INTO Primerdb.Phages" + "(Name, Cluster, Strain) values(?,?,?);"); Statement queryClusters = conn.createStatement(); INSTANCE = ImportPhagelist.getInstance(); INSTANCE.strains.forEach(x -> { List<String[]> phageslist; try { phageslist = INSTANCE.readFile(INSTANCE.path, x); } catch (IOException e) { e.printStackTrace(); phageslist = null; } try { insertPhage.setString(3, x); } catch (SQLException e) { e.printStackTrace(); } phageslist.stream().map(z -> z[0]).collect(Collectors.toSet()).forEach(y -> { try { insertPhage.setString(2, y); List<String[]> phages; try { phages = INSTANCE.readFile(INSTANCE.path, x).stream().filter(t -> t[0].equals(y)) .collect(Collectors.toList()); } catch (IOException e) { e.printStackTrace(); phages = null; } for (String[] phage : phages) { try { insertPhage.setString(1, phage[1]); insertPhage.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } } catch (SQLException e) { e.printStackTrace(); } }); }); insertPhage.close(); System.out.println("DB Built"); } //Inserts primers common to a clusters for all clusters private static void commonInitialize(int bps, Connection connection) throws SQLException, IOException { String base = new File("").getAbsolutePath(); CSV.makeDirectory(new File(base + "/PhageData")); INSTANCE = ImportPhagelist.getInstance(); INSTANCE.parseAllPhages(bps); written = true; Connection db = connection; db.setAutoCommit(false); Statement stat = db.createStatement(); stat.execute("SET FILES LOG FALSE\n"); PreparedStatement st = db.prepareStatement("Insert INTO Primerdb.Primers" + "(Bp,Sequence, CommonP, UniqueP, Picked, Strain, Cluster)" + " Values(?,?,true,false,false,?,?)"); ResultSet call = stat.executeQuery("Select * From Primerdb.Phages;"); List<String[]> phages = new ArrayList<>(); while (call.next()) { String[] r = new String[3]; r[0] = call.getString("Strain"); r[1] = call.getString("Cluster"); r[2] = call.getString("Name"); phages.add(r); } phages.parallelStream().map(x -> x[0]).collect(Collectors.toSet()).parallelStream().forEach(x -> { phages.stream().filter(y -> y[0].equals(x)).map(y -> y[1]).collect(Collectors.toSet()).forEach(z -> { try { List<String> clustphages = phages.stream().filter(a -> a[0].equals(x) && a[1].equals(z)) .map(a -> a[2]).collect(Collectors.toList()); Set<String> primers = Collections.synchronizedSet(CSV .readCSV(base + "/PhageData/" + Integer.toString(bps) + clustphages.get(0) + ".csv")); clustphages.remove(0); clustphages.parallelStream().forEach(phage -> { primers.retainAll( CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + phage + ".csv")); }); int i = 0; for (CharSequence a : primers) { try { //finish update st.setInt(1, bps); st.setString(2, a.toString()); st.setString(3, x); st.setString(4, z); st.addBatch(); } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + z); } i++; if (i == 1000) { i = 0; st.executeBatch(); db.commit(); } } if (i > 0) { st.executeBatch(); db.commit(); } } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + z); } }); }); stat.execute("SET FILES LOG TRUE\n"); st.close(); stat.close(); System.out.println("Common Updated"); } //Runs a check for new strains, clusters, and phages, common and unique clustering public static void runChecks(Connection connection) throws SQLException, IOException, IllegalAccessException, InstantiationException, ClassNotFoundException { INSTANCE = ImportPhagelist.getInstance(); checkPhage(connection); Statement s = connection.createStatement(); ResultSet rs = s.executeQuery("Select Distinct Bp FROM primerdb.primers"); Set<Integer> bpset = new HashSet<>(); while (rs.next()) { bpset.add(rs.getInt("Bp")); } for (int bp : bpset) { commonClusterNewPhages(connection, bp); if (newPhages.size() != 0) uniqueDB(connection, bp); } } //Checks for new phages private static void checkPhage(Connection connection) throws SQLException, IOException { List<String[]> all = INSTANCE.readFileAllStrains(INSTANCE.path); List<String> clusters = all.stream().map(x -> x[0]).collect(Collectors.toList()); Set<String> phages = all.stream().map(x -> x[1]).collect(Collectors.toSet()); List<String> strains = all.stream().map(x -> x[2]).collect(Collectors.toList()); List<String> phageslist = all.stream().map(x -> x[1]).collect(Collectors.toList()); Set<String> dbphages = new HashSet<>(); Statement st = connection.createStatement(); PreparedStatement insertPhages = connection .prepareStatement("INSERT INTO Primerdb.Phages(Name, Cluster, Strain)" + " values(?,?,?);"); String sql = "SELECT * FROM Primerdb.Phages;"; ResultSet rs = st.executeQuery(sql); while (rs.next()) { dbphages.add(rs.getString("Name")); } phages.removeAll(dbphages); List<String[]> phageinfo = new ArrayList<>(); if (phages.size() > 0) { System.out.println("Phages Added:"); phages.forEach(x -> { String[] ar = new String[3]; System.out.println(x); String cluster = clusters.get(phageslist.indexOf(x)); String strain = strains.get(phageslist.indexOf(x)); try { insertPhages.setString(1, x); insertPhages.setString(2, cluster); insertPhages.setString(3, strain); insertPhages.addBatch(); } catch (SQLException e) { e.printStackTrace(); } try { insertPhages.executeBatch(); } catch (SQLException e) { e.printStackTrace(); } ar[0] = x; ar[1] = cluster; ar[2] = strain; phageinfo.add(ar); }); newPhages = phageinfo; } else { System.out.println("No Phages added"); } st.close(); insertPhages.close(); } //common clusters new phages public static void commonClusterNewPhages(Connection connection, int bps) throws SQLException, IOException, ClassNotFoundException, IllegalAccessException, InstantiationException { Connection db = connection; String base = new File("").getAbsolutePath(); db.setAutoCommit(false); PreparedStatement st = db.prepareStatement("UPDATE Primerdb.Primers SET CommonP = False," + " UniqueP = False" + " WHERE Cluster = ? and " + "Strain = ? and Sequence = ? and Bp =?"); Statement stat = db.createStatement(); if (newPhages != null) { List<String[]> phages = newPhages; phages.forEach(x -> { try { CSV.writeDataCSV(x[0], Fasta.process(x[0], bps), bps); CSV.writeDataCSV(x[0], Fasta.processPrimers(x[0], bps), bps); } catch (IOException e) { e.printStackTrace(); } Set<CharSequence> primers = new HashSet<>(); try { ResultSet rs = stat.executeQuery( "SELECT * FROM Primerdb.Primers WHERE" + " Sequence = '" + x[1] + "' and Clusters = '" + x[2] + "' and CommonP = True" + "and Bp =" + Integer.valueOf(bps)); while (rs.next()) { primers.add((CharSequence) rs.getString("Sequence")); } primers.removeAll(CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + x[0] + ".csv")); if (primers.size() != 0) { primers.forEach(y -> { try { //finish update st.setString(1, x[1]); st.setString(2, x[2]); st.setString(3, y.toString()); st.setInt(4, bps); st.addBatch(); } catch (SQLException e) { e.printStackTrace(); } }); st.executeBatch(); db.commit(); } } catch (SQLException e) { e.printStackTrace(); } }); } System.out.println("Common Updated"); st.close(); } //unique clusters all phages public static void uniqueDB(Connection connection, int bps) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, IOException { DpalLoad.main(new String[1]); HSqlPrimerDesign.Dpal_Inst = DpalLoad.INSTANCE_WIN64; String base = new File("").getAbsolutePath(); if (!written) { CSV.makeDirectory(new File(base + "/PhageData")); INSTANCE.readFileAll(INSTANCE.path).stream().forEach(x -> { try { CSV.writeDataCSV(x[1], Fasta.process(x[1], bps), bps); } catch (IOException e) { e.printStackTrace(); } }); } Connection db = connection; db.setAutoCommit(false); Statement stat = db.createStatement(); PrintWriter log = new PrintWriter(new File("javalog.log")); stat.execute("SET FILES LOG FALSE;\n"); PreparedStatement st = db .prepareStatement("UPDATE Primerdb.Primers" + " SET UniqueP = true, Tm = ?, GC =?, Hairpin =?" + "WHERE Cluster = ? and Strain = ? and " + "Sequence = ? and Bp = ?"); ResultSet call = stat.executeQuery("Select * From Primerdb.Phages;"); List<String[]> phages = new ArrayList<>(); while (call.next()) { String[] r = new String[3]; r[0] = call.getString("Strain"); r[1] = call.getString("Cluster"); r[2] = call.getString("Name"); phages.add(r); } phages.stream().map(x -> x[0]).collect(Collectors.toSet()).stream().forEach(x -> { phages.stream().filter(y -> y[0].equals(x)).map(y -> y[1]).collect(Collectors.toSet()).parallelStream() .forEach(z -> { try { Set<String> nonclustphages = phages.stream() .filter(a -> a[0].equals(x) && !a[1].equals(z)).map(a -> a[2]) .collect(Collectors.toSet()); ResultSet resultSet = stat.executeQuery("Select Sequence from primerdb.primers" + " where Strain ='" + x + "' and Cluster ='" + z + "' and CommonP = true" + " and Bp = " + Integer.valueOf(bps) + " "); Set<CharSequence> primers = Collections.synchronizedSet(new HashSet<>()); while (resultSet.next()) { primers.add(resultSet.getString("Sequence")); } for (String phage : nonclustphages) { CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + phage + ".csv") .parallelStream().filter(primer -> primers.contains(primer)) .forEach(primers::remove); } int i = 0; for (CharSequence a : primers) { try { st.setDouble(1, HSqlPrimerDesign.primerTm(a, 0, 800, 1.5, 0.2)); st.setDouble(2, HSqlPrimerDesign.gcContent(a)); st.setBoolean(3, HSqlPrimerDesign.calcHairpin((String) a, 4)); st.setString(4, z); st.setString(5, x); st.setString(6, a.toString()); st.setInt(7, bps); st.addBatch(); } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + z); } i++; if (i == 1000) { i = 0; st.executeBatch(); db.commit(); } } if (i > 0) { st.executeBatch(); db.commit(); } } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + z); } log.println(z); log.flush(); System.gc(); }); }); stat.execute("SET FILES LOG TRUE\n"); st.close(); stat.close(); System.out.println("Unique Updated"); } public static void getClusterSizes(Connection connection) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, IOException { Connection db = connection; db.setAutoCommit(false); Statement stat = db.createStatement(); PrintWriter unique = new PrintWriter(new File("clustercount.log")); ResultSet call = stat.executeQuery("Select DISTINCT Cluster From Primerdb.Primers;"); Set<String> clust = new HashSet<>(); while (call.next()) { clust.add(call.getString("Cluster")); } unique.print("Cluster\t"); unique.print("Phage Count\t"); unique.print("18bp Unique Count\t"); unique.print("22bp Unique Count\t"); unique.print("25bp Unique Count\n"); unique.flush(); for (String c : clust) { call = stat.executeQuery("Select * From Primerdb.Primers where Cluster = '" + c + "'" + "and Bp = " + Integer.toString(18) + "and CommonP = true and UniqueP = true;"); int count = 0; while (call.next()) { count++; } call = stat.executeQuery("Select * From Primerdb.Primers where Cluster = '" + c + "'" + "and Bp = " + Integer.toString(22) + "and CommonP = true and UniqueP = true;"); int count2 = 0; while (call.next()) { count2++; } call = stat.executeQuery("Select * From Primerdb.Primers where Cluster = '" + c + "'" + "and Bp = " + Integer.toString(25) + "and CommonP = true and UniqueP = true;"); int count3 = 0; while (call.next()) { count3++; } call = stat.executeQuery("Select * From Primerdb.Phages where Cluster = '" + c + "';"); int count4 = 0; while (call.next()) { count4++; } unique.print(c + "\t"); unique.print(count4 + "\t"); unique.print(count + "\t"); unique.print(count2 + "\t"); unique.print(count3 + "\n"); unique.flush(); } System.out.println("Meta recorded"); } @SuppressWarnings("Duplicates") @Deprecated private static void mycoCommonInitialize(int bps, Connection connection) throws SQLException, IOException { long time = System.currentTimeMillis(); String base = new File("").getAbsolutePath(); CSV.makeDirectory(new File(base + "/PhageData")); INSTANCE = ImportPhagelist.getInstance(); // INSTANCE.parseAllPhages(bps); written = true; Connection db = connection; db.setAutoCommit(false); Statement stat = db.createStatement(); stat.execute("SET FILES LOG FALSE\n"); PreparedStatement st = db.prepareStatement("Insert INTO Primerdb.Primers" + "(Bp,Sequence, CommonP, UniqueP, Picked, Strain, Cluster)" + " Values(?,?,true,false,false,?,?)"); ResultSet call = stat.executeQuery("Select * From Primerdb.Phages;"); List<String[]> phages = new ArrayList<>(); String strain = ""; while (call.next()) { String[] r = new String[3]; r[0] = call.getString("Strain"); r[1] = call.getString("Cluster"); r[2] = call.getString("Name"); phages.add(r); if (r[2].equals("xkcd")) { strain = r[0]; } } call.close(); String x = strain; Set<String> clust = phages.stream().filter(y -> y[0].equals(x)).map(y -> y[1]).collect(Collectors.toSet()); Map<String, List<String>> clusters = new HashMap<>(); clust.parallelStream().forEach(cluster -> clusters.put(cluster, phages.stream() .filter(a -> a[0].equals(x) && a[1].equals(cluster)).map(a -> a[2]).collect(Collectors.toList()))); for (String z : clusters.keySet()) { try { List<String> clustphages = clusters.get(z); Set<String> primers = Collections.synchronizedSet( CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + clustphages.get(0) + ".csv")); clustphages.remove(0); for (String phage : clustphages) { // String[] seqs = Fasta.parse(base + "/Fastas/" + phage + ".fasta"); // String sequence =seqs[0]+seqs[1]; // Map<String, List<Integer>> seqInd = new HashMap<>(); // for (int i = 0; i <= sequence.length()-bps; i++) { // String sub=sequence.substring(i,i+bps); // if(seqInd.containsKey(sub)){ // seqInd.get(sub).add(i); // }else { // List<Integer> list = new ArrayList<>(); // list.add(i); // seqInd.put(sub,list); // } // } // primers = primers.stream().filter(seqInd::containsKey).collect(Collectors.toSet()); // primers =Sets.intersection(primers,CSV.readCSV(base + "/PhageData/"+Integer.toString(bps) // + phage + ".csv")); // System.gc(); // String[] seqs = Fasta.parse(base + "/Fastas/" + phage + ".fasta"); // String sequence =seqs[0]+seqs[1]; // primers.stream().filter(sequence::contains); primers.retainAll(CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + phage + ".csv")); // Set<CharSequence> prim = primers; // for (CharSequence primer: primers){ // if(seqInd.containsKey(primer)){ // prim.remove(primer); // } // } // primers=prim; } int i = 0; for (String a : primers) { try { //finish update st.setInt(1, bps); st.setString(2, a); st.setString(3, x); st.setString(4, z); st.addBatch(); } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + z); } i++; if (i == 1000) { i = 0; st.executeBatch(); db.commit(); } } if (i > 0) { st.executeBatch(); db.commit(); } } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + z); } System.out.println(z); } stat.execute("SET FILES LOG TRUE\n"); st.close(); stat.close(); System.out.println("Common Updated"); System.out.println((System.currentTimeMillis() - time) / Math.pow(10, 3) / 60); } @SuppressWarnings("Duplicates") @Deprecated public static void mycoUniqueDB(Connection connection, int bps) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, IOException { long time = System.currentTimeMillis(); DpalLoad.main(new String[1]); HSqlPrimerDesign.Dpal_Inst = DpalLoad.INSTANCE_WIN64; String base = new File("").getAbsolutePath(); if (!written) { CSV.makeDirectory(new File(base + "/PhageData")); INSTANCE.parseAllPhages(bps); } Connection db = connection; db.setAutoCommit(false); Statement stat = db.createStatement(); PrintWriter log = new PrintWriter(new File("javalog.log")); stat.execute("SET FILES LOG FALSE;\n"); PreparedStatement st = db .prepareStatement("UPDATE Primerdb.Primers" + " SET UniqueP = true, Tm = ?, GC =?, Hairpin =?" + "WHERE Cluster = ? and Strain = ? and " + "Sequence = ? and Bp = ?"); ResultSet call = stat.executeQuery("Select * From Primerdb.Phages;"); List<String[]> phages = new ArrayList<>(); String strain = ""; while (call.next()) { String[] r = new String[3]; r[0] = call.getString("Strain"); r[1] = call.getString("Cluster"); r[2] = call.getString("Name"); phages.add(r); if (r[2].equals("xkcd")) { strain = r[0]; } } call.close(); String x = strain; Set<String> clust = phages.stream().filter(y -> y[0].equals(x)).map(y -> y[1]).collect(Collectors.toSet()); String[] clusters = clust.toArray(new String[clust.size()]); for (String z : clusters) { try { Set<String> nonclustphages = phages.stream().filter(a -> a[0].equals(x) && !a[1].equals(z)) .map(a -> a[2]).collect(Collectors.toSet()); ResultSet resultSet = stat.executeQuery( "Select Sequence from primerdb.primers" + " where Strain ='" + x + "' and Cluster ='" + z + "' and CommonP = true" + " and Bp = " + Integer.valueOf(bps) + " "); Set<CharSequence> primers = Collections.synchronizedSet(new HashSet<>()); while (resultSet.next()) { primers.add(resultSet.getString("Sequence")); } resultSet.close(); for (String phage : nonclustphages) { // String[] seqs = Fasta.parse(base + "/Fastas/" + phage + ".fasta"); // String sequence =seqs[0]+seqs[1]; // Map<String, List<Integer>> seqInd = new HashMap<>(); // for (int i = 0; i <= sequence.length()-bps; i++) { // String sub=sequence.substring(i,i+bps); // if(seqInd.containsKey(sub)){ // seqInd.get(sub).add(i); // }else { // List<Integer> list = new ArrayList<>(); // list.add(i); // seqInd.put(sub,list); // } // } // primers = primers.stream().filter(primer->!seqInd.containsKey(primer)).collect(Collectors.toSet()); // primers =Sets.difference(primers,CSV.readCSV(base + "/PhageData/"+Integer.toString(bps) // + phage + ".csv")); CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + phage + ".csv").stream() .filter(primers::contains).forEach(primers::remove); // System.gc(); } int i = 0; for (CharSequence a : primers) { try { st.setDouble(1, HSqlPrimerDesign.primerTm(a, 0, 800, 1.5, 0.2)); st.setDouble(2, HSqlPrimerDesign.gcContent(a)); st.setBoolean(3, HSqlPrimerDesign.calcHairpin((String) a, 4)); st.setString(4, z); st.setString(5, x); st.setString(6, a.toString()); st.setInt(7, bps); st.addBatch(); } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + z); } i++; if (i == 1000) { i = 0; st.executeBatch(); db.commit(); } } if (i > 0) { st.executeBatch(); db.commit(); } } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + z); } log.println(z); log.flush(); System.gc(); } stat.execute("SET FILES LOG TRUE\n"); st.close(); stat.close(); System.out.println("Unique Updated"); System.out.println((System.currentTimeMillis() - time) / Math.pow(10, 3) / 60); } public static void primerAnalysis(Connection connection, int bps) throws SQLException, IOException { long time = System.currentTimeMillis(); DpalLoad.main(new String[1]); HSqlPrimerDesign.Dpal_Inst = DpalLoad.INSTANCE_WIN64; String base = new File("").getAbsolutePath(); CSV.makeDirectory(new File(base + "/PhageData")); INSTANCE = ImportPhagelist.getInstance(); INSTANCE.parseAllPhages(bps); System.out.println((System.currentTimeMillis() - time) / Math.pow(10, 3) / 60); time = System.currentTimeMillis(); written = true; Connection db = connection; db.setAutoCommit(false); Statement stat = db.createStatement(); stat.execute("SET FILES LOG FALSE\n"); // PreparedStatement st = db.prepareStatement("Insert INTO Primerdb.Primers" + // "(Bp,Sequence, CommonP, UniqueP, Picked, Strain, Cluster)" + // " Values(?,?,true,false,false,?,?)"); PreparedStatement st = db.prepareStatement( "INSERT INTO Primerdb.Primers" + "(Bp,Sequence,Strain,Cluster,Tm,GC,UniqueP,CommonP,Hairpin) " + "VALUES(?,?,?,?,?,?,true,true,?)"); ResultSet call = stat.executeQuery("Select * From Primerdb.Phages;"); List<String[]> phages = new ArrayList<>(); while (call.next()) { String[] r = new String[3]; r[0] = call.getString("Strain"); r[1] = call.getString("Cluster"); r[2] = call.getString("Name"); phages.add(r); // if(strain.equals("-myco")) { // if (r[2].equals("xkcd")) { // strain = r[0]; // } // }else if(strain.equals("-arthro")){ // if (r[2].equals("ArV1")) { // strain = r[0]; // } // } } call.close(); Set<String> strains = phages.stream().map(y -> y[0]).collect(Collectors.toSet()); for (String x : strains) { Set<String> clust = phages.stream().filter(y -> y[0].equals(x)).map(y -> y[1]) .collect(Collectors.toSet()); Map<String, Integer> clustersNum = new HashMap<>(); Map<Integer, String> clustersName = new HashMap<>(); Map<Integer, List<String>> clusters = new HashMap<>(); Map<Bytes, Primer> primers = new HashMap<>(); int i = 0; for (String cluster : clust) { clustersName.put(i, cluster); clustersNum.put(cluster, i); i++; } clust.parallelStream() .forEach(cluster -> clusters.put(clustersNum.get(cluster), phages.stream().filter(a -> a[0].equals(x) && a[1].equals(cluster)).map(a -> a[2]) .collect(Collectors.toList()))); for (int z : clusters.keySet()) { // try { List<String> clustphages = clusters.get(z); for (String phage : clustphages) { Set<Bytes> phagprimers = //Read from CSV file here //Premade CSV files of all possible //primers in a phage genome CSV.readCSV(base + "/PhageData/" + Integer.toString(bps) + phage + ".csv").stream() .map(l -> new Bytes(l.getBytes())).collect(Collectors.toSet()); for (Bytes primer : phagprimers) { if (!primers.containsKey(primer)) { primers.put(primer, new Primer(z)); } else { Primer select = primers.get(primer); select.phageCount++; if (!select.containsCluster(z)) { select.addCluster(z); } } } } System.out.println(clustersName.get(z)); } int count = 0; Iterator<Map.Entry<Bytes, Primer>> primersSet = primers.entrySet().iterator(); while (primersSet.hasNext()) { Map.Entry<Bytes, Primer> primer = primersSet.next(); Primer primerInf = primer.getValue(); if (primerInf.clusters.length != 1) { primer.setValue(null); } else { int primerClust = -1; for (int cluster : primerInf.clusters) { primerClust = cluster; } if (primerInf.phageCount != clusters.get(primerClust).size()) { primer.setValue(null); } else { count++; } } } System.out.print("Unique Count: "); System.out.println(count); System.out.print("Primer Count: "); System.out.println(primers.size()); i = 0; for (Bytes a : primers.keySet()) { Primer primerInf = primers.get(a); if (primerInf != null) { String primerClust = ""; for (int cluster : primerInf.clusters) { primerClust = clustersName.get(cluster); } String str = new String(a.bytes); try { st.setInt(1, bps); st.setString(2, str); st.setString(3, x); st.setString(4, primerClust); // st.setDouble(5, HSqlPrimerDesign.primerTm(str, 0, 800, 1.5, 0.2)); st.setDouble(5, HSqlPrimerDesign.easytm(str)); st.setDouble(6, HSqlPrimerDesign.gcContent(str)); st.setBoolean(7, HSqlPrimerDesign.calcHairpin(str, 4)); st.addBatch(); } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + primerClust); } i++; if (i == 1000) { i = 0; st.executeBatch(); db.commit(); } } } if (i > 0) { st.executeBatch(); db.commit(); } // } System.out.println("Unique Updated"); System.out.println((System.currentTimeMillis() - time) / Math.pow(10, 3) / 60); } stat.execute("SET FILES LOG TRUE;"); st.close(); stat.close(); } @Deprecated public static void runNewMycoBP(Connection connection, int bps) throws IOException, SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException { // mycoCommonInitialize(bps,connection); // mycoUniqueDB(connection,bps); primerAnalysis(connection, bps); // HSqlPrimerDesign.primerPicks(connection,bps); // connection.createStatement().execute("SHUTDOWN"); } @Deprecated public static void runNewArthroBP(Connection connection, int bps) throws IOException, SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException { // mycoCommonInitialize(bps,connection); // mycoUniqueDB(connection,bps); primerAnalysis(connection, bps); // HSqlPrimerDesign.primerPicks(connection,bps); // connection.createStatement().execute("SHUTDOWN"); } public static void clearDatabase(Connection connection) throws SQLException { Connection db = connection; Statement stmt = db.createStatement(); stmt.execute("TRUNCATE SCHEMA Primerdb RESTART IDENTITY AND COMMIT NO CHECK"); } //Key object for unique sequence map private static class Bytes { byte[] bytes; Bytes(byte[] b) { bytes = b; } @Override public int hashCode() { return new HashCodeBuilder(17, 31). // two randomly chosen prime numbers // if deriving: appendSuper(super.hashCode()). append(bytes).toHashCode(); } @Override public boolean equals(Object obj) { if (!(obj instanceof Bytes)) return false; if (obj == this) return true; Bytes rhs = (Bytes) obj; // return new EqualsBuilder(). // // if deriving: appendSuper(super.equals(obj)). // append(bytes, rhs.bytes). // isEquals(); return Arrays.equals(bytes, rhs.bytes); } } //entry object that stores the data of the key in the unique sequence map private static class Primer { int[] clusters; int phageCount; Primer(int cluster) { clusters = new int[1]; addCluster(cluster); phageCount = 1; } public boolean containsCluster(int cluster) { for (int i = 0; i < clusters.length; i++) { if (clusters[i] == cluster) { return true; } } return false; } public void addCluster(int cluster) { int[] temp = new int[phageCount + 1]; for (int i = 0; i < clusters.length; i++) { temp[i] = clusters[i]; } clusters = temp; clusters[phageCount++] = cluster; } } }