在matlab中采用封装类的形式将数据存入数据库

前言说明

1. 本文采用的odbc方式进行
2. 本文采用matlab类的形式，也可以自行用面向过程的思想，编写函数。封装为类，仅仅为了方便调用。

Sqlserver数据库表设计

封装类 DBHelperForMatlab类

在matlab中新建DBHelperForMatlab类，这里仅写了插入数据函数。

• 所有需要插入数据库时候，统一调用该代码。在数据库账户变更后，只需要改变本类，不需要改变其他代码。当然，单独写个函数文件，也是可行的。
• 与matlab自带的insert.m函数相比，封装后，会有返回值，提示保存是否成功等。由于insert.m函数本身并没有返回值，这是当调通insert函数后，默认cherub数据成功。
• 直接调用该类的方法，用户不用每次都要写关闭数据库连接事件。
• 不足matlab 类效率低，一致被人诟病；尤其在循环调用时，这个可以亲自测试

具体代码如下：

classdef DBHelperForMatlab
    % DBHelperForMatlab  用matlab通过odbc方式建立的数据库操作帮助类
    % 参考 odbc方式数据库操作 https://blog.csdn.net/cxrsdn/article/details/82632863
    % 参考 Matlab面向对象编程实例 https://blog.csdn.net/miscclp/article/details/26090137
    % 参考 matlab规范 https://blog.csdn.net/qq_15971883/article/details/82884353
    % 参考odbc的详细配置方式 https://www.bbsmax.com/A/MAzAvjreJ9/
   properties   (GetAccess ='private',SetAccess ='private') %设置私有变量，不能被该类外访问
      datasource= 'CustomTest';  %前面设置的数据源名称（实际上应该是封装了访问数据所在电脑的ip地址以及数据库名称），告诉matlab要访问操作的数据库位于哪台计算机（ip地址）中的那个数据库；  
      userName='s22222a'; %数据库用户账户
      password ='13344444446'; %登录密码     
    end
    
    methods (Static)   
        % DBHelperForMatlab 显式定义构造函数
        function obj=DBHelperForMatlab()         
        end
        %  InsertDB 将数据插入到数据库表中
        %tableName 数据库表名
        %colnames 数据库列名（字段）
        %data   与数据库列名相对应的数据，注意数据格式要和数据库中的字段类型类似；数值类型，字符类型
        % 返回值 -1：数据库连接失败，没有打开数据库。 1：数据插入成功
         function exeState = InsertDB(tableName,colnames,data)
            obj=DBHelperForMatlab();             
             %CanOPen  判断是否成功打开数据库链接，成功返回1，失败返回0          
            connection=database( obj.datasource,obj.userName,obj.password);
            openState=isopen(connection);
            if openState==0
                exeState=-1; %-1表示没有打开数据库。
            else 
                % https://ww2.mathworks.cn/help/database/ug/database.odbc.connection.execute.html
                insert(connection,tableName,colnames,data);  
                commit(connection);   % 将更新保存到数据库中 
                exeState=1;  % 1 成功将数据插入到数据库。
            end
             close(connection);
         end
    end
end

调用 DBHelperForMatlab类，插入数据库

function  result=testFun()
% testFun 测试封装的插入数据库函数 
arr={'id','StuName','ClassId','age'};  %表中的字段，前2个字段是字符型，后2个字段是数值型 
da={'f999','sd', 124,394};   %前2个字段是字符型，后2个字段是数值型 ；如果字段类型都是数值，也可以用矩阵等形式做参数，如 da=[12 3 4]
 result = DBHelperForMatlab.InsertDB( '[CustomTest].[dbo].TStudent', arr,da) %如果有多行数据需要插入，可以循环调用。
 end 

matlab中的databasetool中的insert.m函数的部分注释

