MySQL8 .NET框架4的Nhibernate

Question

我目前正在.NET框架4中建立一个以Nhibernate和MySQL 8作为我的DB的项目。

我似乎不知道我哪里出了问题，但我猜是因为我的hibernate.cfg.xml中的方言，它不会返回任何东西。

我想知道从我的MySQL8配置xml中这一行是否正确。

<property name="dialect">NHibernate.Dialect.MySQLDialect</property>
<property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>

我想运行的代码：

MyConfig = new Configuration();
        MyConfig.Configure();
        MyConfig.AddAssembly(typeof(Users).Assembly);
        MySessionFactory = MyConfig.BuildSessionFactory();
        MySession = MySessionFactory.OpenSession();
        using (MySession)
        {
            int id = 1;
            Users user = MySession.CreateCriteria(typeof(Users))
                                  .Add(Restrictions.Eq("UserId", id))
                                  .UniqueResult<Users>();
            Console.WriteLine($"Get {user}");
        }

The Users.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="My"
                   namespace="My.Domain">
    <class name="Users">
        <id name="UserId">
            <generator class="native"></generator>
        </id>
        <property name ="Username" />
        <property name ="FirstName" />
        <property name ="LastName" />
        <property name ="Status" />
    </class>
</hibernate-mapping>

Answer 1

NHibernate MySQL8 MVC5 .NET框架4.5.2

必要工具：

• Microsoft Visual Studio Community2019
• .NET Framework 4.5.2
• MySQL Community v8.0.22

NuGet包:依赖关系

4.1.2.4000

• NHibernateX 1.3.1.4000

• MySql
• NHibernate Data 6.9.12
• MySql数据实体6.9.12

在项目中添加NHibernate文件夹并创建以下文件：

• NHibernate.cfg.xml
• NHibertnateSession.cs

在Models文件夹中创建这些文件：

• Product.cs
• Product.hbm.xml

在ProductController.cs控制器文件夹中创建：

MySQL表

CREATE TABLE `products` (
  `Id` int(11) NOT NULL,
  `Name` varchar(45) DEFAULT NULL,
  `Brand` varchar(45) DEFAULT NULL,
  `CreateAt` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`Id`)
);

NHibernate.cfg.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
        <!-- Microsoft SQL Server -->
        <!--<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
        <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
        <property name="connection.connection_string">Server=DESKTOP-U0JOPLS;database=my_db;Integrated Security=SSPI;</property>
        <property name="dialect">NHibernate.Dialect.MsSql2012Dialect</property>-->
        
    <!-- MySQL Server -->
    <property name="dialect">NHibernate.Dialect.MySQLDialect</property>
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
    <property name="connection.connection_string">Server=localhost;Port=3306;Database=healthresearch;Uid=root;Pwd=ssl@1234;</property>
    <mapping assembly="NHibernateMySQL" />
    
    </session-factory>
</hibernate-configuration>

NHibertnateSession.cs

using NHibernate;
using NHibernate.Cfg;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace NHibernateMySQL.Models
{
    public class NHibertnateSession
    {
        public static ISession OpenSession()
        {
            var mConfiguration = new Configuration();
            var path = HttpContext.Current.Server.MapPath(@"~\NHibernate\NHibernate.cfg.xml");
            mConfiguration.Configure(path);
            var model = HttpContext.Current.Server.MapPath(@"~\Models\Product.hbm.xml");
            mConfiguration.AddFile(model);
            ISessionFactory mSession = mConfiguration.BuildSessionFactory();
            return mSession.OpenSession();
        }
    }
}

Product.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace NHibernateMySQL.Models
{
    public class Product
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string Brand { get; set; }
        public virtual string CreateAt { get; set; }
    }
}

Product.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" assembly="NHibernateMySQL" namespace="NHibernateMySQL.Models">
    <class name="Product" table="products" dynamic-update="true" >
        <cache usage="read-write"/>
        <id name="Id" column="Id" type="int">
            <generator class="native" />
        </id>
        <property name="Name" />
        <property name="Brand" />
        <property name="CreateAt" />
    </class>
</hibernate-mapping>

ProductController.cs

using NHibernate;
using NHibernate.Linq;
using NHibernateMySQL.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;

namespace NHibernateMySQL.Controllers
{
    public class ProductController : ApiController
    {

        //======================================================| GET
        //[Authorize]
        [Route("Api/Product/GetAllProducts")]
        [HttpGet]
        public async Task<IHttpActionResult> GetAllProducts()
        {
            using (ISession session = NHibertnateSession.OpenSession())
            {
                //var response = session.Query<Products>().ToList();
                var response = await session.Query<Product>().ToListAsync();
                if (response != null)
                {
                    return Ok(response);
                }
                return NotFound();
            }
        }

        //[Authorize]
        [Route("Api/Task/GetProductById", Name = "GetProductById")]
        [HttpGet]
        public async Task<IHttpActionResult> GetProductById([FromUri] int id)
        {
            using (ISession session = NHibertnateSession.OpenSession())
            {
                var response = await session.Query<Product>().Where(index => index.Id == id).FirstOrDefaultAsync();
                if (response != null)
                {
                    return Ok(response);
                }
                return NotFound();
            }
        }

        //======================================================| Add
        [Route("Api/Product/Add")]
        [HttpPost]
        public async Task<IHttpActionResult> AddProduct([FromBody] Product item)
        {
            using (var _session = NHibertnateSession.OpenSession())
            {
                using (var _transaction = _session.BeginTransaction())
                {
                    var response = await _session.SaveAsync(item);
                    _transaction.Commit();
                    if (response != null)
                    {
                        return CreatedAtRoute(nameof(GetProductById), new { id = item.Id }, item); //https://www.jasoncavett.com/blog/converting-to-attribute-routing-in-webapi-applications/
                    }
                    return null;  //failure;
                }
            }
        }

        //======================================================| Delete
        //[Authorize]
        [Route("Api/Product/Delete")]
        [HttpDelete]
        public async Task<IHttpActionResult> DeleteProductById([FromUri] int id)
        {
            using (var _session = NHibertnateSession.OpenSession())
            {
                using (var _transaction = _session.BeginTransaction())
                {
                    var model = await _session.Query<Product>().Where(index => index.Id == id).FirstOrDefaultAsync();
                    if (model != null)
                    {
                        var response = _session.DeleteAsync(model);
                        _transaction.Commit();
                        if (response != null)
                        {
                            return Ok(response);
                        }
                        return NotFound();
                    }
                    return NotFound();
                }
            }
        }

        //======================================================| Put/Update
        [Route("Api/Product/Update")]
        [HttpPut]
        public async Task<IHttpActionResult> UpdateProductById([FromUri] int id, [FromBody] Product item)
        {
            using (var _session = NHibertnateSession.OpenSession())
            {
                using (var _transaction = _session.BeginTransaction())
                {
                    var model = await _session.Query<Product>().Where(index => index.Id == id).FirstOrDefaultAsync();
                    if (model != null)
                    {
                        model.Name = item.Name;
                        model.Brand = item.Brand;
                    }
                    var response = _session.SaveOrUpdateAsync(model);
                    _transaction.Commit();
                    if (response != null)
                    {
                        return Ok(response);
                    }
                    return NotFound();
                }
            }
        }

    }
}

源代码：https://github.com/kamalstis1009/NHibernateMySQL