org.apache.commons.dbutils.QueryRunner Java Examples

/**
 * 
 * @Title:       insertSql   
 * @Description: TODO(带可变参数, 执行sql插入，返回新增记录的自增主键。注意： 若插入的表无自增主键则返回 0，异常的话则返回 null)   
 * @param:       @param sql 执行的sql语句
 * @param:       @param para 参数
 * @param:       @return
 * @param:       @throws SQLException      
 * @return:      Long      
 * @throws
 */
public Long insertSql(String sql, Object... para) throws SQLException {
	logger.debug("InsertSql: {}, para: {}", sql, ToStringBuilder.reflectionToString(para));
	QueryRunner runner = new QueryRunner();
	Connection conn = null;
	Long id = null;
	try {
		conn = getConnection();
		id = (Long) runner.insert(conn, sql, new ScalarHandler<Object>(), para);
	} catch (SQLException e) {
		logger.error("------insertSql error: {}------", e.getMessage());
		throw e;
	} finally {
		if (conn != null && conn.getAutoCommit() == true) {
			freeConnection();
		}
	}
	return id;
}
 

/**
 * Executes the given {@link org.amforeas.jdbc.DynamicFinder} object.
 * @param database database name or schema where to execute the {@link org.amforeas.jdbc.DynamicFinder}
 * @param df an instance of {@link org.amforeas.jdbc.DynamicFinder}
 * @param limit an instance of {@link amforeas.jdbc.LimitParam}
 * @param order an instance of {@link amforeas.jdbc.OrderParam}
 * @param params a vararg of Object instances used as parameters for the QueryRunner.
 * @return a List of {@link amforeas.rest.xstream.Row} with the records found by the DynamicFinder.
 * @throws SQLException from the QueryRunner
 * @see org.apache.commons.dbutils.QueryRunner
 * @see amforeas.sql.dialect.Dialect
 */
public 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(AmforeasUtils.varargToString(params));

    final DatabaseConfiguration dbconf = this.factory.getConfiguration().getDatabaseConfiguration(database);
    final Dialect dialect = this.factory.getDialectFactory().getDialect(dbconf);
    final String query = dialect.toStatementString(df, limit, order);

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

@Override
public int deleteByIds(final Class<?> clazz, final List<String> ids) {
    return process(new Handler<Integer>() {
        @Override
        public Integer handle(Connection connection, QueryRunner qr) throws SQLException {
            StringBuilder deleteSQL = new StringBuilder("delete from " + SqlUtils.getTableName(clazz));
            deleteSQL.append(" where id in (");
            for (String id : ids) {
                deleteSQL.append("?,");
            }
            deleteSQL = deleteSQL.delete(deleteSQL.length() - 1, deleteSQL.length());
            deleteSQL.append(")");
            return qr.update(connection, deleteSQL.toString(), ids.toArray());
        }
    });
}
 

public static List<Map<String, Object>> query(String sql, String slot, String url, String username, String password) {

        QueryRunner qr = new QueryRunner();
        Connection connection = null;
        try {
            connection = getConnection(slot, url, username, password);
            List<Map<String, Object>> result = qr.query(connection, sql, new MapListHandler());
            if (CollectionUtils.isNotEmpty(result)) {
                return result;
            }
        } catch (SQLException e) {
            //
        } finally {
            close(connection);
        }
        return new ArrayList<>();
    }
 

public static void main(String[] args) throws SQLException {

		final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
		final String driver = "org.h2.Driver";
		final String usr = "sa";
		final String pwd = "";

		QueryRunner run = new QueryRunner();

		DbUtils.loadDriver(driver);
		Connection conn = DriverManager.getConnection(url, usr, pwd);
		// -----------------------------------------------------------------------------------

		try {
			List<Map<String, Object>> maps = run.query(conn,
					"SELECT * FROM employee", new MapListHandler());
			System.out.println(maps);
		} finally {
			DbUtils.close(conn);
		}

	}
 