function insert( connect,tableName,fieldNames,data )
%INSERT Export MATLAB data into database table.
%   INSERT(CONNECT,TABLENAME,FIELDNAMES,DATA).
%   CONNECT is an object of type database.ODBCConnection, FIELDNAMES
%   is a cell array of database column names, TABLENAME is the
%   database table and DATA is a cell array, numeric matrix,
%   structure, dataset, or table.
%
%   If DATA is a structure, dataset, or table, it needs to be formatted in
%   a certain way. Each field in the structure or each variable in the
%   dataset or table must be a cell array or double vector of size m*1
%   where m is the number of rows to be inserted.
%
%   Example:
%
%   The following INSERT command inserts the contents of
%   the cell array in to the database table yearlySales
%   for the columns defined in the cell array colNames.
%
%   insert(conn,'yearlySales', colNames, monthlyTotals);
%
%   where, the cell array colNames contains the value:
%   colNames = {'salesTotal'};
%
%   'monthlyTotals' is a cell array containing the data to be
%   inserted into the database table 'yearlySales'
%
%   See also connection/datainsert, connection/fastinsert

%   Copyright 1984-2016 The MathWorks, Inc.
%   山南编辑，补充部分中文翻译。2019年8月20日18:12:53

% Check for valid connection
if(~isopen(connect))
  % shannan汉化 error(message('database:database:invalidConnection'));
    error(message('连接数据库失败！'));  
end

%Input validation
p = inputParser;

p.addRequired('tableName', @database.internal.utilities.DatabaseUtils.tablenameCheck);
p.addRequired('fieldNames', @database.internal.utilities.DatabaseUtils.fieldnamesCheck);
p.addRequired('data', @database.internal.utilities.DatabaseUtils.insertDataCheck);

%Catch Input Validation errors
p.parse(tableName,fieldNames,data);

tablename = char(p.Results.tableName);
fieldNames = cellstr(p.Results.fieldNames);
data = p.Results.data;

%Get size of data
switch class(data)
    
    case {'int8','uint8','int16','uint16','uint32','int32','int64','uint64','single'}
        data = double(data);
        numberOfCols = size(data,2);
        if numberOfCols ~= length(fieldNames)
            error(message('database:insert:numerofColumnsMismatch'))
        end
        
    case {'cell','double'}
        numberOfCols = size(data,2);
        if numberOfCols ~= length(fieldNames)
            error(message('database:insert:numerofColumnsMismatch'))
        end
        
    case 'struct'
        sflds = fieldnames(data);
        if length(sflds) ~= length(fieldNames)
            error(message('database:insert:numerofColumnsMismatch'))
        end
        
        fchk = setxor(sflds,fieldNames);
        if ~isempty(fchk)
          error(message('database:insert:fieldMismatch','Structure fields'))
        end

        % order of columnnames and struct variable names should match
        for i=1:length(fieldNames)

          if strcmpi(sflds{i}, fieldNames{i}) == 0
            error(message('database:insert:fieldMismatch','Structure fields'))
          end

        end
    
        numberOfCols = length(sflds);
        
        data = database.internal.utilities.DatabaseUtils.validateStruct(data);
        
    case 'dataset'
        dSummary = summary(data);
        sflds = {dSummary.Variables.Name};
        fchk = setxor(sflds,fieldNames);
        if ~isempty(fchk)
            error(message('database:insert:fieldMismatch','Dataset variables'))
        end
        
        % order of columnnames and struct variable names should match
        for i=1:length(fieldNames)
          if strcmpi(sflds{i}, fieldNames{i}) == 0
            error(message('database:insert:fieldMismatch','Dataset variables'))
          end
        end
        
        numberOfCols = length(sflds);
        
        
        %convert dataset to struct before pushing to the C API
        data = dataset2struct(data, 'AsScalar', true);
        
        data = database.internal.utilities.DatabaseUtils.validateStruct(data);
        
    case 'table'
        sflds = data.Properties.VariableNames;
        
        if length(sflds) ~= length(fieldNames)
            error(message('database:insert:numerofColumnsMismatch'))
        end
        
        fchk = setxor(sflds,fieldNames);
        if ~isempty(fchk)
          error(message('database:insert:fieldMismatch','Table variables'))
        end

        % order of columnnames and struct variable names should match
        for i=1:length(fieldNames)

          if strcmpi(sflds{i}, fieldNames{i}) == 0
            error(message('database:insert:fieldMismatch','Table variables'))
          end

        end
        
        numberOfCols = length(sflds);
        
        %convert table to struct before pushing to the C API
        data = table2struct(data, 'ToScalar',true);
        
        data = database.internal.utilities.DatabaseUtils.validateStruct(data);
        
    otherwise
        error(message('database:insert:inputDataError'))
        
