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:com.sql.SystemErrorEmailList.java

/**
 * Gathers a list of active email addresses to send to for the email
 * for the daily crash report email./*from   ww w  .  j  a  v  a  2 s  .c o  m*/
 * 
 * @return
 */
public static List<String> getActiveEmailAddresses() {
    List<String> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT EmailAddress " + "FROM SystemErrorEmailList " + "WHERE active = 1";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            if (rs.getString("EmailAddress") != null) {
                list.add(rs.getString("EmailAddress"));
            }
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:com.sql.SystemEmail.java

/**
 * Gathers active email accounts for sending or receiving. 
 * //  w  w  w .  j  av a 2 s  . co  m
 * @return
 */
public static boolean loadEmailConnectionInformation() {
    List<SystemEmailModel> systemEmailList = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM SystemEmail WHERE active = 1";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            SystemEmailModel row = new SystemEmailModel();
            row.setId(rs.getInt("id"));
            row.setActive(rs.getInt("active"));
            row.setSection(rs.getString("section"));
            row.setEmailAddress(rs.getString("emailAddress"));
            row.setUsername(rs.getString("username"));
            row.setPassword(rs.getString("password"));
            row.setIncomingURL(rs.getString("incomingURL"));
            row.setIncomingPort(rs.getInt("incomingPort"));
            row.setIncomingProtocol(rs.getString("incomingProtocol"));
            row.setIncomingFolder(rs.getString("incomingFolder"));
            row.setOutgoingURL(rs.getString("outgoingURL"));
            row.setOutgoingPort(rs.getInt("outgoingPort"));
            row.setOutgoingProtocol(rs.getString("outgoingProtocol"));
            row.setOutgoingFolder(rs.getString("outgoingFolder"));
            systemEmailList.add(row);
        }
        Global.setSystemEmailParams(systemEmailList);
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
        return false;
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return true;
}

From source file:com.sql.EmailOutInvites.java

/**
 * Get a list of all of the email invites awaiting to be sent.
 * // w w w. j a v  a 2  s . c  o m
 * @return List EmailOutInvitesModel
 */
