excel 导入插入数据库

最新推荐文章于 2024-09-06 10:40:56 发布

zlm_86

最新推荐文章于 2024-09-06 10:40:56 发布

阅读量554

点赞数

文章标签： excel 数据库

本文链接：https://blog.csdn.net/zlm_86/article/details/72178538

版权

package com.meihui.nhitsm.temp.application.pj.imp;

import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.meihui.nhitsm.util.Constants;
import com.meihui.nhitsm.util.id.UUIDGenerator;
import com.meihui.nhitsm.util.key.KeyUtil;
import com.meihui.nhitsm.util.log.ModuleVersion;

import jxl.Sheet;
import jxl.Workbook;
import com.platform.eserver.log.LoggerFactory;
/**
*
* 
* Title:
* 
* 
* Description: 从Excel表中导入配件信息
* 
* 
* Copyright: Copyright (c) 2017
* 
* 
* Company: MH
* 
*
* @author zlm
* @version 1.0
*/
public class InsertParts {
 //数据库连接对象
 private static Connection conn =null;
 //数据库操作执行对象
 private static Statement stmt = null;
 //数据库结果集
 static ResultSet rs = null;

 /**
 * 程序执行入口
 * @param args
 */
 public static void main(String[] args) {
 // TODO Auto-generated method stub
 System.setProperty("ESERVER_HOME", "E:\\release\\eserver1.00");
 System.out.println("start");
 BufferedOutputStream out = null;
 try {
 //获取数据库连接
 getDBConnection();
 System.out.println("[InsertParts.main]成功获取数据库连接");
 //读取excel
 InputStream is = new FileInputStream("D:/配件.xls");
 jxl.Workbook rwb = Workbook.getWorkbook(is);
 Sheet st = rwb.getSheet(0); // 读取第一个sheet
 int rowNum = st.getRows();// 行数
 for (int i = 1; i < rowNum; i++) {
 System.out.println("[InsertParts.main]准备获取第"+i+"行记录.");
 PJInfo pJInfo = getPJInfo(i, st);
 if(pJInfo==null){
 System.out.println("[InsertParts.main]获取配件记录失败:第"+i+"行记录.");
 //忽略这条,继续处理下一条
 continue;
 }
 //获取实例ID
 String instanceID = getInstanceID(pJInfo.getInstanceCode());
 System.out.println("实例ID :" +instanceID);
 //如果不存在,忽略这一条,继续处理下一条
 if(instanceID == null || "".equals(instanceID)){
 continue;
 }
 //根据实例ID获取实例分类的ID
 String typeId = getTypeByInstanceID(instanceID);
 //根据实例分类的ID,获取对应的配件ID
 String pjTypeId = getPJTypeID(typeId);
 if(pjTypeId == null || "".equals(pjTypeId)){
 System.out.println("[InsertParts.main]未获取'配件'这个分类");
 continue;
 }
 //查pjTypeID下，有没有这条记录的名为"配件分类(cpu,内存,硬盘来自excel表格)"的分类
 String ptypeId = insertPJType(pjTypeId, pJInfo);
 //将excel中内容插入数据库
 insertPJInfo(pJInfo,ptypeId,instanceID );
 //插入关联表
 insertRelation(instanceID,ptypeId);
 }
 } catch (Exception e) {

 e.printStackTrace();
 } finally {
 if (out != null) {
 try {
 out.close();
 } catch (IOException e) {
 e.printStackTrace();
 }
 }
 try {
 if (stmt != null) {
 stmt.close();
 }
 if (conn != null && !conn.isClosed()) {
 conn.close();
 }
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }
 }
 }

 /**
 * 数据库连接对象
 */
 private static void getDBConnection(){
 //连接数据库
 try {
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Properties pro = new Properties();
 pro.setProperty("user", "NHEXPRESSWAY");
 pro.setProperty("password", "nh_123");
 //获取连接
 conn = DriverManager.getConnection("jdbc:oracle:thin:@172.18.9.247:1521:nhyw", pro);
 stmt = conn.createStatement();
 } catch (ClassNotFoundException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }
 }

 /**
 * 从excel中读取某行记录
 * @param rowNum 要读取的行
 * @param st excel数据源
 * @return 返回配件对象
 */
 private static PJInfo getPJInfo(int rowNum, Sheet st) {
 try {
 PJInfo pjInfo = new PJInfo();
 //实例编码
 pjInfo.setInstanceCode(st.getCell(0, rowNum).getContents());
 //配件分类
 pjInfo.setTypeSort(st.getCell(1, rowNum).getContents());
 //类别名称
 pjInfo.setTypeName(st.getCell(2, rowNum).getContents());
 //实例品牌
 pjInfo.setBrandDesc(st.getCell(3, rowNum).getContents());
 //实例型号
 pjInfo.setCiModel(st.getCell(4, rowNum).getContents());
 //使用日期
 pjInfo.setBeginUseTime(st.getCell(5, rowNum).getContents());
 return pjInfo;
 } catch (Exception e) {
 e.printStackTrace();
 return null;
 }
 }

