Examples with Connection org.sql2o.Connection used on opensource projects

Example 16 with Connection

use of org.sql2o.Connection in project runelite by runelite.

the class XpTrackerService method update.

public void update(String username, HiscoreResult hiscoreResult) {
    try (Connection con = sql2o.open()) {
        PlayerEntity playerEntity = findOrCreatePlayer(username);
        XpEntity currentXp = findXpAtTime(username, Instant.now());
        if (currentXp != null) {
            XpData hiscoreData = XpMapper.INSTANCE.hiscoreResultToXpData(hiscoreResult);
            XpData existingData = XpMapper.INSTANCE.xpEntityToXpData(currentXp);
            if (hiscoreData.equals(existingData)) {
                log.debug("Hiscore for {} already up to date", username);
                return;
            }
        }
        con.createQuery("insert into xp (player,attack_xp,defence_xp,strength_xp,hitpoints_xp,ranged_xp,prayer_xp,magic_xp,cooking_xp,woodcutting_xp," + "fletching_xp,fishing_xp,firemaking_xp,crafting_xp,smithing_xp,mining_xp,herblore_xp,agility_xp,thieving_xp,slayer_xp,farming_xp," + "runecraft_xp,hunter_xp,construction_xp,attack_rank,defence_rank,strength_rank,hitpoints_rank,ranged_rank,prayer_rank,magic_rank," + "cooking_rank,woodcutting_rank,fletching_rank,fishing_rank,firemaking_rank,crafting_rank,smithing_rank,mining_rank,herblore_rank," + "agility_rank,thieving_rank,slayer_rank,farming_rank,runecraft_rank,hunter_rank,construction_rank,overall_rank) values (:player,:attack_xp,:defence_xp," + ":strength_xp,:hitpoints_xp,:ranged_xp,:prayer_xp,:magic_xp,:cooking_xp,:woodcutting_xp,:fletching_xp,:fishing_xp,:firemaking_xp," + ":crafting_xp,:smithing_xp,:mining_xp,:herblore_xp,:agility_xp,:thieving_xp,:slayer_xp,:farming_xp,:runecraft_xp,:hunter_xp," + ":construction_xp,:attack_rank,:defence_rank,:strength_rank,:hitpoints_rank,:ranged_rank,:prayer_rank,:magic_rank,:cooking_rank," + ":woodcutting_rank,:fletching_rank,:fishing_rank,:firemaking_rank,:crafting_rank,:smithing_rank,:mining_rank,:herblore_rank," + ":agility_rank,:thieving_rank,:slayer_rank,:farming_rank,:runecraft_rank,:hunter_rank,:construction_rank,:overall_rank)").addParameter("player", playerEntity.getId()).addParameter("attack_xp", hiscoreResult.getAttack().getExperience()).addParameter("defence_xp", hiscoreResult.getDefence().getExperience()).addParameter("strength_xp", hiscoreResult.getStrength().getExperience()).addParameter("hitpoints_xp", hiscoreResult.getHitpoints().getExperience()).addParameter("ranged_xp", hiscoreResult.getRanged().getExperience()).addParameter("prayer_xp", hiscoreResult.getPrayer().getExperience()).addParameter("magic_xp", hiscoreResult.getMagic().getExperience()).addParameter("cooking_xp", hiscoreResult.getCooking().getExperience()).addParameter("woodcutting_xp", hiscoreResult.getWoodcutting().getExperience()).addParameter("fletching_xp", hiscoreResult.getFletching().getExperience()).addParameter("fishing_xp", hiscoreResult.getFishing().getExperience()).addParameter("firemaking_xp", hiscoreResult.getFiremaking().getExperience()).addParameter("crafting_xp", hiscoreResult.getCrafting().getExperience()).addParameter("smithing_xp", hiscoreResult.getSmithing().getExperience()).addParameter("mining_xp", hiscoreResult.getMining().getExperience()).addParameter("herblore_xp", hiscoreResult.getHerblore().getExperience()).addParameter("agility_xp", hiscoreResult.getAgility().getExperience()).addParameter("thieving_xp", hiscoreResult.getThieving().getExperience()).addParameter("slayer_xp", hiscoreResult.getSlayer().getExperience()).addParameter("farming_xp", hiscoreResult.getFarming().getExperience()).addParameter("runecraft_xp", hiscoreResult.getRunecraft().getExperience()).addParameter("hunter_xp", hiscoreResult.getHunter().getExperience()).addParameter("construction_xp", hiscoreResult.getConstruction().getExperience()).addParameter("attack_rank", hiscoreResult.getAttack().getRank()).addParameter("defence_rank", hiscoreResult.getDefence().getRank()).addParameter("strength_rank", hiscoreResult.getStrength().getRank()).addParameter("hitpoints_rank", hiscoreResult.getHitpoints().getRank()).addParameter("ranged_rank", hiscoreResult.getRanged().getRank()).addParameter("prayer_rank", hiscoreResult.getPrayer().getRank()).addParameter("magic_rank", hiscoreResult.getMagic().getRank()).addParameter("cooking_rank", hiscoreResult.getCooking().getRank()).addParameter("woodcutting_rank", hiscoreResult.getWoodcutting().getRank()).addParameter("fletching_rank", hiscoreResult.getFletching().getRank()).addParameter("fishing_rank", hiscoreResult.getFishing().getRank()).addParameter("firemaking_rank", hiscoreResult.getFiremaking().getRank()).addParameter("crafting_rank", hiscoreResult.getCrafting().getRank()).addParameter("smithing_rank", hiscoreResult.getSmithing().getRank()).addParameter("mining_rank", hiscoreResult.getMining().getRank()).addParameter("herblore_rank", hiscoreResult.getHerblore().getRank()).addParameter("agility_rank", hiscoreResult.getAgility().getRank()).addParameter("thieving_rank", hiscoreResult.getThieving().getRank()).addParameter("slayer_rank", hiscoreResult.getSlayer().getRank()).addParameter("farming_rank", hiscoreResult.getFarming().getRank()).addParameter("runecraft_rank", hiscoreResult.getRunecraft().getRank()).addParameter("hunter_rank", hiscoreResult.getHunter().getRank()).addParameter("construction_rank", hiscoreResult.getConstruction().getRank()).addParameter("overall_rank", hiscoreResult.getOverall().getRank()).executeUpdate();
    }
}

