
Задача:
Имеем excel таблицу с данными (автогенерация). Распарсить данные из Excel и сохранить в MySQL. Предварительно создав таблицы, использовать PRIMARY KEY, AUTO_INCREMENT.
Все файлы будут доступны в конце проекта во вложении. В excel удобная функция для автогенерации: =СЛУЧМЕЖДУ(“01.01.2008″;”31.12.2018”)
Использовал:
Spring context | Spring Jdbc | NamedParameterJdbc |
Apache POI | MySQL | MAMP |
Основной момент в этой задаче, использование Apache POI API для парсинга из Excel в Java.
Импортируем необходимые библиотеки для работы с Excel.
import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; |
Вычитываем файл и указываем страницу (getSheetAt) с которой будем работать.
HSSFWorkbook work = new HSSFWorkbook(new FileInputStream(EXCEL_FILE)); HSSFSheet hssfSheet = work.getSheetAt(0); |
Проходим в цикле (for (int rn=1; rn<=200; rn++)). Начиная с второй строки rn=1, так как первая строка = 0 и она содержит названия столбцов.
Далее проходим по ячейкам.
HSSFRow row2 = hssfSheet.getRow(rn); // processing row HSSFCell first_name = row2.getCell(0); HSSFCell second_name = row2.getCell(1); HSSFCell phone = row2.getCell(2); HSSFCell phone_code = row2.getCell(3); |
Есть интересный момент. Работа с форматом даты. Выбрал решение DateFormat с использованием pattern.
DateFormat format = new SimpleDateFormat("dd-MMM-yyyy"); Date formatBirthday = format.parse(birthday.toString()); //12.08.1968 DateFormat format2 = new SimpleDateFormat("yyyy"); Date formatYear = format2.parse(year.toString()); |
Затем, используем Spring.
ClassPathXmlApplicationContext contextdb = new ClassPathXmlApplicationContext("db.xml"); NamedParameterJdbcTemplate nquOracle = new NamedParameterJdbcTemplate(contextdb.getBean(DataSource.class)); |
Собираем в HashMap коллекцию. Для удобства работы в дальнейшем с NamedParameterJdbcTemplate и обновлением БД.
Немного учебника:
Класс HashMap использует хеш-таблицу для хранения карточки, обеспечивая быстрое время выполнения запросов get() и put() при больших наборах. Класс реализует интерфейс Map (хранение данных в виде пар ключ/значение). Ключи и значения могут быть любых типов, в том числе и null. При этом все ключи обязательно должны быть уникальны, а значения могут повторяться. Данная реализация не гарантирует порядка элементов.
HashMap<String, Object> param = new HashMap<>(); param.put("first_name", first_name.toString()); param.put("second_name", second_name.toString()); param.put("phone", phone.toString()); param.put("phone_code", phone_code.toString()); |
Заполняем таблицу.
String insertSql = "INSERT INTO PERSON_TEMP (id, First_name, Second_name, Phone, Phone_code, Passport_number, Born_country, " + "Birthday, Email, Company, Job, Salary, State, Car_mark, Car_model, Vin, Power, Year, Car_price, Hire_date) VALUES " + "(id, :first_name, :second_name, :phone, :phone_code, :passport_number, :born_country, :formatBirthday, :email, :company, :job, :salary, :state, :car_mark, :car_model, :vin, :power, :formatYear, :car_price, :hire_date)"; // execute insert + count number of records / records processed by the executed query nquOracle.update(insertSql, param); update ++; |
Поработаем с созданием тестовых таблиц. Пригодятся в дальнейшем для создания и тестирования web service. Несколько скриптов.
-- Remove old tables DROP TABLE IF EXISTS PERSON_TEMP; DROP TABLE IF EXISTS Documents; DROP TABLE IF EXISTS Vehicle; DROP TABLE IF EXISTS Employer; DROP TABLE IF EXISTS Contacts; DROP TABLE IF EXISTS Person; -- Create TABLE CREATE TABLE PERSON_TEMP ( id INT(11) NOT NULL AUTO_INCREMENT, First_name VARCHAR(150), Second_name VARCHAR(150), Phone VARCHAR(150), Phone_code INT(150), Passport_number INT(150), Born_country VARCHAR(150), Birthday DATE, Email VARCHAR(150), Company VARCHAR(150), Job VARCHAR(150), Salary INT(150), State VARCHAR(150), Car_mark VARCHAR(150), Car_model VARCHAR(150), Vin VARCHAR(150), POWER INT(150), YEAR DATE, Car_price INT(150), Hire_date DATE, PRIMARY KEY (id) ); -- Create other table CREATE TABLE Person ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, First_name VARCHAR(150), Second_name VARCHAR(150), Birthday DATE ); CREATE TABLE Contacts ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, Phone VARCHAR(150), Phone_code INT(150), Email VARCHAR(150), FOREIGN KEY (id) REFERENCES Person(id) ); CREATE TABLE Employer ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, Company VARCHAR(150), Job VARCHAR(150), Hire_date DATE, Salary INT(150), FOREIGN KEY (id) REFERENCES Person(id) ); CREATE TABLE Vehicle ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, Car_mark VARCHAR(150), Car_model VARCHAR(150), Vin VARCHAR(150), POWER INT(150), YEAR DATE, Car_price INT(150), FOREIGN KEY (id) REFERENCES Person(id) ); CREATE TABLE Documents ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, Passport_number INT(150), Born_country VARCHAR(150), State VARCHAR(150), FOREIGN KEY (id) REFERENCES Person(id) ); |
После заполнения таблицы PERSON_TEMP, раскидываем данные по нашим тестовым таблицам.
INSERT INTO Person (id, First_name, Second_name, Birthday) SELECT id, First_name, Second_name, Birthday FROM PERSON_TEMP; INSERT INTO Contacts (id, Phone, Phone_code, Email) SELECT id, Phone, Phone_code, Email FROM PERSON_TEMP; INSERT INTO Employer (id, Company, Job, Hire_date, Salary) SELECT id, Company, Job, Hire_date, Salary FROM PERSON_TEMP; INSERT INTO Vehicle (id, Car_mark, Car_model, Vin, POWER, YEAR, Car_price) SELECT id, Car_mark, Car_model, Vin, POWER, YEAR, Car_price FROM PERSON_TEMP; INSERT INTO Documents (id, Passport_number, Born_country, State) SELECT id, Passport_number, Born_country, State FROM PERSON_TEMP; |
В итоге имеем 5 хороших тестовых таблиц для написания тестов и использования в будущих проектов.
SELECT * FROM Person; SELECT * FROM Contacts; SELECT * FROM Employer; SELECT * FROM Vehicle; SELECT * FROM Documents; |
Attached files:
Test data – Excel file
Github ExcelToDbProject