servcontentlist.java

to disp contents of a db

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.wwr.content;

import com.wwr.commons.DBConnection;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.displaytag.decorator.TableDecorator;

/**
 *
 * @author user
 */
public class ServContentList {

    private DBConnection dbc;
    private ResultSet rs;
    private CallableStatement cstmt;
    private String sub_type,  status,  insert_Date,  expiry_date,  execute_date,  description,  content_type;
    private int content_id;
    private String edit = "Edit";
    public ArrayList contentList,  contentType;
    private int queID;
    private String ans1,  ans2,  queDesc;

    public ArrayList getContenList(String type) {
        try {
            dbc = new DBConnection();
            ContentListDTO cDTO;
            contentList = new ArrayList();

            String query = "";
//            rs = dbc.execQuery("select * from tbl_content_details where content_type = '"+type+"' order by content_id");
           
                query = "{call dbo.GetContentList(?)}";
                cstmt = dbc.exe_callable_statement(query);
                cstmt.setString(1, type);
                rs = cstmt.executeQuery();

                while (rs.next()) {
                    content_id = rs.getInt("content_id");
                    sub_type = rs.getString("sub_type");
                    status = rs.getString("status");
                    insert_Date = rs.getString("insert_Date");
                    expiry_date = rs.getString("expiry_date");
                    execute_date = rs.getString("execute_date");
                    description = rs.getString("description");
                    content_type = rs.getString("content_type");

                    cDTO = new ContentListDTO();
                    cDTO.setContent_id(content_id);
                    cDTO.setSub_type(sub_type);
                    cDTO.setStatus(status);
                    cDTO.setInsert_Date(insert_Date);
                    cDTO.setExpiry_date(expiry_date);
                    cDTO.setExecute_date(execute_date);
                    cDTO.setDescription(description);
                    cDTO.setContent_type(content_type);
                    cDTO.setEdit(edit);
                    contentList.add(cDTO);
                }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dbc != null) {
                try {
                    dbc.close();
                } catch (Exception ex) {
                    Logger.getLogger(ServContentList.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        System.out.println("Content List Size in Class : " + contentList.size());
        return contentList;
    }

    public ArrayList getContestQuestionList() {
try {
            dbc = new DBConnection();
            ContentListDTO cDTO;
            contentList = new ArrayList();

            String query = "select  * from tbl_question_master";
            rs = dbc.execQuery(query);
            while (rs.next()) {
                queID = rs.getInt("Question_id");
                queDesc = rs.getString("description");
                ans1 = rs.getString("answer");
                ans2 = rs.getString("answer1");
                cDTO = new ContentListDTO();
                cDTO.setQueID(queID);
                cDTO.setQueDesc(queDesc);
                cDTO.setAns1(ans1);
                cDTO.setAns2(ans2);
                contentList.add(cDTO);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dbc != null) {
                try {
                    dbc.close();
                } catch (Exception ex) {
                    Logger.getLogger(ServContentList.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        System.out.println("Content List Size in Class : " + contentList.size());
        return contentList;
    }

    public ArrayList getContenType() {
        try {
            dbc = new DBConnection();
            ContentListDTO cDTO;
            contentType = new ArrayList();
            String query = "{call  dbo.GetDistinctContentType}";
            cstmt = dbc.exe_callable_statement(query);
            rs = cstmt.executeQuery();

            //rs = dbc.execQuery("select distinct content_type from tbl_content_details");
            while (rs.next()) {
                content_type = rs.getString("content_type");
                cDTO = new ContentListDTO();
                cDTO.setContent_type(content_type);
                contentType.add(cDTO);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (dbc != null) {
                try {
                    dbc.close();
                } catch (Exception ex) {
                    ex.printStackTrace();
//                    Logger.getLogger(ServContentList.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        System.out.println("Content List Size in Class : " + contentType.size());
        return contentType;
    }
}