Example usage for org.apache.commons.dbutils QueryRunner 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:DbUtilsUseBeanMySQL.java

public static void main(String[] args) {
    Connection conn = null;/*w  w  w .  jav  a  2  s . co  m*/
    String jdbcURL = "jdbc:mysql://localhost/octopus";
    String jdbcDriver = "com.mysql.jdbc.Driver";
    String user = "root";
    String password = "root";

    try {
        DbUtils.loadDriver(jdbcDriver);
        conn = DriverManager.getConnection(jdbcURL, user, password);

        QueryRunner qRunner = new QueryRunner();
        List beans = (List) qRunner.query(conn, "select id, name from animals_table",
                new BeanListHandler(Employee.class));

        for (int i = 0; i < beans.size(); i++) {
            Employee bean = (Employee) beans.get(i);
            bean.print();
        }
    } catch (SQLException e) {
        // handle the exception
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(conn);
    }
}

---

From source file:DbUtilsUseMapMySQL.java

public static void main(String[] args) {
    Connection conn = null;//from   w  w  w .ja  v  a 2  s.co  m
    String jdbcURL = "jdbc:mysql://localhost/octopus";
    String jdbcDriver = "com.mysql.jdbc.Driver";
    String user = "root";
    String password = "root";

    try {
        DbUtils.loadDriver(jdbcDriver);
        conn = DriverManager.getConnection(jdbcURL, user, password);

        QueryRunner qRunner = new QueryRunner();

        List mapList = (List) qRunner.query(conn, "select id, name from animals_table", new MapListHandler());

        for (int i = 0; i < mapList.size(); i++) {
            Map map = (Map) mapList.get(i);
            System.out.println("id=" + map.get("id"));
            System.out.println("name=" + map.get("name"));
            System.out.println("-----------------");
        }

        System.out.println("DbUtils_UseMap_MySQL: end.");

    } catch (SQLException e) {
        // handle the exception
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(conn);
    }
}

---

From source file:dbutils.ExampleJDBC.java

/**
 * MapListHandler ResultSet??ListListMap
 *//*from w ww  . j ava  2  s  . co  m*/
