IBatis.NET连接MySQL数据库配置笔记

Software Version：IBatis.NET 1.6.2.0

MySQL :5.5.10

mysql-connector-net:6.4.3

visual studio 2010

-----------------------------------------------------------------------------------------------

首先在官网上下载IBatisNET的bin包http://www.mybatis.org/dotnet.html，在mysql官网上下载mysql-connection-net：http://dev.mysql.com/downloads/connector/net/，笔者选择的是“Windows (x86, 32-bit), MSI Installer”版本。

以下是具体事例步骤：

1、将SqlMapConfig.xsd、SqlMap.xsd、provider.xsd三个文件Copy到“D:\Program Files (x86)\Microsoft Visual Studio 10.0\Xml\Schemas”目录中，以便vs能够识别IBatis的一些属性

ps：“D:\Program Files (x86)\Microsoft Visual Studio 10.0\Xml\Schemas”目录是VS的安装目录，请根据自己的目录自行解决。

2、新建一个Project，笔者新建的是WinForm项目。

3、将“Ibatis.DataMapper.1.6.2.bin”目录下的IBatisNet.DataMapper.dll、IBatisNet.Common.dll两个文件copy到项目中。

并且添加到引用里面。

ps：以上两个dll文件是否必须添加到项目中有待论证。

4、将“Ibatis.DataMapper.1.6.2.bin”目录下的providers.config复制到项目中，并且把除MySql以外的其他provider的enabled属性设置为false，MySql的enabled属性设置为true：

<provider
    name="MySql"
    description="MySQL, MySQL provider 6.4.3.0"
    enabled="true"
    assemblyName="MySql.Data, Version=6.4.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionClass="MySql.Data.MySqlClient.MySqlConnection"
    commandClass="MySql.Data.MySqlClient.MySqlCommand"
    parameterClass="MySql.Data.MySqlClient.MySqlParameter"
    parameterDbTypeClass="MySql.Data.MySqlClient.MySqlDbType"
    parameterDbTypeProperty="MySqlDbType"
    dataAdapterClass="MySql.Data.MySqlClient.MySqlDataAdapter"
    commandBuilderClass="MySql.Data.MySqlClient.MySqlCommandBuilder"
    usePositionalParameters="false"
    useParameterPrefixInSql="true"
    useParameterPrefixInParameter="true"
    parameterPrefix="?"
    allowMARS="false"   
  />

ps：注意version的版本号匹配。

5、“Ibatis.DataMapper.1.6.2.bin”目录下的sample.SqlMap.config文件复制到项目中，改名为SqlMap.config（不是必须的）。内容如下：

<?xml version="1.0" encoding="utf-8"?>
<sqlMapConfig
  xmlns="http://ibatis.apache.org/dataMapper"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <settings>
        <setting useStatementNamespaces="false"/>
        <setting cacheModelsEnabled="true"/>
    </settings>

    <providers resource="..\..\providers.config"/>

    <database>
        <provider name="MySql"/>
        <dataSource name="mybatisSource"
                   connectionString="Host=localhost;
                   UserName=root;
                   Password=123;
                   Database=mybatis;
                   Port=3306;" />
    </database>

    <sqlMaps>
        <sqlMap resource="..\..\User.xml"/>
    </sqlMaps>

</sqlMapConfig>

6、添加Mapper文件User.xml

<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="User"
        xmlns="http://ibatis.apache.org/mapping"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <alias>
        <typeAlias alias="User" type="MybatisTest.User,MybatisTest" />
    </alias>

    <statements>
        <insert id="InsertUser" parameterClass="User">
            INSERT INTO user
            (id, name)
            VALUES
            (#Id#,#Name#)
        </insert>

        <select id="SelectUser" parameterClass="int" resultMap="select_user_result">
            SELECT
            id
            ,name
            FROM user
            WHERE
            id = #value#
        </select>

        <select id="SelectUserList" parameterClass="int" resultMap="select_user_result">
            SELECT
            id
            ,name
            FROM user
        </select>

        <update id="update_user" parameterClass="User">
            UPDATE user set
            name = #Name#
            WHERE
            id = #Id#
        </update>
       
    </statements>
    <resultMaps>
        <resultMap id="select_user_result" class="User">
            <result property="Id" column="id"/>
            <result property="Name" column="name"/>
        </resultMap>
    </resultMaps>
</sqlMap>

7、实现程序：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using IBatisNet.DataMapper.Configuration;
using IBatisNet.DataMapper;

namespace MybatisTest
{
    public partial class Form1 : Form
    {
        private static ISqlMapper mapper;
        public Form1()
        {
            InitializeComponent();
            InitMapper();
        }

        public void InitMapper()
        {
            DomSqlMapBuilder builder = new DomSqlMapBuilder();
            mapper = builder.Configure("..\\..\\SqlMap.config");
        }

        private void InsertButton_Click(object sender, EventArgs e)
        {
            string id = this.IDTextBox.Text.Equals("") ? "0" : this.IDTextBox.Text;
            User user = new User()
            {
                Id = int.Parse(id),
                Name = this.NameTextBox.Text,
            };
            try
            {
                mapper.Insert("InsertUser", user);
                MessageBox.Show(this, "Insert success!", "Tips"
                    , MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.Message, "Error"
                    , MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void SelectButton_Click(object sender, EventArgs e)
        {
            if (this.IDTextBox.Text.Equals("") == true)
            {
                MessageBox.Show(this, "The id is null,please input a number!", "Tips"
                , MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                int id = int.Parse(this.IDTextBox.Text);
                User user = mapper.QueryForObject("SelectUser", id) as User;

                this.ResultLabel.Text = "Result is :\nid:" + user.Id.ToString()
                    + "\nname:" + user.Name;
            }
        }

        private void SelectListButton_Click(object sender, EventArgs e)
        {
            List<User> users = mapper.QueryForList<User>("SelectUserList", null) as List<User>;
            this.ResultLabel.Text = "Result is :\n";
            foreach (User user in users)
            {
                this.ResultLabel.Text += "id:" + user.Id.ToString()
                    + "\nname:" + user.Name+"\n";
            }
        }

        private void UpdateButton_Click(object sender, EventArgs e)
        {
            string id = this.IDTextBox.Text.Equals("") ? "0" : this.IDTextBox.Text;
            User user = new User()
            {
                Id = int.Parse(id),
                Name = this.NameTextBox.Text,
            };

            try
            {
                int result = mapper.Update("update_user", user);
                if (result > 0)
                {
                    MessageBox.Show(this, "Update success!", "Tips"
                    , MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show(this, "Update failed!", "Tips"
                    , MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.Message, "Error"
                    , MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}

界面设计如下：

ps：MySQL中需要手动创建数据库mybatis，以及表user