GVKun编程网logo

oracle 数据导入 数据和备注（comment）乱码问题解决办法（oracle数据库导入的数据乱码）

1

对于想了解oracle数据导入数据和备注的读者，本文将是一篇不可错过的文章，我们将详细介绍comment乱码问题解决办法，并且为您提供关于.NETMVC学习笔记（六）—数据导入、10w+Excel数据

对于想了解oracle 数据导入 数据和备注的读者，本文将是一篇不可错过的文章，我们将详细介绍comment乱码问题解决办法，并且为您提供关于.NET MVC 学习笔记（六）— 数据导入、10w+ Excel 数据导入，怎么优化？、Apipost 一大波新功能来袭！连接数据库、TCP 协议、MySQL DDL 数据导入、ArangoDB 数据导入的有价值信息。

本文目录一览：

oracle 数据导入 数据和备注（comment）乱码问题解决办法（oracle数据库导入的数据乱码）

oracle 数据导入 数据和备注（comment）乱码问题解决办法（oracle数据库导入的数据乱码）

转载自：http://www.jb51.cc/article/p-uqifbrhw-mc.html

1 源数据库:

字符集:SIMPLIFIEDCHInesE_CHINA.AL32UTF8
EXP导出客户端字符集(windows导出)：NLS_LANG=SIMPLIFIEDCHInesE_CHINA.ZHS16GBK

2 目标数据库:

字符集：SIMPLIFIED CHInesE_CHINA.ZHS16GBK
IMP导入客户端字符集(linux客户端导入):zh_CN.UTF-8

导入出现的问题:

1 在PL/sql中查询数据为乱码
2 表注释为乱码

解决问题1：在PL/sql中查询数据为乱码

数据查询乱码，如果源数据库和目标数据库字符集不冲突的话，可以考虑为PL/sql所在的客户端的字符集问题。

在PL/sql所在的客户端(pl/sql都装在windows上)环境变量中增加:

至此，查询结果正常。

解决问题2：表注释为乱码

因为是在linux端进行imp导入的，在Linux端执行export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK，然后进行imp重新导入，发现注释正常。

总结:一般出现乱码，考虑三方面的问题:

1.1 导入后如果查询数据为乱码，查看PL/sql所在客户端的字符集，修改为中文字符集。 1.2 导入后如果查询数据为乱码，查看源端和目标端的字符集是否包含(修改数据库字符集比较复杂，不建议) 2 导入后如果表注释为乱码，查看导入客户端的字符集是否和导出端的字符集是否一致。

.NET MVC 学习笔记（六）— 数据导入

.NET MVC 学习笔记（六）— 数据导入

 

.NET MVC 学习笔记（六）—— 数据导入

在程序使用过程中，有时候需要新增大量数据，这样一条条数据去 Add 明显不是很友好，这时候最好就是有一个导入功能，导入所需要的数据，下面我们就一起来看一下导入功能。

一。在导入之前，首先我们需要下载模板，模板下载方法

$("#btnDownloadTemplate")
	.click(function () {
		window.location.href = "@Url.Content("~/Content/ImportClientDataTemplate.xlsx")";
	});

MVC .NET 框架中该方法可以在很容易的下载模板文件。

如果是纯前端框架，该方法则无效，运行效果为直接在浏览器中打开模板文件，显然不是我们想要的。此时可以使用以下办法：

/*
 * 下载文件
 */
window.downloadFile = function(sUrl) {
	//iOS devices do not support downloading. We have to inform user about this.
	if(/(iP)/g.test(navigator.userAgent)) {
		alert(''Your device does not support files downloading. Please try again in desktop browser.'');
		return false;
	}

	//If in Chrome or Safari - download via virtual link click
	if(window.downloadFile.isChrome || window.downloadFile.isSafari) {
		//Creating new link node.
		var link = document.createElement(''a'');
		link.href = sUrl;

		if(link.download !== undefined) {
			//Set HTML5 download attribute. This will prevent file from opening if supported.
			var fileName = sUrl.substring(sUrl.lastIndexOf(''/'') + 1, sUrl.length);
			link.download = fileName;
		}

		//Dispatching click event.
		if(document.createEvent) {
			var e = document.createEvent(''MouseEvents'');
			e.initEvent(''click'', true, true);
			link.dispatchEvent(e);
			return true;
		}
	}

	// Force file download (whether supported by server).
	if(sUrl.indexOf(''?'') === -1) {
		sUrl += ''?download'';
	}

	window.open(sUrl, ''_self'');
	return true;
}

window.downloadFile.isChrome = navigator.userAgent.toLowerCase().indexOf(''chrome'') > -1;
window.downloadFile.isSafari = navigator.userAgent.toLowerCase().indexOf(''safari'') > -1;

方法调用：

downloadFile("../assets/template/Template.xlsx");

二。数据导入

1. 导入按钮：

<div class="btn-group" style="text-align:right;width:82px">
	<label class="input-group-btn">
		<input id="btnSelectData" type="file" name="file" accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" style="left: -9999px; position: absolute;">
		<span class="btn btn-default" style="border-radius:3px">导入会员</span>
	</label>
</div>

2. 导入按钮事件

// 选择文件事件
$("#btnSelectData").change(function (e) {
	var file = e.target.files[0] || e.dataTransfer.files[0];
	if (file) {
		$.bootstrapLoading.start(
			{
				loadingTips: "正在处理数据，请稍候...",
				opacity: 0.8,
				//loading页面透明度
				backgroundColor: "#000",
				TipsColor: "#555",
			});
		// 获取文件资源
		var file = document.getElementById("btnSelectData").files[0];
		var formData = new FormData();
		formData.append(''ExcelData'', file);
		// 保存信息
		$.ajax({
			type: "POST",
			async: true,
			url: "@Url.Content("~/Client/ImportClientData")",
			data: formData,
		contentType: false,
		processData: false,
		mimeType: "multipart/form-data",
		success: function (response) {
			response = $.parseJSON(response);
			var option = {
				message: response.ResultMessage,
				title: response.ResultTitle
			};
			Ewin.alert(option);
			if (response.ResultTitle == "Success") {
				$(''.message-dialog'').on(''hide.bs.modal'', function () {
					refresh();
				});
			}
		},
		complete: function () {
			$.bootstrapLoading.end();
			$("#btnSelectData").val('''');
		}
	});
  }
});

其中 $.bootstrapLoading 是 Loading 功能，导入过程中等待界面，需要导入 PerfectLoad.js

3. Controller 方法

/// <summary>
/// 导入文件
/// </summary>
/// <returns></returns>
public JsonResult ImportClientData()
{
	string result = String.Empty;
	String fileName = String.Empty;
	// 员工信息
	List<ClientDomain> lsClient = new List<ClientDomain>();
	try
	{
		if (Request.Files.Count > 0)
		{
			HttpPostedFileBase file = Request.Files["ExcelData"];
			String filePath = @"../Upload/TempData/";
			if (Directory.Exists(Server.MapPath(filePath)) == false)//如果不存在就创建file文件夹
			{
				Directory.CreateDirectory(Server.MapPath(filePath));
			}
			fileName = Server.MapPath(filePath) + file.FileName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + Path.GetExtension(file.FileName);
			file.SaveAs(fileName);
			// 解析XML文件
			//读取xml
			XmlDocument xdoc = new XmlDocument();
			xdoc.Load(Server.MapPath("/App_Data/ExcelTemplate.xml"));
			XmlElement root = xdoc.DocumentElement;   //获取根节点
			XmlNode node = xdoc.SelectSingleNode("/Functions/Function[@name=''Client'']");
			// 字段列表
			List<ExcelField> lsExcelFields = new List<ExcelField>();
			foreach (XmlNode item in node.ChildNodes)
			{
				String columnName = item.SelectSingleNode("columnName").InnerText;
				String fieldName = item.SelectSingleNode("fieldName").InnerText;
				lsExcelFields.Add(new ExcelField() { ColumnName = columnName, FieldName = fieldName });
			}
			// 获取Excel信息
			for (int iIndex = 0; iIndex < NPOIHelper.GetNumberOfSheets(fileName); iIndex++)
			{
				// Read the CLP head information
				DataTable dtDatas = NPOIHelper.ReadExcel(fileName, iIndex, 0, 0);
				if (dtDatas == null)
					throw new Exception("Read excel error.");
				ClientDomain client = null;
				for (Int32 iRow = 0; iRow < dtDatas.Rows.Count; iRow++)
				{
					client = new ClientDomain();
					// 遍历所有属性
					lsExcelFields.ForEach(item =>
					{
						String sValue = dtDatas.Rows[iRow][item.ColumnName].ToString();
						Type t = client.GetType();
						PropertyInfo propertyInfo = t.GetProperty(item.FieldName);
						if (propertyInfo.PropertyType == typeof(DateTime) || propertyInfo.PropertyType == typeof(DateTime?))
						{
							if (!String.IsNullOrEmpty(sValue))
							{
								propertyInfo.SetValue(client, DateTime.Parse(sValue), null);
							}
						}
						else if (propertyInfo.PropertyType == typeof(Decimal) || propertyInfo.PropertyType == typeof(Decimal?))
						{
							propertyInfo.SetValue(client, Decimal.Parse(sValue), null);
						}
						else if (propertyInfo.PropertyType == typeof(Int32) || propertyInfo.PropertyType == typeof(Int32?))
						{
							propertyInfo.SetValue(client, Int32.Parse(sValue), null);
						}
						else
						{
							propertyInfo.SetValue(client, sValue, null);
						}
					});
					lsClient.Add(client);
				}
			}
		}
		//保存员工
		result = service.SaveImportDatas(lsClient, CurrentLoginUser);
		// 删除临时文件
		CommonMethod.DeleteFile(fileName);
		if (String.IsNullOrEmpty(result))
		{
			LogHelper.LogOperate(String.Format("导入会员信息{0}条", lsClient.Count), Constant.OPEARTE_LOG_INFO, CurrentLoginUser);
			return new JsonResult()
			{
				JsonRequestBehavior = JsonRequestBehavior.AllowGet,
				Data = new { ResultTitle = Constant.Result_Title_Success, ResultMessage = String.Format(MessageConstant.MESSAGE_IMPORT_SUCCESS, lsClient.Count) }
			};
		}
		else
		{
			LogHelper.LogOperate(String.Format("导入会员信息失败：{0}", result), Constant.OPEARTE_LOG_WARNING, CurrentLoginUser);
			return new JsonResult()
			{
				JsonRequestBehavior = JsonRequestBehavior.AllowGet,
				Data = new { ResultTitle = Constant.Result_Title_Warning, ResultMessage = result }
			};
		}
	}
	catch (Exception ex)
	{
		Log.SaveException(ex);
		return new JsonResult()
		{
			JsonRequestBehavior = JsonRequestBehavior.AllowGet,
			Data = new { ResultTitle = Constant.Result_Title_Error, ResultMessage = ex.Message }
		};
	}
	finally
	{
		// 删除临时文件
		CommonMethod.DeleteFile(fileName);
	}
}

ExcelField.cs

/// <summary>
/// Excel Field
/// </summary>
[Serializable]
[DataContract]
public class ExcelField
{
	[DataMember]
	public String ColumnName { get; set; }

	[DataMember]
	public String FieldName { get; set; }
}

ExcelTemplate.xml

<?xml version="1.0" encoding="utf-8" ?>
<Functions>
  <Function name="Client">
    <field>
      <columnName>卡号</columnName>
      <fieldName>CardNo</fieldName>
    </field>
    <field>
      <columnName>姓名</columnName>
      <fieldName>UserName</fieldName>
    </field>
    <field>
      <columnName>性别</columnName>
      <fieldName>Sex</fieldName>
    </field>
    <field>
      <columnName>出生日期</columnName>
      <fieldName>Birthdate</fieldName>
    </field>
    <field>
      <columnName>手机号</columnName>
      <fieldName>Phone</fieldName>
    </field>
    <field>
      <columnName>地址</columnName>
      <fieldName>Address</fieldName>
    </field>
    <field>
      <columnName>积分</columnName>
      <fieldName>Score</fieldName>
    </field>
    <field>
      <columnName>等级</columnName>
      <fieldName>GradeCode</fieldName>
    </field>
  </Function>
</Functions>

导入数据到数据库

/// <summary>
/// 导入数据
/// </summary>
/// <param name="manager"></param>
/// <param name="lsClient"></param>
/// <param name="user"></param>
/// <returns></returns>
public string SaveImportDatas(DBManager manager, List<ClientDomain> lsClient, LoginUser user)
{
	Int32 iCount = 50;
	Int32 iRunSize = (lsClient.Count / iCount) + 1;
	List<ClientDomain> newList = null;
	string result = String.Empty;
	String sUserId = user.RolesName;
	try
	{
		var waits = new List<EventWaitHandle>();
		for (Int32 iIndex = 0; iIndex < iRunSize; iIndex++)
		{
			//计算每个线程执行的数据
			Int32 startIndex = (iIndex * iCount);
			Int32 iPage = iCount;
			if ((lsClient.Count - startIndex) < iCount)
			{
				iPage = (lsClient.Count - startIndex);
			}
			newList = lsClient.GetRange(startIndex, iPage);
			var handler = new ManualResetEvent(false);
			waits.Add(handler);
			ParamModel data = new ParamModel();
			data.UserId = sUserId;
			data.Data = newList;
			data.manager = manager;
			new Thread(new ParameterizedThreadStart(ImportData)).Start(new Tuple<ParamModel, EventWaitHandle>(data, handler));
			WaitHandle.WaitAll(waits.ToArray());
		}
	}
	catch (Exception ex)
	{
		Log.SaveException(ex);
		result = ex.Message;
	}
	return result;
}

/// <summary>
/// 导入数据
/// </summary>
/// <param name="obj"></param>
private void ImportData(Object obj)
{
	var p = (Tuple<ParamModel, EventWaitHandle>)obj;
	ParamModel param = p.Item1 as ParamModel;
	String sUserId = param.UserId;
	DBManager manager = param.manager;
	List<ClientDomain> models = param.Data as List<ClientDomain>;
	models.ForEach(model =>
	{
		List<ClientDomain> clients = ClientBiz.GetDomainByExactFilter(new ClientFilter() { CardNo = model.CardNo }) as List<ClientDomain>;
		if (clients == null || clients.Count == 0)
		{
			// 添加
			model.CreateUser = sUserId;
			model.CreateDateTime = DateTime.Now;
			model.UpdateUser = sUserId;
			model.UpdateDateTime = DateTime.Now;

			String sql = DataHelper.GenerateInsertSQL(DbTableName.Client, model, new LoginUser() { Uid = sUserId }, DateTime.Now);

			manager.Execute(sql, model);
		}
		else
		{
			// 更新
			model.Id = clients[0].Id;
			model.CreateUser = clients[0].CreateUser;
			model.CreateDateTime = clients[0].CreateDateTime;
			model.UpdateUser = sUserId;
			model.UpdateDateTime = DateTime.Now;

			String sql = DataHelper.GenerateUpdateAllFieldSQL(DbTableName.Client, model, new LoginUser() { Uid = sUserId }, DateTime.Now);

			manager.Execute(sql, model);
		}
	});
	p.Item2.Set();
}

以上，数据导入功能完成。

以下程序运行效果

 PS： 在 Excel 导入时，也可以在 ExcelTemplate.xml 中配置一些字段 Check 的问题

例如：

<field>
      <columnName>卡号</columnName>
      <fieldName>CardNo</fieldName>
      <checkList>
        <!--NotNull:非空 Length:字段长度 Type:字段类型-->
        <NotNull>Y</NotNull>
        <Length>20</Length>
        <Type></Type>
      </checkList>
    </field>

ExcelField.cs & CheckModel.cs

/// <summary>
/// Excel 字段
/// </summary>
public class ExcelField
{
	/// <summary>
	/// Excel列名
	/// </summary>
	[DataMember]
	public String ColumnName { get; set; }
	/// <summary>
	/// 字段名称
	/// </summary>
	[DataMember]
	public String FieldName { get; set; }
	/// <summary>
	/// 检测
	/// </summary>
	[DataMember]
	public CheckModel CheckModel { get; set; }
}

/// <summary>
/// 检查项目
/// </summary>
public class CheckModel
{
	/// <summary>
	/// 非空
	/// </summary>
	[DataMember]
	public String NotNull { get; set; }
	/// <summary>
	/// 字段长度检测
	/// </summary>
	[DataMember]
	public Int32 Length { get; set; }
	/// <summary>
	/// 字段类型
	/// </summary>
	[DataMember]
	public String Type { get; set; }
}

字段信息获取以及字段检查方法

/// <summary>
/// 获取所有Excel字段
/// </summary>
/// <param name="functionName">功能名</param>
/// <returns></returns>
public static List<ExcelField> GetExcelFields(String functionName)
{
	// 解析XML文件
	//读取xml
	XmlDocument xdoc = new XmlDocument();
	xdoc.Load("Content/ExcelTemplate.xml");
	XmlElement root = xdoc.DocumentElement;   //获取根节点
	XmlNode node = xdoc.SelectSingleNode(String.Format("/Functions/Function[@name=''{0}'']", functionName));
	// 字段列表
	List<ExcelField> lsExcelFields = new List<ExcelField>();
	foreach (XmlNode item in node.ChildNodes)
	{
		String columnName = item.SelectSingleNode("columnName").InnerText;
		String fieldName = item.SelectSingleNode("fieldName").InnerText;
		ExcelField excelField = new ExcelField();
		// 列名
		excelField.ColumnName = columnName;
		// 字段名
		excelField.FieldName = fieldName;
		XmlNodeList childNode = item.SelectNodes("checkList");
		if (childNode != null && childNode.Count != 0)
		{
			CheckModel check = new CheckModel();
			// 非空判断
			check.NotNull = childNode[0].SelectSingleNode(Constant.Check_NotNull) == null ? "" : (childNode[0].SelectSingleNode(Constant.Check_NotNull).FirstChild == null ? "" : childNode[0].SelectSingleNode(Constant.Check_NotNull).FirstChild.Value);
			// 长度判断
			check.Length = childNode[0].SelectSingleNode(Constant.Check_Length) == null ? -1 : (childNode[0].SelectSingleNode(Constant.Check_Length).FirstChild == null ? -1 : Int32.Parse(childNode[0].SelectSingleNode(Constant.Check_Length).FirstChild.Value));
			// 字段类型
			check.Type = childNode[0].SelectSingleNode(Constant.Check_Type) == null ? "" : (childNode[0].SelectSingleNode(Constant.Check_Type).FirstChild == null ? "" : childNode[0].SelectSingleNode(Constant.Check_Type).FirstChild.Value);
			excelField.CheckModel = check;
		}
		lsExcelFields.Add(excelField);
	}

	return lsExcelFields;
}

/// <summary>
/// 检查字段
/// </summary>
/// <param name="excel"></param>
/// <param name="value"></param>
/// <param name="iRowIndex"></param>
/// <returns></returns>
public static String CheckFieldValue(ExcelField excel, String value, Int32 iRowIndex)
{
	StringBuilder sb = new StringBuilder();
	try
	{
		// 非空判断
		if (Constant.NotNull_Y.Equals(excel.CheckModel.NotNull) && String.IsNullOrEmpty(value))
		{
			sb.AppendLine(String.Format("第{0}行，{1}列值不能为空。", iRowIndex, excel.ColumnName));
		}
		// 长度判断
		if (excel.CheckModel.Length != -1 && (!String.IsNullOrWhiteSpace(value) && value.Length > excel.CheckModel.Length))
		{
			sb.AppendLine(String.Format("第{0}行，{1}列值长度不能超过{2}。", iRowIndex, excel.ColumnName, excel.CheckModel.Length));
		}
		// 类型判断
		if (!String.IsNullOrWhiteSpace(excel.CheckModel.Type))
		{
			// 正则表达式
			String pattern = String.Empty;
			// 表达式结果
			Boolean bResult = true;
			switch (excel.CheckModel.Type)
			{
				// 正整数判断
				case Constant.Type_PositiveInteger:
					pattern = @"^[0-9]*[1-9][0-9]*$";
					bResult = Regex.IsMatch(value ?? "", pattern);
					if (!bResult)
					{
						sb.AppendLine(String.Format("第{0}行，{1}列值应该输入正整数。", iRowIndex, excel.ColumnName));
					}
					break;
				case Constant.Type_Telephone:
					pattern = @"(/(/d{3,4}/)|/d{3,4}-|/s)?/d{7,14}";
					bResult = Regex.IsMatch(value ?? "", pattern);
					if (!bResult)
					{
						sb.AppendLine(String.Format("第{0}行，{1}列值应该输入正确电话号码。", iRowIndex, excel.ColumnName));
					}
					break;
				case Constant.Type_Email:
					pattern = @"^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$";
					bResult = Regex.IsMatch(value ?? "", pattern);
					if (!bResult)
					{
						sb.AppendLine(String.Format("第{0}行，{1}列值应该输入正确邮箱。", iRowIndex, excel.ColumnName));
					}
					break;
			}
		}
		return sb.ToString().Trim();
	}
	catch (Exception ex)
	{
		return ex.Message;
	}
}

/// <summary>
/// 设置属性值
/// </summary>
/// <param name="obj">对象</param>
/// <param name="fieldName">字段名</param>
/// <param name="value">字段值</param>
public static void SetPropertyInfoValue(Object obj,String fieldName, String value)
{
	Type t = obj.GetType();
	PropertyInfo propertyInfo = t.GetProperty(fieldName);
	// 时间类型
	if (propertyInfo.PropertyType == typeof(DateTime) || propertyInfo.PropertyType == typeof(DateTime?))
	{
		if (!String.IsNullOrEmpty(value))
		{
			DateTime dt;
			if (DateTime.TryParse(value, out dt))
			{
				propertyInfo.SetValue(obj, dt, null);
			}
		}
	}
	// Decimal 类型
	else if (propertyInfo.PropertyType == typeof(Decimal)|| propertyInfo.PropertyType == typeof(Decimal?))
	{
		if (!String.IsNullOrEmpty(value))
		{
			Decimal dValue;
			if (Decimal.TryParse(value, out dValue))
			{
				propertyInfo.SetValue(obj, dValue, null);
			}
		}
	}
	// Int32 类型
	else if (propertyInfo.PropertyType == typeof(Int32) || propertyInfo.PropertyType == typeof(Int32?))
	{
		if (!String.IsNullOrEmpty(value))
		{
			Int32 iValue;
			if (Int32.TryParse(value, out iValue))
			{
				propertyInfo.SetValue(obj, iValue, null);
			}
		}
	}
	else
	{
		propertyInfo.SetValue(obj, value, null);
	}
}

　

10w+ Excel 数据导入，怎么优化？

10w+ Excel 数据导入，怎么优化？

作者：后青春期的Keats
https://www.cnblogs.com/keats...

需求说明

项目中有一个 Excel 导入的需求：缴费记录导入。

由实施 / 用户 将别的系统的数据填入我们系统中的 Excel 模板，应用将文件内容读取、校对、转换之后产生欠费数据、票据、票据详情并存储到数据库中。

在我接手之前可能由于之前导入的数据量并不多没有对效率有过高的追求。但是到了 4.0 版本，我预估导入时Excel 行数会是 10w+ 级别，而往数据库插入的数据量是大于 3n 的，也就是说 10w 行的 Excel，则至少向数据库插入 30w 行数据。

因此优化原来的导入代码是势在必行的。我逐步分析和优化了导入的代码，使之在百秒内完成(最终性能瓶颈在数据库的处理速度上，测试服务器 4g 内存不仅放了数据库，还放了很多微服务应用。处理能力不太行)。

具体的过程如下，每一步都有列出影响性能的问题和解决的办法。

导入 Excel 的需求在系统中还是很常见的，我的优化办法可能不是最优的，欢迎读者在评论区留言交流提供更优的思路

一些细节

  • 数据导入：导入使用的模板由系统提供，格式是 xlsx (支持 65535+行数据) ，用户按照表头在对应列写入相应的数据
  • 数据校验：数据校验有两种：
  • 字段长度、字段正则表达式校验等，内存内校验不存在外部数据交互。对性能影响较小
  • 数据重复性校验，如票据号是否和系统已存在的票据号重复(需要查询数据库，十分影响性能)
  • 数据插入：测试环境数据库使用 MySQL 5.7，未分库分表，连接池使用 Druid

迭代记录

第一版：POI + 逐行查询校对 + 逐行插入

这个版本是最古老的版本，采用原生 POI，手动将 Excel 中的行映射成 ArrayList 对象，然后存储到 List<ArrayList> ，代码执行的步骤如下：

  1. 手动读取 Excel 成 List
  2. 循环遍历，在循环中进行以下步骤
  3. 检验字段长度
  4. 一些查询数据库的校验，比如校验当前行欠费对应的房屋是否在系统中存在，需要查询房屋表
  5. 写入当前行数据
  6. 返回执行结果，如果出错 / 校验不合格。则返回提示信息并回滚数据

显而易见的，这样实现一定是赶工赶出来的，后续可能用的少也没有察觉到性能问题，但是它最多适用于个位数/十位数级别的数据。存在以下明显的问题：

  • 查询数据库的校验对每一行数据都要查询一次数据库，应用访问数据库来回的网络IO次数被放大了 n 倍，时间也就放大了 n 倍
  • 写入数据也是逐行写入的，问题和上面的一样
  • 数据读取使用原生 POI，代码十分冗余，可维护性差。

第二版：EasyPOI + 缓存数据库查询操作 + 批量插入

针对第一版分析的三个问题，分别采用以下三个方法优化

缓存数据，以空间换时间

逐行查询数据库校验的时间成本主要在来回的网络IO中，优化方法也很简单。将参加校验的数据全部缓存到 HashMap 中。直接到 HashMap 去命中。另外关注公众号Java技术栈回复福利获取一份Java面试题资料。

例如：校验行中的房屋是否存在，原本是要用 区域 + 楼宇 + 单元 + 房号 去查询房屋表匹配房屋ID，查到则校验通过，生成的欠单中存储房屋ID，校验不通过则返回错误信息给用户。而房屋信息在导入欠费的时候是不会更新的。

并且一个小区的房屋信息也不会很多(5000以内)因此我采用一条SQL，将该小区下所有的房屋以 区域/楼宇/单元/房号 作为 key，以 房屋ID 作为 value，存储到 HashMap 中，后续校验只需要在 HashMap 中命中。

自定义 SessionMapper

Mybatis 原生是不支持将查询到的结果直接写人一个 HashMap 中的，需要自定义 SessionMapper。

SessionMapper 中指定使用 MapResultHandler 处理 SQL 查询的结果集

@Repository  
public class SessionMapper extends SqlSessionDaoSupport {  
  
    @Resource  
    public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {  
        super.setSqlSessionFactory(sqlSessionFactory);  
    }  
  
    // 区域楼宇单元房号 - 房屋ID  
    @SuppressWarnings("unchecked")  
    public Map<String, Long> getHouseMapByAreaId(Long areaId) {  
        MapResultHandler handler = new MapResultHandler();  
  
 this.getSqlSession().select(BaseUnitMapper.class.getName()+".getHouseMapByAreaId", areaId, handler);  
        Map<String, Long> map = handler.getMappedResults();  
        return map;  
    }  
}

MapResultHandler 处理程序，将结果集放入 HashMap

public class MapResultHandler implements ResultHandler {  
    private final Map mappedResults = new HashMap();  
  
    @Override  
    public void handleResult(ResultContext context) {  
        @SuppressWarnings("rawtypes")  
        Map map = (Map)context.getResultObject();  
        mappedResults.put(map.get("key"), map.get("value"));  
    }  
  
    public Map getMappedResults() {  
        return mappedResults;  
    }  
}

示例 Mapper

@Mapper  
@Repository   
public interface BaseUnitMapper {  
    // 收费标准绑定 区域楼宇单元房号 - 房屋ID  
    Map<String, Long> getHouseMapByAreaId(@Param("areaId") Long areaId);  
}

示例 Mapper.xml

<select id="getHouseMapByAreaId" resultMap="mapResultLong">  
    SELECT  
        CONCAT( h.bulid_area_name, h.build_name, h.unit_name, h.house_num ) k,  
        h.house_id v  
    FROM  
        base_house h  
    WHERE  
        h.area_id = #{areaId}  
    GROUP BY  
        h.house_id  
</select>  
              
<resultMap id="mapResultLong" type="java.util.HashMap">  
    <result property="key" column="k" javaType="string" jdbcType="VARCHAR"/>  
    <result property="value" column="v" javaType="long" jdbcType="INTEGER"/>  
</resultMap>

之后在代码中调用 SessionMapper 类对应的方法即可。

使用 values 批量插入

MySQL insert 语句支持使用 values (),(),() 的方式一次插入多行数据，通过 mybatis foreach 结合 java 集合可以实现批量插入，代码写法如下：

<insert id="insertList">  
    insert into table(colom1, colom2)  
    values  
    <foreach collection="list" item="item" index="index" separator=",">  
     ( #{item.colom1}, #{item.colom2})  
    </foreach>  
</insert>

使用 EasyPOI 读写 Excel

EasyPOI采用基于注解的导入导出,修改注解就可以修改Excel，非常方便，代码维护起来也容易。

第三版：EasyExcel + 缓存数据库查询操作 + 批量插入

第二版采用 EasyPOI 之后，对于几千、几万的 Excel 数据已经可以轻松导入了，不过耗时有点久(5W 数据 10分钟左右写入到数据库)不过由于后来导入的操作基本都是开发在一边看日志一边导入，也就没有进一步优化。

但是好景不长，有新小区需要迁入，票据 Excel 有 41w 行，这个时候使用 EasyPOI 在开发环境跑直接就 OOM 了，增大 JVM 内存参数之后，虽然不 OOM 了，但是 CPU 占用 100% 20 分钟仍然未能成功读取全部数据。另外关注公众号Java技术栈回复JVM46获取一份JVM调优教程。

故在读取大 Excel 时需要再优化速度。莫非要我这个渣渣去深入 POI 优化了吗？别慌，先上 GITHUB 找找别的开源项目。这时阿里 EasyExcel 映入眼帘：

emmm，这不是为我量身定制的吗！赶紧拿来试试。

EasyExcel 采用和 EasyPOI 类似的注解方式读写 Excel，因此从 EasyPOI 切换过来很方便，分分钟就搞定了。也确实如阿里大神描述的：41w行、25列、45.5m 数据读取平均耗时 50s，因此对于大 Excel 建议使用 EasyExcel 读取。

第四版：优化数据插入速度

在第二版插入的时候，我使用了 values 批量插入代替逐行插入。每 30000 行拼接一个长 SQL、顺序插入。整个导入方法这块耗时最多，非常拉跨。后来我将每次拼接的行数减少到 10000、5000、3000、1000、500 发现执行最快的是 1000。

结合网上一些对 innodb_buffer_pool_size 描述我猜是因为过长的 SQL 在写操作的时候由于超过内存阈值，发生了磁盘交换。限制了速度，另外测试服务器的数据库性能也不怎么样，过多的插入他也处理不过来。所以最终采用每次 1000 条插入。

每次 1000 条插入后，为了榨干数据库的 CPU，那么网络IO的等待时间就需要利用起来，这个需要多线程来解决，而最简单的多线程可以使用 并行流 来实现，接着我将代码用并行流来测试了一下：

10w行的 excel、42w 欠单、42w记录详情、2w记录、16 线程并行插入数据库、每次 1000 行。插入时间 72s，导入总时间 95 s。

并行插入工具类

并行插入的代码我封装了一个函数式编程的工具类，也提供给大家

/**  
 * 功能：利用并行流快速插入数据  
 *  
 * @author Keats  
 * @date 2020/7/1 9:25  
 */  
public class InsertConsumer {  
    /**  
     * 每个长 SQL 插入的行数，可以根据数据库性能调整  
     */  
    private final static int SIZE = 1000;  
  
    /**  
     * 如果需要调整并发数目，修改下面方法的第二个参数即可  
     */  
    static {  
        System.setProperty("java.util.concurrent.ForkJoinPool.common.parallelism", "4");  
    }  
  
    /**  
     * 插入方法  
     *  
     * @param list     插入数据集合  
     * @param consumer 消费型方法，直接使用 mapper::method 方法引用的方式  
     * @param <T>      插入的数据类型  
     */  
    public static <T> void insertData(List<T> list, Consumer<List<T>> consumer) {  
        if (list == null || list.size() < 1) {  
            return;  
        }  
  
        List<List<T>> streamList = new ArrayList<>();  
  
        for (int i = 0; i < list.size(); i += SIZE) {  
            int j = Math.min((i + SIZE), list.size());  
            List<T> subList = list.subList(i, j);  
            streamList.add(subList);  
        }  
        // 并行流使用的并发数是 CPU 核心数，不能局部更改。全局更改影响较大，斟酌  
        streamList.parallelStream().forEach(consumer);  
    }  
}

这里多数使用到很多 Java8 的API，不了解的朋友可以翻看我之前关于 Java 的博客。方法使用起来很简单：

InsertConsumer.insertData(feeList, arrearageMapper::insertList);

其他影响性能的内容

日志

避免在 for 循环中打印过多的 info 日志

在优化的过程中，我还发现了一个特别影响性能的东西：info 日志，还是使用 41w行、25列、45.5m 数据，在 开始-数据读取完毕 之间每 1000 行打印一条 info 日志，缓存校验数据-校验完毕 之间每行打印 3+ 条 info 日志，日志框架使用 Slf4j 。打印并持久化到磁盘。下面是打印日志和不打印日志效率的差别

打印日志

不打印日志

我以为是我选错 Excel 文件了，又重新选了一次，结果依旧

缓存校验数据-校验完毕，不打印日志耗时仅仅是打印日志耗时的 1/10 ！

总结

提升Excel导入速度的方法：

  • 使用更快的 Excel 读取框架(推荐使用阿里 EasyExcel)
  • 对于需要与数据库交互的校验、按照业务逻辑适当的使用缓存。用空间换时间
  • 使用 values(),(),() 拼接长 SQL 一次插入多行数据
  • 使用多线程插入数据，利用掉网络IO等待时间(推荐使用并行流，简单易用)
  • 避免在循环中打印无用的日志

如果你觉得阅读后有收获，不妨点个推荐吧！

关注公众号Java技术栈回复"面试"获取我整理的2020最全面试题及答案。

推荐去我的博客阅读更多：

1.Java JVM、集合、多线程、新特性系列教程

2.Spring MVC、Spring Boot、Spring Cloud 系列教程

3.Maven、Git、Eclipse、Intellij IDEA 系列工具教程

4.Java、后端、架构、阿里巴巴等大厂最新面试题

觉得不错，别忘了点赞+转发哦！

Apipost 一大波新功能来袭！连接数据库、TCP 协议、MySQL DDL 数据导入

Apipost 一大波新功能来袭！连接数据库、TCP 协议、MySQL DDL 数据导入

OSC 请你来轰趴啦！1028 苏州源创会，一起寻宝 AI 时代

本次 Apipost 带来数据库功能、TCP 协议调试等重量级功能，感兴趣的话大家升级试试吧～

数据库配置

Apipost 提供了数据库连接功能，以便开发者可以在接口调试中可以使用数据库获取入参或进行断言校验 目前 7.2.2 的 Apipost 支持 Mysql、SQL Sever、Oracle、Clickhouse、达梦数据库、PostgreSQL、Redis、MongoDB8 种数据库的连接操作
目前 Apipost 支持在 Api 调试、接口用例、自动化测试的预执行、后执行脚本中进行数据库的相关操作

如何连接数据库

在「项目设置」-「公共资源维护」-「连接数据库」中配置需要连接的数据库信息

数据库脚本配置

在项目设置中配置完成后可以在预、后执行脚本中添加数据库脚本：

在数据库脚本中输入 sql 语句后可以将结果设置为变量，可配置变量类型。

开启控制台打印结果后可以在控制台查看 sql 语句的执行结果

TCP 服务

Apipost 新增 TCP 协议接口调试，调试前需要创建 TCP 服务，并配置服务 IP 和端口，报文接收判断。

在 TCP 服务下新建 TCP 接口即可调试

MySql DDL 数据模型导入

现在导入数据结构时支持导入 MySql DDL

ArangoDB 数据导入

ArangoDB 数据导入

目录

  • arangoimp 方法
    • 参数解析
    • 实例展示
  • python 方法
    • 单条导入
    • 批量导入

<span id="j1">1.arangoimp 方法 </span>

<span id="j1.1"> 参数解析 </span>

全局配置部分 (Global configuration)

  • --backslash-escape <boolean>

use backslash as the escape character for quotes, used for csv (default: false)

  • --batch-size <uint64>

size for individual data batches (in bytes) (default: 16777216)

  • --collection <string>

collection name (default: "")

  • --configuration <string>

the configuration file or ''none'' (default: "")

  • --convert <boolean>

convert the strings ''null'', ''false'', ''true'' and strings containing numbers into non-string types (csv and tsv only) (default: true)

  • --create-collection <boolean>

create collection if it does not yet exist (default: false)

  • --create-collection-type <string>

type of collection if collection is created (edge or document). possible values: "document", "edge" (default: "document")

  • --file <string>

file name ("-" for STDIN) (default: "")

  • --from-collection-prefix <string>

_from collection name prefix (will be prepended to all values in ''_from'') (default: "")

  • --ignore-missing <boolean>

ignore missing columns in csv input (default: false)

  • --on-duplicate <string>

action to perform when a unique key constraint violation occurs. Possible values: ignore, replace, update, error. possible values: "error", "ignore", "replace", "update" (default: "error")

  • --overwrite <boolean>

overwrite collection if it exist (WARNING: this will remove any data from the collection) (default: false)

  • --progress <boolean>

show progress (default: true)

  • --quote <string>

quote character(s), used for csv (default: """)

  • --remove-attribute <string...>

remove an attribute before inserting an attribute into a collection (for csv and tsv only) (default: )

  • --separator <string>

field separator, used for csv and tsv (default: "")

  • --skip-lines <uint64>

number of lines to skip for formats (csv and tsv only) (default: 0)

  • --threads <uint32>

Number of parallel import threads. Most useful for the rocksdb engine (default: 2)

  • --to-collection-prefix <string>

_to collection name prefix (will be prepended to all values in ''_to'') (default: "")

  • --translate <string...>

translate an attribute name (use as --translate "from=to", for csv and tsv only) (default: )

  • --type <string>

type of import file. possible values: "auto", "csv", "json", "jsonl", "tsv" (default: "json")

  • --version <boolean>

reports the version and exits (default: false)

Section ''log'' (Configure the logging)

  • --log.color <boolean>

use colors for TTY logging (default: true)

  • --log.level <string...>

the global or topic-specific log level (default: "info")

  • --log.output <string...>

log destination(s) (default: )

  • --log.role <boolean>

log server role (default: false)

  • --log.use-local-time <boolean>

use local timezone instead of UTC (default: false)

  • --log.use-microtime <boolean>

use microtime instead (default: false)

Section ''server'' (Configure a connection to the server)

  • --server.authentication <boolean>

require authentication credentials when connecting (does not affect the server-side authentication settings) (default: true)

  • --server.connection-timeout <double>

connection timeout in seconds (default: 5)

  • --server.database <string>

database name to use when connecting (default: "_system")

  • --server.endpoint <string>

endpoint to connect to, use ''none'' to start without a server (default: "http+tcp://127.0.0.1:8529")

  • --server.password <string>

password to use when connecting. If not specified and authentication is required, the user will be prompted for a password (default: "")

  • --server.request-timeout <double>

request timeout in seconds (default: 1200)

  • --server.username <string>

username to use when connecting (default: "root")

Section ''ssl'' (Configure SSL communication)

  • --ssl.protocol <uint64>

ssl protocol (1 = SSLv2, 2 = SSLv2 or SSLv3 (negotiated), 3 = SSLv3, 4 = TLSv1, 5 = TLSV1.2). possible values: 1, 2, 3, 4, 5 (default: 5)

Section ''temp'' (Configure temporary files)

  • --temp.path <string>

path for temporary files (default: "")

<span id="j1.2"> 应用实例 </span>

  • 导入节点集合数据
arangoimp --server.endpoint tcp://127.0.0.1:8529 --server.username root --server.password ××× --server.database _system --file test.csv --type csv --create-collection true --create-collection-type document --overwrite true --collection "test"
  • 导入边集合数据
arangoimp --server.endpoint tcp://127.0.0.1:8529 --server.username root --server.password *** --server.database _system --file test.csv --type csv --create-collection true --create-collection-type document --overwrite true --collection "test"

<span id="j2">python 方法 </span>

<span id="j2.1"> 单条导入 </span>

from arango import ArangoClient

# Initialize the ArangoDB client.
client = ArangoClient()

# Connect to "test" database as root user.
db = client.db(''test'', username=''root'', password=''passwd'')

# Get the API wrapper for "students" collection.
students = db.collection(''students'')

# Create some test documents to play around with.
lola = {''_key'': ''lola'', ''GPA'': 3.5, ''first'': ''Lola'', ''last'': ''Martin''}

# Insert a new document. This returns the document metadata.
metadata = students.insert(lola)

<span id="j2.2"> 批量数据导入 </span>

由于每一次 insert 就会产生一次数据库连接，当数据规模较大时，一次次插入比较浪费网络资源，这时候就需要使用 Transactions 了

from arango import ArangoClient

# Initialize the ArangoDB client.
client = ArangoClient()

# Connect to "test" database as root user.
db = client.db(''test'', username=''root'', password=''passwd'')

# Get the API wrapper for "students" collection.
students = db.collection(''students'')

# Begin a transaction via context manager. This returns an instance of
# TransactionDatabase, a database-level API wrapper tailored specifically
# for executing transactions. The transaction is automatically committed
# when exiting the context. The TransactionDatabase wrapper cannot be
# reused after commit and may be discarded after.
with db.begin_transaction() as txn_db:

    # Child wrappers are also tailored for transactions.
    txn_col = txn_db.collection(''students'')

    # API execution context is always set to "transaction".
    assert txn_db.context == ''transaction''
    assert txn_col.context == ''transaction''

    # TransactionJob objects are returned instead of results.
    job1 = txn_col.insert({''_key'': ''Abby''})
    job2 = txn_col.insert({''_key'': ''John''})
    job3 = txn_col.insert({''_key'': ''Mary''})

# Upon exiting context, transaction is automatically committed.
assert ''Abby'' in students
assert ''John'' in students
assert ''Mary'' in students

# Retrieve the status of each transaction job.
for job in txn_db.queued_jobs():
    # Status is set to either "pending" (transaction is not committed yet
    # and result is not available) or "done" (transaction is committed and
    # result is available).
    assert job.status() in {''pending'', ''done''}

# Retrieve the job results.
metadata = job1.result()
assert metadata[''_id''] == ''students/Abby''

metadata = job2.result()
assert metadata[''_id''] == ''students/John''

metadata = job3.result()
assert metadata[''_id''] == ''students/Mary''

# Transactions can be initiated without using a context manager.
# If return_result parameter is set to False, no jobs are returned.
txn_db = db.begin_transaction(return_result=False)
txn_db.collection(''students'').insert({''_key'': ''Jake''})
txn_db.collection(''students'').insert({''_key'': ''Jill''})

# The commit must be called explicitly.
txn_db.commit()
assert ''Jake'' in students
assert ''Jill'' in students

参考资料

<a href="https://docs.arangodb.com/3.3/Manual/GettingStarted/Installing/Linux.html" target="_blank">AranfoDB Document v3.3 </a>

<a href="https://python-driver-for-arangodb.readthedocs.io/en/master/transaction.html" target="_blank">python-arango document</a>

欢迎转载，转载请注明网址：https://www.cnblogs.com/minglex/p/9705481.html

今天关于oracle 数据导入 数据和备注和comment乱码问题解决办法的讲解已经结束，谢谢您的阅读，如果想了解更多关于.NET MVC 学习笔记（六）— 数据导入、10w+ Excel 数据导入，怎么优化？、Apipost 一大波新功能来袭！连接数据库、TCP 协议、MySQL DDL 数据导入、ArangoDB 数据导入的相关知识，请在本站搜索。

本文标签：

上一篇Oracle 12c新特性之——TABLE ACCESS BY INDEX ROWID BATCHED（oracle12c介绍）

下一篇Oracle 两种高级查询总结（oracle高级查询案例）

相关文章