mybatis的操作数据库基础

1、domain类

 1 package com.xiaostudy.mybatis.domain;
 2 
 3 /**
 4  * @desc domain类
 5  * @author xiaostudy
 6  *
 7  */
 8 public class User {
 9     private int id;
10     private String username;
11     private String password;
12 
13     public int getId() {
14         return id;
15     }
16 
17     public void setId(int id) {
18         this.id = id;
19     }
20 
21     public String getUsername() {
22         return username;
23     }
24 
25     public void setUsername(String username) {
26         this.username = username;
27     }
28 
29     public String getPassword() {
30         return password;
31     }
32 
33     public void setPassword(String password) {
34         this.password = password;
35     }
36 
37     @Override
38     public String toString() {
39         return "User [id=" + id + ", username=" + username + ", password=" + password + "]";
40     }
41 
42 }

2、dao接口

 1 package com.xiaostudy.mybatis.dao;
 2 
 3 import java.io.IOException;
 4 import java.util.List;
 5 
 6 import com.xiaostudy.mybatis.domain.User;
 7 
 8 /**
 9  * @desc dao接口
10  * @author xiaostudy
11  *
12  */
13 public interface UserDao {
14     
15     /**
16      * @desc 根据用户ID查询用户信息
17      * @param id 参数
18      * @return User 返回类型
19      * @throws Exception 异常
20      */
21     public User findUserById(int id) throws Exception;
22     
23     /**
24      * @desc 根据用户名称查询用户信息
25      * @param username 参数
26      * @return User 返回类型
27      * @throws Exception 异常
28      */
29     public User findUserByUsername(String username) throws Exception;
30     
31     /**
32      * @desc 根据用户名称模糊查询list集合
33      * @param username 参数
34      * @return List<User> 返回类型
35      * @throws IOException 异常
36      */
37     public List<User> findUserByUsernames(String username) throws IOException;
38     
39     /**
40      * @desc 查询所有用户
41      * @return List<User> 返回类型
42      * @throws IOException 异常
43      */
44     public List<User> findAllUser() throws IOException;
45     
46     /**
47      * @desc 添加用户
48      * @param user 参数
49      * @return int 返回类型
50      * @throws IOException 异常
51      */
52     public int insertUser(User user) throws IOException;
53     
54     /**
55      * @desc 根据用户id修改用户名称
56      * @param user 参数
57      * @return int 返回类型
58      * @throws IOException 异常
59      */
60     public int updateUserUsername(User user) throws IOException;
61     
62     /**
63      * @desc 根据用户id修改用户名称和密码
64      * @param user 参数
65      * @return int 返回类型
66      * @throws IOException 异常
67      */
68     public int updateUserUsernamePassword(User user) throws IOException;
69 
70 }

