Java Query类代码示例

import org.sql2o.Query; //导入依赖的package包/类
public void importStuff(Path path) throws IOException {
	Stream<String> stream;
	if (!path.toFile().exists()
			&& new File(path.toFile().getPath() + ".gz").exists()) {
		path = Paths.get(path.toFile().getPath() + ".gz");
		stream = GZIPFiles.lines(path);
	} else
		stream = Files.lines(path);
	LOGGER.info("Starting import of {} into {}", path, tableName);
	final Iterator<String[]> iter = stream.map(x -> x.split(","))
			.iterator();
	long i = 1; //error if 0 due to criteria below
	Query query = null;
	try {
		while (iter.hasNext()) {
			if (query == null)
				query = sql2o.beginTransaction()
						.createQuery(String.format(sql, tableName));

			final String[] data = iter.next();
			addParameters(data, query).addParameter("corpus", corpusId)
					.addToBatch();
			//Nicer batch import leaks memory, thus using new connections is recommended
			++i;

			if ((i % IMPORT_BATCH_SIZE) == 0)
				query.executeBatch();
			if ((i % IMPORT_COMMIT_SIZE) == 0) {
				query.getConnection().commit();
				LOGGER.info("Finishd import of {}", i);
				query = null;
			}
		}
	} finally {
		if (query != null)
			query.executeBatch().commit();
	}
	LOGGER.info("Finished import of {} rows from {} into {}", i, path,
			tableName);
}
 

import org.sql2o.Query; //导入依赖的package包/类
@Override
public Class<? extends Page> getHomePage() {
    Sql2o sql2o = Spring.getBean(Sql2o.class);
    try (Connection connection = sql2o.open()) {
        Query query = connection.createQuery("SELECT groovy.java_class FROM setting INNER JOIN page on setting.value = page.page_id INNER JOIN groovy on page.groovy_id = groovy.groovy_id where setting.name = 'home_page'");
        String javaClass = query.executeAndFetchFirst(String.class);
        if (Strings.isNullOrEmpty(javaClass)) {
            return DashboardPage.class;
        } else {
            GroovyClassLoader classLoader = Spring.getBean(GroovyClassLoader.class);
            try {
                return (Class<? extends Page>) classLoader.loadClass(javaClass);
            } catch (ClassNotFoundException | ClassCastException e) {
                return DashboardPage.class;
            }
        }
    }
}
 

import org.sql2o.Query; //导入依赖的package包/类
@Test
public void testUUID() throws Exception {

    try (Connection connection = sql2o.beginTransaction()) {
        connection.createQuery("create table uuidtest(id uuid primary key, val uuid null)").executeUpdate();

        UUID uuid1 = UUID.randomUUID();
        UUID uuid2 = UUID.randomUUID();
        UUID uuid3 = UUID.randomUUID();
        UUID uuid4 = null;

        Query insQuery = connection.createQuery("insert into uuidtest(id, val) values (:id, :val)");
        insQuery.addParameter("id", uuid1).addParameter("val", uuid2).executeUpdate();
        insQuery.addParameter("id", uuid3).addParameter("val", uuid4).executeUpdate();

        Table table = connection.createQuery("select * from uuidtest").executeAndFetchTable();

        assertThat((UUID)table.rows().get(0).getObject("id"), is(equalTo(uuid1)));
        assertThat((UUID)table.rows().get(0).getObject("val"), is(equalTo(uuid2)));
        assertThat((UUID)table.rows().get(1).getObject("id"), is(equalTo(uuid3)));
        assertThat(table.rows().get(1).getObject("val"), is(nullValue()));

        connection.rollback();
    }

}
 

