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:net.sf.l2j.gameserver.datatables.NpcWalkerRoutesTable.java

public void load() {
    _routes = new FastList<L2NpcWalkerNode>();
    java.sql.Connection con = null;
    try {//w ww.j  av  a  2  s. c  o  m
        con = L2DatabaseFactory.getInstance().getConnection();
        PreparedStatement statement = con.prepareStatement(
                "SELECT route_id, npc_id, move_point, chatText, move_x, move_y, move_z, delay, running FROM walker_routes");
        ResultSet rset = statement.executeQuery();
        L2NpcWalkerNode route;
        while (rset.next()) {
            route = new L2NpcWalkerNode();
            route.setRouteId(rset.getInt("route_id"));
            route.setNpcId(rset.getInt("npc_id"));
            route.setMovePoint(rset.getString("move_point"));
            route.setChatText(rset.getString("chatText"));
            route.setMoveX(rset.getInt("move_x"));
            route.setMoveY(rset.getInt("move_y"));
            route.setMoveZ(rset.getInt("move_z"));
            route.setDelay(rset.getInt("delay"));
            route.setRunning(rset.getBoolean("running"));
            _routes.add(route);
        }
        rset.close();
        statement.close();
        _log.info("WalkerRoutesTable: Loaded " + _routes.size() + " Npc Walker Routes.");
        rset.close();
        statement.close();
    } catch (Exception e) {
        _log.fatal("WalkerRoutesTable: Error while loading Npc Walkers Routes: " + e.getMessage());
    } finally {
        try {
            con.close();
        } catch (Exception e) {
        }
    }
}

From source file:org.apache.lucene.store.jdbc.handler.AbstractFileEntryHandler.java

public long fileLength(final String name) throws IOException {
    return ((Long) jdbcTemplate.execute(table.sqlSelectSizeByName(), new PreparedStatementCallback() {
        @Override/*from  w  w w.  j a va2  s  .  c  om*/
        public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ps.setFetchSize(1);
            ps.setString(1, name);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return new Long(rs.getLong(1));
            }
            return new Long(0L);
        }
    })).longValue();
}

From source file:org.chimi.s4s.metainfo.mysql.MysqlMetaInfoDaoTest.java

private void assertData(String id, FileMetadata metadata) throws Throwable {
    Connection conn = dataSource.getConnection();
    PreparedStatement pstmt = conn.prepareStatement("select * from FILE_METADATA where FILE_METADATA_ID = ?");
    int idValue = Integer.parseInt(id);
    pstmt.setInt(1, idValue);/*from w  w  w .  ja  v  a2 s. com*/
    ResultSet rs = pstmt.executeQuery();

    assertTrue(rs.next());
    assertEquals(idValue, rs.getInt("FILE_METADATA_ID"));
    assertEquals(metadata.getServiceId(), rs.getString("SERVICE_ID"));
    assertEquals(metadata.getFileName(), rs.getString("FILE_NAME"));
    assertEquals(metadata.getLength(), rs.getLong("FILE_LENGTH"));
    assertEquals(metadata.getMimetype(), rs.getString("MIMETYPE"));
    SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
    assertEquals(format.format(metadata.getUploadTime()), format.format(rs.getTimestamp("UPLOAD_TIME")));
    assertEquals(metadata.getFileId(), rs.getString("FILE_ID"));

    rs.close();
    pstmt.close();
    conn.close();
}

From source file:org.apache.kylin.jdbc.KylinConnectionTest.java

@Test
public void testJdbcClientCalcitePropsInUrl() throws Exception {
    String sql = "select 1 as val";

    // mock client
    when(client.executeQuery(anyString(), Mockito.<List<Object>>any(), Mockito.<Map<String, String>>any()))
            .thenReturn(getMockResult());
    Map<String, String> toggles = new HashMap<>();
    Properties info = new Properties();
    info.setProperty("caseSensitive", "false");
    info.setProperty("unquotedCasing", "UNCHANGED");
    try (KylinConnection conn = getConnectionWithMockClient("jdbc:kylin:test_url/test_db", info)) {
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        try (ResultSet resultSet = preparedStatement.executeQuery()) {
            verify(client).executeQuery(eq(sql), Mockito.<List<Object>>any(),
                    argThat(new ArgumentMatcher<Map<String, String>>() {
                        @Override
                        public boolean matches(Map<String, String> argument) {
                            String propsStr = argument.get("JDBC_CLIENT_CALCITE_PROPS");
                            assertNotNull(propsStr);
                            Properties props = new Properties();
                            try {
                                props.load(new StringReader(propsStr));
                            } catch (IOException e) {
                                throw new RuntimeException(e);
                            }/*from   w w  w . j  a  va  2s  .  co  m*/
                            assertEquals("false", props.getProperty("caseSensitive"));
                            assertEquals("UNCHANGED", props.getProperty("unquotedCasing"));
                            return true;
                        }
                    }));

            assertTrue(resultSet.next());
            ResultSetMetaData metaData = resultSet.getMetaData();
            assertEquals("VAL", metaData.getColumnName(1));
            assertEquals(1, resultSet.getInt("VAL"));
        }
    }
}

From source file:de.klemp.middleware.controller.Controller.java

