Added ReturnList & ReturnOne to insert/update/delete

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Added ReturnList & ReturnOne to insert/update/delete

maajeck
Hi,

I want to get results from an insert, update or delete. The reason for this is that PostgreSQL and probably others have features to do smart things in a queries.

An example
WITH a AS (
UPDATE tab
SET cola=#{cola}
WHERE colb=#{colb}
RETURING *
)
SELECT a.cola + 100, tac.colc
FROM a
JOIN tac on a.cola=tac.colc

Or:
UPDATE tab
SET cola = #{cola}
WHERE colb=5
RETURNING *

You can also try wrap it into a select, but when running it in a transaction the session in not marked as dirty and the rollback is not triggered, causing other problems in my application. The first solution I override SqlSession where all selects are marked as dirty, though by looking at the code it didn't seem the right solution. The next solution was to add an extra attribute to select to tell the select should be marked as dirty, but that will put the responsibility to the user. So I created a patch to add functions like updateReturnOne/updateReturnList to resolve this.

Sample mapper-file:
<update id="updatePerformanceData" parameterType="map" resultMap="selectBean">
UPDATE tab
SET cola = #{cola}
WHERE colb=5
RETURNING *
</update>

Snipped to: (change to selectOne to see the problem)
try (SqlSession session = openSession(client)) {
    if (session == null) {
        throw new DbException(name, "Could not open a session on " + dbConnection);
    }
Object obj = session.updateReturnOne("updatePerformanceData",params);
if(obj instanceof SelectBean) {
    SelectBean bean = (SelectBean)obj;
// do something
} else {
obj = session.insertReturnOne("insertPerformanceDate",params);
// do somethingelse
}
throw RuntimeException("Create problem");
}
// check table tab if everything is reverted

I've attached the patch-file, though I can create a pull request to github of it (after I created test-cases for it).


Regards,
Marco

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

returnOne.patch (26K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Added ReturnList & ReturnOne to insert/update/delete

maajeck
Hi,

I wrote this email in the holiday season, so probably nobody has seen it. A patched version is currently running for a couple of months without any problems. Can someone add it to the next branch / tag?


Regards,
Marco

On Wed, Jul 27, 2016 at 9:05 AM, maajeck <[hidden email]> wrote:
Hi,

I want to get results from an insert, update or delete. The reason for this is that PostgreSQL and probably others have features to do smart things in a queries.

An example
WITH a AS (
UPDATE tab
SET cola=#{cola}
WHERE colb=#{colb}
RETURING *
)
SELECT a.cola + 100, tac.colc
FROM a
JOIN tac on a.cola=tac.colc

Or:
UPDATE tab
SET cola = #{cola}
WHERE colb=5
RETURNING *

You can also try wrap it into a select, but when running it in a transaction the session in not marked as dirty and the rollback is not triggered, causing other problems in my application. The first solution I override SqlSession where all selects are marked as dirty, though by looking at the code it didn't seem the right solution. The next solution was to add an extra attribute to select to tell the select should be marked as dirty, but that will put the responsibility to the user. So I created a patch to add functions like updateReturnOne/updateReturnList to resolve this.

Sample mapper-file:
<update id="updatePerformanceData" parameterType="map" resultMap="selectBean">
UPDATE tab
SET cola = #{cola}
WHERE colb=5
RETURNING *
</update>

Snipped to: (change to selectOne to see the problem)
try (SqlSession session = openSession(client)) {
    if (session == null) {
        throw new DbException(name, "Could not open a session on " + dbConnection);
    }
Object obj = session.updateReturnOne("updatePerformanceData",params);
if(obj instanceof SelectBean) {
    SelectBean bean = (SelectBean)obj;
// do something
} else {
obj = session.insertReturnOne("insertPerformanceDate",params);
// do somethingelse
}
throw RuntimeException("Create problem");
}
// check table tab if everything is reverted

I've attached the patch-file, though I can create a pull request to github of it (after I created test-cases for it).


Regards,
Marco

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Added ReturnList & ReturnOne to insert/update/delete

Jeff Butler
If you make a pull request with tests it is much more likely that someone will review it.

I could see this could be useful for some stored procedure calls also.  We would need to see what the impact is in the code and that would be much easier with a pull request.

Jeff Butler



On Fri, Oct 21, 2016 at 10:11 AM Marco van Eck <[hidden email]> wrote:
Hi,

I wrote this email in the holiday season, so probably nobody has seen it. A patched version is currently running for a couple of months without any problems. Can someone add it to the next branch / tag?


Regards,
Marco

On Wed, Jul 27, 2016 at 9:05 AM, maajeck <[hidden email]> wrote:
Hi,

I want to get results from an insert, update or delete. The reason for this is that PostgreSQL and probably others have features to do smart things in a queries.

An example
WITH a AS (
UPDATE tab
SET cola=#{cola}
WHERE colb=#{colb}
RETURING *
)
SELECT a.cola + 100, tac.colc
FROM a
JOIN tac on a.cola=tac.colc

Or:
UPDATE tab
SET cola = #{cola}
WHERE colb=5
RETURNING *

You can also try wrap it into a select, but when running it in a transaction the session in not marked as dirty and the rollback is not triggered, causing other problems in my application. The first solution I override SqlSession where all selects are marked as dirty, though by looking at the code it didn't seem the right solution. The next solution was to add an extra attribute to select to tell the select should be marked as dirty, but that will put the responsibility to the user. So I created a patch to add functions like updateReturnOne/updateReturnList to resolve this.

Sample mapper-file:
<update id="updatePerformanceData" parameterType="map" resultMap="selectBean">
UPDATE tab
SET cola = #{cola}
WHERE colb=5
RETURNING *
</update>

Snipped to: (change to selectOne to see the problem)
try (SqlSession session = openSession(client)) {
    if (session == null) {
        throw new DbException(name, "Could not open a session on " + dbConnection);
    }
Object obj = session.updateReturnOne("updatePerformanceData",params);
if(obj instanceof SelectBean) {
    SelectBean bean = (SelectBean)obj;
// do something
} else {
obj = session.insertReturnOne("insertPerformanceDate",params);
// do somethingelse
}
throw RuntimeException("Create problem");
}
// check table tab if everything is reverted

I've attached the patch-file, though I can create a pull request to github of it (after I created test-cases for it).


Regards,
Marco

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.