public <T extends Table<?>> T getFirst(Class<T> clazz, LinkedHashMap<String, Object> condition) {
    try {
        QueryRunner runner = getJdbcTemplate(clazz);
        TableInfo info = SuperTable.getTableInfo(clazz);
        String[] key = new String[condition.keySet().size()];
        String sql = selectSql.toSqlString(info.pkName(), info.annotation().tableName(), info.buildDbColumns(), condition.keySet().toArray(key));
        //List<T> result = (List<T>) runner.query(sql, info.newInstance(), condition.values().toArray());
        List<T> list = runner.query(sql, new TableListHandler<>(clazz), condition.values().toArray());
        if (list.isEmpty()) {
            return null;
        }
        return list.get(0);
    } catch (Exception e) {
        LOGGER.error("", e);
        return null;
    }
}
 

public <T extends Table<?>> List<T> getList(Class<T> clazz, LinkedHashMap<String, Object> condition, int limitBegin, int limitEnd) {
    try {
        QueryRunner runner = getJdbcTemplate(clazz);
        TableInfo info = SuperTable.getTableInfo(clazz);
        String[] key = new String[condition.keySet().size()];
        String sql = selectSql.toSqlString(info.pkName(), info.annotation().tableName(),
                info.buildDbColumns(), limitBegin, limitEnd, condition.keySet().toArray(key));
        if (limitBegin > 0) {
            condition.put("limitBegin", limitBegin);
        }
        if (limitEnd > 0) {
            condition.put("limitEnd", limitEnd);
        }
        //Table<?> instance = info.classAccess.newInstance();
        //return (List<T>) runner.query(sql, condition.values().toArray(), instance);
        return runner.query(sql, new TableListHandler<>(clazz), condition.values().toArray());
    } catch (Exception e) {
        LOGGER.error("", e);
    }
    return null;
}
 

/**
 * For a given database or schema, execute the statement returned by the {@link amforeas.sql.dialect.Dialect} listOfTablesStatement()
 * method and return a List of {@link amforeas.rest.xstream.Row} with all the tables available.
 * @param database the name of the database to query.
 * @return a List of {@link amforeas.rest.xstream.Row} with all the tables available.
 * @throws SQLException 
 */
public List<Row> getListOfTables (final String database) throws SQLException {
    l.debug("Obtaining the list of tables for the database {}", database);

    final ResultSetHandler<List<Row>> res = new AmforeasResultSetHandler(true);
    final DatabaseConfiguration dbconf = this.factory.getConfiguration().getDatabaseConfiguration(database);
    final Dialect dialect = this.factory.getDialectFactory().getDialect(dbconf);
    final QueryRunner run = this.factory.getJDBCConnectionFactory().getQueryRunner(dbconf);

    try {
        List<Row> results = run.query(dialect.listOfTablesStatement(), res);
        l.debug("Received {} results.", results.size());
        return results;
    } catch (SQLException ex) {
        throw ex;
    }
}
 

/**
 * @see io.apiman.gateway.engine.IRegistry#publishApi(io.apiman.gateway.engine.beans.Api, io.apiman.gateway.engine.async.IAsyncResultHandler)
 */
@Override
public void publishApi(Api api, IAsyncResultHandler<Void> handler) {
    Connection conn = null;
    try {
        conn = ds.getConnection();
        conn.setAutoCommit(false);
        QueryRunner run = new QueryRunner();

        // First delete any record we might already have.
        run.update(conn, "DELETE FROM gw_apis WHERE org_id = ? AND id = ? AND version = ?",  //$NON-NLS-1$
                api.getOrganizationId(), api.getApiId(), api.getVersion());

        // Now insert a row for the api.
        String bean = mapper.writeValueAsString(api);
        run.update(conn, "INSERT INTO gw_apis (org_id, id, version, bean) VALUES (?, ?, ?, ?)",  //$NON-NLS-1$
                api.getOrganizationId(), api.getApiId(), api.getVersion(), bean);

        DbUtils.commitAndClose(conn);
        handler.handle(AsyncResultImpl.create((Void) null, Void.class));
    } catch (SQLException | JsonProcessingException e) {
        handler.handle(AsyncResultImpl.create(e));
    }
}
 

