Java Sql2o类代码示例

import org.sql2o.Sql2o; //导入依赖的package包/类
@Test
public void testInsertAdsTxtRecords() throws SQLException {
    List<AdsTxtRecord> adsTxtRecords = new ArrayList<>();
    AdsTxtRecord adsTxtRecord = new AdsTxtRecord();
    adsTxtRecord.setInsertDate(new DateTime());
    adsTxtRecord.setCertAuthId("CertAuthId");
    adsTxtRecord.setAccType("AccType");
    adsTxtRecord.setAdServingDomain("AdServingDomain");
    adsTxtRecord.setPublisherAccId("PublisherAccId");
    adsTxtRecord.setSourceDomain("SourceDomain");
    adsTxtRecords.add(adsTxtRecord);
    jdbcDataStore.insertAdsTxtRecords(adsTxtRecords);

    List<String> results;
    try (org.sql2o.Connection con = new Sql2o(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD).open()) {
        results = con.createQuery("SELECT source_domain FROM adstxt_results").executeAndFetch(String.class);
    }
    assertEquals(1, results.size());
    assertEquals("SourceDomain", results.get(0));
}
 

import org.sql2o.Sql2o; //导入依赖的package包/类
@SuppressWarnings("unchecked")
public static void main(final String[] args) throws Exception {
	final Map<String, Object> opts = new Docopt(doc).parse(args);
	final Configuration config = Configuration
			.readYamlFile(opts.get("<dbconfig>").toString());
	opts.putIfAbsent("--external", false);

	if ((boolean) opts.get("server"))
		Server.startServer(
				new DatabaseService(prepareSql2o(config), config), config, (boolean) opts.get("--external"));
	else if ((boolean) opts.get("import"))
		DatabaseService.importTables(config, prepareSql2o(config));
	else if ((boolean) opts.get("initialize"))
		DatabaseService.initializeTables(prepareSql2o(config));
	else if ((boolean) opts.get("demo")) {
		final Sql2o sql2o = prepareSql2o(config);
		DatabaseService.initializeTables(sql2o);
		DatabaseService.importTables(config, sql2o);
		Server.startServer(new DatabaseService(sql2o, config), config, (boolean) opts.get("--external"));
	} else if ((boolean) opts.get("error"))
		Server.startErrorServer(config,
				(ArrayList<String>) opts.get("<message>"));
	else
		throw new IllegalArgumentException();
}
 

import org.sql2o.Sql2o; //导入依赖的package包/类
public Map<String, Object> getMediaIdsForUseId(Long id) {
    Sql2o sql2o = configuration.getSql2oEpg2vdr();

    try (Connection con = sql2o.open()) {
        List<Map<String, Object>> result = con.createQuery(selectMediaIdsFromView + " WHERE cnt_useid = :eventId;") //
                .addParameter("eventId", id) //
                .executeAndFetchTable() //
                .asList();

        if ((result != null) && !result.isEmpty()) {
            return result.get(0);
        } else {
            return Collections.<String, Object>emptyMap();
        }
    }
}
 

import org.sql2o.Sql2o; //导入依赖的package包/类
@Override
public Map<String, Object> getEpgDataForRecording(String recFilename) {
    Sql2o sql2o = configuration.getSql2oHsqldb();

    try (Connection con = sql2o.open()) {
        List<Map<String, Object>> r = con.createQuery(selectFromRecordingList) //
                .addParameter("filename", recFilename) //
                .executeAndFetchTable() //
                .asList();

        if ((r != null) && (r.size() > 0)) {
            // make a deep copy of the result to be able to enrich data
            Map<String, Object> newResult = new HashMap<>();
            r.get(0).keySet().stream().forEach(s -> newResult.put(s, r.get(0).get(s)));

            newResult.putAll(extractDescription((String) newResult.get("longdescription")));
            return newResult;
        } else {
            return Collections.emptyMap();
        }
    }
}
 

import org.sql2o.Sql2o; //导入依赖的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.Sql2o; //导入依赖的package包/类
@Autowired
public ItemService(@Qualifier("Runelite SQL2O") Sql2o sql2o)
{
	this.sql2o = sql2o;

	try (Connection con = sql2o.open())
	{
		con.createQuery(CREATE_ITEMS)
			.executeUpdate();

		con.createQuery(CREATE_PRICES)
			.executeUpdate();

		try
		{
			con.createQuery(CREATE_PRICES_FK)
				.executeUpdate();
		}
		catch (Sql2oException ex)
		{
			// Ignore, happens when index already exists
		}
	}
}
 

import org.sql2o.Sql2o; //导入依赖的package包/类
@Autowired
public XteaService(
	@Qualifier("Runelite SQL2O") Sql2o sql2o,
	@Qualifier("Runelite Cache SQL2O") Sql2o cacheSql2o,
	CacheService cacheService
)
{
	this.sql2o = sql2o;
	this.cacheSql2o = cacheSql2o;
	this.cacheService = cacheService;

	try (Connection con = sql2o.beginTransaction())
	{
		con.createQuery(CREATE_SQL)
			.executeUpdate();
	}
}
 

