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:com.aurel.track.dbase.UpdateDbSchema.java

private static boolean hasTables(Connection conn) {
    boolean hasTables = false;
    try {//  ww w.j a va 2s  .c om
        DatabaseMetaData md = conn.getMetaData();
        String userName = md.getUserName();
        String url = md.getURL();
        boolean isOracleOrDb2 = url.startsWith("jdbc:oracle") || url.startsWith("jdbc:db2");
        ResultSet rsTables = md.getTables(conn.getCatalog(), isOracleOrDb2 ? userName : null, null, null);
        LOGGER.info("Getting the tables metadata");
        if (rsTables != null && rsTables.next()) {
            LOGGER.info("Find TSITE table...");

            while (rsTables.next()) {
                String tableName = rsTables.getString("TABLE_NAME");
                String tablenameUpperCase = tableName.toUpperCase();
                if ("TSITE".equals(tablenameUpperCase)) {
                    LOGGER.info("TSITE table found");
                    hasTables = true;
                    break;
                } else {
                    if (tablenameUpperCase.endsWith("TSITE")) {
                        LOGGER.info(tablenameUpperCase + " table found");
                        hasTables = true;
                        break;
                    }
                }
            }
        }
    } catch (Exception e) {
        LOGGER.debug(ExceptionUtils.getStackTrace(e));
    }
    if (!hasTables) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT OBJECTID FROM TSITE");
            if (rs.next()) {
                hasTables = true;
            }
        } catch (SQLException e) {
            LOGGER.info("Table TSITE  does not exist");
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    return hasTables;
}

From source file:com.concursive.connect.web.modules.setup.utils.SetupUtils.java

/**
 * Determines if the database schema has been created
 *
 * @param db//  w  w w  .ja  v  a2  s.c  o m
 * @return
 */
public static boolean isDatabaseInstalled(Connection db) {
    int count = -1;
    try {
        Statement st = db.createStatement();
        ResultSet rs = st.executeQuery("SELECT count(*) AS recordcount " + "FROM database_version ");
        rs.next();
        count = rs.getInt("recordcount");
        rs.close();
        st.close();
    } catch (Exception e) {
    }
    return count > 0;
}

From source file:com.tfm.utad.sqoopdata.SqoopVerticaDB.java

private static Long findMaxID(Connection conn) {
    Long id = (long) 0;
    Statement stmt = null;
    String query;/* w ww  .j  a v a2  s.c  om*/
    try {
        stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        query = "SELECT MAX(id) AS id FROM s1.coordinates";
        LOG.info("Query execution: " + query);
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            id = (long) rs.getInt("id");
        }
    } catch (SQLException e) {
        LOG.error("SQLException error: " + e.toString());
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                LOG.error("Statement error: " + ex.toString());
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                LOG.error("Connection error: " + ex.toString());
            }
        }
    }
    return id;
}

From source file:net.firejack.platform.core.utils.db.DBUtils.java

private static ResultSet selectDataFromSource(Connection sourceConnection, TablesMapping mapping)
        throws SQLException {
    Map<Column, Column> columnMapping = mapping.getColumnMapping();
    StringBuilder selectQuery = new StringBuilder("select ");
    for (Map.Entry<Column, Column> columnEntry : columnMapping.entrySet()) {
        Column sourceColumn = columnEntry.getKey();
        selectQuery.append(sourceColumn.getName()).append(',');
    }//w ww . ja v  a  2s . co  m
    if (!columnMapping.isEmpty()) {
        selectQuery.replace(selectQuery.length() - 1, selectQuery.length(), "");
    }
    selectQuery.append(" from ").append(mapping.getSourceTable().getName());
    String sql = selectQuery.toString();
    Statement statement = sourceConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = statement.executeQuery(sql);
    rs.setFetchSize(DEFAULT_BATCH_SIZE);
    return rs;
}

From source file:com.redhat.rhn.frontend.action.common.test.RhnSetActionTest.java

public static void verifyRhnSetData(Long uid, String setname, int size)
        throws HibernateException, SQLException {
    Session session = null;//from   w ww. j  a  va 2s .c om
    Connection c = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        session = HibernateFactory.getSession();
        session.flush();
        c = session.connection();
        stmt = c.createStatement();
        String query = "select * from rhnset where user_id = " + uid.toString();
        rs = stmt.executeQuery(query);

        assertNotNull(rs);

        int cnt = 0;
        while (rs.next()) {
            assertEquals(uid.longValue(), rs.getLong("USER_ID"));
            assertEquals(setname, rs.getString("LABEL"));
            cnt++;
        }

        assertEquals(size, cnt);
    } catch (SQLException e) {
        log.error("Error validating data.", e);
        throw e;
    } finally {
        HibernateHelper.cleanupDB(rs, stmt);
    }
}

From source file:com.trackplus.ddl.DataReader.java

