Example usage for java.sql Statement executeQuery

List of usage examples for java.sql Statement executeQuery

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. 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:net.codjo.dataprocess.server.treatmenthelper.TreatmentHelper.java

private static List<TreatmentFragment> checkIntegrityRepositoryContent(Connection con) throws SQLException {
    List<TreatmentFragment> treatmentFragmentList = new ArrayList<TreatmentFragment>();
    Statement stmt = con.createStatement();
    try {//from   w  w  w . j a  v a2 s  .com
        ResultSet rs = stmt.executeQuery("select TREATMENT_ID, CONTENT from PM_REPOSITORY_CONTENT "
                + " where CONTENT not like '%</" + DataProcessConstants.TREATMENT_ENTITY_XML + ">%'");
        try {
            while (rs.next()) {
                String content = rs.getString("CONTENT");
                String contentFragment = content.substring(content.length() - LENGTH)
                        .replace(DataProcessConstants.SPECIAL_CHAR_REPLACER_N, " ")
                        .replace(DataProcessConstants.SPECIAL_CHAR_REPLACER_R, " ");
                treatmentFragmentList.add(new TreatmentFragment(rs.getString("TREATMENT_ID"), contentFragment));
            }
            return treatmentFragmentList;
        } finally {
            rs.close();
        }
    } finally {
        stmt.close();
    }
}

From source file:com.thoughtworks.go.server.database.DatabaseFixture.java

