Parse XML file. How to save into database

Question

XML

<?xml version="1.0" standalone="yes"?>
<sdnList>
  <sdnEntry>
    <uid>1</uid>
    <lastName>AAAAAAAA</lastName>
    <sdnType>Entity</sdnType>
    <programList>
      <program>UID</program>
    </programList>
    <akaList>
      <aka>
        <uid>12</uid>
        <type>a.k.a.</type>      
        <lastName>ABCD</lastName>
      </aka>
    </akaList>
    <addressList>
      <address>
        <uid>25</uid>
        <city>City</city>
        <country>Country</country>
      </address>
    </addressList>
  </sdnEntry>
  <sdnEntry>
    <uid>2</uid>
    <lastName>BBBBBB</lastName>
    <sdnType>Entity</sdnType>
    <programList>
      <program>UID</program>
    </programList>
    <akaList>
      <aka>
        <uid>219</uid>
        <type>a.k.a.</type>
        <category>weak</category>
        <lastName>BCC</lastName>
      </aka>
      <aka>
        <uid>220</uid>
        <type>a.k.a.</type>
        <category>strong</category>
        <lastName>ABABAB</lastName>
      </aka>
    </akaList>
    <addressList>
      <address>
        <uid>199</uid>
        <address1>Address</address1>
        <city>City</city>
        <postalCode>CODE</postalCode>
        <country>Country</country>
      </address>
      <address>
        <uid>200</uid>
        <address1>Address</address1>
        <city>City</city>
        <postalCode>CODE</postalCode>
        <country>Country</country>
      </address>
      <address>
        <uid>201</uid>
        <address1>Address</address1>
        <city>City</city>
        <postalCode>CODE</postalCode>
        <country>Country</country>
      </address>
    </addressList>
  </sdnEntry>
  <sdnEntry>
    <uid>3</uid>
    <lastName>CCCCCCC</lastName>
    <sdnType>Entity</sdnType>
    <programList>
      <program>UID</program>
    </programList>
    <addressList>
      <address>
        <uid>247</uid>
        <address1>Address</address1>
        <city>City</city>
        <country>Country</country>
      </address>
    </addressList>
  </sdnEntry>
</sdnList>

Service

@Service
public class UploadURLServiceImpl implements UploadURLService {

    @Autowired
    private final FileDTORepository fileDTORepository;

    @Autowired
    public UploadURLServiceImpl(FileDTORepository fileDTORepository) {
        this.fileDTORepository = fileDTORepository;
    }

    @Override
    public boolean uploadData(String url) {
        try (BufferedInputStream in = new BufferedInputStream(new URL(url).openStream());
             FileOutputStream fileOutputStream = new FileOutputStream(new File("sdn.xml"))) {
            byte dataBuffer[] = new byte[1024];
            int bytesRead;
            while ((bytesRead = in.read(dataBuffer, 0, 1024)) != -1) {
                fileOutputStream.write(dataBuffer, 0, bytesRead);
            }
        } catch (IOException e) {
            return false;
        }
        return true;
    }

