[Mybatis] Oracle java.sql.SQLException: 부적합한 열 유형: 1111

 SQLException: 부적합한 열 유형: 1111

 

Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: Error setting null for parameter #1 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 부적합한 열 유형: 1111

null을 허용하지 않는 컬럼에 null이 삽입되거나,

혹은 올바르지 않은 입력값이 삽입된 경우에 나타나는 에러다.

 

 

 

 해결

# 기존 코드

<insert id="boardDataInsert" parameterType="java.util.HashMap">
		<selectKey keyProperty="board_seq" resultType="int" order="AFTER">
			SELECT NVL(MAX(BOARD_SEQ),0) AS BOARD_SEQ FROM CMS_BOARD_DATA
		</selectKey>
		INSERT INTO CMS_BOARD_DATA
		( BOARD_SEQ, SITEID, BOARDID
		<if test='categoryid != null and categoryid > "0"'>
			, CATEGORYID
		</if>
		, USERNM, NOTICE_YN
		, NOTICE_SDATE, NOTICE_EDATE, OPEN_TYPE, OPEN_SDATE, OPEN_EDATE
		, TITLE, TITLE_COLORTYPE, TITLE_COLOR, CONTENT_TYPE, MAINTEXT
		, LINK_URL, LINK_TYPE, YOUTUBU_URL, SUBTITLES, CONTENT
		, INSERTDATE, INSERTUSER, INSERTIP, DEL_YN, HIT, RE_YN, USERPWD
		)
		VALUES
		( (SELECT NVL(MAX(BOARD_SEQ)+1,0) AS BOARD_SEQ FROM CMS_BOARD_DATA), #{siteid}, #{boardid}
		<if test='categoryid != null and categoryid > "0"'>
			, #{categoryid}
		</if>
		, #{usernm}, #{notice_yn}
		, #{notice_sdate}, #{notice_edate}, #{open_type}, #{open_sdate}, #{open_edate}
		, #{title}, #{title_colortype}, #{title_color}, #{content_type}, #{maintext}
		, #{link_url}, #{link_type}, #{youtubu_url}, #{subtitles}, #{content}
		, #{insertdate}, #{insertuser}, #{insertip}, 'N', #{hit}, 'N', #{userpwd}
		)
	</insert>

 

 

# 변경 코드

<insert id="boardDataInsert" parameterType="java.util.HashMap">
		<selectKey keyProperty="board_seq" resultType="int" order="AFTER">
			SELECT NVL(MAX(BOARD_SEQ),0) AS BOARD_SEQ FROM CMS_BOARD_DATA
		</selectKey>
		INSERT INTO CMS_BOARD_DATA
		( BOARD_SEQ, SITEID, BOARDID
		<if test='categoryid != null and categoryid > "0"'>
			, CATEGORYID
		</if>
		, USERNM, NOTICE_YN
		, NOTICE_SDATE, NOTICE_EDATE, OPEN_TYPE, OPEN_SDATE, OPEN_EDATE
		, TITLE, TITLE_COLORTYPE, TITLE_COLOR, CONTENT_TYPE, MAINTEXT
		, LINK_URL, LINK_TYPE, YOUTUBU_URL, SUBTITLES, CONTENT
		, INSERTDATE, INSERTUSER, INSERTIP, DEL_YN, HIT, RE_YN, USERPWD
		)
		VALUES
		( (SELECT NVL(MAX(BOARD_SEQ)+1,0) AS BOARD_SEQ FROM CMS_BOARD_DATA), #{siteid, jdbcType=NUMERIC}, #{boardid, jdbcType=NUMERIC}
		<if test='categoryid != null and categoryid > "0"'>
			, #{categoryid}
		</if>
		, #{usernm, jdbcType=VARCHAR}, #{notice_yn, jdbcType=VARCHAR}
		, #{notice_sdate, jdbcType=VARCHAR}, #{notice_edate, jdbcType=VARCHAR}, #{open_type, jdbcType=VARCHAR}, #{open_sdate, jdbcType=VARCHAR}, #{open_edate, jdbcType=VARCHAR}
		, #{title, jdbcType=VARCHAR}, #{title_colortype, jdbcType=VARCHAR}, #{title_color, jdbcType=VARCHAR}, #{content_type, jdbcType=VARCHAR}, #{maintext, jdbcType=CLOB}
		, #{link_url, jdbcType=VARCHAR}, #{link_type, jdbcType=VARCHAR}, #{youtubu_url, jdbcType=VARCHAR}, #{subtitles, jdbcType=VARCHAR}, #{content, jdbcType=CLOB}
		, #{insertdate, jdbcType=VARCHAR}, #{insertuser, jdbcType=VARCHAR}, #{insertip, jdbcType=VARCHAR}, 'N', #{hit, jdbcType=NUMERIC}, 'N', #{userpwd, jdbcType=VARCHAR}
		)
	</insert>

 

 

 

#{value값 + , jdbcType="타입"}

타입	형식
varchar	#{value, jdbcType=VARCHAR}
int / number	#{value, jdbcType=NUMERIC}
CLOB	#{value, jdbcType=CLOB}