Java 三级分类 excel逆向生成到数据库

上述就是表格的格式

需求呢就是

一级分类 001

二级分类 001001

三级分类 001001001

酱紫~

根据需求创建分类表

-- auto-generated definition
create table kor_sys_category
(
	ID int(12) auto_increment primary key,
	PARENT_CATE_ID varchar(20) null,
	CATE_ID varchar(20) null,
	CATE_NAME varchar(100) null,
	STATES char default '0' null,
        ORDER int null
)
;

废话不多说了。贴个代码吧。也是做一下记录

   ExcelReader reader = ExcelUtil.getReader("/Users/jiangjiacheng/Desktop/123.xlsx");

   List<List<Object>> readAll = reader.read();

excel读取使用的工具是 http://www.hutool.cn/ 里面的解析excel的 自己去看一下吧~

package com.yunxin.iambuyer.ucenter.generate;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;

import java.sql.*;
import java.util.List;

/**
 * Created by jiangjiacheng on 2018/4/19.
 *
 *根据EXCEL逆向生成到数据库
 *
 */
public class generateCategory {



        public  static  String url = "数据库url";
        public  static  String username = "用户名" ;
        public  static  String password = "密码" ;
        public  static Connection con = null;
        public  static Statement s = null;
        public  static ResultSet r= null;
        public  static PreparedStatement pstmt = null;

        static {
            try {
                con = DriverManager.getConnection(url , username , password ) ;
                s=con.createStatement();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        public static void main(String[] args) throws SQLException {
            ExcelReader reader = ExcelUtil.getReader("/Users/jiangjiacheng/Desktop/123.xlsx");
            List<List<Object>> readAll = reader.read();

            String oneCode = "00";
            String twoCode = "00";

            int oneCodeCount = 0;
            int twoCodeCount = 0;
            int threeCodeCount = 0;

            for (List<Object> objects : readAll) {
                int i =1;
                for (Object object : objects) {
                    String str = (String) object;
                    if(!isEmpty(str)){
                        if(i==1){//证明是一级
                            if(select(str)){
                                oneCodeCount++;
                                if(oneCodeCount >= 10){
                                    oneCode = "0"+oneCodeCount;
                                }else{
                                    oneCode = "00"+oneCodeCount;
                                }
                                twoCodeCount = 0 ;
                                threeCodeCount = 0 ;
                                insert( null,oneCode, str);
                            }
                        }else if(i ==2){
                            if(select(str)){
                                twoCodeCount++;
                                if(twoCodeCount >= 10){
                                    twoCode = oneCode+ "0"+twoCodeCount;
                                }else{
                                    twoCode = oneCode+ "00"+twoCodeCount;
                                }
                                threeCodeCount = 0 ;
                                insert( oneCode, twoCode , str);
                            }
                        }else if(i == 3){
                            if(select(str)){
                                String threeCode;
                                threeCodeCount++;
                                if(threeCodeCount >= 10){
                                    threeCode = twoCode+ "0"+threeCodeCount;
                                }else{
                                    threeCode = twoCode+ "00"+threeCodeCount;
                                }

                                insert( twoCode, threeCode , str);
                            }
                        }
                    }
                    i++;
                }
            }
        }

        public static void insert(String pcode,String code,  String name) throws SQLException {
            System.out.println(pcode + "-" +code + "-" + name);
            String sql;

            int indexStart  = name.indexOf("(");
            int indexEnd = name.indexOf(")");
            name = name.substring(indexStart+1,indexEnd);

            if(isEmpty(pcode)){
                sql = "INSERT INTO imb_prodb.kor_sys_category (PARENT_CATE_ID,CATE_ID,CATE_NAME) VALUES (null,?,?);";
                pstmt = con.prepareStatement(sql);
                pstmt.setString(1, code);
                pstmt.setString(2, name);
            }else{
                sql = "INSERT INTO imb_prodb.kor_sys_category (PARENT_CATE_ID,CATE_ID,CATE_NAME) VALUES (?,?,?);";
                pstmt = con.prepareStatement(sql);
                pstmt.setString(1, pcode);
                pstmt.setString(2, code);
                pstmt.setString(3, name);
            }
            pstmt.executeUpdate();
        }

        public static boolean select(String  name) throws SQLException {
            String sql;


            r=s.executeQuery("SELECT * FROM imb_prodb.kor_sys_category WHERE CATE_NAME = '"+ name +"' ;");

            int i = 0;

            while(r.next()){

                i++;

            }

            if(i>0){
                return false;
            }else{
                return  true;
            }
        }


        public static boolean isEmpty(String str){
            if(str == null || str.trim().equals("")){
                return  true;
            }else{
                return  false;
            }
        }



}