need help inserting a null value.

[Simon]

When I try and insert null into a nullable oracle field, I get:

### Error updating database. Cause: java.lang.NullPointerException
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.lang.NullPointerException
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:
100)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:
87)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
54)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:35)

My bean:

public class Game extends SkBean {
private long id;
private Long jackpotid = null;
:
Other fields and getters and setters
:

mapper.xml:

<insert id="insert">
<selectKey resultType="long" order="BEFORE" keyProperty="id">select
gameseq.nextval as value from dual</selectKey>
insert into game ( id, jackpotid)
values ( #{id, javaType=long},
#{jackpotid, javaType=Long, jdbcType=BIGINT}
)
/insert>

Oracle Table:
CREATE TABLE SK_ENGINE.GAME
(
ID INTEGER NOT NULL
,JACKPOTID INTEGER
)

Ive read the user guide a few times, and although there is no example
of inserting a null value, it seems to be saying that all I need to do
is set the jdbcType in the parameter values, which I have tried..
There must be something else?

[Nathan Maves]

any chance you are sending null in as the parameter object?

[Simon]

Yes, I am deliberatly - I.e. I want to insert null in to the db (i.e.
let the db use its default).

This is possible accourding to the user guide, but there is no
example, and it less than helpfully says "refer to
preparedStatement.insertNull()" or similar, which i cant see the
relevance of as we are using mybatis, not hand written prepared
statements?

Has anyone manged to insert a null using mybatis?

Thanks,

Simon.

[Larry Meadors]

How are you calling the mapped statement?

[Nathan Maves]

Are you sending one parameter or a complex object like a HashMap?

Send us your mapper interface method.

[Simon]

Its one value - its jackpotId in the example above. The mapper.xml is
above. Basically, im trying to find one example where someone inserts
a null bean field(e.g. Long as long cant be null) into a nullable
oracle field.

The mapper interface is like this:

public interface GameMapper {
Integer insert(Game game);
}

Thanks,

On Jul 25, 7:10 pm, Nathan Maves <nathan.ma...@gmail.com> wrote:
> Are you sending one parameter or a complex object like a HashMap?
>
> Send us your mapper interface method.
>

[Simon]

Anyone know how to let mybaits insert a null bean field (e.g. a null
String) into a nullable DB column?

[Larry Meadors]

#{somefield,jdbcType=VARCHAR}

[Richard Yee]

I'm not having any problem with inserting a null field. I'm doing it with XML and annotations. Post the code that calls the insert method.

Richard

Sent from my iPhone

[Simon Hobbs]

Yes, thats exactly what I have in the xml (see original post).

To recap what generates the null pointer exception when inserting:

My bean:
public class Game extends SkBean {
private long id;

private Long jackpotid = null; //////////////////
<<<<<<<<<<<<<<< this is the one Im trying to insert null!!!!!
:

Other fields and getters and setters
:
mapper.xml:
<insert id="insert">
<selectKey resultType="long" order="BEFORE" keyProperty="id">select
gameseq.nextval as value from dual</selectKey>
insert into game ( id, jackpotid)
values ( #{id, javaType=long},
#{jackpotid, javaType=Long,

jdbcType=BIGINT} <!-------------- LOOK IM SETTING THE JAVA TYPE --->

)
</insert>
Oracle Table:
CREATE TABLE SK_ENGINE.GAME
(
ID INTEGER NOT NULL
,JACKPOTID
INTEGER <<<<<<<<<<<<<

The table field is nullable
)

The mapper interface is like this:
public interface GameMapper {
Integer insert(Game game);

}

Im calling the mapper like this:

Game game = new Game();
SqlSession sqlSession = SqlSessionFactory.getFactory().openSession();
try {
Mapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.insert(game);
} finally {
sqlSesssion.close();

I can insert fine as long as all the bean fields are not null, any
null ones blow up, even if the jdbcType is set.

Has anyone found a way round this? Is it possible to intercept the
SQL and injesct setNull() into the prepared statements or similar?

Does anyone have one single working example which can insert nulls
into the db I can have a look at?

This is the null pointer error:

Simon.

[Richard Yee]

What is the UserMapper class?

Richard

Sent from my iPhone

> org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
> 8)
> at
> org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:
> 100)
> at
> org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:

[Simon Hobbs]

Sorry, cut and paste error. Where it says UserMapper, is actually
GameMapper in the code. Well spotted. Am I taking the right
approach, or is there a missing secret step?

For now, I have made the columns on all my tables not null, and am
using -1 and "" to represent null instead of null. I haven't got a
strategy for dealing with psudo null dates yet - I might have to write
two versions of inserts, one with the date column and one without.
Or investigate the dynamic sql, although all the examples with nulls
are for the where clause of a select, so not sure if this would work
inside an insert.

Simon.

Richard

Sent from my iPhone

> 8)
> at
> org
> .apache
> .ibatis
> .session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:
> 100)
> at
> org
> .apache
> .ibatis
> .session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:

[Richard Yee]

This works for me.

I'm using mySql. This is the table creation statement:

create table games (id int not null auto_increment, jackpotid int, primary key(id));

from mapper xml
        <insert id="insertGame" parameterType="com.demo.Game">
            insert into games(id,
            jackpotid) values(#{id}, #{jackpotid})
  </insert>

This is from my Game bean
public class Game {
    Long _id = null;
    Long _jackpotId = null;
   
    public Game() {
        //
    }
   
    public Game(Long id, Long jackpotId) {
        this._id = id;
        this._jackpotId = jackpotId;
    }
....

main processing code
        String resource = "mybatisConfig.xml";
        Reader reader = Resources.getResourceAsReader(resource);
        sqlMapper = new SqlSessionFactoryBuilder().build(reader);

            Game game = new Game((Long) null, Long.valueOf(46));
            session.insert("insertGame", game);


Everything works as expected.
You don't  need the GameMapper interface class since you are using the XML file. I think if you use the interface, then you will need to annotate the insert method.  The annotation would look something like:
    @Insert("insert into games(id, jackpotid) values(#{id}, #{jackpotid})")
    void insertGame(Game data);

-Richard

org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
     at
org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:
100)
     at
org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java: