前言
開博客這是第一次寫系列文章，從內心上講是有點擔心自己寫不好，寫不全，畢竟是作為java/mybatis學習的過程想把學習的路線和遇到的問題都總結下來，也讓知識點在腦海里能形成一個體系。
開發環境
idea2016、mybatis3、SQLServer2012
pom.xml、mybatis.xml、log4j.properties
先貼上pom.xml是因為他直接和搭建開發環境和測試環境有關系，mybatis.xml則是連接數據庫，log4j.properties在學習階段配置上有助于我們觀察SQL的執行情況。
1、pom.xml
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
<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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
<?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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
### 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 |
配置mapper.xml
mapper.xml在mybatis.xml是專職處理sql的一個環節，各種映射和實現都在這里處理
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
<?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
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
@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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
@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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
@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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
@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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
@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實現增刪改查案例，希望對大家有所幫助，如果大家有任何疑問歡迎給我留言，小編會及時回復大家的！
原文鏈接：http://www.cnblogs.com/sword-successful/p/6504222.html
