package classes;

public class Consumer {
    int id;
    String name;
    String type;
    int abonentId;

    public Consumer(){}
    public Consumer(int id, String name, String type, int abonentId) {
        this.id = id;
        this.name = name;
        this.type = type;
        this.abonentId = abonentId;
    } }
    package classes;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;

public class Parser {

    public static ArrayList<Consumer> parse(String name) {
        Consumer consumer ;
        ArrayList <Consumer> listConsumer = new ArrayList<Consumer>();
        InputStream in = null;
        HSSFWorkbook wb = null;
        try {
            in = new FileInputStream(name);
            wb = new HSSFWorkbook(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row ;
        Iterator<Row> rows = sheet.rowIterator();
        while (rows.hasNext()) {
            row = (HSSFRow) rows.next();
            consumer = new Consumer( (int)row.getCell(0).getNumericCellValue(),
                    row.getCell(1).getStringCellValue(),
                    row.getCell(2).getStringCellValue(),
                    (int)row.getCell(3).getNumericCellValue());
            listConsumer.add(consumer);
        }
        return listConsumer;
    }
}
    package classes;

import java.sql.*;
import java.util.ArrayList;

/**
 * Created by ApotinV on 11.08.2017.
 */
public class Connector {
    public static final String URL = "jdbc:postgresql://localhost/Test?user=postgres&password=admin";
    public static final String USERNAME = "postgresql";
    public static final String PASSWORD = "admin";

    public static void PushDB(ArrayList<Consumer> list){
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(URL);
            if (!connection.isClosed())
                System.out.println("Связь установлена");
           PreparedStatement preparedStatement;
           for (int i = 0; i < list.size(); i++) {
                preparedStatement = connection.prepareStatement("INSERT INTO nro_consumer (id_, name_, type_, abonentid_) VALUE (?,?,?,?);");
                preparedStatement.setInt(1, list.get(i).id);
                preparedStatement.setString(2, list.get(i).name);
                preparedStatement.setString(3,list.get(i).type);
                preparedStatement.setInt(4, list.get(i).abonentId);
                preparedStatement.executeUpdate();
                preparedStatement.close();
            }
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
    package classes;

import java.util.ArrayList;

public class main {
    public static void main(String... args){
        ArrayList<Consumer> list =
                Parser.parse("testfile.xls");
        Connector.PushDB(list);
        //System.out.println(list.get(1).id);
    }}