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

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);
	}
}
 

/**
 * 带可变参数查询,返回执行结果
 * 
 * @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;
}
 

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 <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;
    }
}
 

/**
 * @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;
}
 

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;
}
 

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());
    }
}
 

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<>();
}
 

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;
}
 

@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));
    }
}
 

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;
}
 

/**
 * @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();
    }
}
 

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<>();
}
 

/**
 * 将查询结果集转换成键值对列表返回。
 * 
 * @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);
}
 

/**
 * 将查询结果转换成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);
}
 

@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));
    }
}
 

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);
	}
}
 

/**
 * 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);
}
 

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);
}
 

/**
 * 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);
}