欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MyBatis中SqlSession实现增删改查案例

程序员文章站 2024-03-05 22:09:25
前言      开博客这是第一次写系列文章,从内心上讲是有点担心自己写不好,写不全,毕竟是作为java/mybatis学习的过程想...

前言

     开博客这是第一次写系列文章,从内心上讲是有点担心自己写不好,写不全,毕竟是作为java/mybatis学习的过程想把学习的路线和遇到的问题都总结下来,也让知识点在脑海里能形成一个体系。

开发环境

    idea2016、mybatis3、sqlserver2012

pom.xml、mybatis.xml、log4j.properties

   先贴上pom.xml是因为他直接和搭建开发环境和测试环境有关系,mybatis.xml则是连接数据库,log4j.properties在学习阶段配置上有助于我们观察sql的执行情况。

1、pom.xml

<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>

2、mybatis.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>

 <!-- 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>

3、log4j.properties

### 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

配置mapper.xml

   mapper.xml在mybatis.xml是专职处理sql的一个环节,各种映射和实现都在这里处理

<?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>

建立单元测试

1、准备工作,我建立的是控制台程序,所以在使用sqlsession前先创建sqlsessionfactory。当然,还要加载log4j.properties

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

2、queryusers

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

3、queryuserbyid

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

4、insertuser

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

5、updateuser

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

6、deleteuser

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

总结

      添加、修改、删除的操作要调用sqlsession.commit(),不然数据无法入库的,我刚开始就忘记了这个。

以上所述是小编给大家介绍的mybatis中sqlsession实现增删改查案例,希望对大家有所帮助