使用mybatis访问sql server

原创文章转载请注明出处：@协思, http://zeeman.cnblogs.com

mybatis作为一种半自动化的ORM工具，可以提供更大的灵活性，逐渐受到社区的欢迎。

官方下载地址是：https://code.google.com/p/mybatis/downloads/list?can=3&q=Product%3DMyBatis

我这里下载的是3.2.2版本。

1. 在eclipse中新建dynamic web project, 引入mybatis-3.2.2.jar,sqljdbc4.jar(可以直接复制jar包到WebContent\WEB-INF\lib下)。

2.在src同级目录新建configuration文件夹，新建mybatis.xml文件，用于保存mybastis的全局配置，内容如下：

<?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.可以看到，我们在mappers中定义了一个employee.xml，在实际开发中，在这个文件中定义SQL和实体映射关系，我们现在在configuration下新建maps目录,在maps下新建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>
 

 我们的目录结构现在看起来会是这样子：

4.mybatis是基于会话和数据库交互的，所以我们需要创建SessionFactory，在src下新建package:com.mosoro.example.data, 新建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();
    }
}

注意我们在这里会读取刚才定义的mybatis.xml文件。

5.新建package:com.mosoro.example.model,class:EmployeeInfo.java，这是我们要用的Model类。

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.定义mapper类。新建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,我们准备工作都做好了，现在可以使用mybatis来访问一次数据库了。新建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.修改index.jsp文件如下：

<%@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. 运行这个页面，可以看到有数据从库中抓出来了。我们最终的项目结构是下面这样：