How to put multiple values into a json in java

I’m trying to return a list of values from JDBC but multiple columns of the database to solve this I just made a JSON object to make something like this

{
    "Results 1": {
        "IP": "192.168.1.2",
        "Port": "13442",
        "Domain": "google.com"
    },

    "Results 2": {
        "IP": "192.168.1.2",
        "Port": "13442",
        "Domain": "google.com"
    }
}

The issue is im getting this error WARN 43953 --- [nio-1900-exec-1] .w.s.m.s.DefaultHandlerExceptionResolver : Resolved [org.springframework.web.HttpMediaTypeNotAcceptableException: Could not find acceptable representation]

What I’ve tried

I’ve tried using a hash map but it doesn’t work and I don’t even know if it can return the value like a want it.

What I hope

I hope at the end I can get a list of values similar to the way I showed above, all separate. This is to be displayed on HTML later so if I’m doing something i shouldn’t also let me know

This is my code

    package com.mchugo.que.McHugoQue.Controller;

import com.mchugo.que.McHugoQue.Models.ConnectionDetails;
import com.mchugo.que.McHugoQue.Models.SearchCredential;
import com.mysql.cj.xdevapi.JsonArray;
import org.json.JSONObject;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

@RestController
public class ApiController {

    @RequestMapping(value={"/search/credential", "xyz"}, method={RequestMethod.POST,RequestMethod.PUT, RequestMethod.GET})
    public JsonArray searchCredential(@RequestBody SearchCredential searchCredential){
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;

        String Username = searchCredential.getUsername();

        //grabs connection details
        ConnectionDetails connectionDetails = new ConnectionDetails();

        String username = "";
        String password = "";

        JsonArray array = new JsonArray();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://" + connectionDetails.getHost() + "/" + connectionDetails.getDatabase(), connectionDetails.getUsername(), connectionDetails.getPassword());
            Statement statement = con.createStatement();
            ResultSet res = statement.executeQuery("SELECT * FROM credentials WHERE identifier = '" + Username + "'");

            while(res.next()){
                JSONObject data = new JSONObject();
                username = res.getString("identifier");
                password = res.getString("password");

                data.put("Username", username);
                data.put("Password", password);
                array.add(data);
            }
        }
        catch(Exception ex){
            System.out.println("Exception : " + ex.toString());
        }

        System.out.println(array);
        return(array);
    }
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.6</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.mchugo.que</groupId>
    <artifactId>McHugoQue</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>McHugoQue</name>
    <description>Que for sending data to email for the compermised passwords</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.21</version>
        </dependency>
        <dependency>
            <groupId>com.vaadin.external.google</groupId>
            <artifactId>android-json</artifactId>
            <version>0.0.20131108.vaadin1</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>com.vaadin.external.google</groupId>
            <artifactId>android-json</artifactId>
            <version>0.0.20131108.vaadin1</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>com.sun.mail</groupId>
            <artifactId>jakarta.mail</artifactId>
            <version>1.6.4</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/javax.xml.ws/jaxws-api -->
        <dependency>
            <groupId>javax.xml.ws</groupId>
            <artifactId>jaxws-api</artifactId>
            <version>2.3.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/commons-lang/commons-lang -->
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.dataformat</groupId>
            <artifactId>jackson-dataformat-xml</artifactId>
            <version>2.10.2</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

What I tried

HashMap<String, String> data = new HashMap<>();

        for(int i = 0; i <= 10; i++){
            HashMap<String, String> data1 = new HashMap<>();
            data1.put("IP", i + "");
            data1.put("PORT", i + 1 + "");
            
            data.put("value " + i, data1);
        }
        System.out.println(data);

Traceback

2022-04-15 13:22:08.415  INFO 43953 --- [           main] c.m.que.McHugoQue.McHugoQueApplication   : Starting McHugoQueApplication using Java 16.0.1 on Macbook-Air.lan with PID 43953 (/Volumes/Drive/FiverrWork/untitled folder/McHugoQue/target/classes started by danielcaminero in /Volumes/Drive/FiverrWork/untitled folder/McHugoQue)
2022-04-15 13:22:08.419  INFO 43953 --- [           main] c.m.que.McHugoQue.McHugoQueApplication   : No active profile set, falling back to 1 default profile: "default"
2022-04-15 13:22:10.677  INFO 43953 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 1900 (http)
2022-04-15 13:22:10.698  INFO 43953 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2022-04-15 13:22:10.698  INFO 43953 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.60]
2022-04-15 13:22:10.852  INFO 43953 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2022-04-15 13:22:10.852  INFO 43953 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 2295 ms
2022-04-15 13:22:11.513  INFO 43953 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 1900 (http) with context path ''
2022-04-15 13:22:11.535  INFO 43953 --- [           main] c.m.que.McHugoQue.McHugoQueApplication   : Started McHugoQueApplication in 4.137 seconds (JVM running for 5.008)
2022-04-15 13:22:21.712  INFO 43953 --- [nio-1900-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2022-04-15 13:22:21.712  INFO 43953 --- [nio-1900-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2022-04-15 13:22:21.713  INFO 43953 --- [nio-1900-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 1 ms
{"Value 1":{"Username":"[e-mail]","Password":"nodea03"}}
2022-04-15 13:22:22.181  WARN 43953 --- [nio-1900-exec-1] .w.s.m.s.DefaultHandlerExceptionResolver : Resolved [org.springframework.web.HttpMediaTypeNotAcceptableException: Could not find acceptable representation]

Answer

You should work with a List instead putting the values inside an object and increasing the counter variable. I would suggest you use something like this

  ....      
  //grabs connection details
  ConnectionDetails connectionDetails = new ConnectionDetails();

  String username = "";
  String password = "";

  JSONArray array = new JSONArray();  
  try {
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://" + connectionDetails.getHost() + "/" + connectionDetails.getDatabase(), connectionDetails.getUsername(), connectionDetails.getPassword());
    Statement statement = connection.createStatement();
    ResultSet res = statement.executeQuery("SELECT * FROM credentials WHERE identifier = '" + Username + "'");
    
    while(res.next()){
        JSONObject data = new JSONObject();
        String username = res.getString("identifier");
        String password = res.getString("password");

        data.put("Username", username);
        data.put("Password", password);
        array.add(data);
    }
  }
  catch(Exception ex){
      System.out.println("Exception : " + ex.toString());
  }

  System.out.println(data);
  return(data);

Your output will look slightly different.

[
  { 
     "Username: "firstUserName",
     "Password": "somePass"
  },
  {
     "Username: "secondUserName",
     "Password": "someOtherPass"
  }
]

It is however better to work with lists/arrays. Otherwise you wold have to access your result by incrementing variables until an error is thrown or something like this.