    @Override
    public void parseSdnFile(String fileName) throws ParserConfigurationException, IOException, SAXException{

        DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();

      // try {
           // dbf.setFeature(XMLConstants.FEATURE_SECURE_PROCESSING, true);

            DocumentBuilder db = dbf.newDocumentBuilder();

            Document doc = db.parse(new File(fileName));

            doc.getDocumentElement().normalize();

            //Element docEl = doc.getDocumentElement();

            NodeList list = doc.getElementsByTagName("sdnEntry");

            List<SdnEntryDTO> ofacs = new ArrayList<>();

            for (int temp = 0; temp < list.getLength(); temp++) {
                    Node node = list.item(temp);

                    if (node.getNodeType() == Node.ELEMENT_NODE) {
                            Element element = (Element) node;

                            String id = element.getElementsByTagName("uid").item(0).getTextContent();
                            String uid = element.getElementsByTagName("uid").item(0).getTextContent();
                            String lastName = element.getElementsByTagName("lastName").item(0).getTextContent();
                            String firstName = "";
                            if ((element.getElementsByTagName("firstName") != null)
                                    && (element.getElementsByTagName("firstName").item(0) != null)) {
                                firstName = element.getElementsByTagName("firstName").item(0).getTextContent();
                            }
                            String program = element.getElementsByTagName("program").item(0).getTextContent();
                            String sdnType = element.getElementsByTagName("sdnType").item(0).getTextContent();

                            //--------------alias

                        List<String> akaList = new ArrayList<>();
                        if (element.getElementsByTagName("akaList") != null && element.getElementsByTagName("akaList").getLength() > 0
                                && ((Element) element.getElementsByTagName("akaList").item(0)).getElementsByTagName("aka") != null) {
                            Node c = element.getElementsByTagName("akaList").item(0);
                            NodeList akaNodeList = ((Element) c).getElementsByTagName("aka");

                            if (akaNodeList != null) {
                                for (int i = 0; i < akaNodeList.getLength(); i++) {
                                    Node akaNode = akaNodeList.item(i);
                                    if (akaNode.getNodeType() == Node.ELEMENT_NODE) {
                                        Element aka = (Element) akaNode;

                                        if (aka.getElementsByTagName("firstName") != null && aka.getElementsByTagName("firstName").getLength() > 0) {
                                            akaList.add(aka.getElementsByTagName("firstName").item(0).getTextContent());
                                        }

                                        if (aka.getElementsByTagName("lastName") != null && aka.getElementsByTagName("lastName").getLength() > 0) {
                                            akaList.add(aka.getElementsByTagName("lastName").item(0).getTextContent());
                                        }
                                    }
                                }
                            }
                        }
                        if (akaList.size() > 0) {

                            SdnEntryDTO sdnEntryDTO = new SdnEntryDTO(Long.parseLong(uid),
                                    firstName + " " + lastName, program, sdnType, Collections.singletonList(String.join(",", akaList)));

                            ofacs.add(sdnEntryDTO);
                        }
                        }
                    }

                for (SdnEntryDTO sdnEntryDTO : ofacs) {
                    fileDTORepository.saveAll(ofacs);
                }
//        } catch (ParserConfigurationException | SAXException | IOException e) {
//            e.printStackTrace();
//        }
    }
}

Entity

@Entity
@Table(name = "customer")
public class SdnEntryDTO {

    @Id
    @GeneratedValue(generator = "uuid")
    private Long id;

    @Column(name = "ofac_id")
    private Long uid;

    @Column(name = "fullName", length = 255)
    private String fullName;

    @Column(name = "program", length = 50)
    private String program;

    @Column(name = "type", length = 50)
    private String sdnType;

    @Column(name = "alias", length = 255)
    private List<String> akaList;

    public SdnEntryDTO() {
    }

    public SdnEntryDTO(Long uid, String fullName, String program, String sdnType, List<String> akaList) {
        this.uid = uid;
        this.fullName = fullName;
        this.program = program;
        this.sdnType = sdnType;
        this.akaList = akaList;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Long getUid() {
        return uid;
    }

    public void setUid(Long uid) {
        this.uid = uid;
    }

    public String getFullName() {
        return fullName;
    }

    public void setFullName(String fullName) {
        this.fullName = fullName;
    }

    public String getProgram() {
        return program;
    }

    public void setProgram(String program) {
        this.program = program;
    }

    public String getSdnType() {
        return sdnType;
    }

    public void setSdnType(String sdnType) {
        this.sdnType = sdnType;
    }

    public List<String> getAkaList() {
        return akaList;
    }

    public void setAkaList(List<String> akaList) {
        this.akaList = akaList;
    }
}

Repository

@Repository
public interface FileDTORepository extends JpaRepository<SdnEntryDTO, Long> {
}

I have an error:

Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.MappingException: Could not determine type for: java.util.List, at table: customer, for columns: [org.hibernate.mapping.Column(alias)]

any ideas?

I will rephrase my question. The output should be: uid: some data fullName: some data program: some data sdnType: some data alias: lastName and fullName from akaList should be here — Alex
– Alex, Commented Sep 5, 2021 at 8:04
Can you please edit your question so that it is clearer what you are looking for? Thanks. — João Dias
– João Dias, Commented Sep 5, 2021 at 11:03
I would like to display the following data: fullName = (firstName + lastName), uid, program, sdnType and alias = (fistName + lastName, belonging to the akaList tag) — Alex
– Alex, Commented Sep 5, 2021 at 16:11
if i add code like this: if ((element.getElementsByTagName("akaList") != null) && (element.getElementsByTagName("akaList").item(0) != null)) { System.out.println("alias : " + element.getElementsByTagName("akaList").item(0).getTextContent())); } else { System.out.println("alias : " + " ");} — Alex
– Alex, Commented Sep 5, 2021 at 17:23
I have: uid : 1 fullName : AAAAAAAA program : UID sdnType : Entity alias : 12 a.k.a. ABCD — Alex
– Alex, Commented Sep 5, 2021 at 17:28

João Dias · Accepted Answer · 2021-09-07 10:54:29Z

1

