Example usage for org.apache.commons.dbutils QueryRunner query

List of usage examples for org.apache.commons.dbutils QueryRunner query

  1. HOME
  2. Java
  3. org.apache.commons
  4. org.apache.commons.dbutils.*
  5. QueryRunner
  6. query

Introduction

In this page you can find the example usage for org.apache.commons.dbutils QueryRunner query.

Prototype

public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException

Source Link

Document

Executes the given SELECT SQL query and returns a result object.

Usage

From source file:com.gs.obevo.dbmetadata.impl.dialects.MsSqlMetadataDialect.java

@Override
public ImmutableCollection<DaRoutine> searchExtraRoutines(final DaSchema schema, String procedureName,
        Connection conn) throws SQLException {
    String nameClause = procedureName != null ? " and ROUTINE_NAME = '" + procedureName + "'\n" : " ";

    String query = "SELECT" + "    ROUTINE_CATALOG," + "    ROUTINE_SCHEMA," + "    ROUTINE_NAME,"
            + "    SPECIFIC_NAME," + "    ROUTINE_TYPE,"
            + "    OBJECT_DEFINITION(OBJECT_ID(ROUTINE_CATALOG + '.' + ROUTINE_SCHEMA + '.' + ROUTINE_NAME)) AS ROUTINE_DEFINITION"
            + " FROM INFORMATION_SCHEMA.ROUTINES" + " WHERE ROUTINE_CATALOG = '" + schema.getName() + "'"
            + nameClause;/* w w  w  .  j  a  va  2s. com*/
    QueryRunner qr = new QueryRunner(); // using queryRunner so that we can reuse the connection
    ImmutableList<Map<String, Object>> maps = ListAdapter.adapt(qr.query(conn, query, new MapListHandler()))
            .toImmutable();

    return maps.collect(new Function<Map<String, Object>, DaRoutine>() {
        @Override
        public DaRoutine valueOf(Map<String, Object> object) {
            DaRoutineType routineType = DaRoutineType
                    .valueOf(((String) object.get("ROUTINE_TYPE")).toLowerCase());
            return new DaRoutinePojoImpl((String) object.get("ROUTINE_NAME"), schema, routineType,
                    (String) object.get("SPECIFIC_NAME"), (String) object.get("ROUTINE_DEFINITION"));
        }
    });
}

From source file:com.neu.controller.MessageController.java

protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response)
        throws Exception {

    DataSource ds = (DataSource) this.getApplicationContext().getBean("myDataSource");

    String action = request.getParameter("action");
    ModelAndView mv = new ModelAndView();

    HttpSession session = request.getSession();
    String userName = (String) session.getAttribute("userName");

    if (action.equalsIgnoreCase("reply")) {

        try {/*  w w w . j ava  2 s .c om*/

            String receiver = request.getParameter("to");
            System.out.println("Printing receiver in reply case: " + receiver);

            QueryRunner run = new QueryRunner(ds);
            ResultSetHandler<UsersBean> user = new BeanHandler<UsersBean>(UsersBean.class);
            UsersBean ub = run.query("select * from userstable where userName =?", user, receiver);
            if (ub != null) {
                System.out.println("printing userEmail received from DB: " + ub.getUserEmail());

                mv.addObject("toEmail", ub.getUserEmail());
                mv.addObject("to", receiver);
            }

            mv.setViewName("reply");

        } catch (SQLException e) {
            System.out.println(e);

        }

    } else if (action.equalsIgnoreCase("sent")) {
        System.out.println("In sent case");

        try {
            String receiver = request.getParameter("to");
            String receiverEmail = request.getParameter("toEmail");

            System.out.println("printing receiver email: " + receiverEmail);

            QueryRunner run = new QueryRunner(ds);
            ResultSetHandler<UsersBean> user = new BeanHandler<UsersBean>(UsersBean.class);
            UsersBean ub = run.query("select * from userstable where userName =?", user, userName);
            if (ub != null) {
                String senderEmail = ub.getUserEmail();
                System.out.println("printing senderemail: " + senderEmail);

                ResultSetHandler<MessageBean> msg = new BeanHandler<MessageBean>(MessageBean.class);
                Object[] params = new Object[4];
                params[0] = userName;
                params[1] = request.getParameter("message");
                Date d = new Date();
                SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
                String messageDate = format.format(d);
                params[2] = messageDate;
                params[3] = receiver;
                int inserts = run.update(
                        "Insert into messages (fromUser,message,messageDate,userName) values(?,?,?,?)", params);//Logic to send the email

                try {
                    Email email = new SimpleEmail();
                    email.setHostName("smtp.googlemail.com");//If a server is capable of sending email, then you don't need the authentication. In this case, an email server needs to be running on that machine. Since we are running this application on the localhost and we don't have a email server, we are simply asking gmail to relay this email.
                    email.setSmtpPort(465);
                    email.setAuthenticator(
                            new DefaultAuthenticator("contactapplication2017@gmail.com", "springmvc"));
                    email.setSSLOnConnect(true);
                    email.setFrom(senderEmail);//This email will appear in the from field of the sending email. It doesn't have to be a real email address.This could be used for phishing/spoofing!
                    email.setSubject("Thanks for Signing Up!");
                    email.setMsg("Welcome to Web tools Lab 5 Spring Application sign up email test!");
                    email.addTo(receiverEmail);//Will come from the database
                    email.send();
                } catch (Exception e) {
                    System.out.println("Email Exception" + e.getMessage());
                    e.printStackTrace();
                }
                mv.setViewName("messageSent");
            } else {
                mv.addObject("error", "true");
                mv.setViewName("index");

            }

        } catch (Exception ex) {
            System.out.println("Error Message" + ex.getMessage());
            ex.printStackTrace();

        }

    }

    return mv;
}