private static int getTableData(BufferedWriter writer, BufferedWriter writerClean, BufferedWriter writerUpdate,
        BufferedWriter writerUpdateClean, Connection con, String tableName,
        StringValueConverter stringValueConverter) throws DDLException {
    Statement st = MetaDataBL.createStatement(con);
    String sql = "SELECT * FROM " + tableName;
    ResultSet rs;/*from  w  w  w.  java2 s  .co m*/
    try {
        rs = st.executeQuery(sql);
    } catch (SQLException e) {
        throw new DDLException(e.getMessage(), e);
    }

    int idx = 0;
    int idxUpdate = 0;

    try {
        ResultSetMetaData md = rs.getMetaData();
        String primaryKey = MetaDataBL.getPrimaryKey(tableName, con);
        int columnCount = md.getColumnCount();
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO " + tableName + "(");
        for (int i = 0; i < columnCount; i++) {
            String columnName = md.getColumnName(i + 1);
            sb.append(columnName);
            if (i < columnCount - 1) {
                sb.append(", ");
            }
        }
        sb.append(") VALUES(");

        StringBuilder updateSQL;
        while (rs.next()) {
            StringBuilder line = new StringBuilder();
            line.append(sb);
            String primaryKeyValue = rs.getString(primaryKey);
            for (int i = 0; i < columnCount; i++) {
                String value;
                String columnName = md.getColumnName(i + 1);
                try {
                    value = stringValueConverter.getStringValue(md, i + 1, rs, tableName);
                } catch (DDLException ex) {
                    LOGGER.warn("Error: " + ex.getMessage() + " for column:" + columnName + " in table:"
                            + tableName + " primary key " + primaryKey + "=" + primaryKeyValue
                            + ". Will be set to NULL!");
                    value = null;
                }
                if (value != null && value.length() > MAX_VALUE_STRING) {

                    if ("MAILBODY".equalsIgnoreCase(columnName) || "CLOBVALUE".equalsIgnoreCase(columnName)) {
                        //blob
                    } else {
                        updateSQL = new StringBuilder();
                        updateSQL.append("UPDATE ").append(tableName).append(" SET ").append(columnName)
                                .append("=");
                        updateSQL.append(value).append("\n WHERE ").append(primaryKey).append("=")
                                .append(primaryKeyValue);
                        updateSQL.append(";");

                        MetaDataBL.appendLine(writerUpdateClean, updateSQL.toString());

                        updateSQL.append(LINE_SEPARATOR);
                        MetaDataBL.appendLine(writerUpdate, updateSQL.toString());

                        idxUpdate++;
                        value = null;
                    }
                }
                line.append(value);
                if (i < columnCount - 1) {
                    line.append(", ");
                }
            }
            line.append(");");

            MetaDataBL.appendLine(writerClean, line.toString());

            line.append(LINE_SEPARATOR);
            MetaDataBL.appendLine(writer, line.toString());

            idx++;
        }
    } catch (SQLException e) {
        throw new DDLException(e.getMessage(), e);
    }
    try {
        rs.close();
    } catch (SQLException e) {
        throw new DDLException(e.getMessage(), e);
    }
    if (idxUpdate > 0) {
        LOGGER.warn("There was " + idxUpdate + " records with String size>" + MAX_VALUE_STRING
                + " found in table:" + tableName);
    }
    return idx;
}

From source file:com.tfm.utad.sqoopdata.SqoopVerticaDB.java

private static void findBetweenMinIDAndMaxID(Connection conn, Long minID, Long maxID) {
    Statement stmt = null;
    String query;/*from ww  w. ja v a 2 s. co  m*/
    try {
        stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        query = "SELECT * FROM s1.coordinates WHERE id > " + minID + " AND id <= " + maxID + "";
        LOG.info("Query execution: " + query);
        ResultSet rs = stmt.executeQuery(query);
        int batch = 0;
        List<CoordinateCartoDB> result = new ArrayList<>();
        long start_time = System.currentTimeMillis();
        while (rs.next()) {
            batch++;
            CoordinateCartoDB cdb = new CoordinateCartoDB((long) rs.getInt("id"), rs.getString("userstr"),
                    rs.getString("created_date"), rs.getString("activity"), rs.getFloat("latitude"),
                    rs.getFloat("longitude"), (long) rs.getInt("userid"));
            result.add(cdb);
            if (batch == 50) {
                sendDataToCartoDB(result);
                batch = 0;
                result = new ArrayList<>();
            }
        }
        if (batch > 0) {
            sendDataToCartoDB(result);
        }
        long end_time = System.currentTimeMillis();
        long difference = end_time - start_time;
        LOG.info("CartoDB API execution time: " + String.format("%d min %d sec",
                TimeUnit.MILLISECONDS.toMinutes(difference), TimeUnit.MILLISECONDS.toSeconds(difference)
                        - TimeUnit.MINUTES.toSeconds(TimeUnit.MILLISECONDS.toMinutes(difference))));
    } catch (SQLException e) {
        LOG.error("SQLException error: " + e.toString());
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                LOG.error("Statement error: " + ex.toString());
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                LOG.error("Connection error: " + ex.toString());
            }
        }
    }
}

From source file:org.apache.drill.test.framework.Utils.java