import org.sql2o.Query; //导入依赖的package包/类
public <S extends Serializable> S save() {
    QueryMeta  queryMeta = SqlBuilder.buildInsertSql(this);
    Connection con       = getConn();
    log.debug(EXECUTE_SQL_PREFIX + " => {}", queryMeta.getSql());
    log.debug(PARAMETER_PREFIX + " => {}", this);
    Query query = con.createQuery(queryMeta.getSql()).bind(this);
    try {
        S s = (S) query.executeUpdate().getKey();
        if (null == Base.connectionThreadLocal.get() && !con.getJdbcConnection().getAutoCommit()) {
            con.commit();
        }
        return s;
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
 

import org.sql2o.Query; //导入依赖的package包/类
private int invoke(QueryMeta queryMeta) {
    log.debug(EXECUTE_SQL_PREFIX + " => {}", queryMeta.getSql());
    log.debug(PARAMETER_PREFIX + " => {}", Arrays.toString(queryMeta.getParams()));
    Connection con   = getConn();
    Query      query = con.createQuery(queryMeta.getSql()).withParams(queryMeta.getParams());
    if (queryMeta.hasColumnMapping()) {
        queryMeta.getColumnMapping().forEach(query::addColumnMapping);
    }
    int result = query.executeUpdate().getResult();
    try {
        if (null == Base.connectionThreadLocal.get() && !con.getJdbcConnection().getAutoCommit()) {
            con.commit();
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
    return result;
}
 

import org.sql2o.Query; //导入依赖的package包/类
public <T> List<T> queryAll(Class<T> type, String sql, Object... args) {
    int pos = 1;

    while (sql.contains(SQL_QM)) {
        sql = sql.replaceFirst("\\?", ":p" + (pos++));
    }

    PageRow pageRow = Base.pageLocal.get();
    sql = SqlBuilder.appendPageParams(sql, pageRow);
    args = args == null ? new Object[]{} : args;

    try (Connection con = getSql2o().open()) {
        log.debug(EXECUTE_SQL_PREFIX + " => {}", sql);
        log.debug(PARAMETER_PREFIX + " => {}", Arrays.toString(args));
        this.cleanParam();
        Query     query     = con.createQuery(sql).withParams(args).throwOnMappingFailure(false);
        QueryMeta queryMeta = SqlBuilder.buildFindAllSql(this, null);
        if (queryMeta.hasColumnMapping()) {
            queryMeta.getColumnMapping().forEach(query::addColumnMapping);
        }
        return query.executeAndFetch(type);
    }
}
 

import org.sql2o.Query; //导入依赖的package包/类
public <T extends ActiveRecord> T find() {
    QueryMeta queryMeta = SqlBuilder.buildFindSql(this);
    Class<T>  type      = (Class<T>) getClass();
    try (Connection con = getSql2o().open()) {
        this.cleanParam();

        log.debug(EXECUTE_SQL_PREFIX + " => {}", queryMeta.getSql());
        log.debug(PARAMETER_PREFIX + " => {}", Arrays.toString(queryMeta.getParams()));

        Query query = con.createQuery(queryMeta.getSql()).withParams(queryMeta.getParams()).throwOnMappingFailure(false);
        if (queryMeta.hasColumnMapping()) {
            queryMeta.getColumnMapping().forEach(query::addColumnMapping);
        }
        return query.executeAndFetchFirst(type);
    }
}
 

import org.sql2o.Query; //导入依赖的package包/类
public void insertSearchTimer(SearchTimer timer) {
    Sql2o sql2o = configuration.getSql2oEpg2vdr();

    try (Connection con = sql2o.beginTransaction()) {
        String sql = "INSERT INTO epg2vdr.searchtimers " + //
                "(inssp, channelids, chexclude, chformat, name, expression, expression1, searchmode, searchfields, searchfields1, casesensitiv, repeatfields, "
                + // "
                "episodename, season, seasonpart, category, genre, year, tipp, noepgmatch, type, state, namingmode, active, source, hits, vdruuid, weekdays, "
                + //
                "nextdays, starttime, endtime, directory, priority, lifetime, vps, childlock) " + //
                "VALUES(UNIX_TIMESTAMP(), :channelids, :chexclude, :chformat, :name, :expression, :expression1, :searchmode, :searchfields, :searchfields1, :casesensitiv, :repeatfields, "
                + //
                ":episodename, :season, :seasonpart, :category, :genre, :year, :tipp, :noepgmatch, :type, :state, :namingmode, :active, :source, :hits, :vdruuid, :weekdays, "
                + //
                ":nextdays, :starttime, :endtime, :directory, :priority, :lifetime, :vps, :childlock)";

        Query query = con.createQuery(sql);
        query.getParamNameToIdxMap().keySet().stream()
                .forEach(key -> query.addParameter(key, timer.getRawDbData().get(key)));
        query.executeUpdate();

        con.commit();
    }
}
 

import org.sql2o.Query; //导入依赖的package包/类
@Test
public void testSql2O() throws SQLException, ParseException {
    Connection connection = DbHelper.objectDb();
    try {
        SingleConnectionDataSource scds = new SingleConnectionDataSource(connection, true);
        Sql2o sql2o = new Sql2o(scds);

        Query query = sql2o.open().createQuery(DbHelper.TEST_DB_OBJECT_QUERY);
        query.setAutoDeriveColumnNames(true);
        query.setResultSetHandlerFactoryBuilder(new SfmResultSetHandlerFactoryBuilder());

        List<DbObject> dbObjects = query.executeAndFetch(DbObject.class);

        assertEquals(1, dbObjects.size());
        DbHelper.assertDbObjectMapping(dbObjects.get(0));

    } finally {
        connection.close();
    }
}
 

import org.sql2o.Query; //导入依赖的package包/类
private Map<String, String> getClosedInfo() {
        Calendar c = Calendar.getInstance();
        c.set(Calendar.MINUTE, 0);
        c.set(Calendar.SECOND, 0);
        c.set(Calendar.MILLISECOND, 0);
        c.set(Calendar.HOUR_OF_DAY, 0);

        Map<String, String> map = new HashMap<>();

        String sql = "select a.JH jh,a.RQ rq,b.DMMC dmmc,a.BZ bz FROM [email protected] a left join [email protected] b on a.bzdm=b.dm  where a.RQ=:dateTime " + //
                " and a.jh in (select code from t_end_tag where type='YOU_JING') and a.BZDM is not null ";

        try (Connection con = sql2o.open()) {
            org.sql2o.Query query = con.createQuery(sql);
            query.addParameter("dateTime", c.getTime());
            List<Row> dataList = query.executeAndFetchTable().rows();
            for (Row row : dataList) {
                String bz = row.getString("BZ") == null ? "" : ":" + row.getString("BZ");
                map.put(row.getString("jh"), row.getString("dmmc") + bz);
//                log.info(map.get(row.getString("jh")));
            }
        }
        return map;
    }
 

import org.sql2o.Query; //导入依赖的package包/类
/**
     * 三个月内生产时间平均值为0
     *
     * @return
     */
    private Map<String, String> getChangTingClosedInfo() {
        Calendar endTime = Calendar.getInstance();
        Calendar startTime = Calendar.getInstance();
        startTime.set(Calendar.MONTH, endTime.get(Calendar.MONTH) - 3);

        Map<String, String> map = new HashMap<>();

        String sql = "select * from (select avg(scsj) a,jh FROM [email protected] where jh in (select code from t_end_tag where type='YOU_JING')  "
                + " and rq>=:startTime and rq<=:endTime group by jh) "
                + " where a<1 ";

        try (Connection con = sql2o.open()) {
            org.sql2o.Query query = con.createQuery(sql);
            query.addParameter("startTime", startTime.getTime());
            query.addParameter("endTime", endTime.getTime());
            List<Row> dataList = query.executeAndFetchTable().rows();
            for (Row row : dataList) {
//                String bz = row.getString("BZ") == null ? "" : ":" + row.getString("BZ");
                map.put(row.getString("jh"), "CTJ");
                log.info(map.get(row.getString("jh")));
            }
        }
        return map;
    }
 

import org.sql2o.Query; //导入依赖的package包/类
@Override
protected Query addParameters(final String[] s, final Query query) {
	query.addParameter("word", Integer.valueOf(s[0]));
	query.addParameter("year", Integer.valueOf(s[1]));
	query.addParameter("embedding",
			Joiner.on(" ").join(Arrays.copyOfRange(s, 2, s.length)));
	return query;
}
 

import org.sql2o.Query; //导入依赖的package包/类
@Override
protected Query addParameters(final String[] s, final Query query) {
	query.addParameter("word1", Integer.valueOf(s[0]));
	query.addParameter("word2", Integer.valueOf(s[1]));
	query.addParameter("year", Integer.valueOf(s[2]));
	query.addParameter("association", Float.valueOf(s[3]));
	return query;
}
 

import org.sql2o.Query; //导入依赖的package包/类
@Override
protected Query addParameters(final String[] s, final Query query) {
	query.addParameter("word", Integer.valueOf(s[0]));
	query.addParameter("year", Integer.valueOf(s[1]));
	query.addParameter("frequency", Float.valueOf(s[2]));
	return query;
}
 

import org.sql2o.Query; //导入依赖的package包/类
@Override
protected Query addParameters(final String[] s, final Query query) {
	query.addParameter("word", Integer.valueOf(s[0]));
	query.addParameter("year", Integer.valueOf(s[1]));
	query.addParameter("clob",
			Joiner.on(" ").join(Arrays.copyOfRange(s, 2, s.length)));
	return query;
}