/**
 * //删除项目
 *
 * @param id
 * @return
 */
@Override
public int deleteProject(final String id) {
    return process(new Handler<Integer>() {
        @Override
        public Integer handle(Connection connection, QueryRunner qr) throws SQLException {
            //todo 删除all
            //删除项目
            int rs = qr.update(connection, "delete from " + TableNames.PROJECT + " where id =?", id);
            //删除文档
            rs += qr.update(connection, "delete from " + TableNames.DOC + " where projectid =?", id);
            //删除文档历史
            rs += qr.update(connection, "delete from " + TableNames.DOC_HISTORY + " where projectid =?", id);
            //删除项目与用户关联
            rs += qr.update(connection, "delete from " + TableNames.PROJECT_USER + " where projectid =?", id);
            //删除分享
            rs += qr.update(connection, "delete from " + TableNames.SHARE + " where projectid =?", id);
            return rs;
        }
    });
}
 

/**
 * Checks whether the {@code empty.db} script creates an empty database.
 */
@Test
public void testCleanDB() throws Exception {
    try (Connection conn = db.getConnection()) {
        QueryRunner qr = new QueryRunner();

        List<String> results = qr.query(conn, "SELECT * FROM classes;", new ColumnListHandler<>());
        assertEquals(0, results.size());

        final String query2 = String.join("\n",
                "SELECT *",
                "FROM games",
                "WHERE ID > 0"
        );
        results = qr.query(conn, query2, new ColumnListHandler<>());
        assertEquals(0, results.size());

        results = qr.query(conn, "SELECT * FROM mutants;", new ColumnListHandler<>());
        assertEquals(0, results.size());

        results = qr.query(conn, "SELECT * FROM tests;", new ColumnListHandler<>());
        assertEquals(0, results.size());
    }
}
 

/**
 * @see io.apiman.manager.api.core.IMetricsAccessor#getUsage(java.lang.String, java.lang.String, java.lang.String, io.apiman.manager.api.beans.metrics.HistogramIntervalType, org.joda.time.DateTime, org.joda.time.DateTime)
 */
