Example usage for java.sql PreparedStatement getResultSet

List of usage examples for java.sql PreparedStatement getResultSet

Introduction

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

Prototype

ResultSet getResultSet() throws SQLException;

Source Link

Document

Retrieves the current result as a ResultSet object.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection conn = DriverManager
            .getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"
                    + "Dbq=C://Book1.xlsx;");

    PreparedStatement s = conn.prepareStatement("SELECT * FROM [Sheet1$] WHERE [MetricMonth] = ?");
    s.setString(1, "Jul-2013");
    s.execute();/*  w ww .  j a  va 2s. c  o  m*/
    ResultSet rs = s.getResultSet();
    if (rs != null) {
        while (rs.next()) {
            System.out.println(rs.getInt("All"));
        }
    }
    s.close();

    conn.close();
}

From source file:com.l2jserver.model.template.NPCTemplateConverter.java

public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException, JAXBException {
    controllers.put("L2Teleporter", TeleporterController.class);
    controllers.put("L2CastleTeleporter", TeleporterController.class);
    controllers.put("L2Npc", BaseNPCController.class);
    controllers.put("L2Monster", MonsterController.class);
    controllers.put("L2FlyMonster", MonsterController.class);
    Class.forName("com.mysql.jdbc.Driver");

    final File target = new File("generated/template/npc");

    System.out.println("Scaning legacy HTML files...");
    htmlScannedFiles = FileUtils.listFiles(L2J_HTML_FOLDER, new String[] { "html", "htm" }, true);

    final JAXBContext c = JAXBContext.newInstance(NPCTemplate.class, Teleports.class);

    final Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
    {//from w w  w.  j  a v a2  s.c o m
        System.out.println("Converting teleport templates...");
        teleportation.teleport = CollectionFactory.newList();

        final Marshaller m = c.createMarshaller();
        m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);

        final PreparedStatement st = conn.prepareStatement("SELECT * FROM teleport");
        st.execute();
        final ResultSet rs = st.getResultSet();
        while (rs.next()) {
            final TeleportationTemplate template = new TeleportationTemplate();

            template.id = new TeleportationTemplateID(rs.getInt("id"), null);
            template.name = rs.getString("Description");
            TemplateCoordinate coord = new TemplateCoordinate();
            coord.x = rs.getInt("loc_x");
            coord.y = rs.getInt("loc_y");
            coord.z = rs.getInt("loc_z");
            template.point = coord;
            template.price = rs.getInt("price");
            template.item = rs.getInt("itemId");
            if (rs.getBoolean("fornoble")) {
                template.restrictions = new Restrictions();
                template.restrictions.restriction = Arrays.asList("NOBLE");
            }
            teleportation.teleport.add(template);
        }
        m.marshal(teleportation, getXMLSerializer(new FileOutputStream(new File(target, "../teleports.xml"))));
        // System.exit(0);
    }

    System.out.println("Generating template XML files...");
    // c.generateSchema(new SchemaOutputResolver() {
    // @Override
    // public Result createOutput(String namespaceUri,
    // String suggestedFileName) throws IOException {
    // // System.out.println(new File(target, suggestedFileName));
    // // return null;
    // return new StreamResult(new File(target, suggestedFileName));
    // }
    // });

    try {
        final Marshaller m = c.createMarshaller();
        m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);

        final PreparedStatement st = conn.prepareStatement(
                "SELECT npc.*, npcskills.level AS race " + "FROM npc " + "LEFT JOIN npcskills "
                        + "ON(npc.idTemplate = npcskills.npcid AND npcskills.skillid = ?)");
        st.setInt(1, 4416);
        st.execute();
        final ResultSet rs = st.getResultSet();
        while (rs.next()) {
            Object[] result = fillNPC(rs);
            NPCTemplate t = (NPCTemplate) result[0];
            String type = (String) result[1];

            String folder = createFolder(type);
            if (folder.isEmpty()) {
                m.setProperty(Marshaller.JAXB_SCHEMA_LOCATION, "npc ../npc.xsd");
            } else {
                m.setProperty(Marshaller.JAXB_SCHEMA_LOCATION, "npc ../../npc.xsd");
            }

            final File file = new File(target, "npc/" + folder + "/" + t.getID().getID()
                    + (t.getInfo().getName() != null ? "-" + camelCase(t.getInfo().getName().getValue()) : "")
                    + ".xml");
            file.getParentFile().mkdirs();
            templates.add(t);

            try {
                m.marshal(t, getXMLSerializer(new FileOutputStream(file)));
            } catch (MarshalException e) {
                System.err.println("Could not generate XML template file for "
                        + t.getInfo().getName().getValue() + " - " + t.getID());
                file.delete();
            }
        }

        System.out.println("Generated " + templates.size() + " templates");

        System.gc();
        System.out.println("Free: " + FileUtils.byteCountToDisplaySize(Runtime.getRuntime().freeMemory()));
        System.out.println("Total: " + FileUtils.byteCountToDisplaySize(Runtime.getRuntime().totalMemory()));
        System.out.println("Used: " + FileUtils.byteCountToDisplaySize(
                Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory()));
        System.out.println("Max: " + FileUtils.byteCountToDisplaySize(Runtime.getRuntime().maxMemory()));
    } finally {
        conn.close();
    }
}