public static void getMapListData() {
    Connection conn = getConnection();
    QueryRunner qr = new QueryRunner();
    try {
        List results = (List) qr.query(conn, "SELECT id, name, gender, age, team_id FROM test_student",
                new MapListHandler());
        for (Object result : results) {
            Map map = (Map) result;
            System.out.println(
                    "id=" + map.get("id") + " name=" + map.get("name") + " gender=" + map.get("gender"));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(conn);
    }
}

---

From source file:dbutils.ExampleJDBC.java

/**
 * BeanListHandler ResultSet??ListList/*from  www .j a va 2  s . co  m*/
 */
public static void getBeanListData() {
    Connection conn = getConnection();
    QueryRunner qr = new QueryRunner();
    try {
        ResultSetHandler<Student> rsh = new BeanHandler(Student.class);
        Student usr = qr.query(conn,
                "SELECT id, name, gender, age, team_id as teamId FROM test_student WHERE id=1", rsh);
        System.out.println(StringUtils.center("findById", 50, '*'));
        System.out.println("id=" + usr.getId() + " name=" + usr.getName() + " gender=" + usr.getGender());

        List<Student> results = (List<Student>) qr.query(conn,
                "SELECT id, name, gender, age, team_id as teamId FROM test_student LIMIT 10",
                new BeanListHandler(Student.class));
        System.out.println(StringUtils.center("findAll", 50, '*'));
        for (Student result : results) {
            System.out.println(
                    "id=" + result.getId() + "  name=" + result.getName() + "  gender=" + result.getGender());
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(conn);
    }
}

---

From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java

/**
 * query to json//w w w.  ja  v  a2s  .c om
 * 
 * @param userDB
 * @param strQuery
 * @param listParam
 */
@SuppressWarnings("deprecation")
public static JsonArray selectToJson(final UserDBDAO userDB, final String strQuery,
        final List<Object> listParam) throws Exception {
    final JsonArray jsonArry = new JsonArray();

    SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
    QueryRunner qr = new QueryRunner(client.getDataSource());
    qr.query(strQuery, listParam.toArray(), new ResultSetHandler<Object>() {

        @Override
        public Object handle(ResultSet rs) throws SQLException {
            ResultSetMetaData metaData = rs.getMetaData();

            while (rs.next()) {
                JsonObject jsonObj = new JsonObject();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String value = rs.getString(i) == null ? "" : rs.getString(i);

                    jsonObj.addProperty(columnName.toLowerCase(), value);
                }
                jsonArry.add(jsonObj);
            }

            return jsonArry;
        }
    });

    return jsonArry;
}

---

From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java

/**
 * result to html_table//from w ww. j  a v a2  s.  c o  m
 * 
 * @param userDB
 * @param strQuery
 * @param listParam
 * @return
 * @throws Exception
 */
@SuppressWarnings("deprecation")
public static String selectToHTML_TABLE(final UserDBDAO userDB, final String strQuery,
        final List<Object> listParam) throws Exception {

    SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
    QueryRunner qr = new QueryRunner(client.getDataSource());
    Object strHTMLTable = qr.query(strQuery, listParam.toArray(), new ResultSetHandler<Object>() {

        @Override
        public Object handle(ResultSet rs) throws SQLException {

            try {
                return ResultSetUtil.makeResultSetTOHTML(rs, 1000);
            } catch (Exception e) {
                return e.getMessage();
            }
        }
    });

    return strHTMLTable.toString();
}

---

From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java

/**
 * query to csv/*from  w  w  w. ja  v a 2 s.c  o m*/
 * 
 * @param userDB
 * @param strQuery
 * @param listParam
 * @param isAddHead is true add head title
 * @param strDelimiter if delimite is null default comma(,)
 */
@SuppressWarnings("deprecation")
public static String selectToCSV(final UserDBDAO userDB, final String strQuery, final List<Object> listParam,
        final boolean isAddHead, final String strDelimiter) throws Exception {
    final StringWriter stWriter = new StringWriter();

    SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
    QueryRunner qr = new QueryRunner(client.getDataSource());
    qr.query(strQuery, listParam.toArray(), new ResultSetHandler<Object>() {

        @Override
        public Object handle(ResultSet rs) throws SQLException {
            ResultSetMetaData metaData = rs.getMetaData();

            char strDel;
            if ("".equals(strDelimiter)) {
                strDel = ',';
            } else if (StringUtils.equalsIgnoreCase("\t", strDelimiter)) {
                strDel = (char) 9;
            } else {
                strDel = strDelimiter.charAt(0);
            }

            CSVWriter csvWriter = new CSVWriter(stWriter, strDel);
            if (isAddHead) {
                String[] arryString = new String[metaData.getColumnCount()];
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    arryString[i - 1] = metaData.getColumnLabel(i);
                }
                csvWriter.writeNext(arryString);
            }

            while (rs.next()) {
                String[] arryString = new String[metaData.getColumnCount()];
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    arryString[i - 1] = rs.getString(i);
                }
                csvWriter.writeNext(arryString);
            }

            return stWriter.toString();
        }
    });

    return stWriter.toString();
}

---

From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java

/**
 * query to xml/*from   w ww  . j av  a2 s  . c o  m*/
 * 
 * @param userDB
 * @param strQuery
 * @param listParam
 */
@SuppressWarnings("deprecation")
public static String selectToXML(final UserDBDAO userDB, final String strQuery, final List<Object> listParam)
        throws Exception {
    final StringWriter stWriter = new StringWriter();

    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    DocumentBuilder builder = factory.newDocumentBuilder();
    final Document doc = builder.newDocument();
    final Element results = doc.createElement("Results");
    doc.appendChild(results);

    SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
    QueryRunner qr = new QueryRunner(client.getDataSource());
    qr.query(strQuery, listParam.toArray(), new ResultSetHandler<Object>() {

        @Override
        public Object handle(ResultSet rs) throws SQLException {
            ResultSetMetaData metaData = rs.getMetaData();

            while (rs.next()) {
                Element row = doc.createElement("Row");
                results.appendChild(row);
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnName = metaData.getColumnName(i);
                    Object value = rs.getObject(i) == null ? "" : rs.getObject(i);
                    Element node = doc.createElement(columnName);
                    node.appendChild(doc.createTextNode(value.toString()));
                    row.appendChild(node);
                }
            }

            return stWriter.toString();
        }
    });

    DOMSource domSource = new DOMSource(doc);
    TransformerFactory tf = TransformerFactory.newInstance();
    tf.setAttribute("indent-number", 4);

    Transformer transformer = tf.newTransformer();
    transformer.setOutputProperty(OutputKeys.INDENT, "yes");

    transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
    transformer.setOutputProperty(OutputKeys.METHOD, "xml");
    transformer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");//"ISO-8859-1");
    StreamResult sr = new StreamResult(stWriter);
    transformer.transform(domSource, sr);

    return stWriter.toString();
}

