org.apache.commons.dbutils.QueryRunner Java Examples

The following examples show how to use org.apache.commons.dbutils.QueryRunner. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: DbConnect.java    From mcg-helper with Apache License 2.0 6 votes vote down vote up
/**
 * 
 * @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;
}
 
Example #2
Source File: JDBCExecutor.java    From amforeas with GNU General Public License v3.0 6 votes vote down vote up
/**
 * 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;
    }
}
 
Example #3
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 6 votes vote down vote up
@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());
        }
    });
}
 
Example #4
Source File: EsJdbcManager.java    From tunnel with Apache License 2.0 6 votes vote down vote up
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<>();
    }
 
Example #5
Source File: MapListHandlerExample.java    From maven-framework-project with MIT License 6 votes vote down vote up
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);
		}

	}
 
Example #6
Source File: FastJdbc.java    From litchi with Apache License 2.0 6 votes vote down vote up
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;
    }
}
 
Example #7
Source File: FastJdbc.java    From litchi with Apache License 2.0 6 votes vote down vote up
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;
}
 
Example #8
Source File: JDBCExecutor.java    From amforeas with GNU General Public License v3.0 6 votes vote down vote up
/**
 * 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;
    }
}
 
Example #9
Source File: JdbcRegistry.java    From apiman with Apache License 2.0 6 votes vote down vote up
/**
 * @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));
    }
}
 
Example #10
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 6 votes vote down vote up
/**
 * //删除项目
 *
 * @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;
        }
    });
}
 
Example #11
Source File: EmptyDatabaseTest.java    From CodeDefenders with GNU Lesser General Public License v3.0 6 votes vote down vote up
/**
 * 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());
    }
}
 
Example #12
Source File: JdbcMetricsAccessor.java    From apiman with Apache License 2.0 6 votes vote down vote up
/**
 * @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;
}
 
Example #13
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 6 votes vote down vote up
@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);
        }
    });
}
 
Example #14
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 6 votes vote down vote up
@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;
        }
    });
}
 
Example #15
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 6 votes vote down vote up
@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;
        }
    });
}
 
Example #16
Source File: M.java    From ThinkJD with Apache License 2.0 6 votes vote down vote up
/**
 * 获取某个字段值
 * @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;
}
 
Example #17
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 6 votes vote down vote up
@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;
        }
    });
}
 
Example #18
Source File: JdbcRegistry.java    From apiman with Apache License 2.0 6 votes vote down vote up
/**
 * 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$
    }
}
 
Example #19
Source File: JDBCRecordReader.java    From deeplearning4j with Apache License 2.0 6 votes vote down vote up
/**
 * @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;
}
 
Example #20
Source File: JdbcMetricsAccessor.java    From apiman with Apache License 2.0 6 votes vote down vote up
/**
 * @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;
}
 
Example #21
Source File: JdbcRegistry.java    From apiman with Apache License 2.0 6 votes vote down vote up
@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));
    }
}
 
Example #22
Source File: RecordResultSetHandlerTest.java    From moneta with Apache License 2.0 6 votes vote down vote up
@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);
}
 
Example #23
Source File: DbUtilsUnitTest.java    From tutorials with MIT License 5 votes vote down vote up
@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);
}
 
Example #24
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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);
        }
    });
}
 
Example #25
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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);
        }
    });
}
 
Example #26
Source File: AbstractJDBCDriverTest.java    From testcontainers-java with MIT License 5 votes vote down vote up
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);
}
 
Example #27
Source File: OracleJDBCDriverTest.java    From testcontainers-java with MIT License 5 votes vote down vote up
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();
}
 
Example #28
Source File: DbUtilsUnitTest.java    From tutorials with MIT License 5 votes vote down vote up
@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());
}
 
Example #29
Source File: AbstractJDBCDriverTest.java    From testcontainers-java with MIT License 5 votes vote down vote up
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;
}
 
Example #30
Source File: DataFactory.java    From xiaoyaoji with GNU General Public License v3.0 5 votes vote down vote up
@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"));
        }
    });
}