public static List<EmailOutInvitesModel> getQueuedEmailInvites() {
    List<EmailOutInvitesModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM EmailOutInvites";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            EmailOutInvitesModel item = new EmailOutInvitesModel();
            item.setId(rs.getInt("id"));
            item.setSection(rs.getString("Section") == null ? "" : rs.getString("Section"));
            item.setToAddress(rs.getString("TOaddress") == null ? "" : rs.getString("TOaddress"));
            item.setCcAddress(rs.getString("CCaddress") == null ? "" : rs.getString("CCaddress"));
            item.setEmailBody(rs.getString("emailBody") == null ? "" : rs.getString("emailBody"));
            item.setCaseNumber(rs.getString("caseNumber") == null ? "" : rs.getString("caseNumber"));
            item.setHearingType(rs.getString("hearingType") == null ? "" : rs.getString("hearingType"));
            item.setHearingRoomAbv(
                    rs.getString("hearingRoomAbv") == null ? "" : rs.getString("hearingRoomAbv"));
            item.setHearingDescription(
                    rs.getString("hearingDescription") == null ? "" : rs.getString("hearingDescription"));
            item.setHearingStartTime(
                    CalendarCalculation.adjustTimeZoneOffset(rs.getTimestamp("hearingStartTime")));
            item.setHearingEndTime(CalendarCalculation.adjustTimeZoneOffset(rs.getTimestamp("hearingEndTime")));
            item.setEmailSubject(rs.getString("emailSubject") == null ? "" : rs.getString("emailSubject"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:com.sql.CaseType.java

/**
 * Gathers a list of active case types for finding the proper section based 
 * on the case number./*from www  .j  av a2  s  .c o  m*/
 * 
 * @return List CaseTypeModel
 */
public static List<CaseTypeModel> getCaseTypes() {
    List<CaseTypeModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM CaseType WHERE active = 1";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            CaseTypeModel item = new CaseTypeModel();
            item.setId(rs.getInt("id"));
            item.setActive(rs.getBoolean("active"));
            item.setSection(rs.getString("Section"));
            item.setCaseType(rs.getString("caseType"));
            item.setDescription(rs.getString("Description"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:com.sql.EmailOut.java

/**
 * Gathers current emails waiting to be sent out.
 *
 * @return List (EmailOutModel)//w  w w .ja  v  a 2 s  .  c om
 */
public static List<EmailOutModel> getEmailOutQueue() {
    List<EmailOutModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM EmailOut WHERE okToSend = 1";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            EmailOutModel item = new EmailOutModel();
            item.setId(rs.getInt("id"));
            item.setSection(rs.getString("Section"));
            item.setCaseYear(rs.getString("caseYear"));
            item.setCaseType(rs.getString("caseType"));
            item.setCaseMonth(rs.getString("caseMonth"));
            item.setCaseNumber(rs.getString("caseNumber"));
            item.setTo(rs.getString("to"));
            item.setFrom(rs.getString("from"));
            item.setCc(rs.getString("cc"));
            item.setBcc(rs.getString("bcc"));
            item.setSubject(rs.getString("subject"));
            item.setBody(rs.getString("body"));
            item.setUserID(rs.getInt("UserID"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:com.firewallid.util.FISQL.java

public static void updateRowInsertIfNotExist(Connection conn, String tableName,
        Map<String, String> updateConditions, Map<String, String> fields) throws SQLException {
    /* Query *//*  w  ww  . j a  v  a  2 s . c  om*/
    String query = "SELECT " + Joiner.on(", ").join(updateConditions.keySet()) + " FROM " + tableName
            + " WHERE " + Joiner.on(" = ? AND ").join(updateConditions.keySet()) + " = ?";

    /* Execute */
    PreparedStatement pst = conn.prepareStatement(query);
    int i = 1;
    for (String value : updateConditions.values()) {
        pst.setString(i, value);
        i++;
    }
    ResultSet executeQuery = pst.executeQuery();
    if (executeQuery.next()) {
        /* Update */
        query = "UPDATE " + tableName + " SET " + Joiner.on(" = ?, ").join(fields.keySet()) + " = ? WHERE "
                + Joiner.on(" = ? AND ").join(updateConditions.keySet()) + " = ?";
        pst = conn.prepareStatement(query);
        i = 1;
        for (String value : fields.values()) {
            pst.setString(i, value);
            i++;
        }
        for (String value : updateConditions.values()) {
            pst.setString(i, value);
            i++;
        }
        pst.executeUpdate();
        return;
    }

    /* Row is not exists. Insert */
    query = "INSERT INTO " + tableName + " (" + Joiner.on(", ").join(fields.keySet()) + ", "
            + Joiner.on(", ").join(updateConditions.keySet()) + ") VALUES ("
            + StringUtils.repeat("?, ", fields.size() + updateConditions.size() - 1) + "?)";
    pst = conn.prepareStatement(query);
    i = 1;
    for (String value : fields.values()) {
        pst.setString(i, value);
        i++;
    }
    for (String value : updateConditions.values()) {
        pst.setString(i, value);
        i++;
    }
    pst.execute();
}

From source file:Emporium.Controle.ContrVpne.java

public static ArrayList<Vpne> listaVpne(String where, String nomeBD, int idCli) {

    String sql = "SELECT * FROM vpne WHERE idCliente = " + idCli + " ";
    sql = sql + where + " ;";
    Connection conn = Conexao.conectar(nomeBD);
    ArrayList<Vpne> listaVpne = new ArrayList<Vpne>();

    try {/*from www  .  jav a2  s . co m*/
        PreparedStatement valores = conn.prepareStatement(sql);
        valores.executeQuery();
        ResultSet result = (ResultSet) valores.executeQuery();

        while (result.next()) {

            int idCliente = result.getInt("idCliente");
            int idDepartamento = result.getInt("idDepartamento");
            String nomeDepto = result.getString("nomeDepartamento");

            String sro = result.getString("sro");
            String descricao = result.getString("descricao");
            String valor = result.getString("valor");

            String remetente = result.getString("remetente");
            String cnpj_remetente = result.getString("cnpj_remetente");
            String rlogradouro = result.getString("rlogradouro");
            String rnumero = result.getString("rnumero");
            String rbairro = result.getString("rbairro");
            String rcidade = result.getString("rcidade");
            String ruf = result.getString("ruf");

            String destinatario = result.getString("destinatario");
            String cpf_cnpj_dest = result.getString("cpf_cnpj_dest");
            String dlogradouro = result.getString("dlogradouro");
            String dnumero = result.getString("dnumero");
            String dbairro = result.getString("dbairro");
            String dcidade = result.getString("dcidade");
            String dcep = result.getString("dcep");
            String duf = result.getString("duf");

            String data = result.getString("data");

            Destinatario remVpne = new Destinatario(remetente, cnpj_remetente, rlogradouro, rnumero, rbairro,
                    rcidade, ruf);
            Destinatario destVpne = new Destinatario(destinatario, cpf_cnpj_dest, dlogradouro, dnumero, dbairro,
                    dcidade, dcep, duf);

            Vpne vp = new Vpne(sro, descricao, valor, idCliente, idDepartamento, nomeDepto, data, remVpne,
                    destVpne);
            listaVpne.add(vp);
        }
        valores.close();
        return listaVpne;
    } catch (SQLException e) {
        Logger.getLogger(ContrVpne.class.getName()).log(Level.WARNING, e.getMessage(), e);
        return listaVpne;
    } finally {
        Conexao.desconectar(conn);
    }
}

From source file:es.tena.foundation.util.POIUtil.java

public static void generateXLS(String tabla, String filename, Connection conn, String encoding)
        throws SQLException {
    String query = "";
    try {/*from  w  ww .j a  va 2 s . co m*/
        query = "SELECT * FROM (" + tabla + ")";
        PreparedStatement stmt = conn.prepareStatement(query);
        ResultSet rset = stmt.executeQuery();

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(filename);
        String sheetRef = sheet.getPackagePart().getPartName().getName();
        String template = "c:\\temp\\template_" + filename + ".xlsx";
        FileOutputStream os = new FileOutputStream(template);
        wb.write(os);
        os.close();

        File tmp = File.createTempFile("sheet", ".xml");
        Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), encoding);
        generate(fw, rset, encoding);
        rset.close();
        stmt.close();
        fw.close();

        FileOutputStream out = new FileOutputStream(
                "c:\\temp\\" + filename + sdf.format(calendario.getTime()) + ".xlsx");
        FileUtil.substitute(new File(template), tmp, sheetRef.substring(1), out);
        out.close();
        Logger.getLogger(POIUtil.class.getName()).log(Level.INFO, "Creado con exito {0}", filename);
    } catch (Exception ex) {
        ex.printStackTrace();
        Logger.getLogger(POIUtil.class.getName()).log(Level.SEVERE, null, query + "\n" + ex);
        System.out.println(query);
    } finally {
        conn.close();
    }
}

From source file:com.splicemachine.derby.test.framework.SpliceRoleWatcher.java

public static void executeDrop(String roleName) {
    LOG.trace("ExecuteDrop");
    Connection connection = null;
    PreparedStatement statement = null;
    try {//from  w  w w  . j a va2  s. c  om
        connection = SpliceNetConnection.getConnection();
        statement = connection.prepareStatement("select roleid from sys.sysroles where roleid = ?");
        statement.setString(1, roleName);
        ResultSet rs = statement.executeQuery();
        if (rs.next())
            connection.createStatement().execute(String.format("drop role %s", roleName));
        connection.commit();
    } catch (Exception e) {
        LOG.error("error Dropping " + e.getMessage());
        e.printStackTrace();
        throw new RuntimeException(e);
    } finally {
        DbUtils.closeQuietly(statement);
        DbUtils.commitAndCloseQuietly(connection);
    }
}

From source file:net.big_oh.common.jdbc.JdbcProxyExerciser.java

private static void exercisePreparedSelect(Connection con) throws SQLException {
    logger.info(StringUtils.center("exercise prepared select", 100, "-"));

    PreparedStatement preparedStmnt = null;
    ResultSet rs = null;//from  www .  jav  a 2s  .  c om
    try {
        preparedStmnt = con.prepareStatement("SELECT * FROM TEST_TABLE WHERE TEST_COLUMN = ?");
        preparedStmnt.setString(1, "value1");
        rs = preparedStmnt.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString("TEST_COLUMN"));
        }
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(preparedStmnt);
    }
}