Using mybatis access sql server

 

mybatis ORM as a semi-automated tools that can provide greater flexibility, and gradually welcomed by the community.

 

official download address is: https://code.google. com / p / mybatis / downloads / list? can = 3 & q = Product% 3DMyBatis

 

I am here to download the 3.2.2 version.

 

1. in eclipse create dynamic web project, introducing mybatis-3.2.2.jar, sqljdbc4.jar (jar package can be directly copied to WebContent \ WEB-INF \ lib under).

 

 

2. sibling directories in the src folder new configuration, the new mybatis.xml file for saving mybastis global configuration, as follows:

 

  

<?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> 
    <typeAliases> 
        <typeAlias alias="EmployeeInfo" type="com.mosoro.example.model.EmployeeInfo" /> 
    </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://192.168.*.*:1433;databaseName=Northwind; catalogName=Northwind" /> 
                <property name="username" value="sa" /> 
                <property name="password" value="****" /> 
                <property name="poolMaximumActiveConnections" value="50" /> 
            </dataSource> 
        </environment> 
    </environments> 
 
    <mappers> 
        <mapper resource="maps/Employee.xml" /> 
    </mappers> 
</configuration>

 

 

3. you can see, we have defined a mappers employee.xml, in the actual development, in this file defines the mapping between SQL and entities, we are now under the new maps in the configuration directory, maps under the new Employee . xml

 

  

<?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.mosoro.example.mapper.EmployeeMapper"> 
    <select id="GetList" parameterType="int" resultType="EmployeeInfo"><![CDATA[ 
        select * from Employees with(nolock) where EmployeeID = #{EmployeeID} 
    ]]></select> 
</mapper> 
 

 

 

our directory structure now looks something like this:

 

 

4.mybatis is session-based and database interaction, so we need to create the SessionFactory, in the src under the new package: com.mosoro.example.data, New class: SessionFactoryManager.java

 

  

package com.mosoro.example.data; 
 
import org.apache.ibatis.session.SqlSession; 
import org.apache.ibatis.session.SqlSessionFactory; 
import org.apache.ibatis.session.SqlSessionFactoryBuilder; 
 
public class SessionFactoryManager { 
     
    private static SqlSessionFactory _sqlSessionFactory; 
    static { 
        SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder(); 
        _sqlSessionFactory = ssfb.build(SessionFactoryManager.class.getClassLoader() 
                .getResourceAsStream("mybatis.xml")); 
 
    } 
 
    public static SqlSessionFactory getSSF() { 
        return _sqlSessionFactory; 
    } 
 
    public static SqlSession openSession() { 
        return getSSF().openSession(); 
    } 
}

 

 

Note here we'll read just defined mybatis.xml file.

 

5. New package: com.mosoro.example.model, class: EmployeeInfo.java, this is our use of the Model class.

 

  

package com.mosoro.example.model; 
 
public class EmployeeInfo { 
 
    private int EmployeeID; 
    private String LastName; 
 
    public int getEmployeeID() { 
        return EmployeeID; 
    } 
 
    public void setEmployeeID(int EmployeeID) { 
        this.EmployeeID = EmployeeID; 
    } 
 
    public String getLastName(){ 
        return this.LastName; 
    } 
     
    public void setLastName(String LastName){ 
        this.LastName = LastName; 
    } 
}

 

 

 

6. defined mapper classes. New package: com.mosoro.example.mapper, interface: EmployeeMapper.java

 

  

package com.mosoro.example.mapper; 
 
import java.util.List; 
 
import com.mosoro.example.model.EmployeeInfo; 
 
public interface EmployeeMapper { 
    List<EmployeeInfo> GetList(int id); 
}

 

 

 

7.ok, we are ready to work well, you can now use mybatis to access once the database. New package: com.mosoro.example.biz, class: EmployeeBiz.java

 

  

package com.mosoro.example.biz; 
 
import java.util.List; 
 
import org.apache.ibatis.session.SqlSession; 
import org.apache.log4j.Logger; 
 
import com.mosoro.example.data.SessionFactoryManager; 
import com.mosoro.example.mapper.EmployeeMapper; 
import com.mosoro.example.model.EmployeeInfo; 
 
public class EmployeeBiz { 
    private Logger logger = Logger.getLogger(EmployeeBiz.class); 
 
    public List<EmployeeInfo> GetList() { 
        // 获取SqlSession 
        SqlSession session = SessionFactoryManager.openSession(); 
        try { 
            EmployeeMapper userDA = session.getMapper(EmployeeMapper.class); 
            List<EmployeeInfo> users = userDA.GetList(1); 
 
            logger.debug("get emloyee list ok"); 
            return users; 
        } catch (Exception e) { 
            e.printStackTrace(); 
            return null; 
        } finally { 
            session.close(); 
        } 
    } 
 
}

 

 

8. modify index.jsp file as follows:

 

  

<%@page import="org.apache.jasper.tagplugins.jstl.core.ForEach"%> 
<%@ page language="java" contentType="text/html; charset=UTF-8" 
    pageEncoding="UTF-8" 
%> 
<%@ page import = "java.util.*" %> 
<%@ page import = "com.mosoro.example.biz.*" %> 
<%@ page import = "com.mosoro.example.model.*" %> 
 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 
<html> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> 
<title>mybatis example</title> 
</head> 
<body> 
<% 
EmployeeBiz employeeBiz =new EmployeeBiz(); 
List<EmployeeInfo> list =  employeeBiz.GetList(); 
for(EmployeeInfo e : list){ 
    out.println(e.getLastName()+"<br />"); 
} 
%> 
</body> 
</html>

 

 

9. run this page, you can see the data from the library caught out. Our final project structure look like this: