Example usage for java.sql PreparedStatement executeQuery

List of usage examples for java.sql PreparedStatement executeQuery

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. executeQuery

Introduction

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

Prototype

ResultSet executeQuery() throws SQLException;

Source Link

Document

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Usage

From source file:de.erdesignerng.dialect.msaccess.MSAccessFileFormat.java

private static int getTableCount(Connection aConnection, String aTableName) throws SQLException {

    String theColumnName = "theCount";
    short theResult = 0;
    String theSQL = "SELECT Count(MSysObjects.Id) AS " + theColumnName + " " + "FROM MSysObjects "
            + "WHERE (MSysObjects.Name LIKE ?);";

    PreparedStatement theStatement = aConnection.prepareStatement(theSQL);
    theStatement.setString(1, aTableName);

    ResultSet theIdentificationResult = theStatement.executeQuery();

    if (theIdentificationResult != null) {
        if (theIdentificationResult.next()) {
            theResult = theIdentificationResult.getShort(theColumnName);
        }/*ww  w .  j a  v a 2s .  com*/

        theIdentificationResult.close();
    }

    return theResult;

}

From source file:com.chaosinmotion.securechat.server.commands.Devices.java

/**
 * Return the list of device identifiers associated with this account.
 * @param userinfo/*from  w w  w. j  av  a2  s. c  om*/
 * @param requestParams
 * @return
 * @throws IOException 
 * @throws SQLException 
 * @throws ClassNotFoundException 
 */
public static ReturnResult processRequest(Login.UserInfo userinfo, JSONObject requestParams)
        throws ClassNotFoundException, SQLException, IOException {
    String username = requestParams.getString("username");

    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        c = Database.get();

        /*
         *    Get user ID
         */

        ps = c.prepareStatement("SELECT userid " + "FROM Users " + "WHERE username = ?");
        ps.setString(1, username);
        rs = ps.executeQuery();

        int userid = 0;
        if (rs.next()) {
            userid = rs.getInt(1);
        } else {
            return new ReturnResult(Errors.ERROR_UNKNOWNUSER, "Unknown user");
        }
        rs.close();
        rs = null;
        ps.close();
        ps = null;

        /*
         * Get devices
         */
        ps = c.prepareStatement("SELECT Devices.deviceuuid, Devices.publickey " + "FROM Devices, Users "
                + "WHERE Users.userid = Devices.userid " + "AND Users.username = ?");
        ps.setString(1, username);
        rs = ps.executeQuery();

        DeviceReturnResult drr = new DeviceReturnResult(userid);
        while (rs.next()) {
            drr.addDeviceUUID(rs.getString(1), rs.getString(2));
        }
        return drr;
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}

From source file:com.concursive.connect.web.modules.upgrade.utils.UpgradeUtils.java

/**
 * Queries the database to see if the script has already been executed
 *
 * @param db      Description of the Parameter
 * @param version Description of the Parameter
 * @return The installed value//from w w  w  . ja  v  a 2  s .  c  o  m
 * @throws java.sql.SQLException Description of the Exception
 */
public static boolean isInstalled(Connection db, String version) throws SQLException {
    boolean isInstalled = false;
    // Query the installed version
    PreparedStatement pst = db.prepareStatement(
            "SELECT script_version " + "FROM database_version " + "WHERE script_version = ? ");
    pst.setString(1, version);
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        isInstalled = true;
    }
    rs.close();
    pst.close();
    return isInstalled;
}

From source file:mangotiger.sql.SQL.java

public static List<Object> list(final Connection connection, final String sql, final Object[] parameters)
        throws SQLException {
    ResultSet resultSet = null;/*from  ww  w.ja v  a2 s  .  c  o  m*/
    PreparedStatement statement = null;
    try {
        final List<Object> list = new ArrayList<Object>();
        statement = newPreparedStatement(connection, sql, parameters);
        resultSet = statement.executeQuery();
        final ResultSetMetaData metaData = resultSet.getMetaData();
        final int columnCount = metaData.getColumnCount();
        while (resultSet.next()) {
            switch (columnCount) {
            case 1:
                list.add(resultSet.getObject(1));
                break;
            default:
                final Object[] row = new Object[columnCount];
                for (int i = 0; i < columnCount; ++i) {
                    row[i] = resultSet.getObject(i + 1);
                }
                list.add(row);
                break;
            }
        }
        return list;
    } catch (SQLException e) {
        log().error("unable to execute: " + asString(sql, parameters), e);
        throw e;
    } finally {
        close(statement, resultSet);
    }
}

From source file:com.github.ibole.infrastructure.persistence.db.mybatis.pagination.SqlHelper.java

/**
 * //from ww w  .  j  av  a  2s.com
 *
 * @param mappedStatement mapped
 * @param parameterObject ?
 * @param boundSql boundSql
 * @param dialect database dialect
 * @return 
 * @throws java.sql.SQLException sql
 */
public static int getCount(String sql, final MappedStatement mappedStatement, final Transaction transaction,
        final Object parameterObject, final BoundSql boundSql, Dialect dialect) throws SQLException {
    final String countSql = dialect.getCountString(sql);
    if (logger.isDebugEnabled()) {
        logger.debug("Total count SQL [{}] ", countSql);
        logger.debug("Total count Parameters: {} ", parameterObject);
    }
    Connection connection = transaction.getConnection();
    PreparedStatement countStmt = connection.prepareStatement(countSql);
    DefaultParameterHandler handler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
    handler.setParameters(countStmt);

    ResultSet rs = countStmt.executeQuery();
    int count = 0;
    if (rs.next()) {
        count = rs.getInt(1);
    }
    if (logger.isDebugEnabled()) {
        logger.debug("Total count: {}", count);
    }
    return count;

}

From source file:com.seventh_root.ld33.common.player.Player.java

public static Player getByName(Connection databaseConnection, String playerName) throws SQLException {
    if (playersByName.containsKey(playerName))
        return playersByName.get(playerName);
    if (databaseConnection != null) {
        PreparedStatement statement = databaseConnection.prepareStatement(
                "SELECT uuid, name, password_hash, password_salt, resources FROM player WHERE name = ? LIMIT 1");
        statement.setString(1, playerName);
        ResultSet resultSet = statement.executeQuery();
        if (resultSet.next()) {
            return new Player(databaseConnection, UUID.fromString(resultSet.getString("uuid")),
                    resultSet.getString("name"), resultSet.getString("password_hash"),
                    resultSet.getString("password_salt"), resultSet.getInt("resources"));
        }// w ww.  j  a v a  2  s  . c om
    }
    return null;
}

From source file:genericepayadmin.AddIpBean.java

