MyBatis从入门到放弃一：从SqlSession实现增删改查

前言

开博客这是第一次写系列文章，从内心上讲是有点担心自己写不好，写不全，毕竟是作为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实现增删改查

前言

开发环境

pom.xml、mybatis.xml、log4j.properties

配置mapper.xml

建立单元测试

总结

最新文章

热门文章