【Ibatis集成Mycat开源组件】

ibatis集成mycat服务

一、首先搞定ibatis

1）定义数据库配置文件

<?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="db.properties"></properties>

    

    <!-- mybatis别名定义 -->

    <typeAliases> 

        <typeAlias alias="User" type="com.mybatis.test.User"/> 

    </typeAliases> 

    

<!-- 注册对象的空间命名 -->

<environments default="development">

<environment id="development">

<transactionManager type="JDBC" />

<dataSource type="POOLED">

<!-- 1.加载数据库驱动 -->

<property name="driver" value="${db1.driver}" />

<!-- 2.数据库连接地址： -->

<property name="url" value="${db1.url}" />

<!-- 数据库用户... -->

<property name="username" value="${db1.username}" />

<!-- 数据库密码... -->

<property name="password" value="${db1.password}" />

</dataSource>

</environment>

</environments>

<!-- 注册映射文件：java对象与数据库之间的xml文件路径！ -->

<mappers>

        <mapper resource="User.xml"/>

    </mappers>

</configuration>

2）定义SQL文件

<?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.mybatis.test">

 

    <!-- select语句 -->

    <select id="selectUserByID" parameterType="int" resultType="User">

        select * from `t_user` where id = #{id}

    </select>

    

    <select id="selectAllUsers" resultType="User">

        select * from t_user

    </select>

    

    <insert id="addUser" parameterType="User" >

        insert into t_user(id,userName)   values(#{id},#{userName})  

    </insert>

    

    <update id="updateUser" parameterType="User" >

        update t_user set userName=#{userName} where id=#{id}

    </update>

    

    <delete id="deleteUser" parameterType="int">

        delete from t_user where id=#{id}

    </delete>

</mapper>

3）集成关键db.properties,可以配置多数据源

db1.driver = com.mysql.jdbc.Driver

db1.url =jdbc:mysql://localhost:8066/TESTDB

db1.username =root

db1.password =root

 

db2.driver = oracle.jdbc.driver.OracleDriver

db2.url =jdbc:oracle:thin:@localhost:1521:TESTDB

db2.username =root

db2.password =root

 

db3.driver = com.mysql.jdbc.Driver

db3.url =jdbc:mysql://localhost:8066/db3

db3.username =root

db3.password =root

4）测试代码

package cn.com.test;

 

import java.io.Reader;

import java.util.List;

 

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 com.mybatis.test.User;

 

 

public class SqlSessionUtil {

private static final String NS = "com.mybatis.test.";

private static SqlSessionFactory sqlSessionFactory;

    private static Reader reader;

 

    static {

        try {

            reader = Resources.getResourceAsReader("ibatis_config.xml");

            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

 

    public static SqlSessionFactory getSession() {

        return sqlSessionFactory;

    }

 

    public User getUserByID(int userID) {

        SqlSession session = sqlSessionFactory.openSession();

        try {

            User user = session.selectOne(NS + "selectUserByID", userID);

            if (user != null) {

                System.out.println(user.getId() + ":" + user.getUserName());

            }

            

            return user;

        } finally {

            session.close();

        }

    }

 

    public void addUser(User user) {

        SqlSession session = sqlSessionFactory.openSession();

        try {

            int cc = session.insert(NS + "addUser", user);

            System.out.println("add" +cc + " OK ");

        } finally {

            session.close();

        }

    }

    

    public void delete(int id ) {

        SqlSession session = sqlSessionFactory.openSession();

        try {

            int cc = session.delete(NS + "deleteUser", id);

            System.out.println("delete" +cc + " OK ");

        } finally {

            session.close();

        }

    }

    

 

    public void update(User user) {

        SqlSession session = sqlSessionFactory.openSession();

        try {

            int cc = session.update(NS + "updateUser", user);

            System.out.println("update" +cc + " OK ");

        } finally {

            session.close();

        }

    }

    

    public void getUserList() {

        SqlSession session = sqlSessionFactory.openSession();

        try {

            

            List<User> users = session.selectList(NS + "selectAllUsers");

            for (User user : users) {

                System.out.println(user.getId() + ":" + user.getUserName());

            }

 

        } finally {

            session.close();

        }

    }

     public static void main(String[] args) {

          SqlSessionUtil su = new SqlSessionUtil();

         System.out.println(su.getSession());

        //User user = new User(10,"张仨疯");

         //su.addUser(user);

         //

          //user = new User(2,"gaojingsong");

         //su.addUser(user);

 

        User user = su.getUserByID(10);

 

           System.out.println("--------getUserList---------");

           su.getUserList();

 

           user.setUserName("test_mycat");

          su.update(user);

 

         su.delete(1);

}

}

 

 

附件：

附件解压密码：525354786

建表语句：

CREATE TABLE `t_user` (

`id`  int NULL ,

`userName`  varchar(100) NULL ,

PRIMARY KEY (`id`)

)

;

 

select * from t_user

 

 

错误解析

1）schema没有定义表

 

 2）schema定义表，数据库中没有建立表，报片键不存在

 

 

3）新建表，发现mycat新的bug

 


 
 

 

二、验证结果

1）增加

 

2）查询单个和列表

 

3）修改删除

 

4）修改删除验证