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.springsource.insight.plugin.jdbc.PoolingConnectionTest.java

@Test
public void testOperationCollection() throws SQLException {
    DataSourceConnectionFactory connFactory = new DataSourceConnectionFactory(dataSource);
    ObjectPool connPool = new GenericObjectPool();
    PoolableConnectionFactory poolFactory = new PoolableConnectionFactory(connFactory, connPool, null, null,
            false, true);/* ww w  . j  a  v  a  2s.  c  o m*/
    PoolingDataSource poolDs = new PoolingDataSource(poolFactory.getPool());
    String sql = "select * from appointment where owner = 'Agim'";
    Connection c = poolDs.getConnection();
    try {
        PreparedStatement ps = c.prepareStatement(sql);
        try {
            System.out.println("Prepared statement=" + ps.getClass());

            ResultSet rs = ps.executeQuery();
            rs.close();
        } finally {
            ps.close();
        }
    } finally {
        c.close();
    }

    ArgumentCaptor<Operation> opCaptor = ArgumentCaptor.forClass(Operation.class);
    verify(spiedOperationCollector, times(3)).enter(opCaptor.capture());

    List<Operation> ops = opCaptor.getAllValues();
    assertEquals("Mismatched number of operations", 3, ops.size());
    assertSourceCodeLocation("top-op", ops.get(0),
            "org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper", "prepareStatement");
    assertSourceCodeLocation("mid-op", ops.get(1), "org.apache.commons.dbcp.DelegatingConnection",
            "prepareStatement");
    assertSourceCodeLocation("bottom-op", ops.get(2), "org.hsqldb.jdbc.jdbcConnection", "prepareStatement");
}

From source file:com.uiip.gviviani.esercizioweekend.interfaces.impl.DefaultPersonDAO.java

@Override
public PersonModel getPersonInfo(String numero) {
    PersonModel personModel = new PersonModel();
    PhoneModel phone = new PhoneModel();
    MysqlDataSource datasource = new MysqlDataSource();
    datasource.setUser("root");
    datasource.setPassword("root");
    datasource.setUrl("jdbc:mysql://localhost:3306/Rubrica");
    Connection connection = null;
    try {//from  w w w.  j  ava2 s  . co  m
        connection = datasource.getConnection();
        String sql = "SELECT c.nome, c.cognome, c.data_nascita, t.name "
                + "FROM contatti c INNER JOIN telefono t ON (c.modello = t.id)" + "WHERE c.numero = ? ;";
        PreparedStatement stat = connection.prepareStatement(sql);
        stat.setString(1, numero);
        ResultSet res = stat.executeQuery();
        if (res.first()) {
            personModel.setNome(res.getString("nome"));
            personModel.setCognome(res.getString("cognome"));
            personModel.setData(res.getString("data_nascita"));
            personModel.setNumero(numero);
            phone.setNome(res.getString("name"));
            personModel.setModel(phone);
        } else {
            personModel = null;
        }
    } catch (SQLException e) {
        logger.error(e);
        personModel = null;
    } finally {
        DbUtils.closeQuietly(connection);
    }
    return personModel;
}

From source file:minor.register.RegisterAction.java

@Override
public void validate() {
    if (StringUtils.isEmpty(getUname())) {
        addFieldError("uname", "Username cannot be blank.");
    }/*from   w w  w .  j a va  2s  . c o m*/
    if (StringUtils.isEmpty(getUname())) {
        addFieldError("name", "Name cannot be blank.");
    }
    if (StringUtils.isEmpty(getUname())) {
        addFieldError("password", "Password cannot be blank.");
    }
    if (StringUtils.isEmpty(getCpassword())) {
        addFieldError("cpassword", "Confirm Password cannot be blank.");
    }
    if (StringUtils.isEmpty(getEmail())) {
        addFieldError("email", "Email cannot be blank");
    }
    if (!StringUtils.equals(getPassword(), getCpassword())) {
        addFieldError("cpassword", "Passwords don't match.");
    }
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Minor", "root", "");

        PreparedStatement ps = con.prepareStatement("select * from userinfo where username=?");
        ps.setString(1, uname);

        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            addFieldError("uname", "Username already taken.");
        }
        con.close();
    } catch (ClassNotFoundException e) {
        System.out.println(e);
    } catch (SQLException e) {
        System.out.println(e);
    }
}

From source file:id.go.kemdikbud.tandajasa.dao.PegawaiDaoTest.java