 /**
 * 根据实例编码，获取实例ID
 * @param instanceCode 实例编码
 * @return 实例 ID
 */
 private static String getInstanceID(String instanceCode) {
 String sql="select * from CMDB_CI_INSTANCE where INSTANCE_CODE= '"+ instanceCode +"'";
 System.out.println(sql);
 String instanceID = "";
 ResultSet rs;
 try {
 rs = stmt.executeQuery(sql.toString());
 if (rs.next()) {
 instanceID=rs.getString("ID");
 } else {
 System.out.println("[InsertParts.getInstanceID]此实例不存在");
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return instanceID;
 }

 /**
 * 根据实例ID，获取实例对应分类ID
 * @param instanceID 实例ID
 * @return 分类ID
 */
 private static String getTypeByInstanceID(String instanceID) {
 String sql="select * from CMDB_CI_INSTANCE where ID= '"+ instanceID +"'";
 System.out.println("[InsertParts.getTypeByInstanceID]根据实例ID,获取对应分类ID的语句: "+sql);
 String typeId = "";
 ResultSet rs;
 try {
 rs = stmt.executeQuery(sql.toString());
 if (rs.next()) {
 typeId=rs.getString("TYPE_ID");
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return typeId;
 }

 /**
 * 根据实例分类的ID,获取对应的配件ID
 * 查同级分类目录是否存在叫“配件”的目录，存在则返回其typeID
 * @param typeID 实例分类的ID
 * @return 配件的ID
 */
 private static String getPJTypeID(String typeID) {
 String sql= " select ID from CMDB_CI_TYPE where PARENT_ID=("
 + " select PARENT_ID from CMDB_CI_TYPE where id='"+typeID+"') "
 + " and Type_NAME='配件' ";
 System.out.println("[InsertParts.getPJTypeID]根据实例分类的ID,获取对应配件的ID: "+sql);
 String pJTypeID = "";
 ResultSet rs;
 try {
 rs = stmt.executeQuery(sql.toString());
 if (rs.next()) {
 pJTypeID=rs.getString("ID");
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return pJTypeID;
 }

 /**
 * 查pjTypeID下，有没有这条记录的名为"配件分类(cpu,内存,硬盘来自excel表格)"的分类；
 * 有：查出此分类的typeID；
 * 如果没有：插入名为“配件分类”的分类记录，然后查出来，返回typeID
 * @param pjTypeID "配件"的ID
 * @param pjInfo 实体类
 * @return 分类的typeID
 */
 private static String insertPJType(String pjTypeID, PJInfo pjInfo ) {
 String sql=new StringBuffer(" select * from CMDB_CI_TYPE where ")
 .append(" PARENT_ID = '"+pjTypeID+"'")
 .append(" and TYPE_NAME = '"+pjInfo.getTypeSort()+"'")
 .toString();
 System.out.println("[InsertParts.insertPJType]查询pjTypeID下，有没有这条记录的名'配件分类'的分类: "+sql);
 String typeId = "";
 ResultSet rs;
 try {
 rs = stmt.executeQuery(sql.toString());
 if (rs.next()) {
 typeId=rs.getString("ID");
 } else {
 //获得“配件”这个分类的TYPE_CODE
 String typeCode = getTypeCode(pjTypeID);
 //获得分类下最大的编码
 String maxTypeCode = getMaxTypeCode(pjTypeID);
 //插入名为“配件分类”的分类记录
 String insertSql = new StringBuffer("insert into CMDB_CI_TYPE")
 .append(" (id,TYPE_NAME,TYPE_CODE,SORT_ORDER,PARENT_ID) values (")
 .append(" '"+UUIDGenerator.generate()+"',")
 .append(" '"+ pjInfo.getTypeSort() +"',")
 .append(" '"+ KeyUtil.buildResourceTypeCode(typeCode,maxTypeCode) +"', ")
 .append(" 0 ,")
 .append(" '"+ pjTypeID +"' )")
 .toString();
 System.out.println("[InsertParts.insertPJType] 插入名为“配件分类”的分类记录语句 : " + insertSql);
 stmt.executeUpdate(insertSql);
 //插入后，查询分类ID
 String selectSql = new StringBuffer(" select ID from CMDB_CI_TYPE where TYPE_NAME= ")
 .append(" '"+ pjInfo.getTypeSort() +"' and ")
 .append(" PARENT_ID = '"+ pjTypeID +"' ")
 .toString();
 System.out.println("[InsertParts.insertPJType]插入后，查询分类ID :" + selectSql);
 ResultSet rs1 = stmt.executeQuery(selectSql.toString());
 if(rs1.next()){
 typeId=rs1.getString(1);
 }
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return typeId;
 }

 /**
 * 获得"配件"的编码
 * @param pjTypeID "配件"的ID
 * @return "配件"的编码
 */
 private static String getTypeCode(String pjTypeID) {
 String typeCode = "";
 try {
 String pjtcSql = new StringBuffer("select TYPE_CODE from ")
 .append(" CMDB_CI_TYPE where ")
 .append(" id = '"+ pjTypeID +"'")
 .toString();
 System.out.println("[InsertParts.getTypeCode] 获得'配件'分类的TYPE_CODE语句: " + pjtcSql);
 ResultSet rs = stmt.executeQuery(pjtcSql.toString());
 if(rs.next()){
 typeCode = rs.getString("TYPE_CODE");
 System.out.println("[InsertParts.getTypeCode] 配件的ID是 "+ typeCode);
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return typeCode;
 }

 /**
 * 获得"配件"下的最大编码值
 * @param pjTypeID "配件"的ID
 * @return 最大编码
 */
 private static String getMaxTypeCode(String pjTypeID){
 String maxTypeCode = "";
 try {
 String maxSql="select max(TYPE_CODE) as T_CODE from CMDB_CI_TYPE where PARENT_ID='"+pjTypeID+"'";
 System.out.println("[InsertParts.getMaxNumber] 获得分类下最大编码语句 : " +maxSql);
 ResultSet rs = stmt.executeQuery(maxSql.toString());
 if(rs.next()){
 maxTypeCode=rs.getString(1);
 System.out.println("[InsertParts.getMaxNumber]最大编码的值为: " + maxTypeCode);
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return maxTypeCode;
 }

 /**
 * 将excel表中内容插入数据库
 * @param pJInfo 实体类
 * @param ptypeId 分类ID
 */
 private static void insertPJInfo(PJInfo pJInfo , String ptypeId , String instanceID) {
 try {
 //获得管理部门的ID
 String mgrDeptId = getMgrDeptId(instanceID);
 //获取所在位置
 String ciLocation = getCiLocation(instanceID);
 String sql = new StringBuffer(" insert into CMDB_CI_INSTANCE (")
 .append(" id,TYPE_ID,INSTANCE_NAME,BRAND_DESC,CI_MODEL,BEGIN_USE_TIME,MGR_DEPT_ID,CI_LOCATION,INSTANCE_CODE ) values ( ")
 .append(" '"+ UUIDGenerator.generate() +"',")
 .append(" '"+ ptypeId +"',")
 .append(" '"+ pJInfo.getTypeName() +"',")
 .append(" '"+ pJInfo.getBrandDesc() +"',")
 .append(" '"+ pJInfo.getCiModel() +"',")
 .append(" '"+ pJInfo.getBeginUseTime() +"',")
 .append(" '"+ mgrDeptId +"',")
 .append(" '"+ ciLocation +"',")
 .append(" '"+ KeyUtil.buildResourceInstanceCode(mgrDeptId) +"')")
 .toString();
 System.out.println("[InsertParts.insertPJType]将excel表中数据插入数据库语句:" + sql);
 stmt.executeUpdate(sql);
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }

 /**
 * 获得所在位置
 * @param instanceID 主机ID
 * @return 所在位置
 */
 private static String getCiLocation(String instanceID) {
 String ciLocation = "";
 try {
 String sql = new StringBuffer("select CI_LOCATION from CMDB_CI_INSTANCE ")
 .append(" where ID = ")
 .append(" '"+ instanceID +"' ")
 .toString();
 System.out.println("[InsertParts.getCiLocation]获得所在位置的sql语句: " + sql);
 ResultSet rs = stmt.executeQuery(sql.toString());
 if(rs.next()){
 ciLocation=rs.getString(1);
 System.out.println("[InsertParts.getMgrDeptId]所在位置: " + ciLocation);
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return ciLocation;
 }

 /**
 * 获得管理单位部门的ID
 * @param instanceID 主机ID
 * @return 管理部门的ID
 */
 private static String getMgrDeptId(String instanceID) {
 String mgrDeptId = "";
 try {
 String sql = new StringBuffer("select MGR_DEPT_ID from CMDB_CI_INSTANCE ")
 .append(" where ID = ")
 .append(" '"+ instanceID +"' ")
 .toString();
 System.out.println("[InsertParts.getMgrDeptId]获得管理单位部门 ID 的sql语句: " + sql);
 ResultSet rs = stmt.executeQuery(sql.toString());
 if(rs.next()){
 mgrDeptId=rs.getString(1);
 System.out.println("[InsertParts.getMgrDeptId]管理单位部门 ID: " + mgrDeptId);
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 return mgrDeptId;
 }

 /**
 * 插入关联信息表
 * @param instanceID 实例ID
 * @param ptypeId 配件ID
 */
 private static void insertRelation(String instanceID, String ptypeId) {
 try {
 String sql = new StringBuffer("insert into CMDB_INSTANCE_RELATION (")
 .append(" ID,INSTANCE_ID,RELATION_INSTANCE_ID) values ( ")
 .append(" '"+ UUIDGenerator.generate() +"',")
 .append(" '"+ instanceID +"',")
 .append(" '"+ ptypeId +"')")
 .toString();
 System.out.println("[InsertParts.insertPJType] 插入关联表语句:" + sql);
 stmt.executeUpdate(sql);
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }

}