The following for loop instead of yours should do the job, but I totally do not suggest you do so. This is code that is very hard to read. Please consider using other XML parsing libraries, such as JAXB (https://www.baeldung.com/java-xml-libraries).

for (int temp = 0; temp < list.getLength(); temp++) {
    Node node = list.item(temp);
    System.out.println("");

    if (node.getNodeType() == Node.ELEMENT_NODE) {
        Element element = (Element) node;

        System.out.println("uid : " + element.getElementsByTagName("uid").item(0).getTextContent());
        if ((element.getElementsByTagName("firstName") != null)
                && (element.getElementsByTagName("firstName").item(0) != null)) {
            System.out.println("fullName : " + (element.getElementsByTagName("lastName").item(0).getTextContent())
                    + " " + (element.getElementsByTagName("firstName").item(0).getTextContent()));
        } else {
            System.out.println("fullName : " + element.getElementsByTagName("lastName").item(0).getTextContent());
        }
        System.out.println("program : " + element.getElementsByTagName("program").item(0).getTextContent());
        System.out.println("sdnType : " + element.getElementsByTagName("sdnType").item(0).getTextContent());

        List<String> akaList = new ArrayList<>();
        if (element.getElementsByTagName("akaList") != null && element.getElementsByTagName("akaList").getLength() > 0
                && ((Element) element.getElementsByTagName("akaList").item(0)).getElementsByTagName("aka") != null) {
            Node c = element.getElementsByTagName("akaList").item(0);
            NodeList akaNodeList = ((Element) c).getElementsByTagName("aka");

            if (akaNodeList != null) {
                for (int i = 0; i < akaNodeList.getLength(); i++) {
                    Node akaNode = akaNodeList.item(i);
                    if (akaNode.getNodeType() == Node.ELEMENT_NODE) {
                        Element aka = (Element) akaNode;

                        if (aka.getElementsByTagName("firstName") != null && aka.getElementsByTagName("firstName").getLength() > 0) {
                            akaList.add(aka.getElementsByTagName("firstName").item(0).getTextContent());
                        }

                        if (aka.getElementsByTagName("lastName") != null && aka.getElementsByTagName("lastName").getLength() > 0) {
                            akaList.add(aka.getElementsByTagName("lastName").item(0).getTextContent());
                        }
                    }
                }
            }
        }

        if (akaList.size() > 0) {
            System.out.println("alias : " + String.join(",", akaList));
        }
    }
}

Update 06/09/2021

Use @ElementCollection instead of @Column(name = "alias", length = 255). You are defining it as a List, so @Column does not work since Hibernate does not know what to do with the List. @ElementCollection does just that by handling a collection of instances of a basic type or embeddable class. Check this article for more details.

Update 07/09/2021

Conversion of List<String> to a single column in the Database can be achieved as follows:

@Column(name = "alias", length = 255)
@Convert(converter = ListToStringConverter.class)
private List<String> akaList;

Now you need to implement ListToStringConverter as follows:

@Converter
public class ListToStringConverter implements AttributeConverter<List<String>, String>{
    @Override
    public String convertToDatabaseColumn(List<String> attribute) {
        return attribute == null ? null : StringUtils.join(attribute,",");
    }

    @Override
    public List<String> convertToEntityAttribute(String dbData) {
        if (StringUtils.isBlank(dbData))
            return Collections.emptyList();

        try (Stream<String> stream = Arrays.stream(dbData.split(","))) {
            return stream.collect(Collectors.toList());
        }
    }
}

edited Sep 7, 2021 at 10:54

answered Sep 5, 2021 at 17:45

João Dias

17.7k6 gold badges39 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

24 Comments

Alex Over a year ago

outputs the error of these parts of the code: element.getElementsByTagName("akaList")./getElementsByTagName/("aka"), if (/getElementsByTagName/("firstName") != null) {

Alex Over a year ago

and this: NodeList akaNodeList = element.getElementsByTagName("akaList")./getElementsByTagName/("aka");

João Dias Over a year ago

I've updated my answer. Now it is ok, please check it ;)

Alex Over a year ago

Theonly thingis that when I insert this piece ofcode into my application, I get an error: Error creating bean with name 'entityManagerFactory' defined in class path resource [org / springframework / boot / autoconfigure / orm / jpa / HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.MappingException: Could not determine type for: java.util.List, at table: customer, for columns: [org.hibernate.mapping.Column (alias)]

Alex Over a year ago

Do you have any thoughts on this?)

|

Collectives™ on Stack Overflow

Parse XML file. How to save into database

1 Answer 1

24 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

24 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related