官方文档链接

一、添加依赖

maven project

<!--jtds-->
    <dependency>
      <groupId>net.sourceforge.jtds</groupId>
      <artifactId>jtds</artifactId>
      <version>1.3.1</version>
    </dependency>

    <!--mybatis-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>${mybatis.version}</version>
    </dependency>

注：Mac 上开发，使用SqlServer，所以需要jtds

二、配置文件

注：详细的配置可以查看官方文档

相关配置

jdbc 配置

jdpc.properties

jdbc.url=jdbc:jtds:sqlserver://192.168.0.149:1433/IRCSData
jdbc.username=sa
jdbc.password=123
jdbc.driver=net.sourceforge.jtds.jdbc.Driver

注：驱动配置建议自己手敲一遍，直接复制，有时候IDEA 会对它的属性判断有误，导致报错

mybatis 配置

mybatis-config.xml

<?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>

    <properties resource="jdbc.properties"/>


    <typeAliases>
        <typeAlias type="com.jony.intlcc.domain.Room" alias="Room"/>
        <typeAlias type="com.jony.intlcc.domain.User" alias="User"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/Room.xml"/>
        <mapper resource="mapper/User.xml"/>
    </mappers>
</configuration>

XML 映射

Room.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.jony.intlcc.domain.Room">

    <select id="selectByRoomNum" parameterType="string" resultType="Room">
        SELECT
         RID AS roomId,
         FID AS roomType,
         ZIP AS rcuIp,
         ZPORT AS rcuPort
         FROM ROOM
         WHERE ROOMNUM=#{roomNum}
    </select>


</mapper>

注：建议SQL 语句首先在IDEA 自带的Database（文章最后那张图） 试试，或者数据库也可以，包括驱动、用户名、密码等，看能否连接成功

三、使用

domain、dao、dao.Impl

Room.java

package com.jony.intlcc.domain;

import net.sf.json.JSONObject;

/**
 * Created by jony on 2018/4/10.
 */
public class Room {

    private int roomId;
    private int roomType;

    private String rcuIp;
    private int rcuPort;

    public Room() {
    }

    public Room(int roomId, int roomType, String rcuIp, int rcuPort) {
        this.roomId = roomId;
        this.roomType = roomType;
        this.rcuIp = rcuIp;
        this.rcuPort = rcuPort;
    }

    public int getRoomId() {
        return roomId;
    }

    public void setRoomId(int roomId) {
        this.roomId = roomId;
    }

    public int getRoomType() {
        return roomType;
    }

    public void setRoomType(int roomType) {
        this.roomType = roomType;
    }

    public String getRcuIp() {
        return rcuIp;
    }

    public void setRcuIp(String rcuIp) {
        this.rcuIp = rcuIp;
    }

    public int getRcuPort() {
        return rcuPort;
    }

    public void setRcuPort(int rcuPort) {
        this.rcuPort = rcuPort;
    }

    public String toJSONString(){
        //concise
//        return JSONObject.fromObject(this).toString();

        //optimization
        String jsonString = null;

        try {
            JSONObject jsonObject = JSONObject.fromObject(this);
            jsonString = jsonObject.toString();
        } catch (Exception e){
            e.printStackTrace();
        }

        return jsonString;
    }

    @Override
    public String toString() {
        return "Room{" +
                "roomId: " + roomId +
                ", roomType: " + roomType +
                ", rcuIp: " + rcuIp +
                ", rcuPort: " + rcuPort +
                "}";
    }
}

IRoomDao.java

package com.jony.intlcc.dao;

import com.jony.intlcc.domain.Room;

/**
 * Created by jony on 2018/4/10.
 */
public interface IRoomDao {

    Room queryRoomByRoomNum(String roomNum);
}

IRoomDaoImpl.java

package com.jony.intlcc.dao.impl;

import com.jony.intlcc.dao.IRoomDao;
import com.jony.intlcc.domain.Room;
import com.jony.intlcc.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

/**
 * Created by jony on 2018/4/10.
 */
public class IRoomDaoImpl implements IRoomDao{


    /**
     * Check the room information according to room number
     * @param roomNum
     * @return Room Object
     */
    public Room queryRoomByRoomNum(String roomNum) {


        SqlSession sqlSession = MybatisUtil.getSqlSession();
        Room room = null;

        try {

            room = sqlSession.selectOne("selectByRoomNum", roomNum);
            sqlSession.commit();

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            MybatisUtil.closeSession(sqlSession);
        }

        return room;
    }
}

MybatisUtil

package com.jony.intlcc.util;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.Reader;

/**
 * Created by jony on 2/1/18.
 */
public class MybatisUtil {
    private final  static SqlSessionFactory sqlSessionFactory;

    static {
        String resource="mybatis-config.xml";
        Reader reader =null;
        try {
            reader = Resources.getResourceAsReader(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
    }

    /**
     * 获取SqlSessionFactory
     * @return SqlSessionFactory
     */
    public static SqlSessionFactory getSqlSessionFactory(){
        return sqlSessionFactory;
    }

    /**
     * 获取SqlSession
     * @return SqlSession
     */
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
    /**
     * 关闭SqlSession
     */
    public  static void closeSession(SqlSession sqlSession){
        if (sqlSession!=null)
            sqlSession.close();
    }
}

测试

RoomTest.java

import com.jony.intlcc.dao.impl.IRoomDaoImpl;
import com.jony.intlcc.domain.Room;
import com.jony.intlcc.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

/**
 * Created by jony on 2018/4/10.
 */
public class RoomTest {

    SqlSession sqlSession;

    @Test
    public void queryById() {

        sqlSession = MybatisUtil.getSqlSession();
        String roomNum = "1205";
        try {
            Room result = sqlSession.selectOne("selectByRoomNum", roomNum);

            sqlSession.commit();
            System.out.println("result:" + result.toString());

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MybatisUtil.closeSession(sqlSession);
        }

    }

    @Test
    public void roomDaoFunction(){

        String roomNum = "1201";

        Room room = new IRoomDaoImpl().queryRoomByRoomNum(roomNum);

        System.out.println(room.toString());
    }

}

测试结果

测试结果

关于DataSource properties
在新建的数据源中无法直接改Driver，依据不同客户端系统（仅仅相对于数据库链接相对于）以及不同的数据库jdbc链接需要加载不同的驱动

无jtds.png

正确.png

PS：上图左边栏，Drivers 列表，SqlServer 需要区分系统，所以还是MySQL 好使。