Example usage for java.sql Statement executeQuery

Introduction

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

Prototype

ResultSet executeQuery(String sql) throws SQLException;

Source Link

Document

Executes the given SQL statement, which returns a single ResultSet object.

Usage

From source file:edu.ku.brc.specify.conversion.ConvertMiscData.java

/**
 * @param oldDBConn/*from  ww  w . j av a2 s  . c  o  m*/
 * @param newDBConn
 * @param disciplineID
 * @return
 */
public static boolean convertKUFishObsData(final Connection oldDBConn, final Connection newDBConn) {
    IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn);
    IdMapperIFace coMapper = IdMapperMgr.getInstance().addTableMapper("collectionobjectcatalog",
            "CollectionObjectCatalogID", false);

    PreparedStatement pStmt1 = null;
    PreparedStatement pStmt2 = null;
    PreparedStatement pStmt3 = null;
    try {
        pStmt1 = newDBConn.prepareStatement(
                "INSERT INTO collectionobjectattribute (Remarks, CollectionMemberID, TimestampCreated, TimestampModified, Version) VALUES(?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        pStmt2 = newDBConn.prepareStatement(
                "UPDATE collectionobjectattribute SET Remarks=? WHERE CollectionObjectAttributeID = ?");

        pStmt3 = newDBConn.prepareStatement(
                "UPDATE collectionobject SET CollectionObjectAttributeID=? WHERE CollectionObjectID = ?");

        String sql = " SELECT BiologicalObjectID, Text1, TimestampCreated, TimestampModified FROM observation WHERE Text1 IS NOT NULL AND LENGTH(Text1) > 0";
        Statement stmt = oldDBConn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            int oldCOId = rs.getInt(1);
            Integer newCOId = coMapper.get(oldCOId);
            if (newCOId != null) {
                sql = "SELECT CollectionObjectAttributeID, CollectionMemberID FROM collectionobject WHERE CollectionObjectID = "
                        + newCOId;
                Object[] row = BasicSQLUtils.getRow(sql);
                if (row == null || row.length == 0) {
                    log.error("Couldn't get record for  newCOId " + newCOId);
                    continue;
                }

                Integer newCOAId = (Integer) row[0];
                Integer collMemId = (Integer) row[1];

                if (newCOAId != null) // Do Update
                {
                    pStmt2.setString(1, rs.getString(2));
                    pStmt2.setInt(2, newCOAId);
                    pStmt2.executeUpdate();

                } else // Do Insert
                {
                    pStmt1.setString(1, rs.getString(2));
                    pStmt1.setInt(2, collMemId);
                    pStmt1.setTimestamp(3, rs.getTimestamp(3));
                    pStmt1.setTimestamp(4, rs.getTimestamp(4));
                    pStmt1.setInt(5, 1);
                    pStmt1.executeUpdate();
                    newCOAId = BasicSQLUtils.getInsertedId(pStmt1);
                }

                pStmt3.setInt(1, newCOAId);
                pStmt3.setInt(2, newCOId);
                pStmt3.executeUpdate();

            } else {
                log.error("No mapped CO for Obs.BiologicalObjectID " + oldCOId);
            }
        }
        rs.close();
        stmt.close();

        return true;

    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        try {
            if (pStmt1 != null)
                pStmt1.close();
            if (pStmt2 != null)
                pStmt2.close();
            if (pStmt3 != null)
                pStmt3.close();

        } catch (Exception ex) {
        }
    }

    return false;
}

---

From source file:controladores.DepartamentosController.java

private ArrayList<Departamento> getDepartamentos() {
    try {//from  w  ww . j ava2  s . com
        ArrayList<Departamento> listadept = new ArrayList<Departamento>();
        Statement st = this.cn.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM DEPT");
        while (rs.next()) {
            Departamento dept = new Departamento();
            dept.setNumero(rs.getInt("DEPT_NO"));
            dept.setNombre(rs.getString("DNOMBRE"));
            dept.setLocalidad(rs.getString("LOC"));
            listadept.add(dept);
        }
        return listadept;
    } catch (SQLException ex) {
        System.out.println("Error leyendo departamentos: " + ex);
    }
    return null;
}

