Java Code Examples for org.apache.commons.dbutils.QueryRunner#query()

The following examples show how to use org.apache.commons.dbutils.QueryRunner#query() . 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: DbUtilsMapListHandler.java    From maven-framework-project with MIT License 8 votes vote down vote up 
public static void main(String[] args) {
	Connection conn = null;

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

	try {
		DbUtils.loadDriver(driver);
		conn = DriverManager.getConnection(url, usr, pwd);
		QueryRunner query = new QueryRunner();
		List<Map<String, Object>> mapList = (List<Map<String, Object>>) query
				.query(conn, "select * from user", new MapListHandler());
		for (int i = 0; i < mapList.size(); i++) {
			Map<String, Object> map = (Map<String, Object>) mapList.get(i);
			System.out.println("------> " + map.get("userId") + "\t"
					+ map.get("firstName") + "\t" + map.get("emailId"));
		}
	} catch (SQLException se) {
		se.printStackTrace();
	} finally {
		DbUtils.closeQuietly(conn);
	}
}
Example 2
Source File: DbConnect.java    From mcg-helper with Apache License 2.0 6 votes vote down vote up 
/**
 * 带可变参数查询,返回执行结果
 * 
 * @param sql 查询sql
 * @param para 可变参数
 * @return
 */
public List<Map<String, Object>> querySql(String sql, Object... para) throws SQLException {
	logger.debug("查询Sql: {}, 查询参数: {}", sql, ToStringBuilder.reflectionToString(para));
	QueryRunner runner = new QueryRunner();
	Connection conn = null;
	List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
	try {
		conn = getConnection();
		result = runner.query(conn, sql, new MapListHandler(), para);
	} catch (SQLException e) {
		logger.error("查询出错，异常信息: {}", e.getMessage());
		throw e;
	} finally {
		if (conn != null && conn.getAutoCommit() == true) {
			freeConnection();
		}
	}
	return result;
}
Example 3
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 4
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 5
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 6
Source File: JDBCExample.java    From wisp with Apache License 2.0 5 votes vote down vote up 
public static String resultSetToJson(String query) {
    Connection connection = null;
    List<Map<String, Object>> listOfMaps = null;

    try {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306?"
                + "useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries"
                + "=true", "root", "123456");
    } catch (Exception ex) {
        System.err.println("***exception trying to connect***");
        ex.printStackTrace();
    }

    try {
        QueryRunner queryRunner = new QueryRunner();
        listOfMaps = queryRunner.query(connection, query, new MapListHandler());
    } catch (SQLException se) {
        throw new RuntimeException("Couldn't query the database.", se);
    } finally {
        DbUtils.closeQuietly(connection);
    }

    try {
        return new ObjectMapper().writeValueAsString(listOfMaps);

    } catch (JsonProcessingException e) {
        System.out.println(e.toString());
    }

    return null;
}
Example 7
Source File: ScoreDataImporter.java    From beatoraja with GNU General Public License v3.0 5 votes vote down vote up 
public void importFromLR2ScoreDatabase(String path, SongDatabaseAccessor songdb) {
    final int[] clears = { 0, 1, 4, 5, 6, 8, 9 };
    scoredb.createTable();

    try (Connection con = DriverManager.getConnection("jdbc:sqlite:" + path)) {
        QueryRunner qr = new QueryRunner();
        MapListHandler rh = new MapListHandler();
        List<Map<String, Object>> scores = qr.query(con, "SELECT * FROM score", rh);

        List<ScoreData> result = new ArrayList<ScoreData>();
        for (Map<String, Object> score : scores) {
            final String md5 = (String) score.get("hash");
            SongData[] song = songdb.getSongDatas(new String[] { md5 });
            if (song.length > 0) {
                ScoreData sd = new ScoreData();
                sd.setEpg((int) score.get("perfect"));
                sd.setEgr((int) score.get("great"));
                sd.setEgd((int) score.get("good"));
                sd.setEbd((int) score.get("bad"));
                sd.setEpr((int) score.get("poor"));
                sd.setMinbp((int) score.get("minbp"));
                sd.setClear(clears[(int) score.get("clear")]);
                sd.setPlaycount((int) score.get("playcount"));
                sd.setClearcount((int) score.get("clearcount"));
                sd.setSha256(song[0].getSha256());
                sd.setNotes(song[0].getNotes());
                result.add(sd);
            }
        }
        
        this.importScores(result.toArray(new ScoreData[result.size()]), "LR2");
    } catch (Exception e) {
        Logger.getGlobal().severe("スコア移行時の例外:" + e.getMessage());
    }
}
Example 8
Source File: EsPublisher.java    From tunnel with Apache License 2.0 5 votes vote down vote up 
private List<Map<String, Object>> execute(String sql, InvokeContext context) {
    try (Connection conn = getConnection(context)) {
        QueryRunner qr = new QueryRunner();
        List<Map<String, Object>> list = qr.query(conn, sql, new MapListHandler());
        if (list == null) {
            list = new LinkedList<>();
        }
        return list;
    } catch (Exception e) {
        //
    }
    return new LinkedList<>();
}
Example 9
Source File: FastJdbc.java    From litchi with Apache License 2.0 5 votes vote down vote up 
public Long queryScalar(Class<? extends Table> clazz, String sql, LinkedHashMap<String, Object> condition) {
    try {
        QueryRunner runner = getJdbcTemplate(clazz);
        return runner.query(sql, new ScalarHandler<Long>(), condition.values().toArray());
    } catch (Exception e) {
        LOGGER.error("", e);
    }
    return 0L;
}
Example 10
Source File: JdbcRegistry.java    From apiman with Apache License 2.0 5 votes vote down vote up 
@Override
public void listClients(String organizationId, int page, int pageSize, IAsyncResultHandler<List<String>> handler) {
    QueryRunner run = new QueryRunner(ds);
    try {
        List<String> clientList = run.query("SELECT DISTINCT id FROM gw_clients WHERE org_id = ?",
                Handlers.STRING_LIST_COL1_HANDLER, organizationId);
        handler.handle(AsyncResultImpl.create(clientList));
    } catch (SQLException e) {
        handler.handle(AsyncResultImpl.create(e));
    }
}
Example 11
Source File: FastJdbc.java    From litchi with Apache License 2.0 5 votes vote down vote up 
public <T> T queryObject(Class<? extends Table> clazz, String sql, ResultSetHandler<T> handler) {
    try {
        QueryRunner runner = getJdbcTemplate(clazz);
        return runner.query(sql, handler);
    } catch (Exception e) {
        LOGGER.error("", e);
    }
    return null;
}
Example 12
Source File: JdbcMetricsAccessor.java    From apiman with Apache License 2.0 5 votes vote down vote up 
/**
 * @see io.apiman.manager.api.core.IMetricsAccessor#getUsagePerClient(java.lang.String, java.lang.String, java.lang.String, org.joda.time.DateTime, org.joda.time.DateTime)
 */
