MyBatis学习一

MyBatis是弱项，所以需要补一下，那么就做个测试

先准备下jar包，如图：

只是为了学MyBatis就没必要弄其他那么多了，Junit启动就可以了。先配置一个log4j，找之前的文件复制了一个，如下：

log4j.rootLogger =DEBUG,stdout

log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

然后就是mybaits-conf.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
     <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <!-- 配置数据库连接信息 -->
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver" />
                <property name="url" value="jdbc:oracle:thin:@localhost:1521:testdev" />
                <property name="username" value="oa01" />
                <property name="password" value="oa01" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
       <!-- 批量导入 -->
        <package name="cn.hd.dao" />
    </mappers>
</configuration>

建表，随便弄一个测试一下，sql如下：

create table T_USER
(
  USERID    NUMBER not null,
  USERNAME  VARCHAR2(20),
  PASSWORD  VARCHAR2(20),
  REALNAME  VARCHAR2(20),
  DID       NUMBER,
  JID       NUMBER
)

po

package cn.hd.po;

public class User {
	private int userid;
	private String username;
	private String password;
	private String realname;
	private int jid;
	private int did;
   
	/**
	 * @return the jid
	 */
	public int getJid() {
		return jid;
	}

	/**
	 * @param jid the jid to set
	 */
	public void setJid(int jid) {
		this.jid = jid;
	}

	/**
	 * @return the did
	 */
	public int getDid() {
		return did;
	}

	/**
	 * @param did the did to set
	 */
	public void setDid(int did) {
		this.did = did;
	}

	/**
	 * @return the userid
	 */
	public int getUserid() {
		return userid;
	}

	/**
	 * @param userid
	 *            the userid to set
	 */
	public void setUserid(int userid) {
		this.userid = userid;
	}

	/**
	 * @return the username
	 */
	public String getUsername() {
		return username;
	}

	/**
	 * @param username
	 *            the username to set
	 */
	public void setUsername(String username) {
		this.username = username;
	}

	/**
	 * @return the password
	 */
	public String getPassword() {
		return password;
	}

	/**
	 * @param password
	 *            the password to set
	 */
	public void setPassword(String password) {
		this.password = password;
	}

	/**
	 * @return the realname
	 */
	public String getRealname() {
		return realname;
	}

	/**
	 * @param realname
	 *            the realname to set
	 */
	public void setRealname(String realname) {
		this.realname = realname;
	}

}

Mapping

package cn.hd.dao;

import java.util.List;

import cn.hd.po.User;

public interface UserMapper {
   List<User> queryAll();
   User findById(int userid);
}

Junit测试类

package cn.hd.test;

import java.io.Reader;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import cn.hd.po.User;

public class TestMyBatis {
	Logger logger=Logger.getLogger(TestMyBatis.class);
	SqlSession session ;
	@Before
	public void setUp() throws Exception {
		logger.info("单元测试开始");
		// 1、加载配置文件
		Reader reader = Resources.getResourceAsReader("mybaits-conf.xml");
		SqlSessionFactory factory = new SqlSessionFactoryBuilder()
				.build(reader);
		// 2)
		 session = factory.openSession();
	}
	@After
	public void tearDown() throws Exception {
		session.close();
		logger.info("单元测试结束");
	}
	
	@Test
	public void testFindById() {
        User user=session.selectOne("cn.hd.dao.UserMapper.findById", 2);
        System.out.println(user.getRealname());
	}
	@Test
	public void testFindAll() {
        List<User> user=session.selectList("cn.hd.dao.UserMapper.queryAll");
        for(User u :user)
        {
        	System.out.println(u.getRealname());
        }
	}
}

最后贴出项目结构

ok，最后来一个错误总结。

org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:81)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:71)
	at cn.hd.test.TestMyBatis.testFindAll(TestMyBatis.java:42

以上错误是因为使用的session.selectOne查询的多条数据。这里改成session.selectList既可。

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='vlues', mode=IN, javaType=class java.lang.Integer, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: 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: 无效的列类型
### The error may exist in cn/hd/dao/UserMapper.xml
### The error may involve cn.hd.dao.UserMapper.findById-Inline
### The error occurred while setting parameters
### SQL: select  *  from t_user where userid=?
### Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='vlues', mode=IN, javaType=class java.lang.Integer, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: 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: 无效的列类型
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
	
Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='vlues', mode=IN, javaType=class java.lang.Integer, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: 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: 无效的列类型
	at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:89)
	... 29 more
Caused by: org.apache.ibatis.type.TypeException: 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: 无效的列类型
	at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:47)
	at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:87)
	... 38 more
Caused by: java.sql.SQLException: 无效的列类型
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	... 39 more

这个错误是应该传入参数的时候没有传入参数，加上既可。