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.google.visualization.datasource.util.SqlDataSourceHelper.java

/**
 * Executes the given query on the given SQL database table, and returns the
 * result as a DataTable.//from   ww w. j  a v a 2s. c o  m
 *
 * @param query The query.
 * @param databaseDescription The information needed to connect to the SQL database and table.
 *
 * @return DataTable A data table with the data from the specified sql table,
 *     after applying the specified query on it.
 *
 * @throws DataSourceException Thrown when the data source fails to perform the action.
 */
public static DataTable executeQuery(Query query, SqlDatabaseDescription databaseDescription)
        throws DataSourceException {
    Connection con = getDatabaseConnection(databaseDescription);
    String tableName = databaseDescription.getTableName();

    // Build the sql query.
    StrBuilder queryStringBuilder = new StrBuilder();
    buildSqlQuery(query, queryStringBuilder, tableName);
    List<String> columnIdsList = null;
    if (query.hasSelection()) {
        columnIdsList = getColumnIdsList(query.getSelection());
    }
    Statement stmt = null;
    try {
        // Execute the sql query.
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(queryStringBuilder.toString());

        DataTable table = buildColumns(rs, columnIdsList);

        // Fill the data in the data table.
        buildRows(table, rs);
        return table;
    } catch (SQLException e) {
        String messageToUser = "Failed to execute SQL query: " + "\"" + queryStringBuilder.toString() + "\"\n"
                + "SQL error message: " + e.getMessage();
        throw new DataSourceException(ReasonType.INTERNAL_ERROR, messageToUser);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                /* ignore close errors */ }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                /* ignore close errors */ }
        }
    }
}

From source file:com.hangum.tadpole.engine.sql.util.OracleObjectCompileUtils.java

/**
 * other object compile/* w  w w . ja v  a  2  s .co  m*/
 * 
 * @param actionType 
 * @param objType
 * @param objName
 * @param userDB
 */
public static String otherObjectCompile(PublicTadpoleDefine.QUERY_DDL_TYPE actionType, String objType,
        Map<String, String> paramMap, UserDBDAO userDB, boolean isDebug) throws Exception {
    String withDebugOption = "";
    if (isDebug)
        withDebugOption = "DEBUG";

    String sqlQuery = "ALTER " + objType + " " + paramMap.get("full_name") + " COMPILE " + withDebugOption; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$

    java.sql.Connection javaConn = null;
    Statement statement = null;
    ResultSet rs = null;
    try {
        SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
        javaConn = client.getDataSource().getConnection();

        statement = javaConn.createStatement();
        statement.execute(sqlQuery);

        sqlQuery = "Select * From all_Errors where owner = nvl('" + paramMap.get("schema_name") //$NON-NLS-1$//$NON-NLS-2$
                + "', user) and name='" + paramMap.get("object_name") + "' and type = '" + objType //$NON-NLS-1$
                + "' order by type, sequence ";
        rs = statement.executeQuery(sqlQuery);
        StringBuffer result = new StringBuffer();
        while (rs.next()) {
            result.append(prettyMsg(rs.getString("line"), rs.getString("position"), rs.getString("text"))); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        }

        return result.toString();
    } finally {
        try {
            rs.close();
        } catch (Exception e) {
        }
        try {
            statement.close();
        } catch (Exception e) {
        }
        try {
            javaConn.close();
        } catch (Exception e) {
        }
    }
}

From source file:ems.util.DataHandler.java

public static MyModel getVoterDetails(String wardNo, String wardSrNo) {
    String sqlQuery = String.format(Q_S_GET_VOTER, wardNo, wardSrNo);
    Connection con = getConnection();
    Statement s = null;
    ResultSet rs = null;/* w w w  .j  a  v a 2s  . com*/
    try {
        log.info("sqlQuery:" + sqlQuery);
        s = con.createStatement();
        rs = s.executeQuery(sqlQuery);
        while (rs.next()) {
            return new MyModel(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4),
                    rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9),
                    rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13), rs.getString(14),
                    rs.getString(15), rs.getString(16), rs.getString(17));
        }
    } catch (SQLException e) {
        log.error("getVoterDetails: " + e.getMessage());
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (s != null) {
                s.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException ex) {
            log.error("getBoothList: " + ex.getMessage());
        }
    }
    return null;
}

From source file:com.hangum.tadpole.engine.sql.util.OracleObjectCompileUtils.java