private static synchronized void ActiveDevicesToList() {

    deviceActive.clear();/*from w  w w  .j  a v a 2  s . c  om*/
    createDBConnection();
    try {
        PreparedStatement st = conn.prepareStatement("select * from \"OutputDevices\" ;");
        ResultSet r = st.executeQuery();
        while (r.next()) {
            String key = r.getString("class") + "," + r.getString("topic");
            deviceActive.put(key, r.getBoolean("enabled"));
        }

    } catch (SQLException e) {
        logger.error("SQL Exception in ActiveDevicesToList", e);
    }
    closeDBConnection();

}

From source file:com.expedia.edw.cache.dao.GrabberDaoJDBC.java

@Override
public Map<String, String> getData(String schemaAndDBname, String keyName, String valueName) {

    Connection conn = null;/*  w  ww . j a  va2 s. c  o m*/
    MapperDictionary mapperDictionary = new MapperDictionary();
    Map<String, String> dictionary = null;
    PreparedStatement ps = null;
    try {
        conn = dataSource.getConnection();
        ps = conn.prepareStatement("SELECT " + keyName + "," + valueName + "  FROM " + schemaAndDBname);
        dictionary = mapperDictionary.convert(ps.executeQuery());
        ps.close();

    } catch (SQLException e) {
        logger.error(e);
    } finally {

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                logger.error(e);
            }

        }
    }
    return dictionary;
}

From source file:oobbit.orm.Comments.java

/**
 * Returns one comment without User object attached to it.
 *
 * @param commentId/*w  ww  .java  2  s  .c  om*/
 *
 * @return
 *
 * @throws SQLException             PreparedStatement failed to execute
 * @throws NothingWasFoundException
 */
public Comment getOne(int commentId) throws SQLException, NothingWasFoundException {
    PreparedStatement statement = getConnection()
            .prepareStatement("SELECT * FROM `comments` WHERE `comment_id` = ?;");
    statement.setInt(1, commentId);

    ResultSet query = statement.executeQuery();

    if (query.next()) {
        Comment comment = new Comment();
        comment.parse(query);

        return comment;
    } else {
        throw new NothingWasFoundException();
    }
}

From source file:bq.jpa.demo.query.nativequery.service.NativeQueryService.java

/**
 * use jdbc directly/*  w ww  . ja  va2s.c  o  m*/
 */
public void doJDBCQuery() {
    Connection conn = null;
    PreparedStatement ps = null;

    try {
        conn = ds.getConnection();
        ps = conn.prepareStatement(EMPLOYEE_QUERY);
        ps.setLong(1, 1500);
        ResultSet rs = ps.executeQuery();

        List<Employee> employees = new ArrayList<>();
        while (rs.next()) {
            Employee employee = new Employee();
            employee.setId(rs.getInt("pk_employee"));
            employee.setName(rs.getString("name"));
            employee.setSalary(rs.getInt("salary"));
            employees.add(employee);
        }

        ResultViewer.showResult(employees, EMPLOYEE_QUERY);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            ps.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

From source file:com.assignment4.products.Pro_details.java

@GET
@Path("{id}")
@Produces(MediaType.APPLICATION_JSON)/*from ww w .  j a v  a2s . c o  m*/
public String getproduct(@PathParam("id") int id) throws SQLException {

    if (conn == null) {
        return "not connected";
    } else {
        String q = "Select * from products where product_id = ?";
        PreparedStatement ps = conn.prepareStatement(q);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        String result = "";
        JSONArray proArr = new JSONArray();
        while (rs.next()) {
            Map pm = new LinkedHashMap();
            pm.put("productID", rs.getInt("product_id"));
            pm.put("name", rs.getString("name"));
            pm.put("description", rs.getString("description"));
            pm.put("quantity", rs.getInt("quantity"));
            proArr.add(pm);
        }
        result = proArr.toString();

        return result;
    }

}

From source file:libepg.util.db.JDBCAccessorTest.java

/**
 * Test of getConnection method, of class JDBCAccessor.
 *
 *//*from  ww w .  j a  va 2 s .c  om*/
@Test
public void testGetCon() {
    try {

        LOG.info("getCon");
        JDBCAccessor instance = JDBCAccessor.getInstance();
        String url = "jdbc:sqlite::memory:";
        instance.connect(url);
        java.sql.Connection result = instance.getConnection();
        assertNotNull(result);
        LOG.debug("Connected.");

        Statement stmt = result.createStatement();
        //?
        stmt.executeUpdate("create table test1( name string, age integer )");
        LOG.debug("Made table.");

        //?
        String nameVal = "jjj888???";
        int ageVal = 778;
        String sql1 = "insert into test1 values (?,?)";
        PreparedStatement pstmt1 = result.prepareStatement(sql1);
        pstmt1.setString(1, nameVal);
        pstmt1.setInt(2, ageVal);
        pstmt1.executeUpdate();
        LOG.debug("Inserted.");

        //??
        String sql2 = "select * from test1 where name=? and age=?";
        PreparedStatement pstmt2 = result.prepareStatement(sql2);
        pstmt2.setString(1, nameVal);
        pstmt2.setInt(2, ageVal);
        ResultSet rs = pstmt2.executeQuery();
        while (rs.next()) {
            String str1 = rs.getString("name");
            int int2 = rs.getInt("age");
            System.out.println(str1);
            System.out.println(int2);
            assertEquals(nameVal, str1);
            assertEquals(ageVal, int2);
        }
        LOG.debug("Got data.");

    } catch (SQLException ex) {
        LOG.fatal(ex);
        fail();
    } finally {
        JDBCAccessor.getInstance().close();
    }
}