@Override
public UsageHistogramBean getUsage(String organizationId, String apiId, String version,
        HistogramIntervalType interval, DateTime from, DateTime to) {
    UsageHistogramBean rval = new UsageHistogramBean();
    Map<Long, UsageDataPoint> index = generateHistogramSkeleton(rval, from, to, interval, UsageDataPoint.class, Long.class);
    
    try {
        QueryRunner run = new QueryRunner(ds);
        String gbColumn = groupByColumn(interval);
        String sql = "SELECT " + gbColumn + ", count(*) FROM gw_requests WHERE api_org_id = ? AND api_id = ? AND api_version = ? AND rstart >= ? AND rstart < ? GROUP BY " + gbColumn; //$NON-NLS-1$ //$NON-NLS-2$
        ResultSetHandler<UsageHistogramBean> handler = new UsageHistogramHandler(rval, index);
        run.query(sql, handler, organizationId, apiId, version, from.getMillis(), to.getMillis());
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    return rval;
}
 

@Override
public String getDocNamesFromIds(final String[] docIdsArray) {
    return process(new Handler<String>() {
        @Override
        public String handle(Connection connection, QueryRunner qr) throws SQLException {
            StringBuilder sql = new StringBuilder();
            sql.append("select group_concat(name) from doc where id in (");
            if(docIdsArray.length==0)
                return "";
            for(String id:docIdsArray){
                sql.append("?,");
            }
            sql = sql.delete(sql.length()-1,sql.length());
            sql.append(")");
            return qr.query(connection,sql.toString(),new StringResultHandler(),docIdsArray);
        }
    });
}
 

@Override
public ProjectGlobal getProjectGlobal(final String projectId,final String column) {
    return process(new Handler<ProjectGlobal>() {
        @Override
        public ProjectGlobal handle(Connection connection, QueryRunner qr) throws SQLException {
            ProjectGlobal pg = qr.query(connection, "select "+column+" from " + TableNames.PROJECT_GLOBAL + " where projectId=?", new BeanHandler<>(ProjectGlobal.class), projectId);
            if (pg == null) {
                //会有并发问题
                pg = generateProjectGlobal(projectId);
                SQLBuildResult sbr = SqlUtils.generateInsertSQL(pg);
                if (qr.update(connection, sbr.getSql(), sbr.getParams()) == 0) {
                    throw new SystemErrorException("创建project_global失败");
                }
            }
            return pg;
        }
    });
}
 

@Override
public ProjectGlobal getProjectGlobal(final String projectId) {
    return process(new Handler<ProjectGlobal>() {
        @Override
        public ProjectGlobal handle(Connection connection, QueryRunner qr) throws SQLException {
            ProjectGlobal pg = qr.query(connection, "select * from " + TableNames.PROJECT_GLOBAL + " where projectId=?", new BeanHandler<>(ProjectGlobal.class), projectId);
            if (pg == null) {
                //会有并发问题
                pg = generateProjectGlobal(projectId);
                SQLBuildResult sbr = SqlUtils.generateInsertSQL(pg);
                if (qr.update(connection, sbr.getSql(), sbr.getParams()) == 0) {
                    throw new SystemErrorException("创建project_global失败");
                }
            }
            return pg;
        }
    });
}
 

/**
 * 获取某个字段值
 * @param field 获取field字段数据
 * @return 返回字段数据
 * @throws Exception if has error
 */
public String getField(String field) throws Exception{
	this.field(field);
	try {
		if (buildSql_Select()) {
			Object res = new QueryRunner().query(conn, sql, new ScalarHandler<Object>(), param_where);
			D.autoCloseConn(conn);
			if (null != res) {
				return res.toString();
			}
		}
	} catch (Exception e) {
		D.autoCloseConn(conn);
		throw e;
	}
	return null;
}
 

@Override
public int createProject(final Project project) {
    return process(new Handler<Integer>() {
        @Override
        public Integer handle(Connection connection, QueryRunner qr) throws SQLException {
            SQLBuildResult sb = SqlUtils.generateInsertSQL(project);
            int rs = qr.update(connection, sb.getSql(), sb.getParams());
            ProjectUser pu = new ProjectUser();
            pu.setUserId(project.getUserId());
            pu.setId(StringUtils.id());
            pu.setCreateTime(new Date());
            pu.setProjectId(project.getId());
            pu.setStatus(ProjectUser.Status.ACCEPTED);
            pu.setEditable(project.getEditable());
            sb = SqlUtils.generateInsertSQL(pu);
            rs += qr.update(connection, sb.getSql(), sb.getParams());
            return rs;
        }
    });
}
 

/**
 * Ensures that the api referenced by the Contract actually exists (is published).
 * @param contract
 * @param connection
 * @throws RegistrationException
 */
private void validateContract(final Contract contract, Connection connection)
        throws RegistrationException {
    QueryRunner run = new QueryRunner();
    try {
        Api api = run.query(connection, "SELECT bean FROM gw_apis WHERE org_id = ? AND id = ? AND version = ?", //$NON-NLS-1$
                Handlers.API_HANDLER, contract.getApiOrgId(), contract.getApiId(), contract.getApiVersion());
        if (api == null) {
            String apiId = contract.getApiId();
            String orgId = contract.getApiOrgId();
            throw new ApiNotFoundException(Messages.i18n.format("JdbcRegistry.ApiNotFoundInOrg", apiId, orgId));  //$NON-NLS-1$
        }
    } catch (SQLException e) {
        throw new RegistrationException(Messages.i18n.format("JdbcRegistry.ErrorValidatingApp"), e); //$NON-NLS-1$
    }
}
 

/**
 * @see #loadFromMetaData(RecordMetaData)
 */
@Override
public List<Record> loadFromMetaData(List<RecordMetaData> recordMetaDatas) throws IOException {
    List<Record> ret = new ArrayList<>();

    for (RecordMetaData rmd : recordMetaDatas) {
        if (!(rmd instanceof RecordMetaDataJdbc)) {
            throw new IllegalArgumentException(
                "Invalid metadata; expected RecordMetaDataJdbc instance; got: " + rmd);
        }
        QueryRunner runner = new QueryRunner();
        String request = ((RecordMetaDataJdbc) rmd).getRequest();

        try {
            Object[] item = runner
                .query(this.conn, request, new ArrayHandler(), ((RecordMetaDataJdbc) rmd).getParams().toArray());
            ret.add(new org.datavec.api.records.impl.Record(toWritable(item), rmd));
        } catch (SQLException e) {
            throw new IllegalArgumentException("Could not execute statement \"" + request + "\"", e);
        }
    }
    return ret;
}
 

/**
 * @see io.apiman.manager.api.core.IMetricsAccessor#getResponseStats(java.lang.String, java.lang.String, java.lang.String, io.apiman.manager.api.beans.metrics.HistogramIntervalType, org.joda.time.DateTime, org.joda.time.DateTime)
 */
@Override
public ResponseStatsHistogramBean getResponseStats(String organizationId, String apiId, String version,
        HistogramIntervalType interval, DateTime from, DateTime to) {
    ResponseStatsHistogramBean rval = new ResponseStatsHistogramBean();
    Map<Long, ResponseStatsDataPoint> index = generateHistogramSkeleton(rval, from, to, interval, ResponseStatsDataPoint.class, Long.class);
    
    try {
        QueryRunner run = new QueryRunner(ds);
        String gbColumn = groupByColumn(interval);
        String sql = "SELECT " + gbColumn + ", resp_type, count(*) FROM gw_requests WHERE api_org_id = ? AND api_id = ? AND api_version = ? AND rstart >= ? AND rstart < ? GROUP BY resp_type," + gbColumn; //$NON-NLS-1$ //$NON-NLS-2$
        ResultSetHandler<ResponseStatsHistogramBean> handler = new ResponseStatsHistogramHandler(rval, index);
        run.query(sql, handler, organizationId, apiId, version, from.getMillis(), to.getMillis());
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    return rval;
}
 

@Override
@SuppressWarnings("nls")
public void listOrgs(IAsyncResultHandler<List<String>> handler) {
    QueryRunner run = new QueryRunner(ds);
    try {
        List<String> orgList = run.query("SELECT DISTINCT merged.org_id\n" +
                "FROM\n" +
                "    (\n" +
                "        SELECT\n" +
                "            org_id\n" +
                "        FROM\n" +
                "            gw_apis\n" +
                "    UNION \n" +
                "        SELECT\n" +
                "            org_id\n" +
                "        FROM\n" +
                "            gw_clients\n" +
                "    ) merged;",
                Handlers.STRING_LIST_COL1_HANDLER);
        handler.handle(AsyncResultImpl.create(orgList));
    } catch (SQLException e) {
        handler.handle(AsyncResultImpl.create(e));
    }
}
 

@Test
public void testBasicHappyPath() throws Exception {
	QueryRunner runner = new QueryRunner();
	Record[] recArray = runner.query(nativeConnection, 
			"select * from INFORMATION_SCHEMA.SYSTEM_TABLES", handler);
	Assert.assertTrue(recArray != null);
	Assert.assertTrue(recArray.length == 92);
	Assert.assertTrue(searchForColumn(recArray, "Catalog"));
	Assert.assertTrue(!searchForColumn(recArray, "TABLE_CAT"));
	Assert.assertTrue(searchForColumn(recArray, "TABLE_TYPE"));
	
	handler.setStartRow(90L);
	recArray = runner.query(nativeConnection, 
			"select * from INFORMATION_SCHEMA.SYSTEM_TABLES", handler);
	Assert.assertTrue(recArray != null);
	Assert.assertTrue(recArray.length == 3);
	
	handler.setStartRow(null);
	handler.setMaxRows(10L);
	recArray = runner.query(nativeConnection, 
			"select * from INFORMATION_SCHEMA.SYSTEM_TABLES", handler);
	Assert.assertTrue(recArray != null);
	Assert.assertTrue(recArray.length == 10);
}
 

@Test
public void whenDeletingRecord_thenDeleted() throws SQLException {
    QueryRunner runner = new QueryRunner();
    String deleteSQL = "DELETE FROM employee WHERE id = ?";
    int numRowsDeleted = runner.update(connection, deleteSQL, 3);

    assertEquals(numRowsDeleted, 1);
}
 

@Override
public int delete(final String tableName, final String id) {
    return process(new Handler<Integer>() {
        @Override
        public Integer handle(Connection connection, QueryRunner qr) throws SQLException {
            return qr.update(connection, "delete from " + tableName + " where id =?", id);
        }
    });
}
 

@Override
public List<Team> getTeams(final String userId) {
    return process(new Handler<List<Team>>() {
        @Override
        public List<Team> handle(Connection connection, QueryRunner qr) throws SQLException {
            StringBuilder sql = new StringBuilder("select t.* from ")
                    .append(TableNames.TEAM)
                    .append(" t left join team_user tu on tu.teamId=t.id ")
                    .append(" where tu.userId=? or t.userId=?")
                    .append(" order by tu.createTime desc,t.createTime desc ");

            return qr.query(connection, sql.toString(), new BeanListHandler<>(Team.class), userId, userId);
        }
    });
}
 

private void performTestForScriptedSchema(HikariDataSource dataSource) throws SQLException {
    boolean result = new QueryRunner(dataSource).query("SELECT foo FROM bar WHERE foo LIKE '%world'", rs -> {
        rs.next();
        String resultSetString = rs.getString(1);
        assertEquals("A basic SELECT query succeeds where the schema has been applied from a script", "hello world", resultSetString);
        return true;
    });

    assertTrue("The database returned a record as expected", result);
}
 

private void performSimpleTest(String jdbcUrl) throws SQLException {
    HikariDataSource dataSource = getDataSource(jdbcUrl, 1);
    new QueryRunner(dataSource).query("SELECT 1 FROM dual", new ResultSetHandler<Object>() {
        @Override
        public Object handle(ResultSet rs) throws SQLException {
            rs.next();
            int resultSetInt = rs.getInt(1);
            assertEquals("A basic SELECT query succeeds", 1, resultSetInt);
            return true;
        }
    });
    dataSource.close();
}
 

@Test
public void givenResultHandler_whenExecutingQuery_thenEmailsSetted() throws SQLException {
    EmployeeHandler employeeHandler = new EmployeeHandler(connection);

    QueryRunner runner = new QueryRunner();
    List<Employee> employees = runner.query(connection, "SELECT * FROM employee", employeeHandler);

    assertEquals(employees.get(0).getEmails().size(), 2);
    assertEquals(employees.get(2).getEmails().size(), 3);
    assertNotNull(employees.get(0).getEmails().get(0).getEmployeeId());
}
 

private HikariDataSource verifyCharacterSet(String jdbcUrl) throws SQLException {
    HikariDataSource dataSource = getDataSource(jdbcUrl, 1);
    boolean result = new QueryRunner(dataSource).query("SHOW VARIABLES LIKE 'character\\_set\\_connection'", rs -> {
        rs.next();
        String resultSetString = rs.getString(2);
        assertTrue("Passing query parameters to set DB connection encoding is successful", resultSetString.startsWith("utf8"));
        return true;
    });

    assertTrue("The database returned a record as expected", result);
    return dataSource;
}
 

@Override
public List<String> getAllProjectValidIds() {
    return process(new Handler<List<String>>() {
        @Override
        public List<String> handle(Connection connection, QueryRunner qr) throws SQLException {
            return qr.query(connection,"select id from project where status='VALID' and permission='PUBLIC' order by createTime desc ",new ColumnListHandler<String>("id"));
        }
    });
}