Java Sql2o.beginTransaction方法代码示例

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 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 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包/类
public void updateDatabaseSort(String vdrUuid) {
    Sql2o sql2o = configuration.getSql2oEpg2vdr();

    try (Connection con = sql2o.beginTransaction()) {
        Map<String, List<Object>> mapping = readMapping(vdrUuid);

        mapping.values().stream().forEach(m -> {
            List<ChannelModel> channels = m.stream() //
                    .filter(c -> c instanceof ChannelModel).map(c -> (ChannelModel) c)
                    .collect(Collectors.toList());

            channels.stream() //
                .forEach(c -> {
                    con.createQuery("update channelmap set ord=:ord where channelid=:id") //
                        .addParameter("ord", c.getNumber()) //
                        .addParameter("id", c.getChannelId()) //
                        .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();
    }
}
 

import org.sql2o.Sql2o; //导入方法依赖的package包/类
@Test
public void sql2oInsert() throws Exception {
    Sql2o sql2o = new Sql2o("jdbc:postgresql://localhost/postgres", "postgres", "");

    ProductStockEvent event = new ProductStockEvent(
            -1, null, "refNi", "NewEntry", "{\"key\":\"value\"}"
    );

    try (org.sql2o.Connection connection = sql2o.beginTransaction()) {
        connection.createQuery(
                "insert into warehouse.ProductStockHistory(refNo, type, content) " +
                        "values (:refNo, :type, cast(:content AS json))")
                .addParameter("refNo", event.getRefNo())
                .addParameter("type", event.getType())
                .addParameter("content", event.getContent())
                .executeUpdate();
        connection.commit();
    }

    List<ProductStockEvent> events = sql2o.open()
            .createQuery("select * from warehouse.ProductStockHistory where refNo = :refNo order by id")
            .addParameter("refNo", event.getRefNo())
            .executeAndFetch(ProductStockEvent.class);

    Assertions.assertThat(events)
            .isNotEmpty()
            .extracting(ProductStockEvent::getRefNo, ProductStockEvent::getType, ProductStockEvent::getContent)
            .contains(tuple(event.getRefNo(), event.getType(), event.getContent()));
}
 

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

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

        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 void deleteSearchTimer(Long id) {
    Sql2o sql2o = configuration.getSql2oEpg2vdr();

    try (Connection con = sql2o.beginTransaction()) {
        con.createQuery("update searchtimers set state = 'D' where id = :ID").addParameter("ID", id)
                .executeUpdate();

        con.commit();
    }
}
 

import org.sql2o.Sql2o; //导入方法依赖的package包/类
public void saveExtendedChannelConf(List<String> groupsToSave, Map<String, List<ExtendedChannel>> channelsToSave) {
    Sql2o sql2o = configuration.getSql2oHsqldb();

    try (Connection con = sql2o.beginTransaction()) {
        // delete all existing data
        con.createQuery("delete from channel_conf_group").executeUpdate();
        con.createQuery("delete from channel_conf_channel").executeUpdate();

        // save all groups and channels
        AtomicInteger idx = new AtomicInteger();
        AtomicInteger gidx = new AtomicInteger();
        groupsToSave.stream().forEach(s -> {
            con.createQuery("insert into channel_conf_group (id, groupname) values (:id, :name)") //
                    .addParameter("id", idx.get()) //
                    .addParameter("name", s) //
                    .executeUpdate();

            if ((channelsToSave.get(s) != null) && (channelsToSave.get(s).size() > 0)) {                
                channelsToSave.get(s).stream().forEach(c -> {
                    if ((c != null) && !c.getName().endsWith(" OBSOLETE")) {
                        con.createQuery(
                                "insert into channel_conf_channel (id, name, group_id) values (:id, :name, :group_id)") //
                                .addParameter("id", gidx.getAndIncrement()) //
                                .addParameter("name", c.getName()) //
                                .addParameter("group_id", idx.get()) //
                                .executeUpdate();
                    }
                });
            }

            idx.incrementAndGet();
        });

        con.commit();
    }
}
 

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

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

        String syncIdStr = "";

        if (configuration.isUseSyncMap()) {
            // create new sync id if an existing syncid cannot be found
            Integer syncId = getRecSyncId(vdrUuid, con);

            if (syncId == null) {
                con.createQuery(
                        "MERGE INTO configuration USING (VALUES(:name, concat((next value for seq_recording_sync),''))) AS vals(x,y) ON configuration.name = vals.x WHEN MATCHED THEN UPDATE SET configuration.val = vals.y WHEN NOT MATCHED THEN INSERT VALUES vals.x, vals.y") //
                        .addParameter("name", "rec_" + vdrUuid) //
                        .executeUpdate();

                syncId = con.createQuery("select current value for seq_recording_sync from (VALUES(0))") //
                        .executeScalar(Integer.class);
            }

            syncIdStr = "?" + createSyncStr(vdrUuid, syncId);
        } else {
            syncIdStr = "";
        }

        // get recordings
        List<Recording> list = getJsonList(vdrUuid, "recordings/.json" + syncIdStr, "recordings", Recording.class) //
                .orElse(Collections.<Recording>emptyList());

        // insert all recordings
        list.stream().forEach(s -> insertRecording(con, s, vdrUuid));

        con.commit();
    }
}
 

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

    Sql2o sql2o = configuration.getSql2oHsqldb();

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

        // Create parameter body
        List<Map<String, Object>> recList = con
                .createQuery("select file_name, hash from recording where vdr_uuid = :vdruuid") //
                .addParameter("vdruuid", vdrUuid) //
                .executeAndFetchTable() //
                .asList();

        String body = "";
        for (Map<String, Object> s : recList) {
            body += "recordings[]=" + JonglistoUtil.encodePath((String) s.get("file_name")) + "," + s.get("hash")
                    + "\n";
        }

        List<Recording> list;
        list = postAndGetList(vdrUuid, "recordings/sync.json?" + createSyncStr(vdrUuid, syncId), body, "recordings",
                Recording.class);
        processRecordingList(vdrUuid, con, list);

        con.commit();
    }
}
 

import org.sql2o.Sql2o; //导入方法依赖的package包/类
public List<String> getRecDirectories(String vdrUuid) {
    Sql2o sql2o = configuration.getSql2oHsqldb();

    List<String> tmpResult;
    try (Connection con = sql2o.beginTransaction()) {
        tmpResult = con.createQuery("select name from recording where vdr_uuid = :vdruuid order by upper(name)") //
                .addParameter("vdruuid", vdrUuid).executeScalarList(String.class);
    }

    List<String> result = new ArrayList<String>();

    for (String node : tmpResult) {
        int idx = node.lastIndexOf("~");
        if (idx != -1) {
            String path = node.substring(0, idx);

            // check if path(element) already exists
            if (!result.contains(path)) {
                if (!result.stream().filter(s -> s.startsWith(path + "~")).findFirst().isPresent()) {
                    result.add(path);
                }
            }
        }
    }

    return result;
}