Example usage for java.sql Statement execute

List of usage examples for java.sql Statement execute

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. execute

Introduction

In this page you can find the example usage for java.sql Statement execute.

Prototype

boolean execute(String sql) throws SQLException;

Source Link

Document

Executes the given SQL statement, which may return multiple results.

Usage

From source file:HSqlManager.java

@SuppressWarnings("Duplicates")
@Deprecated/* w w w . jav  a  2s.  c o  m*/
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);
}

From source file:me.redstarstar.rdfx.duty.dao.jdbc.ScheduleJdbcDao.java

@Override
public void deleteByWeek(int week) {
    jdbcTemplate.execute((Statement statement) -> {
        statement.execute("DELETE FROM schedule WHERE week = " + week);
        return null;
    });/*from w ww. jav a  2  s .c  o m*/
}

From source file:com.alibaba.wasp.jdbc.TestJdbcConnectionPool.java

@Test
public void testGetConnection() throws SQLException {
    Connection conn = pool.getConnection();
    ResultSet rs;//  w  w  w  .j a  va2  s . co m
    Statement stat = conn.createStatement();
    stat.execute("INSERT INTO test (column1,column2,column3) VALUES (1, 79999, 'testGetConnection')");

    rs = stat.executeQuery("SELECT column1,column2 FROM test where column3='testGetConnection'");

    assertTrue(rs.next());
    assertTrue(rs.getLong("column2") == 79999);
    conn.close();
}

From source file:HSqlPrimerDesign.java