3、dao接口实现类

  1 package com.xiaostudy.mybatis.dao;
  2 
  3 import java.io.IOException;
  4 import java.io.InputStream;
  5 import java.util.List;
  6 
  7 import org.apache.ibatis.io.Resources;
  8 import org.apache.ibatis.session.SqlSession;
  9 import org.apache.ibatis.session.SqlSessionFactory;
 10 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 11 
 12 import com.xiaostudy.mybatis.domain.User;
 13 
 14 /**
 15  * @desc dao接口实现类
 16  * @author xiaostudy
 17  *
 18  */
 19 public class UserDaoImpl implements UserDao {
 20 
 21     private static SqlSessionFactory sqlSessionFactory;
 22     
 23     static {
 24         //配置文件路径
 25         String resource = "config/SqlMapConfig.xml";
 26         try {
 27             //读取配置文件
 28             InputStream inputStream = Resources.getResourceAsStream(resource);
 29             // 初始化SqlSessionFactory
 30             sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 31         } catch (IOException e) {
 32             e.printStackTrace();
 33         }
 34     }
 35 
 36     @Override
 37     public User findUserById(int id) throws Exception {
 38         
 39         // 创建SqlSession
 40         SqlSession sqlSession = sqlSessionFactory.openSession();
 41         
 42         // 调用SqlSession的增删改查方法
 43         // 第一个参数：表示statement的唯一标示
 44         User user = sqlSession.selectOne("test.findUserById", id);
 45         
 46         // 关闭资源
 47         sqlSession.close();
 48         
 49         return user;
 50     }
 51 
 52     @Override
 53     public User findUserByUsername(String username) throws Exception {
 54         
 55         SqlSession sqlSession = sqlSessionFactory.openSession();
 56         
 57         User user = sqlSession.selectOne("test.findUserByUsername", username);
 58         
 59         sqlSession.close();
 60         
 61         return user;
 62     }
 63 
 64     @Override
 65     public List<User> findUserByUsernames(String username) throws IOException {
 66 
 67         SqlSession sqlSession = sqlSessionFactory.openSession();
 68 
 69         List<User> users = sqlSession.selectList("test.findUserByUsernames", "%" + username + "%");
 70 
 71         sqlSession.close();
 72         
 73         return users;
 74     }
 75 
 76     @Override
 77     public List<User> findAllUser() throws IOException {
 78         
 79         SqlSession sqlSession = sqlSessionFactory.openSession();
 80         
 81         List<User> users = sqlSession.selectList("test.findAllUser");
 82         
 83         sqlSession.close();
 84         
 85         return users;
 86     }
 87 
 88     @Override
 89     public int insertUser(User user) throws IOException {
 90 
 91         SqlSession sqlSession = sqlSessionFactory.openSession();
 92         
 93         int i = sqlSession.insert("test.insertUser", user);
 94         
 95         //添加或修改数据的都要提交事务
 96         sqlSession.commit();
 97         sqlSession.close();
 98         
 99         return i;
100     }
101 
102     @Override
103     public int updateUserUsername(User user) throws IOException {
104 
105         SqlSession sqlSession = sqlSessionFactory.openSession();
106         
107         int i = sqlSession.update("test.updateUserUsername", user);
108         
109         sqlSession.commit();
110         sqlSession.close();
111         
112         return i;
113     }
114 
115     @Override
116     public int updateUserUsernamePassword(User user) throws IOException {
117 
118         SqlSession sqlSession = sqlSessionFactory.openSession();
119         
120         int i = sqlSession.update("test.updateUserUsernamePassword", user);
121         
122         sqlSession.commit();
123         sqlSession.close();
124         
125         return i;
126     }
127 
128 
129 }

4、配置文件

db.properties

1 db.driver=com.mysql.jdbc.Driver
2 db.url=jdbc:mysql://localhost:3306/user?useUnicode=true&amp;characterEncoding=utf8
3 db.username=root
4 db.password=123456

SqlMapConfig.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     <!-- 加载java的配置文件 -->
 8     <properties resource="config/db.properties"/>
 9     
10     <!-- 配置mybatis的环境信息，与spring整合，该信息由spring来管理 -->
11     <environments default="development">
12         <environment id="development">
13             <!-- 配置JDBC事务控制，由mybatis进行管理 -->
14             <transactionManager type="JDBC"></transactionManager>
15             <!-- 配置数据源，采用mybatis连接池 -->
16             <dataSource type="POOLED">
17                 <property name="driver" value="${db.driver}" />
18                 <property name="url" value="${db.url}" />
19                 <property name="username" value="${db.username}" />
20                 <property name="password" value="${db.password}" />
21             </dataSource>
22         </environment>
23     </environments>
24 
25     <!-- 加载映射文件 -->
26     <mappers>
27         <mapper resource="config/User.xml" />
28     </mappers>
29     
30 </configuration>

User.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"    
 3                     "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4                     
 5 <!-- namespace：命名空间，对statement的信息进行分类管理 -->
 6 <!-- 注意：在mapper代理时，它具有特殊及重要的作用 -->
 7 <mapper namespace="test">
 8     <!-- 根据用户ID查询用户信息 -->
 9     <!-- select：表示一个MappedStatement对象 -->