public static ResultSet execSQL(String sql, Connection connection) throws SQLException {
    try {/*from w  ww.  ja v a  2  s. c om*/
        Statement statement = connection.createStatement();
        return statement.executeQuery(sql);
    } catch (SQLException e) {
        LOG.error(e.getMessage());
        e.printStackTrace();
        try {
            connection.close();
        } catch (SQLException e1) {
            LOG.error(e.getMessage());
            e1.printStackTrace();
        }
        throw e;
    }
}

From source file:com.baidu.qa.service.test.util.JdbcUtil.java

protected static List<Map<String, Object>> excuteQuerySql(String sqlStr, String dbname) throws Exception {

    List<Map<String, Object>> rltList = new ArrayList<Map<String, Object>>();

    //   ???//from w w  w  .j  a va  2 s.co m
    Connection con = null;
    Statement sm = null;
    ResultSet rs = null;
    try {
        //?
        con = MysqlDatabaseManager.getCon(dbname);
        Assert.assertNotNull("connect to db error:" + dbname, con);

        //??
        sm = con.createStatement();
        rs = sm.executeQuery(sqlStr);
        log.info("[sql:]" + sqlStr);
        ResultSetMetaData rsmd = rs.getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        int count = 0;
        String key;
        Object value;

        //          ???? 
        while (rs.next()) {
            Map<String, Object> expectData = new HashMap<String, Object>();
            count = 0;
            while (count++ < numberOfColumns) {

                key = rsmd.getColumnLabel(count);
                value = rs.getObject(key);
                expectData.put(key, value);
            }
            rltList.add(expectData);
        }

        return rltList;

    } catch (Exception e) {
        throw e;
    } finally {
        if (con != null) {
            con.close();

        }
        if (sm != null) {
            sm.close();
        }
        if (rs != null) {
            rs.close();
        }
    }

}

From source file:com.clavain.alerts.Methods.java

public static void sendUPNotifications(ReturnServiceCheck sc) {
    Integer cid = sc.getCid();//w  ww  . j  av  a2 s. c o m
    try {
        Connection conn = connectToDatabase(com.clavain.muninmxcd.p);
        java.sql.Statement stmt = conn.createStatement();

        ResultSet rs = stmt.executeQuery(
                "SELECT notifications.id as nid, contacts.* FROM `notifications` LEFT JOIN contacts ON notifications.contact_id = contacts.id WHERE check_id = "
                        + cid);
        while (rs.next()) {
            Integer contact_id = rs.getInt("id");
            String dayField = getScheduleFieldToCheck();
            logger.info("[Check Notifications " + cid + "] Found " + rs.getString("contact_name"));
            if (rs.getString(dayField).equals("disabled")) {
                logger.info("[Check Notifications " + cid + "] " + rs.getString("contact_name")
                        + " disabled notifications for today - skipping contact");
            } else {
                String splitField = rs.getString(dayField);
                // figure out if this user got notifications enabled or disabled for the current hour and time
                String[] hours = splitField.split(";");
                long a = getStampFromTimeAndZone(hours[0], rs.getString("timezone"));
                long b = getStampFromTimeAndZone(hours[1], rs.getString("timezone"));
                long cur = (System.currentTimeMillis() / 1000L);
                // if in range send notifications
                if (a < cur && b > cur) {
                    String title = "Service OK: " + sc.getCheckname() + " (" + sc.getChecktype() + ")";
                    String message = "The Service is now up again.";
                    String json = "";
                    if (rs.getInt("email_active") == 1) {
                        logger.info("[Check Notifications " + cid + "] " + rs.getString("contact_name")
                                + " Sending E-Mail");
                        sendMail(title, message, rs.getString("contact_email"));
                        updateCheckNotificationLog(cid, contact_id,
                                "UPTIME E-Mail send to " + rs.getString("contact_email"), "email");
                    }
                    if (rs.getInt("sms_active") == 1) {
                        logger.info("[Check Notifications " + cid + "] " + rs.getString("contact_name")
                                + " Sending SMS");
                        sendSMS(title, message, rs.getString("contact_mobile_nr"), rs.getInt("user_id"));
                        updateCheckNotificationLog(cid, contact_id,
                                "UPTIME SMS send to " + rs.getString("contact_mobile_nr"), "sms");
                    }
                    if (rs.getInt("pushover_active") == 1) {
                        logger.info("[Check Notifications " + cid + "] " + rs.getString("contact_name")
                                + " Sending Pushover Notification");
                        sendPushover(title, message, rs.getString("pushover_key"));
                        updateCheckNotificationLog(cid, contact_id,
                                "UPTIME PushOver Message send to " + rs.getString("pushover_key"), "pushover");
                    }
                } else {
                    logger.info("[Check Notifications " + cid + "] " + rs.getString("contact_name")
                            + " disabled notifications for this timerange - skipping contact");
                }
            }
        }
    } catch (Exception ex) {
        logger.error("Error in sendUPNotifications for CID " + cid + " : " + ex.getLocalizedMessage());
    }
}