@SuppressWarnings("Duplicates")
public static void locations(Connection connection) throws ClassNotFoundException, SQLException,
        InstantiationException, IllegalAccessException, IOException {
    long time = System.nanoTime();
    String base = new File("").getAbsolutePath();
    DpalLoad.main(new String[0]);
    Dpal_Inst = DpalLoad.INSTANCE_WIN64;
    System.out.println(Dpal_Inst);
    Connection db = connection;//ww w. java2  s . co  m
    db.setAutoCommit(false);
    Statement stat = db.createStatement();
    PrintWriter log = new PrintWriter(new File("javalog.log"));
    stat.execute("SET FILES LOG FALSE;");
    PreparedStatement st = db.prepareStatement("INSERT INTO Primerdb.MatchedPrimers("
            + "Primer, PrimerMatch, Comp,FragAVG,FragVAR,H2SD,L2SD, Cluster, Strain)"
            + "Values(?,?,?,?,?,?,?,?,?)");
    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());
        String[] clusters = clust.toArray(new String[clust.size()]);
        //        String z ="A1";
        for (String z : clusters) {
            System.out.println("Starting:" + z);
            List<Primer> primers = new ArrayList<>();
            Set<Matches> matched = new HashSet<>();
            Set<String> clustphage = phages.stream().filter(a -> a[0].equals(x) && a[1].equals(z))
                    .map(a -> a[2]).collect(Collectors.toSet());
            String[] clustphages = clustphage.toArray(new String[clustphage.size()]);
            if (clustphages.length > 1) {
                try {
                    ResultSet resultSet = stat
                            .executeQuery("Select * from primerdb.primers" + " where Strain ='" + x
                                    + "' and Cluster ='" + z + "' and UniqueP = true" + " and Hairpin = false");
                    while (resultSet.next()) {
                        Primer primer = new Primer(resultSet.getString("Sequence"));
                        primer.setTm(resultSet.getDouble("Tm"));
                        primers.add(primer);
                    }
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("Error occurred at " + x + " " + z);
                }
                System.out.println(primers.size());
                Set<Primer> primerlist2 = primers.stream().collect(Collectors.toSet());
                Primer[] primers2 = primerlist2.toArray(new Primer[primerlist2.size()]);
                Map<String, Map<CharSequence, List<Integer>>> locations = Collections
                        .synchronizedMap(new HashMap<>());
                clustphage.stream().forEach(phage -> {
                    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() - 10; i++) {
                        String sub = sequence.substring(i, i + 10);
                        if (seqInd.containsKey(sub)) {
                            seqInd.get(sub).add(i);
                        } else {
                            List<Integer> list = new ArrayList<>();
                            list.add(i);
                            seqInd.put(sub, list);
                        }
                    }
                    Map<CharSequence, List<Integer>> alllocs = new HashMap<>();
                    for (Primer primer : primers2) {
                        List<Integer> locs = new ArrayList<>();
                        String sequence1 = primer.getSequence();
                        String frag = sequence1.substring(0, 10);
                        List<Integer> integers = seqInd.get(frag);
                        if (integers != null) {
                            for (Integer i : integers) {
                                if ((sequence1.length() + i) < sequence.length()
                                        && sequence.substring(i, sequence1.length() + i).equals(sequence1)) {
                                    locs.add(i);
                                }
                            }
                        }
                        alllocs.put(sequence1, locs);
                    }
                    locations.put(phage, alllocs);
                });
                System.out.println("locations found");
                System.out.println((System.nanoTime() - time) / Math.pow(10, 9) / 60.0);
                final int[] k = new int[] { 0 };
                primerlist2.parallelStream().forEach(a -> {
                    int matches = 0;
                    int i = 0;
                    while (primers2[i] != a) {
                        i++;
                    }
                    for (int j = i + 1; j < primers2.length; j++) {
                        double[] frags = new double[clustphages.length];
                        int phageCounter = 0;
                        Primer b = primers2[j];
                        boolean match = true;
                        if (matches > 0) {
                            break;
                        }
                        if (Math.abs(a.getTm() - b.getTm()) > 5.0 || a.getSequence().equals(b.getSequence())) {
                            continue;
                        }
                        for (String phage : clustphages) {
                            List<Integer> loc1 = locations.get(phage).get(a.getSequence());
                            List<Integer> loc2 = locations.get(phage).get(b.getSequence());
                            //                            if(loc1.size()==0){
                            //                                System.out.println(phage+" "+a.getSequence());
                            //                            }
                            if (loc1.size() == 0 || loc2.size() == 0) {
                                //                                if (loc1.size()!=1||loc2.size()!=1){
                                match = false;
                                break;
                            }
                            boolean found = false;
                            int fragCount = 0;
                            int l1 = loc1.get(0);
                            int l2 = loc2.get(0);
                            int count1 = 0;
                            int count2 = 0;
                            int frag = Math.abs(l1 - l2);
                            while (!found) {
                                if (frag >= 500 && frag <= 2000) {
                                    fragCount++;
                                    if (++count1 < loc1.size())
                                        l1 = loc1.get(count1);
                                    else if (++count2 < loc2.size())
                                        l2 = loc2.get(count2);
                                } else if (l1 < l2 && frag < 500) {
                                    count2++;
                                } else if (l1 > l2 && frag < 500) {
                                    count1++;
                                } else if (l1 > l2 && frag > 2000) {
                                    count2++;
                                } else if (l1 < l2 && frag > 2000) {
                                    count1++;
                                } else {
                                    break;
                                }
                                if (count1 < loc1.size() && count2 < loc2.size()) {
                                    l1 = loc1.get(count1);
                                    l2 = loc2.get(count2);
                                    frag = Math.abs(l1 - l2);
                                } else {
                                    if (fragCount == 1) {
                                        found = true;
                                        frags[phageCounter++] = frag + 0.0;
                                    } else {
                                        break;
                                    }
                                }
                            }
                            if (!found) {
                                match = false;
                                break;
                            }

                        }
                        if (match) {
                            matches++;
                            matched.add(new Matches(a, b, frags));
                        }
                    }
                    //                    k[0]++;
                    //                    System.out.println(k[0]);
                });
                System.out.println((System.nanoTime() - time) / Math.pow(10, 9) / 60.0);
                System.out.println("Primers matched");
                int c = 0;
                int i = 0;
                try {
                    for (Matches primerkey : matched) {
                        c++;
                        String primer1 = primerkey.one.getSequence();
                        String primer2 = primerkey.two.getSequence();
                        st.setString(1, primer1);
                        st.setString(2, primer2);
                        st.setDouble(3, complementarity(primer1, primer2, Dpal_Inst));
                        st.setDouble(4, primerkey.stats.getMean());
                        st.setDouble(5, primerkey.stats.getVariance());
                        st.setDouble(6, primerkey.stats.getMean() + 2 * primerkey.stats.getStandardDeviation());
                        st.setDouble(7, primerkey.stats.getMean() - 2 * primerkey.stats.getStandardDeviation());
                        st.setString(8, z);
                        st.setString(9, x);
                        st.addBatch();
                        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(c);
            }
            log.println(z);
            log.flush();
            System.gc();
        }
    }
    stat.execute("SET FILES LOG TRUE;");
    st.close();
    stat.close();
    System.out.println("Matches Submitted");
}

From source file:com.github.lsiu.experiment.eclipselink.TestEclipseLinkEvents.java

@BeforeClass
public void setupDb() throws Exception {
    log.debug("create RESTAURANT_HIST table");
    String sql = IOUtils.toString(this.getClass().getResourceAsStream("/sql/create-history-table.sql"));

    Connection conn = ds.getConnection();
    try {/*w w  w. j ava 2  s.c o m*/
        conn.setAutoCommit(true);
        Statement stmt = conn.createStatement();
        stmt.execute(sql);
    } finally {
        conn.close();
    }

    String testDataDir = "test-data";
    String testFileName = "restaurntData_20130401_233444_700_UTF-8_subset.xml";
    String testFile = "/" + testDataDir + "/" + testFileName;

    log.debug("Prepare database with test file: {}", testFile);
    importer.importData(this.getClass().getResourceAsStream(testFile));
}

From source file:idp.mysql_con.java