---

From source file:controladores.controladorEmpleados.java

@Override
public ModelAndView handleRequest(HttpServletRequest hsr, HttpServletResponse hsr1) throws Exception {
    String apellido = hsr.getParameter("txtApellido");
    ModelAndView mv = new ModelAndView("index");
    Connection con = getConexion();
    ResultSet rs = null;/* w  w w  . ja v  a2s  . com*/
    if (apellido == null) {
        Statement stmt = con.createStatement();
        rs = stmt.executeQuery("Select apellido, oficio, salario, nvl(comision,0) comision from emp");
    } else {
        PreparedStatement ps = con.prepareStatement(
                "Select apellido, oficio, salario, nvl(comision,0) comision from emp where apellido = ?");
        ps.setString(1, apellido);
        rs = ps.executeQuery();
    }
    ArrayList al = new ArrayList();
    while (rs.next()) {
        Empleado emp = new Empleado();
        emp.setApellido(rs.getString("apellido"));
        emp.setOficio(rs.getString("oficio"));
        emp.setSalario(rs.getInt("salario"));
        emp.setComision(rs.getInt("comision"));
        al.add(emp);
    }
    mv.addObject("lista", al);
    return mv;
}

---

From source file:GUI.meilleurePrestataire.java

public meilleurePrestataire() throws SQLException {

    dataset = new DefaultPieDataset();
    Connection connexion = MyConnection.getInstance();
    String requete = "select * from evaluationprestataire";
    Statement statement = connexion.createStatement();
    ResultSet resultat = statement.executeQuery(requete);
    while (resultat.next()) {

        dataset.setValue(/*from  w ww  . j a  va  2s  .  c  o  m*/
                userDAO.retrieveUtilisateurById(resultat.getInt(2)).getNom() + " : " + resultat.getInt(3),
                resultat.getInt(3));

    }
    graphe = ChartFactory.createPieChart3D("Les notes des prestataires", dataset, true, true, false);
    cp = new ChartPanel(graphe);
    this.add(cp);
}

---

From source file:TestAppletNetscape.java

public void paint(Graphics g) {
    System.out.println("paint(): querying the database");
    try {//from  w w  w. j  av a2s .  c  o  m
        PrivilegeManager.enablePrivilege("UniversalConnect");
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("select 'Hello '||initcap(USER) result from dual");
        while (rset.next())
            g.drawString(rset.getString(1), 10, 10);
        rset.close();
        stmt.close();
    } catch (SQLException e) {
        System.err.println("paint(): SQLException: " + e.getMessage());
    }
}

---

From source file:com.taobao.tddl.jdbc.group.integration.TransactionTest.java

@Test
public void springTest() throws Exception {

    Connection conn = ds.getConnection();
    conn.setAutoCommit(false);/*from w w w  .  jav  a 2  s. c  o m*/

    // Statementcrud
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select f1,f2 from crud");
    assertEquals(stmt.executeUpdate("insert into crud(f1,f2) values(10,'str')"), 1);
    assertEquals(stmt.executeUpdate("insert into crud(f1,f2) values(10,'str')"), 1);
    conn.commit();
    conn.setAutoCommit(true);
    rs = stmt.executeQuery("select f1,f2 from crud");
    rs.next();
    assertEquals(rs.getInt(1), 10);
    assertEquals(rs.getString(2), "str");
    rs.close();

    assertEquals(stmt.executeUpdate("delete from crud"), 2);

    stmt.close();
    conn.close();
}

---

From source file:com.flexive.core.Database.java

/**
 * Load a FxString from the content data
 *
 * @param con         an open connection
 * @param column      the name of the column from the translations table to load
 * @param whereClause mandatory where clause
 * @return FxString created from the data table
 * @throws SQLException if a database error occurred
 *///  w w w  .  j  a v  a 2s.c o  m