From source file:com.demo.db.dao.impl.DriverAuditDaoImpl.java

@Override
public DriverAudit getByDriverId(Long driverId) {
    QueryRunner queryRunner = dbHelper.getRunner();

    DriverAudit driverAudit = null;/* ww  w  . ja v  a2s. c om*/
    BeanProcessor beanProcessor = new GenerousBeanProcessor();
    RowProcessor rowProcessor = new BasicRowProcessor(beanProcessor);
    try {
        driverAudit = queryRunner.query("select * from demo_driver_audit where driver_id = ?",
                new BeanHandler<DriverAudit>(DriverAudit.class, rowProcessor), driverId);
        return driverAudit;
    } catch (SQLException e) {
        logger.error("??? ?{}", driverId);
        throw new RuntimeException("???", e);
    }
}

From source file:io.apiman.gateway.engine.jdbc.JdbcRegistry.java

/**
 * Simply pull the client from storage.//from  ww  w . ja va  2 s .c o  m
 * @param apiKey
 * @throws SQLException
 */
protected Client getClientInternal(String apiKey) throws SQLException {
    QueryRunner run = new QueryRunner(ds);
    return run.query("SELECT bean FROM gw_clients WHERE api_key = ?", //$NON-NLS-1$
            Handlers.CLIENT_HANDLER, apiKey);
}

From source file:jp.gr.java_conf.ka_ka_xyz.processor.JPA20AnnotationProcessorFieldAccessTest.java

@Test
public void testBindPersonByFieldAccess() throws SQLException {
    Connection conn = getConnection();
    QueryRunner run = new QueryRunner();
    RowProcessor rp = new BasicRowProcessor(new JPA20AnnotationProcessor(Jpa20Person.class));
    ResultSetHandler<List<Jpa20Person>> prsh = new BeanListHandler<Jpa20Person>(Jpa20Person.class, rp);
    String sql = "SELECT * from person ORDER BY id ASC";
    List<Jpa20Person> persons = run.query(conn, sql, prsh);
    assertEquals(3, persons.size());//w w w  .  jav  a2s. c  o  m
    assertEquals(new Jpa20Person(1, "Howard", "Lovecraft"), persons.get(0));
    assertEquals(new Jpa20Person(2, "August", "Derleth"), persons.get(1));
    assertEquals(new Jpa20Person(3, "Robert", "Bloch"), persons.get(2));

}

From source file:jp.gr.java_conf.ka_ka_xyz.processor.JPA20AnnotationProcessorPropertyAccessTest.java