/**
 * package compile/*from www  .j a  va 2 s  .c  o m*/
 * 
 * @param objectName
 * @param userDB
 */
public static String packageCompile(ProcedureFunctionDAO packageDao, UserDBDAO userDB, boolean isDebug)
        throws Exception {
    String withDebugOption = "";
    if (isDebug)
        withDebugOption = "DEBUG";

    String sqlQuery = "ALTER PACKAGE " + packageDao.getFullName(true/*isPackage*/) + " COMPILE " //$NON-NLS-1$//$NON-NLS-2$
            + withDebugOption + " SPECIFICATION "; //$NON-NLS-1$
    String sqlBodyQuery = "ALTER PACKAGE " + packageDao.getFullName(true/*isPackage*/) + " COMPILE " //$NON-NLS-1$//$NON-NLS-2$
            + withDebugOption + " BODY "; //$NON-NLS-1$

    java.sql.Connection javaConn = null;
    Statement statement = null;
    ResultSet rs = null;
    try {
        SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
        javaConn = client.getDataSource().getConnection();

        statement = javaConn.createStatement();
        statement.execute(sqlQuery);
        statement.execute(sqlBodyQuery);

        sqlQuery = "Select * From all_Errors where owner = nvl('" + packageDao.getSchema_name() //$NON-NLS-1$
                + "', user) and name='" + packageDao.getName() //$NON-NLS-1$
                + "' and type in ('PACKAGE', 'PACKAGE BODY') order by type, sequence ";
        rs = statement.executeQuery(sqlQuery);
        StringBuffer result = new StringBuffer();
        while (rs.next()) {
            result.append(prettyMsg(rs.getString("line"), rs.getString("position"), rs.getString("text"))); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        }
        return result.toString();
    } finally {
        try {
            rs.close();
        } catch (Exception e) {
        }
        try {
            statement.close();
        } catch (Exception e) {
        }
        try {
            javaConn.close();
        } catch (Exception e) {
        }

    }
}

From source file:model.SQLiteModel.java

private static List<Map<String, String>> select(String query) {
    //query = StringEscapeUtils.escapeJavaScript(query);
    //System.out.println(query);
    ResultSet rs = null;/*from   w w w  . j  a v  a 2  s .  c  om*/
    Statement stmt = null;
    int first = 1;
    List<String> columnNames = new ArrayList<String>();
    List<Map<String, String>> data = new ArrayList<Map<String, String>>();
    try {
        stmt = c.createStatement();
        rs = stmt.executeQuery(query);
        while (rs.next()) {
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            if (first == 1) {
                for (int i = 1; i <= count; i++) {
                    columnNames.add(rsmd.getColumnName(i));
                }
            }
            Map<String, String> curr = new HashMap<String, String>();
            for (int i = 1; i <= count; i++) {
                curr.put(columnNames.get(i - 1), rs.getString(i));
            }
            data.add(curr);
            first++;
        }
        stmt.close();
    } catch (Exception e) {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
        System.out.println("Unsuccessful select query: " + query);
        writeLineToLog("Unsuccessful select query: " + query);
    }
    return data;
}

From source file:HSqlManager.java

public static void runChecks(Connection connection) throws SQLException, IOException, IllegalAccessException,
        InstantiationException, ClassNotFoundException {
    INSTANCE = ImportPhagelist.getInstance();
    checkPhage(connection);/*  w ww. j a  va  2  s.  c o m*/
    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);
    }
}

From source file:com.espertech.esper.epl.db.DatabasePollingViewableFactory.java

