问题描述:

Following is my data base table named feeds

column_name | data_type

-------------+-----------------------------

id | integer

category | text

question | text

answer | text

thumb | text

time | timestamp without time zone

The categories will be of comma separated values. I would like to get all comma separated values into an array that has got unique elements (No same categories will be present in the array).

To achieve this, I have created a separate table called category. When the user publishes a feed, i will populate both the feeds table and also category table at the same time as follows.

@POST

@Path("/post")

@Consumes(MediaType.APPLICATION_JSON)

public Response postStrMsg(String msg) {

String songString = "requesting";

JSONObject jsonObj = new JSONObject(msg);

try {

Connection connection = DbConnection.getConnection();

PreparedStatement stmt = connection.prepareStatement(

"INSERT INTO feeds ( CATEGORY , TOPIC , DESCRIPTION , THUMB , TIMEFRAME , AUTHOR ) VALUES( ?, ? , ? , ? , now() , ? )");

stmt.setString(1, jsonObj.getString("category"));

stmt.setString(2, jsonObj.getString("question"));

stmt.setString(3, jsonObj.getString("answer"));

stmt.setString(4, jsonObj.getString("thumb"));

stmt.setString(5, jsonObj.getString("author"));

stmt.executeUpdate();

String query = "INSERT INTO CATEGORY (SECTION) VALUES ('" + jsonObj.getString("category") + "');";

PreparedStatement stmts = connection.prepareStatement(query);

stmts.executeQuery();

songString = Utilities.constructJSON( "Posted Successfully",true);

} catch (Exception ex) {

songString = Utilities.constructJSON("Failure", false, ex.getMessage());

}

return Response.status(200).entity(songString).build();

}

And I used the following code to retrieve the categories when the user wants to know all categories. This will parse all comma separated values and filter all duplicate elements and it will provide the corresponding result.

Query :

"select min(c.id) as id, t.name from category c " + " cross join "

+ "unnest(string_to_array(c.category, ',')) AS t(name) " + "group by t.name order by 1";

Code

@GET

@Path("getCategories")

@Produces(MediaType.APPLICATION_JSON)

public String getCategories() {

ArrayList<Category> output = null;

try {

Connection connection = getConnection();

Statement stmt = connection.createStatement();

String query = "select min(c.id) as id, t.name from category c " + " cross join "

+ "unnest(string_to_array(c.category, ',')) AS t(name) " + "group by t.name order by 1";

ResultSet rs = stmt.executeQuery(query);

output = new ArrayList<Category>();

while (rs.next()) {

output.add(new Category(rs.getString("name")));

}

} catch (Exception ex) {

ex.printStackTrace();

}

JSONArray jsArray = new JSONArray(output);

return jsArray.toString();

}

The issue is that,

  1. Since two different tables are populated at the same time, the performance is being degraded. Is it possible to achieve the same functionality without creating the same table?
  2. No results were returned by the query while posting the feed

网友答案:

I think you need two more tables. One for categories and the other one to combine categories with feed. You should create a category table as follows:

column_name |          data_type
-------------+-----------------------------
id          | integer
category    | text

And feedCategory table as follows:

column_name |          data_type
-------------+-----------------------------
id          | integer
category_id | integer
feed_id     | integer

One feed can have more than one category. So you can populate the last table based on that.

It is not the best way to parse category text each time you will retrieve from database. How about querying feeds by category? Are you gonna parse category column for each feed and see if you have it in there?

相关阅读:
Top