@Test
public void testInsert() throws Exception {
    Golongan g = new Golongan();
    g.setId(99);//from   ww w .java2  s  . co  m

    Pegawai p = new Pegawai();
    p.setNip("123");
    p.setNama("Endy Muhardin");
    p.setGolongan(g);

    DataSource ds = ctx.getBean(DataSource.class);
    Connection koneksiDatabase = ds.getConnection();

    PreparedStatement ps = koneksiDatabase.prepareStatement("select count(*) as jumlah from pegawai");
    ResultSet rsSebelum = ps.executeQuery();
    Assert.assertTrue(rsSebelum.next());
    Long jumlahRecordSebelum = rsSebelum.getLong(1);
    rsSebelum.close();

    PegawaiDao pd = (PegawaiDao) ctx.getBean("pegawaiDao");
    pd.save(p);

    ResultSet rsSetelah = ps.executeQuery();
    Assert.assertTrue(rsSetelah.next());
    Long jumlahRecordSetelah = rsSetelah.getLong("jumlah");
    rsSetelah.close();

    koneksiDatabase.close();
    Assert.assertEquals(new Long(jumlahRecordSebelum + 1), new Long(jumlahRecordSetelah));
}

From source file:dao.GeoTargetQuery.java

/**
 * This method lists all the results for the geotarget text from directories
 * @param conn the connection//from ww  w.ja v a  2 s .com
 * @param collabrumId the collabrumid
 * @return HashSet the set that has the list of moderators for these collabrums.
 * @throws BaseDaoException - when error occurs
 **/
public List run(Connection conn, String ipAddress) throws BaseDaoException {

    if (RegexStrUtil.isNull(ipAddress) || conn == null) {
        return null;
    }

    List ip = GlobalConst.httputil.parseIP4(ipAddress);
    StringBuffer iptable = new StringBuffer("ip4_");
    iptable.append(ip.get(0));

    int b = new Integer((String) ip.get(1)).intValue();
    int c = new Integer((String) ip.get(2)).intValue();

    String sb = "select state from cityByCountry as geo, " + iptable.toString() + " as iptable where b=" + b
            + " and c=" + c
            + " and geo.country=iptable.country and geo.city=iptable.city and (geo.state like '%New York%' or geo.state like '%California%') limit 1";
    try {
        PreparedStatement stmt = conn.prepareStatement(sb);
        if (stmt == null) {
            return null;
        }

        ResultSet rs = stmt.executeQuery();

        Vector columnNames = null;
        Userpage userpage = null;
        List pendingList = new ArrayList();

        if (rs != null) {
            columnNames = dbutils.getColumnNames(rs);
        } else {
            return null;
        }

        while (rs.next()) {
            userpage = (Userpage) eop.newObject(DbConstants.USER_PAGE);
            for (int j = 0; j < columnNames.size(); j++) {
                userpage.setValue((String) columnNames.elementAt(j),
                        (String) rs.getString((String) columnNames.elementAt(j)));
            }
            pendingList.add(userpage);
        }
        return pendingList;
    } catch (Exception e) {
        throw new BaseDaoException("Error occured while executing geotarget run query ", e);
    }
}

From source file:MyPack.AjaxFriendList.java

/**
 * Handles the HTTP <code>GET</code> method.
 *
 * @param request servlet request/*  www  .  j  av  a2s . com*/
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    processRequest(request, response);

    response.setContentType("text/html;charset=UTF-8");
    response.setHeader("Cache-control", "no-cache, no-store");
    response.setHeader("Pragma", "no-cache");
    response.setHeader("Expires", "-1");
    HttpSession s = request.getSession(true);
    try (PrintWriter out = response.getWriter()) {
        //getting AJAX parameter
        String query = request.getParameter("term");
        //Creating JSONArray Object
        JSONArray arrayObj = new JSONArray();
        //Changing Case of AJAX parameter
        query = query.toLowerCase();

        try {
            Connection con = ConnectionClass.getConnected();
            PreparedStatement p = con
                    .prepareStatement("select name from friend where name LIKE '" + query + "%'");
            ResultSet rs = p.executeQuery();
            while (rs.next() == true) {
                arrayObj.put(rs.getString(1));
            }
            out.println(arrayObj.toString());
            out.close();
        } catch (SQLException ea) {
        }
    }
}

From source file:fr.mael.microrss.ttrss.TTRssImporter.java