public static FxString loadContentDataFxString(Connection con, String column, String whereClause)
        throws SQLException {
    Statement stmt = null;
    Map<Long, String> hmTrans = new HashMap<Long, String>(10);
    int defaultLanguageId = -1;
    try {
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT LANG, ISMLDEF, " + column + " FROM "
                + DatabaseConst.TBL_CONTENT_DATA + " WHERE " + whereClause);
        while (rs != null && rs.next()) {
            hmTrans.put(rs.getLong(1), rs.getString(3));
            if (rs.getBoolean(2)) {
                defaultLanguageId = rs.getInt(1);
            }
        }
    } finally {
        if (stmt != null)
            stmt.close();
    }
    return new FxString(defaultLanguageId, hmTrans);
}

---

From source file:com.flexive.core.Database.java

/**
 * Load a FxString from a translation table
 *
 * @param con         an open connection
 * @param table       the base table (NOT the one with translations!)
 * @param column      the name of the columns from the translations table to load
 * @param whereClause mandatory where clause
 * @return FxString created from the data table
 * @throws SQLException if a database error occured
 *///  w  w w.  ja v a  2 s  . c o m
public static FxString loadFxString(Connection con, String table, String column, String whereClause)
        throws SQLException {
    Statement stmt = null;
    Map<Long, String> hmTrans = new HashMap<Long, String>(10);
    long defaultLanguageId = -1;
    try {
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT LANG, DEFLANG, " + column + " FROM " + table + DatabaseConst.ML
                + " WHERE " + whereClause);
        while (rs != null && rs.next()) {
            hmTrans.put(rs.getLong(1), rs.getString(3));
            if (rs.getBoolean(2)) {
                defaultLanguageId = rs.getInt(1);
            }
        }
    } finally {
        if (stmt != null)
            stmt.close();
    }
    return new FxString(defaultLanguageId, hmTrans);
}

---

From source file:database.HashTablesTools.java

public static int countFilesWhichAreAlreadyIndexedInSequenceDB_notgood_either(String tableName,
        String tableFailureName, Map<String, List<MMcifFileInfos>> indexPDBFileInFolder) {

    Connection connection = HashTablesTools.getConnection(tableName, tableFailureName);

    int countOfHashFoundInFailureDB = 0;
    int countOfHashFoundInSequenceDB = 0;
    // fastest//from w ww.j a  va  2  s .  c  om
    Statement stmt = null;
    ResultSet resultFindEntryFailureDb = null;
    for (Map.Entry<String, List<MMcifFileInfos>> entry : indexPDBFileInFolder.entrySet()) {
        A: for (MMcifFileInfos fileInfos : entry.getValue()) {

            try {
                stmt = connection.createStatement();
                String findEntry = "SELECT * from " + tableFailureName + " WHERE pdbfilehash = '"
                        + fileInfos.getHash() + "'";
                resultFindEntryFailureDb = stmt.executeQuery(findEntry);

                if (resultFindEntryFailureDb.next()) {
                    countOfHashFoundInFailureDB += 1;
                    stmt.close();
                    continue A;

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

            // if here not found in failureDB
            try {
                stmt = connection.createStatement();
                String findEntry = "SELECT * from " + tableName + " WHERE pdbfilehash = '" + fileInfos.getHash()
                        + "'";
                resultFindEntryFailureDb = stmt.executeQuery(findEntry);

                if (resultFindEntryFailureDb.next()) {
                    countOfHashFoundInSequenceDB += 1;
                    stmt.close();
                    continue A;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    HashTablesTools.shutdown();
    return countOfHashFoundInFailureDB + countOfHashFoundInSequenceDB;
}

---

From source file:es.us.mwm.testcloudfoundry.DBClient.java

public String getClients() {
    String result = "";
    try {/*from w w w .  ja v  a 2 s  .co m*/
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Client");
        result += "<table>";
        result += "<tr><th>IDCLIENT</th><th>NAME</th></tr>";
        while (rs.next()) {
            result += "<tr>";
            result += "<td>" + rs.getInt("idClient") + "</td>";
            result += "<td>" + rs.getString("Address") + "</td>";
            result += "<td>" + rs.getString("Name") + "</td>";
            result += "</tr>";
        }
        result += "</table>";
        rs.close();
        stmt.close();
    } catch (SQLException ex) {
        log.log(Level.SEVERE, ex.toString(), ex);
    }
    return result;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »