MyBatis中SqlSession实现增删改查案例

 
  org.mybatis
  mybatis
  3.4.2
 
 
  com.microsoft.sqlserver
  sqljdbc4
  4.0
 
 
  commons-dbcp
  commons-dbcp
  1.4
 
 
  junit
  junit
  4.10
 
 
  log4j
  log4j
  1.2.17
 
 

<&＃63;xml version="1.0" encoding="UTF-8"&＃63;>



 
 
  
  
  
 
 
  
   
   
    
    
    
    
   
  
 


 
  
  
 

### Log4j配置 ###
### 与Spring结合需要在web.xml中指定此文件位置，并添加监听器 ###
#定义log4j的输出级别和输出目的地（目的地可以自定义名称，和后面的对应）
#[ level ] , appenderName1 , appenderName2
log4j.rootLogger=DEBUG,console,file

#-----------------------------------#
#1 定义日志输出目的地为控制台
log4j.appender.cOnsole= org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
####可以灵活地指定日志输出格式，下面一行是指定具体的格式 ###
#%c: 输出日志信息所属的类目，通常就是所在类的全名
#%m: 输出代码中指定的消息,产生的日志具体信息
#%n: 输出一个回车换行符，Windows平台为"/r/n"，Unix平台为"/n"输出日志信息换行
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.COnversionPattern=[%c]-%m%n

#-----------------------------------#
#2 文件大小到达指定尺寸的时候产生一个新的文件
log4j.appender.file = org.apache.log4j.RollingFileAppender
#日志文件输出目录
log4j.appender.file.File=log/tibet.log
#定义文件最大大小
log4j.appender.file.MaxFileSize=10mb
###输出日志信息###
#最低级别
log4j.appender.file.Threshold=ERROR
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.COnversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#-----------------------------------#
#3 druid
log4j.logger.druid.sql=INFO
log4j.logger.druid.sql.DataSource=info
log4j.logger.druid.sql.COnnection=info
log4j.logger.druid.sql.Statement=info
log4j.logger.druid.sql.ResultSet=info

#4 mybatis 显示SQL语句部分
log4j.logger.org.mybatis=DEBUG
#log4j.logger.cn.tibet.cas.dao=DEBUG
#log4j.logger.org.mybatis.common.jdbc.SimpleDataSource=DEBUG#
#log4j.logger.org.mybatis.common.jdbc.ScriptRunner=DEBUG#
#log4j.logger.org.mybatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG#
#log4j.logger.java.sql.COnnection=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

<&＃63;xml version="1.0" encoding="UTF-8"&＃63;>


 
 
  select * from t_userinfo
 
 
 
  select * from t_userinfo where id=#{id}
 
 
  select * from t_userinfo where name=#{name,javaType=String,jdbcType=VARCHAR} and address=#{address}
 
 
 
  insert into t_userinfo (name,address) values(#{name},#{address})
 
 
 
  update t_userinfo set name=#{name},address=#{address} where id=#{id}
 
 
 
  delete t_userinfo where id=#{id}
 

SqlSessionFactory sqlSessiOnFactory=null;
 @Before
 public void BeforeClass(){
  try {
   InputStream is=Resources.getResourceAsStream("log4j.properties");
   PropertyConfigurator.configure(is);
   Reader reader = Resources.getResourceAsReader("mybatis.xml");
   sqlSessiOnFactory= new SqlSessionFactoryBuilder().build(reader);
  } catch (IOException e) {
   e.printStackTrace();
  }
 }

@Test
  public void queryUsers() throws Exception {
    //线程不安全类型，放在方法体内
    SqlSession sqlSession=null;
    try {
      sqlSession=sqlSessionFactory.openSession();
      List list = sqlSession.selectList("com.autohome.mapper.User.queryUsers");
      System.out.println("size:"+list.size());
    } catch (Exception e) {
      e.printStackTrace();
    }finally {
      sqlSession.close();
    }
  }

@Test
  public void queryUserById() {
    SqlSession sqlSession=null;
    try{
      sqlSession=sqlSessionFactory.openSession();
      User user = sqlSession.selectOne("com.autohome.mapper.User.queryUserById",2);
      System.out.println("id:"+user.getId()+",name:"+user.getName()+","+user.getAddress());
    }catch(Exception e){
      e.printStackTrace();
    }finally {
      sqlSession.close();
    }
  }

@Test
  public void insertUser(){
    SqlSession sqlSession=null;
    try{
      sqlSession=sqlSessionFactory.openSession();
      User user =new User();
      user.setName("kobe");
      user.setAddress("usa");
      int result = sqlSession.insert("com.autohome.mapper.User.insertUsers",user);
      sqlSession.commit();
      if(result>0){
        System.out.println("insert success....");
      }else{
        System.out.println("insert error....");
      }
    }catch(Exception e){
      e.printStackTrace();
    }finally {
      sqlSession.close();
    }
  }

@Test
 public void updateUser(){
   SqlSession sqlSession=null;
   try{
     sqlSession=sqlSessionFactory.openSession();
     User user =new User();
     user.setId(36);
     user.setName("kobe");
     user.setAddress("usa");
     int result = sqlSession.update("com.autohome.mapper.User.updateUsers",user);
     sqlSession.commit();
     if(result>0){
       System.out.println("update success....");
     }else{
       System.out.println("update error....");
     }
   }catch(Exception e){
     e.printStackTrace();
   }finally {
     sqlSession.close();
   }
 }

@Test
  public void deleteUser(){
    SqlSession sqlSession=null;
    try{
      sqlSession=sqlSessionFactory.openSession();
      int result = sqlSession.delete("com.autohome.mapper.User.deleteUsers",49);
      sqlSession.commit();
      if(result>0){
        System.out.println("delete success....");
      }else{
        System.out.println("delete error....");
      }
    }catch(Exception e){
      e.printStackTrace();
    }finally {
      sqlSession.close();
    }
  }