@Test
public void testBindPersonByPropertyAccess() throws SQLException {
    Connection conn = getConnection();
    QueryRunner run = new QueryRunner();
    RowProcessor rp = new BasicRowProcessor(new JPA20AnnotationProcessor(Jpa20Person.class));
    ResultSetHandler<List<Jpa20Person>> prsh = new BeanListHandler<Jpa20Person>(Jpa20Person.class, rp);
    String sql = "SELECT * from person ORDER BY id ASC";
    List<Jpa20Person> persons = run.query(conn, sql, prsh);
    assertEquals(3, persons.size());//  ww w  .jav a  2 s. c  o m
    assertEquals(new Jpa20Person(1, "Howard", "Lovecraft"), persons.get(0));
    assertEquals(new Jpa20Person(2, "August", "Derleth"), persons.get(1));
    assertEquals(new Jpa20Person(3, "Robert", "Bloch"), persons.get(2));

}

From source file:com.softberries.klerk.dao.DocumentItemDao.java

public List<DocumentItem> findAll(QueryRunner run, Connection conn) throws SQLException {
    List<DocumentItem> items = new ArrayList<DocumentItem>();
    ResultSetHandler<List<DocumentItem>> h = new BeanListHandler<DocumentItem>(DocumentItem.class);
    items = run.query(conn, SQL_FIND_DOCUMENTITEM_ALL, h);
    ProductDao pdao = new ProductDao(path);
    for (DocumentItem di : items) {
        di.setProduct(pdao.find(di.getProduct_id(), run, conn));
    }/*from   www. jav a2 s  . c om*/
    return items;
}

From source file:jp.gr.java_conf.ka_ka_xyz.processor.AnnotationProcessorFieldAccessTest.java

@Test
public void testBindEmployeeByFieldAccess() throws SQLException {
    Connection conn = getConnection();
    QueryRunner run = new QueryRunner();
    RowProcessor rp = new BasicRowProcessor(new AnnotationProcessor(Employee.class));
    ResultSetHandler<List<Employee>> ersh = new BeanListHandler<Employee>(Employee.class, rp);
    String sql = "SELECT * from person INNER JOIN employee ON person.id = employee.person_id ORDER BY id ASC";
    List<Employee> employees = run.query(conn, sql, ersh);
    assertEquals(3, employees.size());/* ww w  . ja  va  2 s  .co  m*/
    assertEquals(new Employee(1, "Howard", "Lovecraft", "EMP001", "Weird Tales Div."), employees.get(0));
    assertEquals(new Employee(2, "August", "Derleth", "EMP002", "Arkham House Div."), employees.get(1));
    assertEquals(new Employee(3, "Robert", "Bloch", "EMP003", "Weird Tales Div."), employees.get(2));
}

From source file:jp.gr.java_conf.ka_ka_xyz.processor.AnnotationProcessorPropertyAccessTest.java

@Test
public void testBindEmployeeByPropertyAccess() throws SQLException {
    Connection conn = getConnection();
    QueryRunner run = new QueryRunner();
    RowProcessor rp = new BasicRowProcessor(new AnnotationProcessor(Employee.class));
    ResultSetHandler<List<Employee>> ersh = new BeanListHandler<Employee>(Employee.class, rp);
    String sql = "SELECT * from person INNER JOIN employee ON person.id = employee.person_id ORDER BY id ASC";
    List<Employee> employees = run.query(conn, sql, ersh);
    assertEquals(3, employees.size());/*from ww w.  ja  v  a  2s. c  om*/
    assertEquals(new Employee(1, "Howard", "Lovecraft", "EMP001", "Weird Tales Div."), employees.get(0));
    assertEquals(new Employee(2, "August", "Derleth", "EMP002", "Arkham House Div."), employees.get(1));
    assertEquals(new Employee(3, "Robert", "Bloch", "EMP003", "Weird Tales Div."), employees.get(2));
}

From source file:com.gs.obevo.dbmetadata.impl.dialects.SybaseAseMetadataDialect.java

@Override
public ImmutableCollection<DaUserType> searchUserTypes(final DaSchema schema, Connection conn)
        throws SQLException {
    QueryRunner query = new QueryRunner();

    ImmutableList<Map<String, Object>> maps = ListAdapter
            .adapt(query
                    .query(conn,//from  w w w  . j  a  va 2 s . c o m
                            "SELECT s1.name as USER_TYPE_NAME\n" + "FROM " + schema.getName()
                                    + "..systypes s1\n" + "WHERE s1.usertype>100",
                            new MapListHandler()))
            .toImmutable();

    return maps.collect(new Function<Map<String, Object>, DaUserType>() {
        @Override
        public DaUserType valueOf(Map<String, Object> map) {
            return new DaUserTypeImpl((String) map.get("USER_TYPE_NAME"), schema);
        }
    });
}