From source file:Main.java

public static long[] getEntryExit(Double id, Calendar date, Connection con, PreparedStatement stmt)
        throws Exception {
    stmt.setDate(1, new java.sql.Date(date.getTimeInMillis()));
    // stmt.setDate(2, new java.sql.Date(date.getTimeInMillis()+1000000));
    stmt.execute();//w w w. j  av  a2s.  c  o  m
    ResultSet rs = stmt.getResultSet();
    if (rs != null) {

        if (rs.next()) {
            Timestamp d1 = rs.getTimestamp(1);
            Timestamp d2 = rs.getTimestamp(2);
            if (d1 != null && d2 != null) {
                System.out.println(id + ":" + new SimpleDateFormat("dd/MM/yyyy").format(date.getTime()) + ":"
                        + d1.toString());
                long[] res = new long[] { d1.getTime(), d2.getTime() };
                return res;
            }
        }
        rs.close();
    }
    return null;
}

From source file:com.sapienter.jbilling.tools.ConvertToBinHexa.java

private static ResultSet getUserRowsToUpdate() throws SQLException {
    PreparedStatement stmt = connection.prepareStatement("SELECT u.ID, u.password"
            + "  FROM base_user u, user_role_map r " + " where u.id = r.user_id " + "   and r.role_id < 3 ");
    stmt.execute();//  ww w .ja  va 2s . c o m
    return stmt.getResultSet();
}

From source file:com.sapienter.jbilling.tools.ConvertToBinHexa.java

private static ResultSet getCCRowsToUpdate() throws SQLException {
    PreparedStatement stmt = connection.prepareStatement("SELECT c.ID, c.name, c.cc_number, m.user_id"
            + "  FROM credit_card c, user_credit_card_map m " + " WHERE c.id = m.credit_card_id");
    stmt.execute();/* w  w w .j a va2s. c  o m*/
    return stmt.getResultSet();
}

From source file:com.dynamobi.ws.util.DB.java

@SuppressWarnings(value = { "unchecked" })
public static <T extends DBLoader> void execute(String query, T obj, List<T> list) {
    Connection conn = null;/*from   w  w w  .  j  a va2 s .c  o  m*/
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        conn = getConnection();
        ps = conn.prepareStatement(query);
        ps.setMaxRows(0);
        if (ps.execute()) {
            rs = ps.getResultSet();
        }

        while (rs != null && rs.next()) {
            obj.loadRow(rs);
            if (list != null) {
                list.add((T) obj.copy());
            }
        }
        obj.finalize();
    } catch (SQLException ex) {
        obj.exception(ex);
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        if (conn != null) {
            releaseConnection();
        }
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException ex1) {
            ex1.printStackTrace();
        }
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex3) {
            ex3.printStackTrace();
        }
    }
}

From source file:org.openmrs.web.filter.util.FilterUtil.java