@Override
public UsagePerClientBean getUsagePerClient(String organizationId, String apiId, String version,
        DateTime from, DateTime to) {
    try {
        QueryRunner run = new QueryRunner(ds);
        String sql = "SELECT client_id, count(*) FROM gw_requests WHERE api_org_id = ? AND api_id = ? AND api_version = ? AND rstart >= ? AND rstart < ? GROUP BY client_id"; //$NON-NLS-1$
        ResultSetHandler<UsagePerClientBean> handler = new UsagePerClientHandler();
        return run.query(sql, handler, organizationId, apiId, version, from.getMillis(), to.getMillis());
    } catch (SQLException e) {
        e.printStackTrace();
        return new UsagePerClientBean();
    }
}
Example 13
Source File: EsPublisher.java    From tunnel with Apache License 2.0 5 votes vote down vote up 
private List<Map<String, Object>> execute(String sql, InvokeContext context) {
    try (Connection conn = getConnection(context)) {
        QueryRunner qr = new QueryRunner();
        List<Map<String, Object>> list = qr.query(conn, sql, new MapListHandler());
        if (list == null) {
            list = new LinkedList<>();
        }
        return list;
    } catch (Exception e) {
        //
    }
    return new LinkedList<>();
}
Example 14
Source File: DbUtilsDemo.java    From JavaTutorial with Apache License 2.0 5 votes vote down vote up 
/**
 * 将查询结果集转换成键值对列表返回。
 * 
 * @param ds JDBC连接池
 * @param userId 用户编号
 */