private static QueryMetaData getExampleQueryMetaData(Connection connection, String[] parameters,
        String sampleSQL, ColumnSettings metadataSetting, boolean isUsingMetadataSQL)
        throws ExprValidationException {
    // Simply add up all input parameters
    List<String> inputParameters = new LinkedList<String>();
    inputParameters.addAll(Arrays.asList(parameters));

    Statement statement;
    try {/*from   w ww.j  av a2s . c  o  m*/
        statement = connection.createStatement();
    } catch (SQLException ex) {
        String text = "Error creating statement";
        log.error(text, ex);
        throw new ExprValidationException(text + ", reason: " + ex.getMessage());
    }

    ResultSet result = null;
    try {
        result = statement.executeQuery(sampleSQL);
    } catch (SQLException ex) {
        try {
            statement.close();
        } catch (SQLException e) {
            log.info("Error closing statement: " + e.getMessage(), e);
        }

        String text;
        if (isUsingMetadataSQL) {
            text = "Error compiling metadata SQL to retrieve statement metadata, using sql text '" + sampleSQL
                    + "'";
        } else {
            text = "Error compiling metadata SQL to retrieve statement metadata, consider using the 'metadatasql' syntax, using sql text '"
                    + sampleSQL + "'";
        }

        log.error(text, ex);
        throw new ExprValidationException(text + ", reason: " + ex.getMessage());
    }

    Map<String, DBOutputTypeDesc> outputProperties;
    try {
        outputProperties = compileResultMetaData(result.getMetaData(), metadataSetting);
    } catch (SQLException ex) {
        try {
            result.close();
        } catch (SQLException e) {
            // don't handle
        }
        try {
            statement.close();
        } catch (SQLException e) {
            // don't handle
        }
        String text = "Error in statement '" + sampleSQL + "', failed to obtain result metadata";
        log.error(text, ex);
        throw new ExprValidationException(text + ", please check the statement, reason: " + ex.getMessage());
    } finally {
        if (result != null) {
            try {
                result.close();
            } catch (SQLException e) {
                log.warn("Exception closing result set: " + e.getMessage());
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                log.warn("Exception closing result set: " + e.getMessage());
            }
        }
    }

    return new QueryMetaData(inputParameters, outputProperties);
}

From source file:ems.util.DataHandler.java

public static List<MyModelSimpleStringProperty> getReportDetails(String reportType, String... params) {
    List<MyModelSimpleStringProperty> reportDetails = new LinkedList<>();
    String sqlQuery = "";
    switch (reportType) {
    case "101":
        sqlQuery = String.format(Q_S_SURNAME_STATUS_, params[0]);
        break;/*from w ww.j a  v a  2s  .  com*/
    case "102":
        sqlQuery = String.format(Q_S_COMMUNITY_STATUS_, params[0]);
        break;
    case "3":
        sqlQuery = String.format(Q_S_AREA_WISE_, params[0], params[1]);
        break;
    case "6":
        sqlQuery = String.format(Q_S_BOOTH_WISE_, params[1]);
        break;
    case "7":
        sqlQuery = String.format(Q_S_COMMUNITY_WISE_, params[0], params[1]);
        break;
    case "8":
        String colorCode = "";
        switch (params[1]) {
        case "Our":
            colorCode = "1";
            break;
        case "Opposite":
            colorCode = "2";
            break;
        case "Unpredictable":
            colorCode = "3";
            break;
        case "Others":
            colorCode = "4";
            break;
        case "All":
            colorCode = "5";
            break;
        }
        sqlQuery = String.format(Q_S_COLOR_CODE_BOOTH_WISE_, params[0], colorCode, colorCode);
        break;
    case "9":
        colorCode = "";
        switch (params[1]) {
        case "Our":
            colorCode = "1";
            break;
        case "Opposite":
            colorCode = "2";
            break;
        case "Unpredictable":
            colorCode = "3";
            break;
        case "Others":
            colorCode = "4";
            break;
        case "All":
            colorCode = "5";
            break;
        }
        sqlQuery = String.format(Q_S_COLOR_CODE_WISE_, colorCode, colorCode);
        break;
    case "11":
        sqlQuery = String.format(Q_S_SETION_WISE_, params[0], params[1]);
        break;
    case "12":
        sqlQuery = String.format(Q_S_SURNAME_WISE_, params[0], params[1]);
        break;
    }

    Connection con = getConnection();
    Statement s = null;
    ResultSet rs = null;
    try {
        log.info("sqlQuery:" + sqlQuery);
        s = con.createStatement();
        rs = s.executeQuery(sqlQuery);
        while (rs.next()) {
            reportDetails.add(new MyModelSimpleStringProperty(rs.getString(1), rs.getString(2), rs.getString(3),
                    rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),
                    rs.getString(9), rs.getString(10), rs.getString(11), "", "", "", "", ""));
        }
    } catch (SQLException e) {
        log.error("getReportDetails: " + e.getMessage());
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (s != null) {
                s.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException ex) {
            log.error("getBoothList: " + ex.getMessage());
        }
    }
    return reportDetails;
}

From source file:com.mycompany.rproject.runnableClass.java