public static Object[][] query(String query, H2Database h2Database) {
    BasicDataSource source = h2Database.createDataSource();
    Connection con = null;//from  w  w w  . j a  v a 2 s. c o  m
    Statement stmt = null;
    ResultSet rs = null;
    try {
        con = source.getConnection();
        stmt = con.createStatement();
        rs = stmt.executeQuery(query);
        int columnCount = rs.getMetaData().getColumnCount();
        List<Object[]> objects = new ArrayList<>();
        while (rs.next()) {
            Object[] values = new Object[columnCount];
            for (int i = 0; i < values.length; i++) {
                values[i] = rs.getObject(i + 1);
            }
            objects.add(values);
        }
        return objects.toArray(new Object[0][0]);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        try {
            assert stmt != null;
            stmt.close();
            con.close();
            assert rs != null;
            rs.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

From source file:ProxyAuthTest.java

private static void runQuery(String sqlStmt) throws Exception {
    Statement stmt = con.createStatement();
    ResultSet res = stmt.executeQuery(sqlStmt);

    ResultSetMetaData meta = res.getMetaData();
    System.out.println("Resultset has " + meta.getColumnCount() + " columns");
    for (int i = 1; i <= meta.getColumnCount(); i++) {
        System.out.println(//from   w  w  w  . j  a v a2s  .  c  o  m
                "Column #" + i + " Name: " + meta.getColumnName(i) + " Type: " + meta.getColumnType(i));
    }

    while (res.next()) {
        for (int i = 1; i <= meta.getColumnCount(); i++) {
            System.out.println("Column #" + i + ": " + res.getString(i));
        }
    }
    res.close();
    stmt.close();
}

From source file:com.autentia.tnt.version.Version.java

public static Version getDatabaseVersion(Connection con) throws SQLException {
    Statement stmt = null;
    ResultSet rs = null;//from  w  w  w.  ja v  a2  s  . c  o m
    String ret = null;

    try {
        stmt = con.createStatement();
        rs = stmt.executeQuery("select version from Version");

        if (rs.next()) {
            ret = rs.getString("version");
        }
    } catch (SQLException e) {
        throw e;
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                log.error("Error al liberar el resultset", e);
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                log.error("Error al liberar el statement", e);
            }
        }
    }

    return new Version(ret == null ? "0" : ret);
}

From source file:io.cloudslang.content.database.services.SQLQueryLobService.java

public static boolean executeSqlQueryLob(SQLInputs sqlInputs) throws Exception {
    if (StringUtils.isEmpty(sqlInputs.getSqlCommand())) {
        throw new Exception("command input is empty.");
    }//  ww w. j  a  v a 2  s.  co m
    boolean isLOB = false;
    ConnectionService connectionService = new ConnectionService();
    try (final Connection connection = connectionService.setUpConnection(sqlInputs)) {

        StringBuilder strColumns = new StringBuilder(sqlInputs.getStrColumns());

        connection.setReadOnly(true);
        Statement statement = connection.createStatement(sqlInputs.getResultSetType(),
                sqlInputs.getResultSetConcurrency());
        statement.setQueryTimeout(sqlInputs.getTimeout());

        ResultSet results = statement.executeQuery(sqlInputs.getSqlCommand());
        ResultSetMetaData mtd = results.getMetaData();
        int iNumCols = mtd.getColumnCount();
        for (int i = 1; i <= iNumCols; i++) {
            if (i > 1)
                strColumns.append(sqlInputs.getStrDelim());
            strColumns.append(mtd.getColumnLabel(i));
        }
        sqlInputs.setStrColumns(strColumns.toString());
        int nr = -1;
        while (results.next()) {
            nr++;
            final StringBuilder strRowHolder = new StringBuilder();
            for (int i = 1; i <= iNumCols; i++) {
                if (i > 1)
                    strRowHolder.append(sqlInputs.getStrDelim());
                Object columnObject = results.getObject(i);
                if (columnObject != null) {
                    String value;
                    if (columnObject instanceof java.sql.Clob) {
                        isLOB = true;
                        final File tmpFile = File.createTempFile("CLOB_" + mtd.getColumnLabel(i), ".txt");

                        copyInputStreamToFile(
                                new ReaderInputStream(results.getCharacterStream(i), StandardCharsets.UTF_8),
                                tmpFile);

                        if (sqlInputs.getLRowsFiles().size() == nr) {
                            sqlInputs.getLRowsFiles().add(nr, new ArrayList<String>());
                            sqlInputs.getLRowsNames().add(nr, new ArrayList<String>());
                        }
                        sqlInputs.getLRowsFiles().get(nr).add(tmpFile.getAbsolutePath());
                        sqlInputs.getLRowsNames().get(nr).add(mtd.getColumnLabel(i));
                        value = "(CLOB)...";

                    } else {
                        value = results.getString(i);
                        if (sqlInputs.isNetcool())
                            value = SQLUtils.processNullTerminatedString(value);
                    }
                    strRowHolder.append(value);
                } else
                    strRowHolder.append("null");
            }
            sqlInputs.getLRows().add(strRowHolder.toString());
        }
    }

    return isLOB;
}

From source file:massbank.extend.ChemicalFormulaUtils.java

/**
 * CIqXg/*w ww. ja v a 2  s.co m*/
 */
public static List<String[]> getIonMassList() throws IOException {
    List<String[]> massList = new ArrayList();
    try {

        Class.forName("com.mysql.jdbc.Driver");
        String conUrl = "jdbc:mysql://localhost/FORMULA_STRUCTURE_RELATION";
        Connection con = DriverManager.getConnection(conUrl, "bird", "bird2006");
        Statement stmt = con.createStatement();
        String sql = "SELECT FORMULA, MASS FROM ION_MASS order by MASS";
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String formula = rs.getString("FORMULA");
            String mass = rs.getString("MASS");
            massList.add(new String[] { formula, mass });
        }
        rs.close();
        stmt.close();
        con.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return massList;
}

From source file:com.oracle.tutorial.jdbc.CachedRowSetSample.java

public static void viewTable(Connection con) throws SQLException {
    Statement stmt = null;
    String query = "select * from MERCH_INVENTORY";
    try {//from  w  w  w  .  j a  va  2s  .  c o m
        stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            System.out.println("Found item " + rs.getInt("ITEM_ID") + ": " + rs.getString("ITEM_NAME") + " ("
                    + rs.getInt("QUAN") + ")");
        }

    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) {
            stmt.close();
        }
    }
}

From source file:application.bbdd.pool.java

public static void realizaConsulta1() {
    Connection conexion = null;//from   www .  ja  va  2  s  . co m
    Statement sentencia = null;
    ResultSet rs = null;

    try {
        // BasicDataSource nos reserva una conexion y nos la devuelve
        conexion = getConexion();
        sentencia = conexion.createStatement();
        rs = sentencia.executeQuery("select count(*) from user");
        rs.next();
        JOptionPane.showMessageDialog(null, "El numero de usuarios es: " + rs.getInt(1));
        logStatistics();

    } catch (SQLException e) {
        JOptionPane.showMessageDialog(null, e.toString());
    } finally {
        try {
            rs.close();
            sentencia.close();
            liberaConexion(conexion);
        } catch (Exception fe) {
            JOptionPane.showMessageDialog(null, fe.toString());
        }
    }
}

From source file:cit360.sandbox.BackEndMenu.java