public void queryMap(DataSource ds, int userId) {
    String sql = "select userId, userName, gender, age from student where userId=?";
    
    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<Map<String, Object>> handler = new MapHandler();
    Map<String, Object> result = null;
    try {
        result = run.query(sql, handler, userId);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }
    
    System.out.println(result);
}
Example 15
Source File: DbUtilsDemo.java    From JavaTutorial with Apache License 2.0 5 votes vote down vote up 
/**
 * 将查询结果转换成Bean返回。
 * 
 * @param ds JDBC连接池
 */
public void queryBean(DataSource ds, int userId) {
    String sql = "select userId, userName, gender, age from student where userId=?";
    
    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<Student> handler = new BeanHandler<Student>(Student.class);
    Student result = null;
    try {
        result = run.query(sql, handler, userId);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }
    
    System.out.println(result);
}
Example 16
Source File: JdbcRegistry.java    From apiman with Apache License 2.0 5 votes vote down vote up 
@Override
public void getClient(String organizationId, String clientId, String clientVersion,
        IAsyncResultHandler<Client> handler) {
    try {
        QueryRunner run = new QueryRunner(ds);
        Client client = run.query("SELECT bean FROM gw_clients WHERE org_id = ? AND id = ? AND version = ?", //$NON-NLS-1$
                Handlers.CLIENT_HANDLER, organizationId, clientId, clientVersion);
        handler.handle(AsyncResultImpl.create(client));
    } catch (SQLException e) {
        handler.handle(AsyncResultImpl.create(e, Client.class));
    }
}
Example 17
Source File: DbUtilsBeanHandler.java    From maven-framework-project with MIT License 5 votes vote down vote up 
public static void main(String[] args) {
	Connection conn = null;

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

	User user = null;

	try {
		// Loading the Driver using DbUtils static method
		DbUtils.loadDriver(driver);
		conn = DriverManager.getConnection(url, usr, pwd);
		QueryRunner query = new QueryRunner();
		user = (User) query.query(conn,
				"select * from user where userId=3", new BeanHandler<User>(
						User.class));

		System.out.println("User Object::  " + user.getUserId() + "\t"
				+ user.getFirstName() + "\t" + user.getLastName() + "\t"
				+ user.getEmailId());

	} catch (SQLException se) {
		se.printStackTrace();
	} finally {
		DbUtils.closeQuietly(conn);
	}
}
Example 18
Source File: JdbcRegistry.java    From apiman with Apache License 2.0 4 votes vote down vote up 
/**
 * Simply pull the client from storage.
 * @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);
}
Example 19
Source File: UpdateManager.java    From xiaoyaoji with GNU General Public License v3.0 4 votes vote down vote up 
private List<Interface> getInterfaces(QueryRunner qr, Connection connection, int start, int limit) throws SQLException {
    return qr.query(connection, "select * from interface limit ?,?", new BeanListHandler<>(Interface.class), start, limit);
}
Example 20
Source File: JdbcRegistry.java    From apiman with Apache License 2.0 2 votes vote down vote up 
/**
 * Gets an api from the DB.
 * @param organizationId
 * @param apiId
 * @param apiVersion
 * @throws SQLException
 */
protected Api getApiInternal(String organizationId, String apiId, String apiVersion) throws SQLException {
    QueryRunner run = new QueryRunner(ds);
    return run.query("SELECT bean FROM gw_apis WHERE org_id = ? AND id = ? AND version = ?", //$NON-NLS-1$
            Handlers.API_HANDLER, organizationId, apiId, apiVersion);
}