---

From source file:jongo.jdbc.JDBCExecutor.java

/**
 * Executes the given {@link org.jongo.jdbc.DynamicFinder} object.
 * @param database database name or schema where to execute the {@link org.jongo.jdbc.DynamicFinder}
 * @param df an instance of {@link org.jongo.jdbc.DynamicFinder}
 * @param limit an instance of {@link jongo.jdbc.LimitParam}
 * @param order an instance of {@link jongo.jdbc.OrderParam}
 * @param params a vararg of Object instances used as parameters for the QueryRunner.
 * @return a List of {@link jongo.rest.xstream.Row} with the records found by the DynamicFinder.
 * @throws SQLException from the QueryRunner
 * @see org.apache.commons.dbutils.QueryRunner
 * @see jongo.sql.dialect.Dialect/* www  .j  a v  a 2 s .c  o  m*/
 */
public static List<Row> find(final String database, final DynamicFinder df, final LimitParam limit,
        final OrderParam order, Object... params) throws SQLException {
    l.debug(df.getSql());
    l.debug(JongoUtils.varargToString(params));

    DatabaseConfiguration dbconf = conf.getDatabaseConfiguration(database);
    Dialect dialect = DialectFactory.getDialect(dbconf);
    String query = dialect.toStatementString(df, limit, order);

    QueryRunner run = JDBCConnectionFactory.getQueryRunner(dbconf);
    ResultSetHandler<List<Row>> res = new JongoResultSetHandler(true);
    try {
        List<Row> results = run.query(query, res, params);
        l.debug("Received " + results.size() + " results.");
        return results;
    } catch (SQLException ex) {
        l.debug(ex.getMessage());
        throw ex;
    }
}

---

From source file:azkaban.project.JdbcProjectLoaderTest.java

@BeforeClass
public static void setupDB() {
    DataSource dataSource = DataSourceUtils.getMySQLDataSource(host, port, database, user, password,
            numConnections);/*from   ww w.  ja  v  a  2 s. c  o  m*/
    testDBExists = true;

    Connection connection = null;
    try {
        connection = dataSource.getConnection();
    } catch (SQLException e) {
        e.printStackTrace();
        testDBExists = false;
        DbUtils.closeQuietly(connection);
        return;
    }

    CountHandler countHandler = new CountHandler();
    QueryRunner runner = new QueryRunner();
    try {
        runner.query(connection, "SELECT COUNT(1) FROM projects", countHandler);
    } catch (SQLException e) {
        e.printStackTrace();
        testDBExists = false;
        DbUtils.closeQuietly(connection);
        return;
    }

    try {
        runner.query(connection, "SELECT COUNT(1) FROM project_events", countHandler);
    } catch (SQLException e) {
        e.printStackTrace();
        testDBExists = false;
        DbUtils.closeQuietly(connection);
        return;
    }

    try {
        runner.query(connection, "SELECT COUNT(1) FROM project_permissions", countHandler);
    } catch (SQLException e) {
        e.printStackTrace();
        testDBExists = false;
        DbUtils.closeQuietly(connection);
        return;
    }

    try {
        runner.query(connection, "SELECT COUNT(1) FROM project_files", countHandler);
    } catch (SQLException e) {
        e.printStackTrace();
        testDBExists = false;
        DbUtils.closeQuietly(connection);
        return;
    }

    try {
        runner.query(connection, "SELECT COUNT(1) FROM project_flows", countHandler);
    } catch (SQLException e) {
        e.printStackTrace();
        testDBExists = false;
        DbUtils.closeQuietly(connection);
        return;
    }

    try {
        runner.query(connection, "SELECT COUNT(1) FROM project_properties", countHandler);
    } catch (SQLException e) {
        e.printStackTrace();
        testDBExists = false;
        DbUtils.closeQuietly(connection);
        return;
    }

    DbUtils.closeQuietly(connection);

    clearDB();
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• »