Producing error when ids don't match, code review

[xragons]

Hello, I'm trying to port some more code over and this is the original method:

    public static LoginUser load(Client c, int id) {

        Connection con = null;

        PreparedStatement ps = null;

        ResultSet rs = null;

        try {

            con = DatabaseManager.getConnection(DatabaseType.STATE);

            ps = con.prepareStatement("SELECT * FROM `characters` WHERE `id` = ?");

            ps.setInt(1, id);

            rs = ps.executeQuery();

            if (!rs.next()) {

                LOG.log(Level.WARNING, "Client requested to load a nonexistent character w/ id {0} (account {1}).", new Object[]{id, c.getAccountId()});

                return null;

            }

            int accountid = rs.getInt(1);

            if (accountid != c.getAccountId()) { //we are aware of our accountid

                LOG.log(Level.WARNING, "Client account {0} is trying to load character {1} which belongs to account {2}", new Object[]{c.getAccountId(), id, accountid});

                return null;

            }

            byte world = rs.getByte(2);

            if (world != c.getWorld()) { 

                LOG.log(Level.WARNING, "Client account {0} is trying to load character {1} on world {2} but exists on world {3}", new Object[]{accountid, id, c.getWorld(), world});

                return null;

            }

            LoginUser user = new LoginUser();

            user.client = c;

            user.worldRanking = rs.getInt(36);

            user.worldRankingChange = rs.getInt(37) - user.worldRanking;

            user.jobRanking = rs.getInt(38);

            user.jobRankingChange = rs.getInt(39) - user.jobRanking;

            rs.close();

            ps.close();

            return user;

        } catch (SQLException ex) {

            LOG.log(Level.WARNING, "Could not load character " + id + " from database", ex);

            return null;

        } finally {

            DatabaseManager.cleanup(DatabaseType.STATE, rs, ps, con);

        }

    }

This is my converted method but, I want it to notify me if a the id's dont match (client -> db). This basically loads everything from the DB to my POJO LoginUser class.

public static LoginUser testLoad(Client client, int id) {
    DataSource dataSource = connection();
    DSLContext create = DSL.using(dataSource, SQLDialect.MYSQL_8_0);
    // map values to POJO
    LoginUser users  = create.select().from(CHARACTERS).
            where(CHARACTERS.ID.eq(id),
                    CHARACTERS.WORLD.eq(client.getWorld()),
                    CHARACTERS.ACCOUNTID.eq(client.getAccountId()))
            .fetchAny().into(LoginUser.class);
    users.client = client;

    users.worldRankingChange = users.worldRankingChange - users.worldRanking;
    users.jobRankingChange = users.jobRankingChange - users.jobRanking;

    return users;
}

How can I produce an error when if there's no id and return it null. Would I have to re-write my testLoad method to use records instead? like this:

public static LoginUser loadRecord(Client client, int id) {
    final DataSource ds = connection();
    final DSLContext ctx = DSL.using(ds, SQLDialect.MYSQL_8_0);

    for (Record record : ctx.select().from(CHARACTERS).where(CHARACTERS.ID.eq(id)).fetch()) {

        int account = record.get(CHARACTERS.ACCOUNTID);
        if (account != client.getAccountId()) {
            LOG.log(Level.WARNING, "Client account {0} is trying to load character {1} which belongs to account {2}", new Object[]{client.getAccountId(), id, account}); // c.accId, id
            return null;
        }

        byte world = record.get(CHARACTERS.WORLD);
        if (world != client.getWorld()) {
            LOG.log(Level.WARNING, "Client account {0} is trying to load character {1} on world {2} but exists on world {3}", new Object[]{account, id, client.getWorld(), world});
            return null;
        }

        LoginUser user = new LoginUser();
        user.client = client;
        user.jobRanking = record.get(CHARACTERS.JOBRANKCURRENTPOS);
        // more code below....

        return user;
    }

    return null;
}

or use result sets like this:

public static LoginUser loadWithResultSet(Client client, int id) {
    final DataSource ds = connection();
    final DSLContext ctx = DSL.using(ds, SQLDialect.MYSQL_8_0);

    Result<CharactersRecord> result = ctx.selectFrom(CHARACTERS).where(CHARACTERS.ID.eq(id)).fetch();
    try (ResultSet rs = result.intoResultSet()) {

        if (!rs.next()) {
            LOG.log(Level.WARNING, "Client requested to load a nonexistent character w/ id {0} (account {1}).", new Object[]{id, client.getAccountId()});
            return null;
        }

        int accountid = rs.getInt(1);
        if (accountid != client.getAccountId()) {
            LOG.log(Level.WARNING, "Client account {0} is trying to load character {1} which belongs to account {2}",
                    new Object[]{client.getAccountId(), id, accountid});
            return null;
        }

        byte world = rs.getByte(2);
        if (world != client.getWorld()) {
            LOG.log(Level.WARNING, "Client account {0} is trying to load character {1} on world {2} but exists on world {3}", new Object[]{accountid, id, c.getWorld(), world});
            return null;
        }

        LoginUser user = new LoginUser();
        user.client = client;
        user.worldRanking = rs.getInt(36);
        user.worldRankingChange = rs.getInt(37) - user.worldRanking;
        user.jobRanking = rs.getInt(38);
        user.jobRankingChange = rs.getInt(39) - user.jobRanking;
        return user;
    } catch (SQLException e) {
        e.printStackTrace();
        return  null;
    }
}

Thanks !

[Lukas Eder]

Hello,

Your question seems to be: Should I use jOOQ 100%, or should I use jOOQ and then revert to using JDBC for some parts of my code?

Why? Because you don't want to / don't have time to refactor everything?

The answer is: I don't know :)

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[xragons]

Hey, 

Sorry I think I wasn't clear in what my question was. My aim is to fully use JOOQ and time isn't an issue for me. I just want to do it right. 

I generally wanted to know if I could log the same way as I would with JDBC if for ex (db accountid != client.accountid), (world id ! = client.world), 

I guess I can just add try catch to my first method to log it as a whole.

Thanks for replying!

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

2018-05-11 10:22 GMT+02:00 xragons <shabbar...@outlook.com>:

I generally wanted to know if I could log the same way as I would with JDBC if for ex (db accountid != client.accountid), (world id ! = client.world), 

Why not? Where do your doubts stem from?

 

I guess I can just add try catch to my first method to log it as a whole.

I don't know what that means :)