10     <!-- id：statement的唯一标示 -->
11     <!-- #{}：表示一个占位符？ -->
12     <!-- #{id}：里面的id表示输入参数的参数名称，如果该参数是简单类型，那么#{}里面的参数名称可以任意 -->
13     <!-- parameterType：输入参数的java类型 -->
14     <!-- resultType：输出结果的所映射的java类型（单条结果所对应的java类型） -->
15     <select id="findUserById" parameterType="int" resultType="com.xiaostudy.mybatis.domain.User">
16         SELECT * FROM USER WHERE id = #{id}
17     </select>
18     
19     <!-- 根据用户名称查询指定用户 -->
20     <select id="findUserByUsername" parameterType="java.lang.String" resultType="com.xiaostudy.mybatis.domain.User">
21         SELECT * FROM USER WHERE username = #{username}
22     </select>
23     
24     <!-- 根据用户名称模糊查询list集合 -->
25     <select id="findUserByUsernames" parameterType="java.lang.String" resultType="com.xiaostudy.mybatis.domain.User">
26         SELECT * FROM USER WHERE username LIKE #{username}
27     </select>
28     
29     <!-- 根据所有用户 -->
30     <select id="findAllUser" resultType="com.xiaostudy.mybatis.domain.User">
31         SELECT * FROM USER 
32     </select>
33     
34     <!-- 添加用户 -->
35     <insert id="insertUser" parameterType="com.xiaostudy.mybatis.domain.User">
36         <selectKey keyProperty="id" resultType="int" order="AFTER">
37             select last_insert_id()
38         </selectKey>
39         insert into user (username, password) values (#{username}, #{password})
40     </insert>
41     
42     <!-- 修改用户名称 -->
43     <update id="updateUserUsername" parameterType="com.xiaostudy.mybatis.domain.User">
44         update user set username=#{username}  where id=#{id}
45     </update>
46     
47     <!-- 修改用户名称和密码 -->
48     <update id="updateUserUsernamePassword" parameterType="com.xiaostudy.mybatis.domain.User">
49         update user set username=#{username}, password=#{password}  where id=#{id}
50     </update>
51 
52 </mapper>

log4j.properties

1 # Global logging configuration
2 log4j.rootLogger=DEBUG, stdout
3 # Console output...
4 log4j.appender.stdout=org.apache.log4j.ConsoleAppender
5 log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
6 log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

5、mysql

mapper代理

mapper代理接口

 1 package com.xiaostudy.mybatis.mapper;
 2 
 3 import java.io.IOException;
 4 import java.util.List;
 5 
 6 import com.xiaostudy.mybatis.domain.User;
 7 
 8 /**
 9  * @desc dao接口
10  * @author xiaostudy
11  *
12  */
13 public interface MapperDao {
14     
15     /**
16      * @desc 根据用户ID查询用户信息
17      * @param id 参数
18      * @return User 返回类型
19      * @throws Exception 异常
20      */
21     public User findUserById(int id) throws Exception;
22     
23     /**
24      * @desc 根据用户名称查询用户信息
25      * @param username 参数
26      * @return User 返回类型
27      * @throws Exception 异常
28      */
29     public User findUserByUsername(String username) throws Exception;
30     
31     /**
32      * @desc 根据用户名称模糊查询list集合
33      * @param username 参数
34      * @return List<User> 返回类型
35      * @throws IOException 异常
36      */
37     public List<User> findUserByUsernames(String username) throws IOException;
38     
39     /**
40      * @desc 查询所有用户
41      * @return List<User> 返回类型
42      * @throws IOException 异常
43      */
44     public List<User> findAllUser() throws IOException;
45     
46     /**
47      * @desc 添加用户
48      * @param user 参数
49      * @return int 返回类型
50      * @throws IOException 异常
51      */
52     public int insertUser(User user) throws IOException;
53     
54     /**
55      * @desc 根据用户id修改用户名称
56      * @param user 参数
57      * @return int 返回类型
58      * @throws IOException 异常
59      */
60     public int updateUserUsername(User user) throws IOException;
61     
62     /**
63      * @desc 根据用户id修改用户名称和密码
64      * @param user 参数
65      * @return int 返回类型
66      * @throws IOException 异常
67      */
68     public int updateUserUsernamePassword(User user) throws IOException;
69 
70 }

MapperDao.java

mapper代理配置文件

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"    
 3                     "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4                     
 5 <!-- namespace：命名空间，对statement的信息进行分类管理 -->
 6 <!-- 注意：在mapper代理时，它具有特殊及重要的作用 -->
 7 <mapper namespace="com.xiaostudy.mybatis.mapper.MapperDao">
 8     <!-- 根据用户ID查询用户信息 -->
 9     <!-- select：表示一个MappedStatement对象 -->
10     <!-- id：statement的唯一标示 -->
11     <!-- #{}：表示一个占位符？ -->
12     <!-- #{id}：里面的id表示输入参数的参数名称，如果该参数是简单类型，那么#{}里面的参数名称可以任意 -->
13     <!-- parameterType：输入参数的java类型 -->
14     <!-- resultType：输出结果的所映射的java类型（单条结果所对应的java类型） -->
15     <select id="findUserById" parameterType="int" resultType="com.xiaostudy.mybatis.domain.User">
16         SELECT * FROM USER WHERE id = #{id}
17     </select>
18     
19     <!-- 根据用户名称查询指定用户 -->
20     <select id="findUserByUsername" parameterType="java.lang.String" resultType="com.xiaostudy.mybatis.domain.User">
21         SELECT * FROM USER WHERE username = #{username}
22     </select>
23     
24     <!-- 根据用户名称模糊查询list集合 -->
25     <select id="findUserByUsernames" parameterType="java.lang.String" resultType="com.xiaostudy.mybatis.domain.User">
26         SELECT * FROM USER WHERE username LIKE #{username}
27     </select>
28     
29     <!-- 根据所有用户 -->
30     <select id="findAllUser" resultType="com.xiaostudy.mybatis.domain.User">
31         SELECT * FROM USER 
32     </select>
33     
34     <!-- 添加用户 -->
35     <insert id="insertUser" parameterType="com.xiaostudy.mybatis.domain.User">
36         <selectKey keyProperty="id" resultType="int" order="AFTER">
37             select last_insert_id()
38         </selectKey>
39         insert into user (username, password) values (#{username}, #{password})
40     </insert>
41     
42     <!-- 修改用户名称 -->
43     <update id="updateUserUsername" parameterType="com.xiaostudy.mybatis.domain.User">
44         update user set username=#{username}  where id=#{id}
45     </update>
46     
47     <!-- 修改用户名称和密码 -->
48     <update id="updateUserUsernamePassword" parameterType="com.xiaostudy.mybatis.domain.User">
49         update user set username=#{username}, password=#{password}  where id=#{id}
50     </update>
51 
52 </mapper>

MapperDao.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     <!-- 加载java的配置文件 -->
 8     <properties resource="config/db.properties"/>
 9     
10     <!-- 配置mybatis的环境信息，与spring整合，该信息由spring来管理 -->
11     <environments default="development">
12         <environment id="development">
13             <!-- 配置JDBC事务控制，由mybatis进行管理 -->
14             <transactionManager type="JDBC"></transactionManager>
15             <!-- 配置数据源，采用mybatis连接池 -->
16             <dataSource type="POOLED">
17                 <property name="driver" value="${db.driver}" />
18                 <property name="url" value="${db.url}" />
19                 <property name="username" value="${db.username}" />
20                 <property name="password" value="${db.password}" />
21             </dataSource>
22         </environment>
23     </environments>
24 
25     <!-- 加载映射文件 -->
26     <mappers>
27         <mapper resource="config/User.xml" />
28         <mapper resource="com/xiaostudy/mybatis/mapper/MapperDao.xml" />
29     </mappers>
30     
31 </configuration>

SqlMapConfig.xml

测试类

 1 package com.xiaostudy.mybatis.mapper;
 2 
 3 import java.io.IOException;
 4 import java.io.InputStream;
 5 import java.util.List;
 6 
 7 import org.apache.ibatis.io.Resources;
 8 import org.apache.ibatis.session.SqlSession;
 9 import org.apache.ibatis.session.SqlSessionFactory;
10 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
11 
12 import com.xiaostudy.mybatis.domain.User;
13 
14 public class MybatisTest {
15 
16     public static void main(String[] args) throws IOException {
17         String resource = "config/SqlMapConfig.xml";
18         InputStream inputStream = Resources.getResourceAsStream(resource);
19 
20         // 创建SqlSessionFactory
21         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
22 
23         // 创建SqlSession
24         SqlSession sqlSession = sqlSessionFactory.openSession();
25         
26         MapperDao mapperDao = sqlSession.getMapper(MapperDao.class);
27         List<User> users = mapperDao.findAllUser();
28         
29         for(User user : users) {
30             System.out.println(user);
31         }
32 
33         sqlSession.close();
34     }
35 
36 }