public void mysql_conn() {
    String url = "jdbc:mysql://localhost:" + lport + "/";
    String db = s.getDb();/*from  w  ww . j  av  a 2  s  .c o  m*/
    String dbUser = s.getDbUser();
    String dbPasswd = s.getDbPassword();
    try {
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection(url + db, dbUser, dbPasswd);
        Statement st = con.createStatement();
        st.execute("SET NAMES 'utf8'");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.github.terma.fastselect.benchmark.PlayerFactoryApacheDrill.java

@Override
public Player createPlayer() throws Exception {
    if (!dataPresent) {
        csvPrinter.close();//from  ww w  .jav  a 2 s  . c  o  m
    }

    try {
        Connection connection = DriverManager
                .getConnection("jdbc:drill:drillbit=localhost:31010;schema=parquet");
        Statement statement = connection.createStatement();
        statement.execute("DROP TABLE IF EXISTS dfs.tmp.tb");
        statement.execute("create table dfs.tmp.tb as (select " + "cast(columns[0] as INT) as prg, "
                + "cast(columns[1] as INT) as csg, " + "cast(columns[2] as INT) as tld, "
                + "cast(columns[3] as INT) as tsg, " + "cast(columns[4] as INT) as mid, "
                + "cast(columns[5] as INT) as cid, " + "cast(columns[6] as INT) as age, "
                + "cast(columns[7] as INT) as crn, " + "cast(columns[8] as BIGINT) as vlc, "
                + "cast(columns[9] as BIGINT) as vsd, " + "cast(columns[10] as BIGINT) as vch, "
                + "cast(columns[11] as INT) as prr, " + "cast(columns[12] as INT) as csr, "
                + "cast(columns[13] as INT) as pror, " + "cast(columns[14] as INT) as proc, "
                + "cast(columns[15] as INT) as csoc, " + "cast(columns[16] as INT) as bsid, "
                + "cast(columns[17] as INT) as cpid, " + "cast(columns[18] as VARCHAR) as tr, "
                + "cast(columns[19] as VARCHAR) as ui, " + "cast(columns[20] as VARCHAR) as prn, "
                + "cast(columns[21] as VARCHAR) as csn " + "from dfs.`" + csvFile.getAbsolutePath() + "`)");
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }

    return new PlayerApacheDrill();
}

From source file:idp.mysql_con.java

public void export_csv() {
    mysql_conn();//from w w  w .  j a  va  2 s . c  o m
    for (int i = 0; i < gui.jTabbedPane1.getTabCount(); i++) {
        String file_path = System.getProperty("user.dir") + "\\src\\main\\temp\\csv\\"
                + gui.jTabbedPane1.getTitleAt(i);
        String table = removeExtension(gui.jTabbedPane1.getTitleAt(i)).toLowerCase();
        file_path = file_path.replace("\\", "/");
        try {
            String sql = "LOAD DATA LOCAL INFILE '" + file_path + "' INTO TABLE " + table
                    + " CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n'";
            System.out.println(sql);
            Statement st = con.createStatement();
            st.execute(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:sf.wicklet.gwt.site.server.db.H2Configurator.java

@Override
public void shutdown(final DataSource datasource, final String dbname) {
    if (Config.DEBUG) {
        System.out.println("# Shutdown database: " + dbname);
    }//ww w  . j a va2  s.c o  m
    try {
        final Connection connection = datasource.getConnection();
        final Statement stmt = connection.createStatement();
        stmt.execute("SHUTDOWN");
    } catch (final SQLException ex) {
        Config.get().getLogger().warn("Could not shutdown embedded database", ex);
    }
}

From source file:io.moquette.spi.impl.security.DBAuthenticatorTest.java

@Before
public void setup() throws ClassNotFoundException, SQLException, NoSuchAlgorithmException {
    Class.forName(ORG_H2_DRIVER);
    this.connection = DriverManager.getConnection(JDBC_H2_MEM_TEST);
    Statement statement = this.connection.createStatement();
    try {//from   ww w  .  ja  v  a  2s . c om
        statement.execute("DROP TABLE ACCOUNT");
    } catch (SQLException sqle) {
        LOG.info("Table not found, not dropping", sqle);
    }
    MessageDigest digest = MessageDigest.getInstance(SHA_256);
    String hash = new String(Hex.encodeHex(digest.digest("password".getBytes(StandardCharsets.UTF_8))));
    try {
        if (statement.execute("CREATE TABLE ACCOUNT ( LOGIN VARCHAR(64), PASSWORD VARCHAR(256))")) {
            throw new SQLException("can't create USER table");
        }
        if (statement.execute("INSERT INTO ACCOUNT ( LOGIN , PASSWORD ) VALUES ('dbuser', '" + hash + "')")) {
            throw new SQLException("can't insert in USER table");
        }
    } catch (SQLException sqle) {
        LOG.error("Table not created, not inserted", sqle);
        return;
    }
    LOG.info("Table User created");
    statement.close();
}