import org.sql2o.Sql2o; //导入依赖的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.Sql2o; //导入依赖的package包/类
public Map<String, Object> getEpgDataForUseId(Long id) {
    Sql2o sql2o = configuration.getSql2oEpg2vdr();

    try (Connection con = sql2o.open()) {
        List<Map<String, Object>> result = con.createQuery(selectFromView + " WHERE cnt_useid = :useid") //
                .addParameter("useid", id) //
                .addParameter("unixtime", System.currentTimeMillis() / 1000L) //
                .executeAndFetchTable() //
                .asList();

        if ((result != null) && !result.isEmpty()) {
            return result.get(0);
        } else {
            return Collections.<String, Object>emptyMap();
        }
    }
}
 

import org.sql2o.Sql2o; //导入依赖的package包/类
public void recUpdate(String vdrUuid) {
    if (!configuration.isUseSyncMap()) {
        fullRecSync(vdrUuid);
        return;
    }

    Sql2o sql2o = configuration.getSql2oHsqldb();

    try (Connection con = sql2o.beginTransaction()) {
        // get sync id
        Integer syncId = getRecSyncId(vdrUuid, con);
        if (syncId == null) {
            // do a full sync
            fullRecSync(vdrUuid);
            return;
        }

        List<Recording> list = getJsonList(vdrUuid, "recordings/updates.json?" + createSyncStr(vdrUuid, syncId),
                "recordings", Recording.class).orElse(Collections.<Recording>emptyList());

        processRecordingList(vdrUuid, con, list);

        con.commit();
    }
}
 

import org.sql2o.Sql2o; //导入依赖的package包/类
public List<String> getImageFilenames(Long useId) {
    Sql2o sql2o = configuration.getSql2oEpg2vdr();

    try (Connection con = sql2o.open()) {
        List<String> result = con.createQuery(
                "select distinct ir.imagename from imagerefs ir, images i where ir.eventid = (select imageid from eventsviewplain where cnt_useid = :useid) and ir.imagename = i.imagename and i.image is not null") //
                .addParameter("useid", useId) //
                .executeAndFetch(String.class);

        if ((result != null) && !result.isEmpty()) {
            return result;
        } else {
            return Collections.<String>emptyList();
        }
    }
}
 

import org.sql2o.Sql2o; //导入依赖的package包/类
public void deleteRecording(String vdrUuid, String fileName) {
    Sql2o sql2o = configuration.getSql2oHsqldb();

    try (Connection con = sql2o.beginTransaction()) {
        con.createQuery("delete from recording where vdr_uuid = :vdruuid and file_name=:fileName") //
                .addParameter("vdruuid", vdrUuid) //
                .addParameter("fileName", fileName) //
                .executeUpdate();

        if (configuration.isUseSyncMap()) {
            delete(vdrUuid, "recordings" + JonglistoUtil.encodePath(fileName) + "?"
                    + createSyncStr(vdrUuid, getRecSyncId(vdrUuid, con)), null);
        } else {
            delete(vdrUuid, "recordings" + JonglistoUtil.encodePath(fileName), null);
        }

        con.commit();
    }

    if (configuration.isUseSyncMap()) {
        recUpdate(vdrUuid);
    } else {
        fullRecSync(vdrUuid);
    }
}
 

import org.sql2o.Sql2o; //导入依赖的package包/类
public void moveRecording(String vdrUuid, String source, String destination) {
    Sql2o sql2o = configuration.getSql2oHsqldb();

    try (Connection con = sql2o.beginTransaction()) {
        String oldName = con
                .createQuery("select name from recording where vdr_uuid = :vdruuid and file_name = :fileName") //
                .addParameter("fileName", source) //
                .addParameter("vdruuid", vdrUuid) //
                .executeScalar(String.class);

        // build new name
        // Directories have to be splitted by '~' not '/'
        String name = null;

        destination = destination.replaceAll("/", "~");

        int idx = oldName.lastIndexOf("~");
        if (idx == -1) {
            name = destination + "~" + name;
        } else {
            name = destination + "~" + oldName.substring(idx + 1, oldName.length());
        }

        moveRecording(con, vdrUuid, source, name);
    }
}
 

import org.sql2o.Sql2o; //导入依赖的package包/类
@Override
public void saveProvider(Provider mainProv, Provider secProv) {
    Sql2o sql2o = configuration.getSql2oHsqldb();

    try (Connection con = sql2o.beginTransaction()) {
        con.createQuery("delete from configuration where name in ('mainprov', 'secprov')").executeUpdate();

        con.createQuery("insert into configuration (name, val) values (:name, :prov)") //
                .addParameter("name", "mainprov") //
                .addParameter("prov", mainProv.toString()) //
                .executeUpdate();

        con.createQuery("insert into configuration (name, val) values (:name, :prov)") //
                .addParameter("name", "secprov") //
                .addParameter("prov", secProv.toString()) //
                .executeUpdate();

        con.commit();
    }
}
 

import org.sql2o.Sql2o; //导入依赖的package包/类
@Override
public void replaceIncludeChannel(List<String> channels) {
    Sql2o sql2o = configuration.getSql2oHsqldb();

    try (Connection con = sql2o.beginTransaction()) {
        con.createQuery("delete from channel_include").executeUpdate();

        channels.stream().forEach(s -> {
            con.createQuery("insert into channel_include (channel_name) values (:ch)") //
                    .addParameter("ch", s) //
                    .executeUpdate();
        });

        con.commit();
    }
}