一、添加依赖
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 好使。
网友评论