MyBatis: add, delete, modify, and query from SqlSession, and mybatissqlsession

MyBatis: add, delete, modify, and query from SqlSession, and mybatissqlsession
Preface

This is the first time I wrote a series of articles on my blog. In my heart, I was a little worried that I could not write well and could not write completely, after all, as a java/mybatis learning process, we want to sum up the learning route and problems we encounter, and let knowledge points form a system in our minds.

Development Environment

Idea2016, mybatis3, SQLServer2012

 

Pom. xml, mybatis. xml, and log4j. properties

First paste the pom. xml is because it is directly related to the development environment and the test environment, mybatis. xml is used to connect to the database, log4j. properties helps us observe SQL Execution in the learning phase configuration.

1. pom. xml

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

2. mybatis. xml

1 <? Xml version = "1.0" encoding = "UTF-8"?> 2 <! DOCTYPE configuration 3 PUBLIC "-// mybatis.org//DTD Config 3.0 // EN" 4 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 5 <configuration> 6 7 <! -- MyBatis configuration for SqlServer --> 8 <typeAliases> 9 <typeAlias alias = "User" type = "com. autohome. model. user "/> 10 <typeAlias alias =" Teacher "type =" com. autohome. model. teacher "/> 11 <typeAlias alias =" Student "type =" com. autohome. model. student "/> 12 </typeAliases> 13 <environments default =" development "> 14 <environment id =" development "> 15 <transactionManager type =" JDBC "/> 16 <dataSource type = "POOLED"> 17 <property name = "driver" value = "com. microsoft. sqlserver. jdbc. SQLServerDriver "/> 18 <property name =" url "value =" jdbc: sqlserver :/// localhost: 1433; databaseName = test "/> 19 <property name =" username "value =" sa "/> 20 <property name =" password "value =" 0 "/> 21 </dataSource> 22 </environment> 23 </environments> 24 25 26 <mappers> 27 <mapper resource = "mapper/User. xml "/> 28 <mapper resource =" mapper/Student. xml "/> 29 </mappers> 30 </configuration>View Code

3. log4j. properties

1 ### Log4j configuration ### 2 ### use Spring in combination with the web. specify the location of the file in xml, and add the listener ### 3 # define the output level and destination of log4j (the destination can be customized with the name corresponding to the following) 4 # [level], appenderName1, appenderName2 5 log4j. rootLogger = DEBUG, console, file 6 7 # ----------------------------------- #8 #1 Define the log output destination as console 9 log4j. appender. console = org. apache. log4j. leleappender10 log4j. appender. console. target = System. out11 log4j. appender. console. threshold = DEBUG12 #### you can flexibly specify the log output format. The following line specifies the specific format ### 13 # % c: the category of the output log information, normally, it is the full name of the Class 14 # % m: the message specified in the output code, the specific information of the generated log 15 # % n: Output A carriage return line break, windows: "/r/n", Unix: "/n", output log information line feed 16 log4j. appender. console. layout = org. apache. log4j. patternLayout17 log4j. appender. console. layout. conversionPattern = [% c]-% m % n18 19 # ----------------------------------- #20 # When the file size reaches the specified size, a new file 21 log4j is generated. appender. file = org. apache. log4j. rollingFileAppender22 # log file output directory 23 log4j. appender. file. file = log/tibet. log24 # defines the maximum file size of 25 log4j. appender. file. maxFileSize = 10mb26 ### output log information ### 27 # minimum level 28 log4j. appender. file. threshold = ERROR29 log4j. appender. file. layout = org. apache. log4j. patternLayout30 log4j. appender. file. layout. conversionPattern = [% p] [% d {yy-MM-dd}] [% c] % m % n31 32 # --------------------------------- #33 #3 druid34 log4j. logger. druid. SQL = INFO35 log4j. logger. druid. SQL. dataSource = info36 log4j. logger. druid. SQL. connection = info37 log4j. logger. druid. SQL. statement = info38 log4j. logger. druid. SQL. resultSet = info39 40 #4 mybatis show SQL statement part 41 log4j.logger.org. mybatis = DEBUG42 # log4j.logger.cn. tibet. cas. dao = DEBUG43 # log4j.logger.org. mybatis. common. jdbc. simpleDataSource = DEBUG #44 # log4j.logger.org. mybatis. common. jdbc. scriptRunner = DEBUG #45 # log4j.logger.org. mybatis. sqlmap. engine. impl. sqlMapClientDelegate = DEBUG #46 # log4j. logger. java. SQL. connection = DEBUG47 log4j. logger. java. SQL = DEBUG48 log4j. logger. java. SQL. statement = DEBUG49 log4j. logger. java. SQL. resultSet = DEBUG50 log4j. logger. java. SQL. preparedStatement = DEBUGView Code

 

Configure mapper. xml

Mapper. xml is a dedicated part of SQL processing in mybatis. xml, and various mappings and implementations are processed here.

<? Xml version = "1.0" encoding = "UTF-8"?> <! DOCTYPE mapper PUBLIC "-// mybatis.org//DTD Mapper 3.0 //" http://mybatis.org/dtd/mybatis-3-mapper.dtd "> <mapper namespace =" com. autohome. mapper. User "> <! -- Query all users --> <select id = "queryUsers" resultType = "com. autohome. model. User"> select * from t_userinfo </select> <! -- Query by 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> <! -- Add user --> <insert id = "insertUsers" parameterType = "com. autohome. model. user "> insert into t_userinfo (name, address) values (# {name}, # {address}) </insert> <! -- Modify user --> <update id = "updateUsers" parameterType = "com. autohome. model. user "> update t_userinfo set name =#{ name}, address =#{ address} where id =#{ id} </update> <! -- Delete a user --> <delete id = "deleteUsers" parameterType = "int"> delete t_userinfo where id =#{ id} </delete> </mapper>

　　

Establish unit test

1. Preparations: I have created a console program. Therefore, create sqlsessionfactory before using sqlsession. Of course, you also need to load 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 {// thread unsafe type, placed in the method body 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();        }    }

　　

Summary

Sqlsession. commit () must be called for adding, modifying, and deleting operations. Otherwise, data cannot be stored in the database. I forgot this at the beginning.