public final static void connect() {
    Connection conn = null;/* w ww. j a v  a  2s  .com*/
    try {
        conn = DriverManager.getConnection("jdbc:mysql://localhost/cit361-sandbox?" + "user=root&password=");

        // Do something with the Connection

    } catch (SQLException ex) {
        // handle any errors
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());

    }

    if (null != conn) {
        System.out.println("Connected to database!");
    } else {
        System.out.println("Failed to make connection!");
    }
    try {
        Statement stmt = conn.createStatement();
        String query = "select * from movies ;";
        //movies is the table name
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            String name = rs.getObject(2).toString();
            String Start_Time = rs.getObject(3).toString();
            System.out.println(name + ": " + Start_Time);
            //movies table has name and price columns

        }
    } catch (SQLException e) {
        for (Throwable ex : e) {
            System.err.println("Error occurred " + ex);
        }
        System.out.println("Error in fetching data");
    }
}

From source file:com.bigdata.etl.util.DwUtil.java

public static void bulkInsert(String tableName, List<Map<String, String>> lst) {

    ResultSet rs = null;/*w w w  . j a  v  a2s.com*/
    java.sql.Statement stmt = null;

    try (java.sql.Connection conn = DataSource.getConnection()) {
        stmt = conn.createStatement();
        rs = stmt.executeQuery("select top 0 * from " + tableName);
        try (SQLServerBulkCopy bulk = new SQLServerBulkCopy(url + "user=" + user + ";password=" + password)) {
            SQLServerBulkCopyOptions sqlsbc = new SQLServerBulkCopyOptions();
            sqlsbc.setBulkCopyTimeout(60 * 60 * 1000);
            bulk.setBulkCopyOptions(sqlsbc);
            bulk.setDestinationTableName(tableName);
            ResultSetMetaData rsmd = rs.getMetaData();
            if (lst == null) {
                return;
            }
            // System.out.println(LocalTime.now() + " "+Thread.currentThread().getId()+" "+lst.size());
            try (CachedRowSetImpl x = new CachedRowSetImpl()) {
                x.populate(rs);
                for (int k = 0; k < lst.size(); k++) {
                    Map<String, String> map = lst.get(k);
                    x.last();
                    x.moveToInsertRow();
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        String name = rsmd.getColumnName(i).toUpperCase();
                        int type = rsmd.getColumnType(i);//package java.sql.Type?

                        try {
                            switch (type) {
                            case Types.VARCHAR:
                            case Types.NVARCHAR:
                                int len = rsmd.getColumnDisplaySize(i);
                                String v = map.get(name);
                                if (map.containsKey(name)) {
                                    x.updateString(i, v.length() > len ? v.substring(0, len) : v);
                                } else {
                                    x.updateNull(i);
                                }
                                break;
                            case Types.BIGINT:
                                if (map.containsKey(name) && map.get(name).matches("\\d{1,}")) {
                                    x.updateLong(i, Long.valueOf(map.get(name)));
                                } else {
                                    //   x.updateLong(i, 0);
                                    x.updateNull(i);
                                }
                                break;
                            case Types.FLOAT:
                                if (map.containsKey(name) && map.get(name).matches("([+-]?)\\d*\\.\\d+$")) {
                                    x.updateFloat(i, Float.valueOf(map.get(name)));
                                } else {
                                    x.updateNull(i);

                                }
                                break;
                            case Types.DOUBLE:
                                if (map.containsKey(name) && map.get(name).trim().length() > 0
                                        && StringUtils.isNumeric(map.get(name))) {
                                    x.updateDouble(i, Double.valueOf(map.get(name)));
                                } else {
                                    x.updateNull(i);
                                }
                                break;

                            case Types.INTEGER:
                                if (map.containsKey(name) && map.get(name).matches("\\d{1,}")) {
                                    x.updateInt(i, Integer.valueOf(map.get(name)));
                                } else {
                                    x.updateNull(i);
                                }
                                break;

                            default:
                                throw new RuntimeException("? " + type);
                            }
                            /*
                            if(map.containsKey("SYS_TELECOM"))
                            System.err.println(map.get("SYS_TELECOM"));
                             */
                        } catch (RuntimeException | SQLException e) {
                            Logger.getLogger(DwUtil.class.getName()).log(Level.SEVERE,
                                    "? name=" + name + " v=" + map.get(name), e);
                        }

                    }
                    x.insertRow();
                    x.moveToCurrentRow();
                    //x.acceptChanges();
                }

                long start = System.currentTimeMillis();
                bulk.writeToServer(x);
                long end = System.currentTimeMillis();
                System.out.println(LocalTime.now() + " " + Thread.currentThread().getId() + " "
                        + (end - start) + "ms" + " " + x.size());
            }
        }

    } catch (SQLException e) {
        Logger.getLogger(DwUtil.class.getName()).log(Level.SEVERE, null, e);
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DwUtil.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
}