Example 17 with Connection

use of org.sql2o.Connection in project runelite by runelite.

the class XteaService method submit.

@RequestMapping(method = POST)
public void submit(@RequestBody XteaRequest xteaRequest) {
    try (Connection con = sql2o.beginTransaction()) {
        CacheEntry cache = cacheService.findMostRecent();
        if (cache == null) {
            throw new InternalServerErrorException("No most recent cache");
        }
        Query query = con.createQuery("insert into xtea (region, rev, key1, key2, key3, key4) " + "values (:region, :rev, :key1, :key2, :key3, :key4)");
        for (XteaKey key : xteaRequest.getKeys()) {
            int region = key.getRegion();
            int[] keys = key.getKeys();
            XteaEntry xteaEntry = findLatestXtea(con, region);
            if (keys.length != 4) {
                throw new IllegalArgumentException("Key length must be 4");
            }
            // already have these?
            if (xteaEntry != null && xteaEntry.getKey1() == keys[0] && xteaEntry.getKey2() == keys[1] && xteaEntry.getKey3() == keys[2] && xteaEntry.getKey4() == keys[3]) {
                continue;
            }
            if (!checkKeys(cache, region, keys)) {
                continue;
            }
            query.addParameter("region", region).addParameter("rev", xteaRequest.getRevision()).addParameter("key1", keys[0]).addParameter("key2", keys[1]).addParameter("key3", keys[2]).addParameter("key4", keys[3]).addToBatch();
        }
        query.executeBatch();
        con.commit();
    }
}

Example 18 with Connection

use of org.sql2o.Connection in project sql2o by aaberg.

the class H2Tests method testUUID.

/**
 * Ref issue #73
 */
@Test
public void testUUID() {
    try (Connection connection = new Sql2o(ds).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();
    }
}

Example 19 with Connection

use of org.sql2o.Connection in project sql2o by aaberg.

the class IssuesTest method testErrorWhenFieldDoesntExist.

/**
 * Test for issue #132 ( https://github.com/aaberg/sql2o/issues/132 )
 * Ref change done in pull request #75
 * Also see comment on google groups
 * https://groups.google.com/forum/#!topic/sql2o/3H4XJIv-i04
 *
 * If a column cannot be mapped to a property, an exception should be thrown. Today it is silently ignored.
 */
@Test
public void testErrorWhenFieldDoesntExist() {
    class LocalPojo {

        private long id;

        private String strVal;

        public long getId() {
            return id;
        }

        public String getStrVal() {
            return strVal;
        }
    }
    String createQuery = "create table testErrorWhenFieldDoesntExist(id_val integer primary key, str_val varchar(100))";
    try (Connection connection = sql2o.open()) {
        connection.createQuery(createQuery).executeUpdate();
        String insertSql = "insert into testErrorWhenFieldDoesntExist(id_val, str_val) values (:val1, :val2)";
        connection.createQuery(insertSql).addParameter("val1", 1).addParameter("val2", "test").executeUpdate();
        Exception ex = null;
        try {
            // This is expected to fail to map columns and throw an exception.
            LocalPojo p = connection.createQuery("select * from testErrorWhenFieldDoesntExist").executeAndFetchFirst(LocalPojo.class);
        } catch (Exception e) {
            ex = e;
        }
        assertNotNull(ex);
    }
}

Example 20 with Connection

use of org.sql2o.Connection in project sql2o by aaberg.

the class IssuesTest method testIgnoreSqlComments.

/**
 * Reproduce issue #142 (https://github.com/aaberg/sql2o/issues/142)
 */
@Test
public void testIgnoreSqlComments() {
    class ThePojo {

        public int id;

        public int intval;

        public String strval;
    }
    String createSql = "create table testIgnoreSqlComments(id integer primary key, intval integer, strval varchar(100))";
    String insertQuery = "insert into testIgnoreSqlComments (id, intval, strval)\n " + "-- It's a comment!\n" + "values (:id, :intval, :strval);";
    String fetchQuery = "select id, intval, strval\n" + "-- a 'comment'\n" + "from testIgnoreSqlComments\n" + "/* and, it's another type of comment!*/" + "where intval = :param";
    try (Connection connection = sql2o.open()) {
        connection.createQuery(createSql).executeUpdate();
        for (int idx = 0; idx < 100; idx++) {
            int intval = idx % 10;
            connection.createQuery(insertQuery).addParameter("id", idx).addParameter("intval", intval).addParameter("strval", "teststring" + idx).executeUpdate();
        }
        List<ThePojo> resultList = connection.createQuery(fetchQuery).addParameter("param", 5).executeAndFetch(ThePojo.class);
        assertEquals(10, resultList.size());
    }
}