• Java导入Excel文件


    package com.cme.core;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.Map.Entry;
    import java.util.UUID;
    
    import org.apache.commons.lang.StringUtils;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import com.cme.core.entity.CountryMapping;
    import com.cme.core.entity.Currency;
    import com.cme.core.entity.EnterpriseAddressInfo;
    import com.cme.core.entity.IndustryInfo;
    import com.cme.utils.JdbcUtils;
    
    /**
     * Hello world!
     *
     */
    public class App {
        // 产业行业分类
        private static Map<String, List<IndustryInfo>> infos = new HashMap<String, List<IndustryInfo>>();
        // 企业类型
        private static Map<String, String> enterpriseTypes = new HashMap<String, String>();
        // 城市
        private static List<CountryMapping> cms = new ArrayList<CountryMapping>();
        // 币种
        private static Map<String, String> currencys = new HashMap<String, String>();
    
        static {
            infos = getIndustryInfos();
            enterpriseTypes = getEnterpriseTypes();
            cms = getCountryMappings();
            currencys = getCurrencys();
        }
    
        public static void main(String[] args) {
            long beginTime = System.currentTimeMillis();
            exec("F:/企业/企业:常山亮/21-02.xlsx");// 文件地址*********************************************************************************************
            long endTime = System.currentTimeMillis();
            System.out.println(endTime - beginTime);
        }
    
        /**
         * 递归读取excel文件
         * 
         * @param pathName
         */
        public static void exec(String pathName) {
            File file = new File(pathName);
    
            if (file.isDirectory()) {
                File[] childs = file.listFiles();
                for (File child : childs) {
                    exec(child.getPath());
                }
            } else if (file.getName().endsWith(".xls")
                    || file.getName().endsWith(".xlsx")) {
                // 读取并导入excel文件
                readExcel(file);
            } else {
                System.out.println("不需要导入的文件:" + file.getName());
            }
        }
    
        /**
         * 读取并导入excel文件
         * 
         * @param file
         */
        public static void readExcel(File file) {
            try {
                Workbook workBook = null;
                if (file.getName().endsWith(".xls")) {
                    workBook = new HSSFWorkbook(new FileInputStream(file));
                } else if (file.getName().endsWith(".xlsx")) {
                    workBook = new XSSFWorkbook(new FileInputStream(file));
                }
                int numberOfSheets = workBook.getNumberOfSheets();
                // 一次读取3000条数据
                int pageSize = 3000;
                // 依次导入每个sheet里面的数据
                for (int i = 0; i < 1; i++) {
                    Sheet sheet = workBook.getSheetAt(i);
                    // 算出总记录数
                    int totalCount = sheet.getLastRowNum();
                    // 算出总页数
                    int totalPage = getTotalPage(totalCount, pageSize);    
                    Row header = sheet.getRow(1);
                    if (header != null) {
                        int celNum = 58;// Excel列数****************************************************************************************************************************
                        //int celNum = header.getPhysicalNumberOfCells();
                        List<List<Object>> datas = null;
                        List<Object> data = null;
                        for (int j = 1; j <= totalPage; j++) {
                            datas = new ArrayList<List<Object>>();
                            int firstResult = j == 1 ? 1 : getFirstResult(j,
                                    pageSize) + 1;
                            int lastResult = pageSize * j > totalCount ? totalCount
                                    : pageSize * j;
                            for (int k = firstResult; k <= lastResult; k++) {
                                Row row = sheet.getRow(k);
                                if (row != null) {
                                    data = new ArrayList<Object>();
                                    for (int t = 0; t < celNum; t++) {
                                        Cell cell = row.getCell(t);
                                        if (cell == null) {
                                            data.add(null);
                                        } else {
                                            cell.setCellType(CellType.STRING);
                                            String value = cell
                                                    .getStringCellValue();
                                            if (StringUtils.isNotBlank(value))
                                                value = value.trim();
                                            data.add(cell.getStringCellValue());
                                        }
                                    }
                                    data.add(file.getPath());
                                    datas.add(data);
                                }
                            }
                            // 导入数据
                            System.out.println("filePath=" + file.getPath());
                            importData(datas);
                        }
                    }
                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        /**
         * 导入数据
         * 
         * @param datas
         */
    
        public static void importData(List<List<Object>> datas) {
            String column = "id, enterprise_name, enterprise_code, enterprise_import_code, enterprise_nationality_code, industry_code, divide, enterprise_type, user_level,"
                    + " enterprise_introduce_text, enterprise_introduce_image, enterprise_registration,address_registration, registered_capital,currency, technical_capacity,"
                    + " enterprise_logo, business_photo, achievements_and_honors, enterprise_licence, qualification_level, ebbody, address, business_scope, agree_ally,"
                    + " create_time, create_user, last_update_time, last_update_user, abroad, imported, status, deleted, credit_rating, service_area, service_domain,"
                    + " qualification_level_img, labels, isshow, isrecommend, sort_no";
            StringBuffer sb = new StringBuffer("insert into pf_enterprise_info(").append(column).append(") values (?");
            String[] columns = column.split(",");
            for (int i = 2; i <= columns.length - 8; i++) {
                if (i == 11) {sb.append(",null");continue;}//enterprise_introduce_image
                if (i == 20) {sb.append(",null");continue;}//enterprise_licence
                if (i == 22) {sb.append(",null");continue;}//ebbody
                if (i == 23) {sb.append(",null");continue;}//address
                if (i == 25) {sb.append(",1");continue;}//agree_ally
                if (i == 26) {sb.append(",null");continue;}//create_time
                if (i == 27) {sb.append(",null");continue;}//create_user
                if (i == 28) {sb.append(",null");continue;}//last_update_time
                if (i == 29) {sb.append(",null");continue;}//last_update_user
                if (i == 30) {sb.append(",0");continue;}//abroad
                if (i == 31) {sb.append(",1");continue;}//imported
                if (i == 32) {sb.append(",0");continue;}//status
                if (i == 33) {sb.append(",0");continue;}//deleted
                sb.append(",?");
            }
            sb.append(",null");//credit_rating
            sb.append(",null");//service_area
            sb.append(",null");//service_domain
            sb.append(",null");//qualification_level_img
            sb.append(",1,1,1,0);");        
    
            String column2 = "id, enterprise_id, enterprise_code, enterprise_office, address_office, contact_person, phone_num, contact_num, fax,"
                    + " email, facebook, linkedin, twitter, microBlog, website, wechat, qq, zip_code, ismaster, isdel";
            StringBuffer sb2 = new StringBuffer("insert into pf_enterprise_contact_info(").append(column2).append(") values (?");;
            String[] columns2 = column2.split(",");
            for (int i = 2; i <= columns2.length - 2; i++) {
                sb2.append(",?");
            }
            sb2.append(",0,0);");//ismaster isdel
            
            String sql = sb.toString();
            String sql2 = sb2.toString();
            System.out.println(sql);
            System.out.println(sql2);
    
            Connection conn = null;
            PreparedStatement ps = null;
            PreparedStatement ps2 = null;
            
            try {            
                // 2.建立连接
                conn = JdbcUtils.getConnection();
                conn.setAutoCommit(false);
                for (int i = 0; i < datas.size(); i++) {
                    // 3.创建语句          
                    
                    ps = conn.prepareStatement(sql);
                    List<Object> data = datas.get(i);        
                    String id = UUID.randomUUID().toString().replace("-", "");
                    ps.setObject(1, id);
                    Object obj = null;
                    for (int j = 0; j < data.size(); j++) {
                        obj= data.get(j);
                        if (j == 4) {//enterprise_name                        
                            ps.setObject(2, obj);
                            continue;
                        } 
                        if (j == 0) {//enterprise_code
                            ps.setObject(3, obj);
                            /*****************************
                            ps.setObject(23, obj);
                            ****************************/
                            continue;
                        } 
                        if (j == 5) {//enterprise_import_code
                            ps.setObject(4, obj);
                            continue;
                        } 
                        if (j == 12) {//enterprise_nationality_code
                            ps.setObject(5, obj);
                            continue;
                        } 
                        if (j == 3) {//industry_code
                            ps.setObject(6, obj);
                            continue;
                        } 
                        if (j == 9) {//divide
                            ps.setObject(7, obj);
                            continue;
                        } 
                        if (j == 8) {//enterprise_type
                            ps.setObject(8, obj);
                            continue;
                        } 
                        if (j == 23) {//user_level
                            ps.setObject(9, obj);
                            continue;
                        } 
                        if (j == 24) {//enterprise_introduce_text
                            ps.setObject(10, obj);
                            continue;
                        }                     
                        if (j == 19) {//enterprise_registration
                            ps.setObject(11, obj);
                            continue;
                        } 
                        if (j == 20) {//address_registration
                            ps.setObject(12, obj);
                            continue;
                        } 
                        if (j == 13) {//registered_capital
                            ps.setObject(13, obj);
                            continue;
                        }
                        if (j == 15) {//currency
                            ps.setObject(14, obj);
                            continue;
                        }
                        if (j == 21) {//technical_capacity
                            ps.setObject(15, obj);
                            continue;
                        } 
                        if (j == 6) {//enterprise_logo
                            ps.setObject(16, obj);
                            continue;
                        } 
                        if (j == 25) {//business_photo
                            ps.setObject(17, obj);
                            continue;
                        } 
                        if (j == 27) {//achievements_and_honors
                            ps.setObject(18, obj);
                            continue;
                        } 
                        if (j == 26) {//qualification_level
                            ps.setObject(19, obj);
                            continue;
                        } 
                        if (j == 10) {//business_scope
                            ps.setObject(20, obj);
                            continue;
                        } 
    
                        
                        /********************************************pf_enterprise_contact_info********************************************/
                        /********************************************************************************
                        if (j == 39) {//enterprise_office
                            ps.setObject(24, obj);
                            continue;
                        } 
                        if (j == 40) {//address_office
                            ps.setObject(25, obj);
                            continue;
                        }  
                        if (j == 29) {//contact_person
                            ps.setObject(26, obj);
                            continue;
                        } 
                        if (j == 31) {//phone_num
                            ps.setObject(27, obj);
                            continue;
                        } 
                        if (j == 30) {//contact_num
                            ps.setObject(28, obj);
                            continue;
                        } 
                        if (j == 33) {//fax
                            ps.setObject(29, obj);
                            continue;
                        }  
                        if (j == 32) {//email
                            ps.setObject(30, obj);
                            continue;
                        } 
                        if (j == 41) {//facebook
                            ps.setObject(31, obj);
                            continue;
                        } 
                        if (j == 42) {//linkedin
                            ps.setObject(32, obj);
                            continue;
                        } 
                        if (j == 43) {//twitter
                            ps.setObject(33, obj);
                            continue;
                        } 
                        if (j == 45) {//microBlog
                            ps.setObject(34, obj);
                            continue;
                        } 
                        if (j == 34) {//website
                            ps.setObject(35, obj);
                            continue;
                        } 
                        if (j == 46) {//wechat
                            ps.setObject(36, obj);
                            continue;
                        } 
                        if (j == 47) {//qq
                            ps.setObject(37, obj);
                            continue;
                        }  
                        if (j == 35) {//zip_code
                            ps.setObject(38, obj);
                            continue;
                        } 
                        ********************************************************************************/
                    }                
                    // 4.执行语句
                    ps.execute();                
                    ps.close();
                    
                    obj = null;
                    ps2 = conn.prepareStatement(sql2);
                    ps2.setObject(1, UUID.randomUUID().toString().replace("-", ""));
                    ps2.setObject(2, id);                
                    for (int j = 0; j < data.size(); j++) {
                        obj= data.get(j);                        
                        if (j == 0) {//enterprise_code
                            ps2.setObject(3, obj);
                            continue;
                        }                     
                        if (j == 38) {//enterprise_office
                            ps2.setObject(4, obj);
                            continue;
                        } 
                        if (j == 39) {//address_office
                            ps2.setObject(5, obj);
                            continue;
                        }  
                        if (j == 28) {//contact_person
                            ps2.setObject(6, obj);
                            continue;
                        } 
                        if (j == 30) {//phone_num
                            ps2.setObject(7, obj);
                            continue;
                        } 
                        if (j == 29) {//contact_num
                            ps2.setObject(8, obj);
                            continue;
                        } 
                        if (j == 32) {//fax
                            ps2.setObject(9, obj);
                            continue;
                        }  
                        if (j == 31) {//email
                            ps2.setObject(10, obj);
                            continue;
                        } 
                        if (j == 40) {//facebook
                            ps2.setObject(11, obj);
                            continue;
                        } 
                        if (j == 41) {//linkedin
                            ps2.setObject(12, obj);
                            continue;
                        } 
                        if (j == 42) {//twitter
                            ps2.setObject(13, obj);
                            continue;
                        } 
                        if (j == 44) {//microBlog
                            ps2.setObject(14, obj);
                            continue;
                        } 
                        if (j == 33) {//website
                            ps2.setObject(15, obj);
                            continue;
                        } 
                        if (j == 45) {//wechat
                            ps2.setObject(16, obj);
                            continue;
                        } 
                        if (j == 46) {//qq
                            ps2.setObject(17, obj);
                            continue;
                        }  
                        if (j == 34) {//zip_code
                            ps2.setObject(18, obj);
                            continue;
                        } 
                    }
                    ps2.execute();                
                    ps2.close();
                     
                }
                conn.commit();
            } catch (Exception e) {
                JdbcUtils.rollback(conn);
                e.printStackTrace();
            } finally {
                JdbcUtils.free(null, ps, conn);
                JdbcUtils.free(null, ps2, conn);
            }
        }
    
        /**
         * 根据经营范围获取企业行业产业分类
         * 
         * @param businessScope
         * @return
         */
        public static IndustryInfo getEnterpriseIndustry(String businessScope) {
            if (StringUtils.isBlank(businessScope)) {
                return null;
            }
            for (String key : infos.keySet()) {
                if (businessScope.contains(key)) {
                    return infos.get(key).get(0);
                }
            }
            return null;
        }
    
        /**
         * 根据企业类型获取企业类型编码
         * 
         * @param enterpriseTypeName
         * @return
         */
        public static String getEnterpriseTypeCode(String enterpriseTypeName) {
            if (StringUtils.isBlank(enterpriseTypeName)) {
                return null;
            }
            for (Entry<String, String> entry : enterpriseTypes.entrySet()) {
                String typeName = entry.getValue();
                if (enterpriseTypeName.startsWith(typeName)) {
                    return entry.getKey();
                }
                for (int i = typeName.length() - 1; i > 0; i--) {
                    if (enterpriseTypeName.startsWith(typeName.substring(0, i))) {
                        return entry.getKey();
                    }
                }
                if (enterpriseTypeName.contains(typeName)) {
                    return entry.getKey();
                }
            }
            return null;
        }
    
        public static Currency getCurrency(String registeredCapital) {
            if (StringUtils.isBlank(registeredCapital)) {
                return null;
            }
            boolean flag = false;
            Currency c = new Currency();
            for (Entry<String, String> entry : currencys.entrySet()) {
                String name = entry.getValue();
                if (registeredCapital.contains(name)) {
                    registeredCapital = registeredCapital.replace(name, "");
                    flag = true;
                }
                if (!flag) {
                    for (int i = name.length() - 1; i > 0; i--) {
                        String tmp = name.substring(0, i);
                        if (registeredCapital.contains(tmp)) {
                            registeredCapital = registeredCapital.replace(tmp, "");
                            flag = true;
                            break;
                        }
                    }
                }
                if (flag) {
                    c.setCode(entry.getKey());
                    c.setName(name);
                    c.setRegisteredCapital(registeredCapital.trim());
                    break;
                }
            }
            return c;
        }
    
        /**
         * 根据地址获取省-市-县-街道地址
         * 
         * @param address
         * @param provNameT
         * @param cityNameT
         * @param countryNameT
         * @return
         */
        public static EnterpriseAddressInfo getEnterpriseCountry(String address,
                String provNameT, String cityNameT, String countryNameT) {
            String provName = null;
            String provId = null;
            String countryName = null;
            String countryId = null;
            String cityName = null;
            String cityId = null;
    
            boolean hasProv = false;
            boolean hasCity = false;
            boolean hasCountry = false;
    
            if (StringUtils.isBlank(address)) {
                return null;
            }
    
            for (CountryMapping cm : cms) {
    
                if (StringUtils.isNotBlank(provNameT)
                        && (cm.getProvName().contains(provNameT) || provNameT
                                .contains(cm.getProvName()))) {
                    hasProv = true;
                }
    
                address = address.trim();
                // 获取省
                if (!hasProv
                        && address.startsWith(getShortProvName(cm.getProvName()))) {
                    hasProv = true;
                }
    
                if (hasProv) {
                    provId = cm.getProvId();
                    provName = cm.getProvName();
                    address = address.replaceAll(getShortProvName(cm.getProvName())
                            .concat("省"), "");
                    address = address.replaceAll(getShortProvName(cm.getProvName())
                            .concat("市"), "");
                    address = address.replaceAll(cm.getProvName(), "");
                    break;
                }
            }
    
            for (CountryMapping cm : cms) {
                if (StringUtils.isNotBlank(cityNameT)
                        && (cm.getCityName().contains(cityNameT) || cityNameT
                                .contains(cm.getCityName()))) {
                    hasCity = true;
                }
    
                address = address.trim();
                if (!hasCity
                        && address.startsWith(getShortCityName(cm.getCityName()))) {
                    hasCity = true;
                }
    
                if (hasCity) {
                    address = address.replaceAll(getShortCityName(cm.getCityName())
                            .concat("市"), "");
                    address = address.replaceAll(getShortCityName(cm.getCityName())
                            .concat("区"), "");
                    address = address.replaceAll(cm.getCityName(), "");
                    cityId = cm.getCityId();
                    cityName = cm.getCityName();
                    if (!hasProv) {
                        provId = cm.getProvId();
                        provName = cm.getProvName();
                        hasProv = true;
                    }
                    break;
                }
            }
    
            for (CountryMapping cm : cms) {
                if (StringUtils.isNotBlank(countryNameT)
                        && StringUtils.isNotBlank(cm.getCountryName())
                        && (cm.getCountryName().contains(countryNameT) || countryNameT
                                .contains(cm.getCountryName()))) {
                    hasCountry = true;
                }
    
                // 获取县
                address = address.trim();
    
                if (!hasCountry
                        && StringUtils.isNotBlank(cm.getCountryName())
                        && address.startsWith(getShortCountryName(cm
                                .getCountryName()))) {
                    hasCountry = true;
                }
    
                if (hasCountry) {
                    address = address.replaceAll(
                            getShortCountryName(cm.getCountryName()).concat("县"),
                            "");
                    address = address.replaceAll(
                            getShortCountryName(cm.getCountryName()).concat("区"),
                            "");
                    address = address.replaceAll(cm.getCountryName(), "");
                    countryId = cm.getCountryId();
                    countryName = cm.getCountryName();
                    if (!hasProv) {
                        provId = cm.getProvId();
                        provName = cm.getProvName();
                        hasProv = true;
                    }
                    if (!hasCity) {
                        cityId = cm.getCityId();
                        cityName = cm.getCityName();
                        hasCity = true;
                    }
                    break;
                }
            }
    
            EnterpriseAddressInfo eai = new EnterpriseAddressInfo();
            eai.setProvId(provId);
            eai.setProvName(provName);
            eai.setCityId(cityId);
            eai.setCityName(cityName);
            eai.setCountryId(countryId);
            eai.setCountryName(countryName);
            eai.setAddress(address.trim());
            return eai;
        }
    
        public static String getShortProvName(String provName) {
            if (StringUtils.isBlank(provName)) {
                return null;
            }
            if (provName.length() == 2) {
                return provName;
            }
            return provName.replace("省", "").replace("市", "");
        }
    
        public static String getShortCityName(String cityName) {
            if (StringUtils.isBlank(cityName)) {
                return null;
            }
            if (cityName.length() == 2) {
                return cityName;
            }
            return cityName.replace("市", "").replace("区", "");
        }
    
        public static String getShortCountryName(String countryName) {
            if (StringUtils.isBlank(countryName)) {
                return null;
            }
            if (countryName.length() == 2) {
                return countryName;
            }
            return countryName.replace("县", "").replace("区", "");
        }
    
        /**
         * 获取所有企业类型
         * 
         * @return
         */
        public static Map<String, String> getEnterpriseTypes() {
            Map<String, String> result = new HashMap<String, String>();
            String sql = "select *from cme_treedictionary_info where pid = 'FE01'";
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                // 2.建立连接
                conn = JdbcUtils.getConnection();
                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String code = rs.getString("dkey");
                    String name = rs.getString("dvalue");
                    result.put(code, name);
                }
            } catch (Exception e) {
                JdbcUtils.rollback(conn);
                e.printStackTrace();
            } finally {
                JdbcUtils.free(rs, ps, conn);
            }
            return result;
        }
    
        public static Map<String, String> getCurrencys() {
            Map<String, String> result = new HashMap<String, String>();
            String sql = "select *from cme_treedictionary_info where pid = 'CT01'";
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                // 2.建立连接
                conn = JdbcUtils.getConnection();
                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String code = rs.getString("dkey");
                    String name = rs.getString("dvalue");
                    result.put(code, name);
                }
            } catch (Exception e) {
                JdbcUtils.rollback(conn);
                e.printStackTrace();
            } finally {
                JdbcUtils.free(rs, ps, conn);
            }
            return result;
        }
    
        /**
         * 获取地区(省-市-县)
         * 
         * @return
         */
        public static List<CountryMapping> getCountryMappings() {
            List<CountryMapping> result = new ArrayList<CountryMapping>();
            String sql = "select *from base_area";
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                // 2.建立连接
                conn = JdbcUtils.getConnection();
                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String provId = rs.getString("provId");
                    String provName = rs.getString("provName");
                    String cityId = rs.getString("cityId");
                    String cityName = rs.getString("cityName");
                    String countryId = rs.getString("countryId");
                    String countryName = rs.getString("countryName");
                    CountryMapping cm = new CountryMapping();
                    cm.setProvId(provId);
                    cm.setProvName(provName);
                    cm.setCityId(cityId);
                    cm.setCityName(cityName);
                    cm.setCountryId(countryId);
                    cm.setCountryName(countryName);
                    result.add(cm);
                }
            } catch (Exception e) {
                JdbcUtils.rollback(conn);
                e.printStackTrace();
            } finally {
                JdbcUtils.free(rs, ps, conn);
            }
            return result;
        }
    
        /**
         * 获取行业分类
         * 
         * @return
         */
        public static Map<String, List<IndustryInfo>> getIndustryInfos() {
            Map<String, List<IndustryInfo>> result = new HashMap<String, List<IndustryInfo>>();
            String sql = "select * from sys_industry_info";
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                // 2.建立连接
                conn = JdbcUtils.getConnection();
                ps = conn.prepareStatement(sql);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String code = rs.getString("code");
                    String name = rs.getString("name");
                    String desc = rs.getString("description");
                    IndustryInfo info = new IndustryInfo();
                    info.setCode(code);
                    info.setName(name);
                    if (StringUtils.isNotBlank(desc)) {
                        String[] keys = desc.split(",");
                        for (String key : keys) {
                            if (result.get(key) != null) {
                                result.get(key).add(info);
                            } else {
                                List<IndustryInfo> infos = new ArrayList<IndustryInfo>();
                                infos.add(info);
                                result.put(key, infos);
                            }
                        }
                    }
                }
            } catch (Exception e) {
                JdbcUtils.rollback(conn);
                e.printStackTrace();
            } finally {
                JdbcUtils.free(rs, ps, conn);
            }
            return result;
        }
    
        public static int getTotalPage(int totalCount, int pageSize) {
            return (totalCount + pageSize - 1) / pageSize;
        }
    
        public static int getFirstResult(int pageNo, int pageSize) {
            return (pageNo - 1) * pageSize;
        }
    
    }
    package com.cme.utils;
    
    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;
    
    public final class JdbcUtils {
        private static String url = "jdbc:mysql://10.0.6.177:3306/cme?useUnicode=true&characterEncoding=utf-8";
        private static String user = "root";
        private static String password = "root";
    
        private JdbcUtils() {
        }
    
        static {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                Properties pro = new Properties();
                InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
                pro.load(is);
                url = pro.getProperty("url",url);
                user = pro.getProperty("username",user);
                password = pro.getProperty("password",password);
            } catch (Exception e) {
                throw new ExceptionInInitializerError(e);
            }
        }
    
        /**
         * 获取数据库链接对象(从连接池中获取)
         * @return
         * @throws SQLException
         */
        public static Connection getConnection() {
             try {
                return DriverManager.getConnection(url, user, password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
        
        public static void rollback(Connection conn){
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        /**
         * 释放链接对象(这里释放Connection是调用数据源的free方法)
         * @param rs
         * @param st
         * @param conn
         */
        public static void free(ResultSet rs, Statement st, Connection conn) {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (st != null)
                        st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    if (conn != null)
                        try {
                             conn.close();
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                }
            }
        }
    }

    confing文件

    url=jdbc:mysql://10.0.6.177:3306/cme?useUnicode=true&characterEncoding=utf-8
    username=root
    password=root
  • 相关阅读:
    BZOJ4975: [Lydsy1708月赛]区间翻转( 博弈&逆序对)
    BZOJ4550: 小奇的博弈(NIMK博弈& 组合数& DP)
    BZOJ5301: [Cqoi2018]异或序列(莫队)
    BZOJ5450: 轰炸(水题,Tarjan缩点求最长路)
    BZOJ5125: [Lydsy1712月赛]小Q的书架(DP决策单调性)
    codevs 2495 水叮当的舞步
    bzoj 1086: [SCOI2005]王室联邦
    bzoj 3720: Gty的妹子树
    bzoj 1024: [SCOI2009]生日快乐
    bzoj 1085: [SCOI2005]骑士精神
  • 原文地址:https://www.cnblogs.com/yueyuepeng/p/6868002.html
Copyright © 2020-2023  润新知