end

% Create INSERT query to be prepared.
fieldNamesString = '';
parameterString = '';

% Create the field name string for the INSERT statement .. this defines the
% fields in the database table that will be receive data.
% Also build variable ?'s string for later set* commands
% 根据用户传入的表名、列字段 拼接成 标准SQL语句 insert [TStudent](id,StuName,ClassId,age) values (?,?,?,?)
% 注意该标准SQL语句并没有赋值，而是用？代替

for i=1:numberOfCols
    if ( i == numberOfCols)
        fieldNamesString = [ fieldNamesString fieldNames{i}];  %#ok
        parameterString = [parameterString '?)'];                          %#ok
    else
        fieldNamesString = [ fieldNamesString fieldNames{i} ',' ]; %#ok
        parameterString = [parameterString '?,'];                              %#ok
    end
end

try
    
    %Get Database Product name
    dbProdName = connect.DatabaseProductName;
    
    validDB = 0;
    if strcmpi(dbProdName, 'Oracle') || strcmpi(dbProdName, 'PostgreSQL') || strcmpi(dbProdName, 'Microsoft SQL Server')
        validDB = 1;
    end

    if validDB
      
        transactionStmt = connect.Handle.createStatement;
        % Save the current setting and turn off auto-commit
        oldState = connect.AutoCommit;
        connect.AutoCommit = 'off';
    end
    
    %Using the faster ODBC API method for MySQL 
    if(strcmpi(dbProdName, 'MySQL') || strcmpi(dbProdName, 'Access'))
      
        %Create SELECT string
        selectString = ['select ' fieldNamesString ' from ' tablename ' where 1=2'];
        
        %Create new statement for INSERT
        insertStmt = connect.Handle.createStatement;
        insertStmt.insertData(dbProdName, selectString, data);
        
        %close both statements
        insertStmt.closeStatement;
        
    else
        
        % Create the SQL statement 
        parameterizedInsertString = [ 'INSERT INTO '  tablename ' (' fieldNamesString ') ' 'VALUES ( ' parameterString ];   
        %Need to run exec query to get column attributes
        curs = exec(connect,['select ' fieldNamesString ' from ' tablename ' where 1=2']);        
        if ~isempty(curs.Message)
            error(message('database:insert:insertError', curs.Message))
        end
        
        curs = fetch(curs, 1);
        
        if ~isempty(curs.Message)
            error(message('database:insert:insertError', curs.Message))
        end
        
        %Create new statement for INSERT
        insertStmt = connect.Handle.createStatement;
     % 完整的数据库赋值语句是下面这句。 data是数据库表各字段所对应的具体值
        insertStmt.insertData(curs.Statement,parameterizedInsertString, data);        
        %close both statements
        insertStmt.closeStatement;
        close(curs);  
    end        
    
    % close transaction stmt
    if validDB 
      if strcmpi(oldState, 'on')
        % Commit changes 
        transactionStmt.executeQuery('COMMIT');
      end
      
      % Close the transaction statement
      transactionStmt.closeStatement;
      % Reset auto-commit to original value
      connect.AutoCommit = oldState;
    end
    

    
catch e
    %close statements and then throw error
    
    if(exist('curs', 'var'))
        close(curs);
    end
    
    if(exist('insertStmt', 'var'))
        insertStmt.closeStatement;
    end
    
    if validDB 
        if strcmpi(oldState, 'on')
          % Rollback changes in case of failures
          transactionStmt.executeQuery('ROLLBACK');
        end
        % Close the transaction statement
        transactionStmt.closeStatement;
        % Reset auto-commit to original value
        connect.AutoCommit = oldState;
    end
  
    % Reset auto-commit to original value
    throw(e);
end

end