private Map<Integer, Feed> addFeeds(Connection connection, Map<Integer, Category> categories)
        throws SQLException {
    Map<Integer, Feed> feeds = new HashMap<Integer, Feed>();
    PreparedStatement statement = connection.prepareStatement("SELECT * FROM ttrss_feeds");
    ResultSet rs = null;//  w  w w  .j  a v  a2  s.  c o  m
    try {
        rs = statement.executeQuery();
        while (rs.next()) {
            Feed feed = new Feed();
            //            feed.setCategory(categories.get(rs.getInt("cat_id")));
            //            feed.setTitle(rs.getString("title"));
            //            feed.setUser(securityUtil.getCurrentUser());
            feed.setUrl(rs.getString("feed_url"));
            feed = feedService.save(feed);
            feeds.put(rs.getInt("id"), feed);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        rs.close();
    }
    return feeds;
}

From source file:com.uiip.gviviani.esercizioweekend.interfaces.impl.DefaultPersonDAO.java

@Override
public boolean inserisciPerson(PersonModel person, String nomeTel) {
    MysqlDataSource datasource = new MysqlDataSource();
    datasource.setUser("root");
    datasource.setPassword("root");
    datasource.setUrl("jdbc:mysql://localhost:3306/Rubrica");
    Connection connection = null;
    try {// ww  w. j  a  va 2 s  .  com
        connection = datasource.getConnection();
        String sql = "INSERT INTO contatti (nome, cognome, data_nascita, numero, modello) VALUE "
                + "(?, ?, ?, ?, ?);";
        int id;
        String sql2 = "SELECT id FROM telefono WHERE name = ? ;";
        PreparedStatement stat2 = connection.prepareStatement(sql2);
        stat2.setString(1, nomeTel);
        ResultSet res = stat2.executeQuery();
        if (res.first()) {
            id = res.getInt("id");
            PreparedStatement stat = connection.prepareStatement(sql);
            stat.setString(1, person.getNome());
            stat.setString(2, person.getCognome());
            stat.setString(3, person.getData());
            stat.setString(4, person.getNumero());
            stat.setInt(5, id);
            if (stat.executeUpdate() > 0) {
                return true;
            }
        }
    } catch (SQLException e) {
        logger.error(e);
    } finally {
        DbUtils.closeQuietly(connection);
    }
    return false;
}

From source file:mx.com.pixup.portal.dao.MunicipioGenerateDaoJdbc.java

public void generateXML(int idMunicipioPasado) {

    //querys//from w  w  w .  j a va  2  s. co m
    String sql = "SELECT * from municipio WHERE id = ?";

    try {
        //seccion de preparacion de la query
        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, idMunicipioPasado);
        ResultSet resultSet = preparedStatement.executeQuery();
        //seccion de nodo raiz

        resultSet.next();
        /*while (resultSet.next()) {
                
        //aqu se hace la magia para el XML
        }*/
        Element municipio = new Element("municipio");
        Attribute id = new Attribute("id", Integer.toString(resultSet.getInt("id")));

        this.xmlLogico.setRootElement(municipio);
        municipio.setAttribute(id);

        //Elementos de 2do nivel
        Element nombre = new Element("nombre");
        nombre.setText(resultSet.getString("nombre"));
        Element id_estado = new Element("id_estado");
        id_estado.setText(Integer.toString(resultSet.getInt("id_estado")));

        municipio.addContent(nombre);
        municipio.addContent(id_estado);

        //se genera el xml fsico
        this.xmlFisico.setFormat(Format.getRawFormat());
        this.xmlFisico.output(this.xmlLogico, this.archivoFisico);
    } catch (Exception e) {
        //*** se quit el return porque el mtodo es void
        System.out.println(e.getMessage());
    }

}

From source file:com.l2jfree.gameserver.communitybbs.Manager.ForumsBBSManager.java

/**
 *
 *//*from   w  ww. j  av  a 2 s.  c  om*/
private void load() {
    Connection con = null;
    try {
        con = L2DatabaseFactory.getInstance().getConnection(con);
        PreparedStatement statement = con.prepareStatement("SELECT forum_id FROM forums WHERE forum_type=0");
        ResultSet result = statement.executeQuery();
        while (result.next()) {
            int forumId = result.getInt("forum_id");
            Forum f = new Forum(forumId, null);
            addForum(f);
        }
        result.close();
        statement.close();
    } catch (Exception e) {
        _log.warn("data error on Forum (root): ", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}