public static int getPages(Connection con) throws Exception {
    int totalcount = 0;
    PreparedStatement ps = null;
    ResultSet rs = null;//  ww  w .  j  a v a 2s  .c  o m
    try {
        String sql = "select ceil(count(*)/10) as totalpage from webservice_validator";
        ps = con.prepareStatement(sql);
        rs = ps.executeQuery();
        if (rs.next()) {
            totalcount = rs.getInt("totalpage");
        }
    } catch (Exception e) {
        System.out.println(e.getMessage());
    } finally {
        try {
            if (ps != null)
                ps.close();
            if (rs != null)
                rs.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    return totalcount;
}

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

public static String executeSqlCommand(final SQLInputs sqlInputs) throws Exception {
    final ConnectionService connectionService = new ConnectionService();
    try (final Connection connection = connectionService.setUpConnection(sqlInputs)) {

        connection.setReadOnly(false);//  w  w w .j  a v a 2s . co  m

        final String dbType = sqlInputs.getDbType();
        if (ORACLE_DB_TYPE.equalsIgnoreCase(dbType)
                && sqlInputs.getSqlCommand().toLowerCase().contains(DBMS_OUTPUT)) {

            final PreparedStatement preparedStatement = connection.prepareStatement(sqlInputs.getSqlCommand());
            preparedStatement.setQueryTimeout(sqlInputs.getTimeout());
            OracleDbmsOutput oracleDbmsOutput = new OracleDbmsOutput(connection);
            preparedStatement.executeQuery();
            sqlInputs.setIUpdateCount(preparedStatement.getUpdateCount());
            preparedStatement.close();
            final String output = oracleDbmsOutput.getOutput();
            oracleDbmsOutput.close();
            return output;
        } else {
            final Statement statement = connection.createStatement(sqlInputs.getResultSetType(),
                    sqlInputs.getResultSetConcurrency());
            statement.setQueryTimeout(sqlInputs.getTimeout());
            try {
                statement.execute(sqlInputs.getSqlCommand());
            } catch (SQLException e) {
                if (SYBASE_DB_TYPE.equalsIgnoreCase(dbType)) {
                    //during a dump sybase sends back status as exceptions.
                    if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dump")) {
                        return SQLUtils.processDumpException(e);
                    } else if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("load")) {
                        return SQLUtils.processLoadException(e);
                    }
                } else {
                    throw e;
                }
            }

            ResultSet rs = statement.getResultSet();
            if (rs != null) {
                ResultSetMetaData rsMtd = rs.getMetaData();
                if (rsMtd != null) {
                    sqlInputs.getLRows().clear();
                    int colCount = rsMtd.getColumnCount();

                    if (sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) {
                        while (rs.next()) {
                            if (colCount >= 4) {
                                sqlInputs.getLRows().add(rs.getString(4));
                            }
                        }
                    } else {
                        String delimiter = (StringUtils.isNoneEmpty(sqlInputs.getStrDelim()))
                                ? sqlInputs.getStrDelim()
                                : ",";
                        String strRowHolder;
                        while (rs.next()) {
                            strRowHolder = "";
                            for (int i = 1; i <= colCount; i++) {
                                if (i > 1) {
                                    strRowHolder += delimiter;
                                }
                                strRowHolder += rs.getString(i);
                            }
                            sqlInputs.getLRows().add(strRowHolder);
                        }
                    }
                    rs.close();
                }

            }
            //For sybase, when dbcc command is executed, the result is shown in warning message
            else if (dbType.equalsIgnoreCase(SYBASE_DB_TYPE)
                    && sqlInputs.getSqlCommand().trim().toLowerCase().startsWith("dbcc")) {
                SQLWarning warning = statement.getWarnings();
                while (warning != null) {
                    sqlInputs.getLRows().add(warning.getMessage());
                    warning = warning.getNextWarning();
                }
            }

            sqlInputs.setIUpdateCount(statement.getUpdateCount());
        }
    }
    return "Command completed successfully";
}

From source file:mangotiger.sql.SQL.java

public static Map<String, List<Object>> map(final Connection connection, final String sql,
        final Object[] parameters) throws SQLException {
    ResultSet resultSet = null;/*from w  ww  . jav a2s. co  m*/
    PreparedStatement statement = null;
    try {
        final Map<String, List<Object>> map = new TreeMap<String, List<Object>>();
        statement = newPreparedStatement(connection, sql, parameters);
        resultSet = statement.executeQuery();
        final ResultSetMetaData metaData = resultSet.getMetaData();
        final int columnCount = metaData.getColumnCount();
        for (int i = 0; i < columnCount; ++i) {
            final String columnName = metaData.getColumnName(i);
            map.put(columnName, new ArrayList<Object>());
        }
        while (resultSet.next()) {
            for (int i = 0; i < columnCount; ++i) {
                final String columnName = metaData.getColumnName(i);
                map.get(columnName).add(resultSet.getObject(1));
            }
        }
        return map;
    } catch (SQLException e) {
        log().error("unable to execute: " + asString(sql, parameters), e);
        throw e;
    } finally {
        close(statement, resultSet);
    }
}

From source file:com.seventh_root.ld33.common.player.Player.java

public static Player getByUUID(Connection databaseConnection, UUID uuid) throws SQLException {
    if (playersByUUID.containsKey(uuid.toString()))
        return playersByUUID.get(uuid.toString());
    if (databaseConnection != null) {
        PreparedStatement statement = databaseConnection.prepareStatement(
                "SELECT uuid, name, password_hash, password_salt, resources FROM player WHERE uuid = ? LIMIT 1");
        statement.setString(1, uuid.toString());
        ResultSet resultSet = statement.executeQuery();
        if (resultSet.next()) {
            Player player = new Player(databaseConnection, UUID.fromString(resultSet.getString("uuid")),
                    resultSet.getString("name"), resultSet.getString("password_hash"),
                    resultSet.getString("password_salt"), resultSet.getInt("resources"));
            cachePlayer(player);//from  ww  w  .j av a 2s . c o m
            return player;
        }
    }
    return null;
}