/**
 * This method uses passed in connection to load system default locale. If connection is passed
 * as null it creates separate connection that should be closed before return from method
 *
 * @param connection (optional) the jdbc connection to be used for extracting default locale
 * @return the string that contains system default locale or null
 *///from www .  j  a v  a2 s .co  m
public static String readSystemDefaultLocale(Connection connection) {
    String systemDefaultLocale = null;
    Boolean needToCloseConection = false;
    try {
        if (connection == null) {
            connection = DatabaseUpdater.getConnection();
            needToCloseConection = true;
        }
        String select = "select property_value from global_property where property = ?";
        PreparedStatement statement = connection.prepareStatement(select);
        statement.setString(1, OpenmrsConstants.GLOBAL_PROPERTY_DEFAULT_LOCALE);
        if (statement.execute()) {
            ResultSet results = statement.getResultSet();
            if (results.next()) {
                systemDefaultLocale = results.getString(1);
            }
        }
    } catch (Exception e) {
        log.error("Error while retrieving system default locale", e);
    } finally {
        if (needToCloseConection && connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                log.debug("Error while closing the database", e);
            }
        }
    }
    return systemDefaultLocale;
}

From source file:com.freemedforms.openreact.db.DbSchema.java

/**
 * Determine if a patch has been applied yet.
 * /*ww  w  .  j a v a  2 s .  c  o  m*/
 * @param patchName
 * @return Success.
 */
public static boolean isPatchApplied(String patchName) {
    Connection c = Configuration.getConnection();

    int found = 0;

    PreparedStatement cStmt = null;
    try {
        cStmt = c.prepareStatement("SELECT COUNT(*) FROM tPatch " + " WHERE patchName = ? " + ";");
        cStmt.setString(1, patchName);

        boolean hadResults = cStmt.execute();
        if (hadResults) {
            ResultSet rs = cStmt.getResultSet();
            rs.next();
            found = rs.getInt(1);
            rs.close();
        }
    } catch (NullPointerException npe) {
        log.error("Caught NullPointerException", npe);
    } catch (Throwable e) {
    } finally {
        DbUtil.closeSafely(cStmt);
        DbUtil.closeSafely(c);
    }

    return (boolean) (found > 0);
}

From source file:org.openmrs.web.filter.util.FilterUtil.java

/**
 * This is a utility method that can be used for retrieving user id by given user name and sql
 * connection//from  www.  j av a  2 s  . c  o m
 *
 * @param userNameOrSystemId the name of user
 * @param connection the java sql connection to use
 * @return not null id of given user in case of success or null otherwise
 * @throws SQLException
 */
public static Integer getUserIdByName(String userNameOrSystemId, Connection connection) throws SQLException {

    String select = "select user_id from users where system_id = ? or username = ?";
    PreparedStatement statement = connection.prepareStatement(select);
    statement.setString(1, userNameOrSystemId);
    statement.setString(2, userNameOrSystemId);
    Integer userId = null;
    if (statement.execute()) {
        ResultSet results = statement.getResultSet();
        if (results.next()) {
            userId = results.getInt(1);
        }
    }
    return userId;
}

From source file:com.l2jserver.model.template.NPCTemplateConverter.java

private static Skills fillSkillList(final ObjectFactory factory, ResultSet npcRs, int npcId)
        throws SQLException {
    final Connection conn = npcRs.getStatement().getConnection();
    final Skills skills = factory.createNPCTemplateSkills();

    final PreparedStatement st = conn.prepareStatement("SELECT * FROM npcskills WHERE npcid = ?");
    st.setInt(1, npcId);//w w w  . ja v a2 s. com
    st.execute();
    final ResultSet rs = st.getResultSet();
    while (rs.next()) {
        Skills.Skill s = factory.createNPCTemplateSkillsSkill();
        s.setId(new SkillTemplateID(rs.getInt("skillid"), null));
        s.setLevel(rs.getInt("level"));
        skills.getSkill().add(s);
    }
    if (skills.getSkill().size() == 0)
        return null;
    return skills;
}