public static void use() throws IOException {
    AWSCredentials awsCreds = new PropertiesCredentials(
            new File("/Users/paulamontojo/Desktop/AwsCredentials.properties"));

    AmazonSQS sqs = new AmazonSQSClient(awsCreds);

    Region usWest2 = Region.getRegion(Regions.US_WEST_2);
    sqs.setRegion(usWest2);/*  ww w .j av a 2  s  .  c om*/
    String myQueueUrl = "https://sqs.us-west-2.amazonaws.com/711690152696/MyQueue";

    System.out.println("Receiving messages from MyQueue.\n");

    ReceiveMessageRequest receiveMessageRequest = new ReceiveMessageRequest(myQueueUrl);
    List<Message> messages = sqs.receiveMessage(receiveMessageRequest).getMessages();
    while (messages.isEmpty()) {

        messages = sqs.receiveMessage(receiveMessageRequest).getMessages();
    }

    String messageRecieptHandle = messages.get(0).getReceiptHandle();

    String a = messages.get(0).getBody();

    sqs.deleteMessage(new DeleteMessageRequest(myQueueUrl, messageRecieptHandle));

    //aqui opero y cuando acabe llamo para operar el siguiente.

    String n = "";
    String dbName = "mydb";
    String userName = "pmontojo";
    String password = "pmontojo";
    String hostname = "mydb.cued7orr1q2t.us-west-2.rds.amazonaws.com";
    String port = "3306";
    String jdbcUrl = "jdbc:mysql://" + hostname + ":" + port + "/" + dbName + "?user=" + userName + "&password="
            + password;
    Connection conn = null;
    Statement setupStatement = null;
    Statement readStatement = null;
    ResultSet resultSet = null;
    String results = "";
    int numresults = 0;
    String statement = null;

    try {

        conn = DriverManager.getConnection(jdbcUrl);

        setupStatement = conn.createStatement();

        String insertUrl = "select video_name from metadata where id = " + a + ";";
        String checkUrl = "select url from metadata where id = " + a + ";";

        ResultSet rs = setupStatement.executeQuery(insertUrl);

        rs.next();

        System.out.println("este es el resultdo " + rs.getString(1));

        String names = rs.getString(1);
        ResultSet ch = setupStatement.executeQuery(checkUrl);
        ch.next();
        System.out.println("este es la url" + ch.getString(1));
        String urli = ch.getString(1);

        while (urli == null) {
            ResultSet sh = setupStatement.executeQuery(checkUrl);
            sh.next();
            System.out.println("este es la url" + sh.getString(1));
            urli = sh.getString(1);

        }
        setupStatement.close();
        AmazonS3 s3Client = new AmazonS3Client(awsCreds);

        S3Object object = s3Client.getObject(new GetObjectRequest(bucketName, names));

        IOUtils.copy(object.getObjectContent(),
                new FileOutputStream(new File("/Users/paulamontojo/Desktop/download.avi")));

        putOutput();
        write();
        putInDb(sbu.toString(), a);

    } catch (SQLException ex) {
        // handle any errors
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());
    } finally {
        System.out.println("Closing the connection.");
        if (conn != null)
            try {
                conn.close();
            } catch (SQLException ignore) {
            }
    }

    use();

}

From source file:com.hangum.tadpole.engine.sql.util.OracleObjectCompileUtils.java

public static String viewCompile(TableDAO viewDao, UserDBDAO userDB) throws Exception {
    String sqlQuery = "ALTER VIEW " + viewDao.getFullName() + " COMPILE "; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

    java.sql.Connection javaConn = null;
    Statement statement = null;
    ResultSet rs = null;/* w ww .  ja  v  a  2  s  .  co m*/
    try {
        SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
        javaConn = client.getDataSource().getConnection();

        statement = javaConn.createStatement();
        statement.execute(sqlQuery);
        //? all_errors syscat ? . sys.all_errors, syscat.all_errors  ??  ? ? ? . 
        sqlQuery = "Select * From all_Errors where owner = '" + viewDao.getSchema_name() + "' and name='" //$NON-NLS-1$//$NON-NLS-2$
                + viewDao.getName() + "' and type = 'VIEW' order by type, sequence ";
        rs = statement.executeQuery(sqlQuery);

        StringBuffer result = new StringBuffer();
        while (rs.next()) {
            result.append(prettyMsg(rs.getString("line"), rs.getString("position"), rs.getString("text"))); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        }

        return result.toString();
    } finally {
        try {
            rs.close();
        } catch (Exception e) {
        }
        try {
            statement.close();
        } catch (Exception e) {
        }
        try {
            javaConn.close();
        } catch (Exception e) {
        }
    }
}