MyBatis中SqlSession实现增删改查案例

<dependencies> <dependency>  <groupId>org.mybatis</groupId>  <artifactId>mybatis</artifactId>  <version>3.4.2</version> </dependency> <dependency>  <groupId>com.microsoft.sqlserver</groupId>  <artifactId>sqljdbc4</artifactId>  <version>4.0</version> </dependency> <dependency>  <groupId>commons-dbcp</groupId>  <artifactId>commons-dbcp</artifactId>  <version>1.4</version> </dependency> <dependency>  <groupId>junit</groupId>  <artifactId>junit</artifactId>  <version>4.10</version> </dependency> <dependency>  <groupId>log4j</groupId>  <artifactId>log4j</artifactId>  <version>1.2.17</version> </dependency> </dependencies>

<?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> <!-- MyBatis针对SqlServer进行的配置 --> <typeAliases>  <typeAlias alias="User" type="com.autohome.model.User"/>  <typeAlias alias="Teacher" type="com.autohome.model.Teacher" />  <typeAlias alias="Student" type="com.autohome.model.Student" /> </typeAliases> <environments default="development">  <environment id="development">   <transactionManager type="JDBC"/>   <dataSource type="POOLED">    <property name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>    <property name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=test"/>    <property name="username" value="sa"/>    <property name="password" value="0"/>   </dataSource>  </environment> </environments> <mappers>  <mapper resource="mapper/User.xml"/>  <mapper resource="mapper/Student.xml"/> </mappers></configuration>

### Log4j配置 ###### 与Spring结合需要在web.xml中指定此文件位置，并添加监听器 ####定义log4j的输出级别和输出目的地（目的地可以自定义名称，和后面的对应）#[ level ] , appenderName1 , appenderName2log4j.rootLogger=DEBUG,console,file#-----------------------------------##1 定义日志输出目的地为控制台log4j.appender.console = org.apache.log4j.ConsoleAppenderlog4j.appender.console.Target = System.outlog4j.appender.console.Threshold=DEBUG####可以灵活地指定日志输出格式，下面一行是指定具体的格式 ####%c: 输出日志信息所属的类目，通常就是所在类的全名#%m: 输出代码中指定的消息,产生的日志具体信息#%n: 输出一个回车换行符，Windows平台为"/r/n"，Unix平台为"/n"输出日志信息换行log4j.appender.console.layout = org.apache.log4j.PatternLayoutlog4j.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=ERRORlog4j.appender.file.layout=org.apache.log4j.PatternLayoutlog4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n#-----------------------------------##3 druidlog4j.logger.druid.sql=INFOlog4j.logger.druid.sql.DataSource=infolog4j.logger.druid.sql.Connection=infolog4j.logger.druid.sql.Statement=infolog4j.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=DEBUGlog4j.logger.java.sql=DEBUGlog4j.logger.java.sql.Statement=DEBUGlog4j.logger.java.sql.ResultSet=DEBUGlog4j.logger.java.sql.PreparedStatement=DEBUG

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.autohome.mapper.User"> <!-- 查询全部用户 --> <select id="queryUsers" resultType="com.autohome.model.User">  select * from t_userinfo </select> <!-- 按照ID查询 --> <select id="queryUserById" parameterType="int" resultType="com.autohome.model.User">  select * from t_userinfo where id=#{id} </select> <select id="queryUserByAddress" resultType="com.autohome.model.User">  select * from t_userinfo where name=#{name,javaType=String,jdbcType=VARCHAR} and address=#{address} </select> <!-- 添加用户 --> <insert id="insertUsers" parameterType="com.autohome.model.User">  insert into t_userinfo (name,address) values(#{name},#{address}) </insert> <!-- 修改用户 --> <update id="updateUsers" parameterType="com.autohome.model.User">  update t_userinfo set name=#{name},address=#{address} where id=#{id} </update> <!-- 删除用户 --> <delete id="deleteUsers" parameterType="int">  delete t_userinfo where id=#{id} </delete></mapper>

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<User> 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();    }  }