I have query which will return 8 columns for order placement POC, First 4 will be same for few rows(person details) and remaining 4 alone will differ(order details). Its a non normalized table.

COL1   COL2   COL3   COL4   COL5  COL6  COL7  COL8
-----------------------------------------------------
a      aaa    bbb    ccc    ddd   eee   fff   ggg
a      aaa    bbb    ccc    hhh   iii   jjj   kkk

My object looks like

class OrderBook{
   int id;
   int name;
   String age;
   String mailId;
   List<Order> orderList;
}

class Order{
   String productName;
   int price;
   int quantity;
   String address;
}

how can i loop through the result set and map the rows into the Orderbook object. Response should look something like below

{
   "id": 1,
   "name": "dude",
   "age" : 22,
   "mailId": "[email protected]",
   "orderList": [
        {
            "productName": "Milk",
            "price":23,
            "quantity":2,
            "address": "dude, 1st street"
        },
        {
            "productName": "Egg",
            "price":5,
            "quantity":10,
            "address": "dude, 1st street"
        }
    ]
}

I dont know how to loop it here

while(rs.next()){
    ???
}

CodePudding user response：

You should convert resultset to DataObject (OrderBook) firstly.

List<OrderBook> list = new ArrayList<>();
while(rs.next()){
   OrderBook orderBook = mapToOrderBook(rs);
   list.add(orderBook);
}

Few basic functions which you should write.

private List<OrderBook> getOrderBooks();
private OrderBook mapToOrderBook(ResultSet rs);

If you don't know how to get data from resultset:

https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html

Then you can use jackson to convert List to json data. Spring mvc already handled it for you.

@RequestMapping("/orderbooks")
public List<OrderBook> getOrderBooks() {
  return orderbookService.getOrderBooks();
}

CodePudding user response：

If you are using ResultSet directly you have to implement transformers directly from RS to Object instance. And use it from your DAO layer Something like:

@Override
public User getByID(int id) {
    OrderBook book = new OrderBook();
    try {
        PreparedStatement select = OrderBookTransformer.getInstance().getSelectStatementById(id);
        ResultSet rs = select.executeQuery();
        while (rs.next()) {
            book = OrderBookTransformer.getInstance().fromRsToObject(rs);
        }
    } catch (SQLException e) {
        log.error(e);
    }
    return book;
}

Where OrderBookTransformer could be like:

@Override
public OrderBook fromRsToObject(ResultSet rs) {
    OrderBook book = new OrderBook();
    try {
        user.setId(rs.getInt("id"));
        user.setName(rs.getInt("name"));
        user.setAge(rs.getString("age"));
        // set other fields
    } catch (SQLException e) {
        log.error(e);
    }
    return user;
}

For the initialisation transformer I used the Singleton pattern:

public class OrderBookTransformer implements BaseTranformer<OrderBook> {

    private static final Logger log = Logger.getLogger(OrderBookTransformer.class);
    private static OrderBookTransformer instance = null;

    protected OrderBookTransformer() {
    }

    public static OrderBookTransformer getInstance() {
        if (instance == null) {
            instance = new OrderBookTransformer();
        }
        return instance;
    }

And select statement something like:

@Override
public PreparedStatement getSelectStatementById(int id) {
    PreparedStatement select = null;
    try (Connection connection = ConnectionManager.getConnection()) {
        select = connection
                .prepareStatement("SELECT FROM order_books WHERE id = ?");
        select.setInt(1, id);
    } catch (SQLException e) {
        log.error(e);
    }
    return select;
}

My suggestion will be to use Spring Data instead of processing the Result Set directly.

You will need to do just a few more steps:

• add spring data dependency to your build tool
• add JPA annotations to your model with relations to it:

    @Entity
    class OrderBook {
       @Id
       @GeneratedValue(strategy = GenerationType.IDENTITY)
       private Integer id;
       private Integer name;
       private String age;
       private String mailId;
    
       @ElementCollection(targetClass = Order.class, fetch = FetchType.EAGER)
        @CollectionTable(name = "orders", joinColumns = @JoinColumn(name = "book_id"))
       private List<Order> orderList;
       // no args constructor   getters/setters
    }
    
    @Entity
    class Order {
       @Id
       @GeneratedValue(strategy = GenerationType.IDENTITY)
       private String productName;
       private Integer price; // consider use Double here
       private Integer quantity;
       private String address;
    }

• create a repository for this class:

public OrderBookRepository implements JpaRepository<OrderBook, Integer> {}

and use it for retrieving data from DB.