MyBatis - 範例與步驟總結

1. 建立config檔案，用來設定連線DB的envirimont各種設定
   jdbc.properties:
   

    
   jdbc.driver=org.hsqldb.jdbcDriver
   jdbc.url=jdbc:hsqldb:mem:my-project-test;shutdown=true
   jdbc.username=sa
   jdbc.password=
   

   
   mybatis-config.xml:
   

     
   < configuration>
     
     
         
     
     
     
       
         
         
           
           
           
           
         
       
     
     
       
     
   </ configuration>
   

2. 建立Mapper的interface，並定義所有會被用來呼叫的CRUD等functions， 之後會與Mapper.xml對應
   UserMapper.java:
   

   public interface UserMapper{
   
    public void insertUser(User user);
    public User getUserById(String userId);
    public List getAllUsers();
    public void updateUser(User user);
    public void deleteUser(String userId);
   
   }
   

3. 建立mapper.xml，每一個id都需與出現在步驟2的interface functions名稱相同，並定義好每一個functions所需處理的SQL  Command
   UserMapper.xml
   

   < mapper namespace="cmpnts.UserMapper">
   
     < select id='getUserById' parameterType='String' resultType='User'>
        SELECT 
         userid as Userid, 
         email as Email , 
         Password, 
         name as Name
        FROM USER 
        WHERE USERID = #{userId}
     </ select>
   
   
     
      
      
     
   
     
     < select id="getAllUsers" resultMap="userMap">
       SELECT 
         userid , 
         email , 
         Password, 
         name
        FROM USER 
     </ select> 
   
     
       INSERT INTO USER(userid,email, password, name)
       VALUES(#{_userid},#{_email}, #{_password}, #{_name})
       
     
     
     
       UPDATE USER 
       SET
        PASSWORD = #{_password},
        NAME = #{_name},
        EMAIL = #{_email}
       WHERE USERID = #{_userid}
     
     
     
     
       DELETE FROM USER WHERE USERID = #{userid}
     
   </ mapper>
   

4. 如果會使用比較複雜的資料型態，且有在步驟2、3時使用，記得要定義好這些資料型態
   User.java:
   

   public class User {
    private String _userid;
    private String _name;
    private String _password;
    private String _email;
   
    public String getUserId() {
     return _userid;
    }
   
    public void setUserId(String userid) {
     this._userid = userid;
    }
   
    public String getName() {
     return _name;
    }
   
    public void setName(String username) {
     this._name = username;
    }
   
    public String getPassword() {
     return _password;
    }
   
    public void setPassword(String pwd) {
     this._password = pwd;
    }
    
    public String getEmaill(){
     return _email;
    }
    
    public void setEmaill(String mail){
     _email = mail;
    }
    
    @Override
    public String toString(){
     StringBuffer buf = new StringBuffer();
     buf.append("UserId:").append(this.getUserId());
     buf.append("\r\n\tname:").append(this.getName());
     buf.append("\r\n\tpassword:").append(this.getPassword());
     buf.append("\r\n\temail:").append(this.getEmaill());
     return buf.toString();
    }
   }
   

呼叫main.java:

public class Main {

 /**
  * @param args
  */
 public static void main(String[] args) {
  String resource = "mybatis-config.xml";
  InputStream inputStream;
  try {
   inputStream = Resources.getResourceAsStream(resource);
   SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
     .build(inputStream);

   createTableUser();
   getAllUsers(sqlSessionFactory);
   
   //insert user
   User nuser = new User();
   nuser.setUserId("nuid");
   nuser.setPassword("nuserpwd");
   nuser.setName("rosie");
   nuser.setEmaill("rosie@mail.com.tw");
   insertUser(sqlSessionFactory,nuser);
   System.out.println("---After---");
   getAllUsers(sqlSessionFactory);
   
   User uuser = getUserById(sqlSessionFactory,"Q12");
   uuser.setEmaill("Q12update@mail.com.tw");
   uuser.setPassword("Q12updatePwd");
   uuser.setName("Q12updateName");
   updateUser(sqlSessionFactory,uuser);
   System.out.println("---UpdateAfter---");
   getAllUsers(sqlSessionFactory);
   
   deleteUser(sqlSessionFactory,"Q12");
   System.out.println("---DeleteAfter---");
   getAllUsers(sqlSessionFactory);
   
  } catch (Exception e) {
   e.printStackTrace();
  }
 }
 
 private static User getUserById(SqlSessionFactory sqlSessionFactory,String userid){
  return UserDAO.getUserById(sqlSessionFactory,userid); 
 }
 
 private static void updateUser(SqlSessionFactory sqlSessionFactory,User user){
  UserDAO.updateUser(sqlSessionFactory,user);
 }
 
 private static void deleteUser(SqlSessionFactory sqlSessionFactory, String userid) {
  UserDAO.deleteUser(sqlSessionFactory, userid);
 }
 
 private static void getAllUsers(SqlSessionFactory sqlSessionFactory){
  List list = UserDAO.getAllUsers(sqlSessionFactory);
  for(User user : list)
   System.out.println(user);
 }
 
 private static void insertUser(SqlSessionFactory sqlSessionFactory, User user) {
  UserDAO.insertUser(sqlSessionFactory, user);
 }

 public static void createTableUser() {
  try {
   // DBUnitUtilities.createTable(create);
   Connection conn = DBUnitUtilities.createHsqlDBConnection();
   SqlGetResultMain sqlmain = new SqlGetResultMain(conn);
   sqlmain.setIsAutoCommit(false);
   int update = sqlmain.update(new CreateUser());

   conn = DBUnitUtilities.createHsqlDBConnection();
   MsSqlConnection dbunitConnection = 
     new MsSqlConnection(conn, null);
   DBUnitUtilities.insertTableDataFromExistingXml("res/UserDatas.xml",
     dbunitConnection);
   
   sqlmain.commit();
  } catch (Exception ex) {
   ex.printStackTrace();
  }
 }
}