countsavecontroller.java

一个简单的数据连接例子

package com.smartjavaer.ckgl;

import java.sql.Connection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.List;

import javax.faces.context.FacesContext;
import javax.faces.model.DataModel;
import javax.faces.model.ListDataModel;
//import javax.faces.model.ListDataModel;

public class CountSaveController {
    private DataModel model;
    // 数据模型
    private DataModel modelPage = null;
    //分页 数据模型
    private int currentPage = 0;
    //当前页
    private int currentCount = 0;

    private int sumCount = 0;

    private int sumPage = 0;
    //总页数

    private final int count = 5;
    //每页记录数

    public int sumRecord = 0;
    //总记录数

    private int rowIndex = -1;
    // 当前选中行索引

    private int maxRowIndex = 0;
    // 记录最大索引

    private Connection conn;
    //数据库连接
    public String findString = "";
    //查找字符串
    private List countSaveKeys = null;
    //provider字段
    private String countSavestr = 
        "countSave_ID,merchCode,merchName,unit,count";

    private int gotopage;

    public CountSaveController() {
        countSaveKeys = CountSaveInfo.getCountSaveKeys();

    }


    //当前页

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }
    //当前页

    public int getCurrentPage() {
        return currentPage;
    }
    //当前页

    public void setCurrentCount(int currentCount) {
        this.currentCount = currentCount;
    }

    public int getCurrentCount() {
        return currentCount;
    }

    public void setSumPage(int sumPage) {
        this.sumPage = sumPage;
    }

    public int getSumPage() {
        return sumPage;
    }

    public void setSumCount(int sumCount) {
        this.sumCount = sumCount;
    }

    public int getSumCount() {
        return sumCount;
    }


    public DataModel getModel() {
        if (modelPage == null) {
            getAll();
        }
        return modelPage;
    }

    public int getSumRecord() {
        return sumRecord;
    }

    public void setSumRecord(int sumRecord) {
        this.sumRecord = sumRecord;
    }

    public void setGotopage(int gotopage) {
        this.gotopage = gotopage;
    }

    public int getGotopage() {
        return gotopage;
    }

    //起始页

    public void FirstPageList() {
        List pageData = new ArrayList();
        List allData = new ArrayList();

        //数据模型
        allData = (ArrayList)model.getWrappedData();
        //起始页数据模型              //sumRecord < count ? sumRecord : count
        pageData = allData.subList(0, Math.min(sumRecord, count));
        this.setCurrentCount(pageData.size());
        this.setCurrentPage(1);
        getPage(pageData);
        gotopage=currentPage;
    }

    //上一页

    public void PrePageList() {
        List lstpage = new ArrayList();
        if (currentPage - 1 > 0) {
            List lst = new ArrayList();
            currentPage--;
            lst = (ArrayList)model.getWrappedData();
            lstpage = 
                    lst.subList(currentPage * this.count - this.count, currentPage * 
                                this.count);
            getPage(lstpage);
        }
        gotopage=currentPage;
    }

    //下一页

    public void NextPageList() {
        List lstpage = new ArrayList();
        if (currentPage + 1 <= sumPage) {
            List lst = new ArrayList();
            currentPage++;
            lst = (ArrayList)model.getWrappedData();
            if (sumRecord < currentPage * this.count) {
                lstpage = 
                        lst.subList(currentPage * this.count - this.count, sumRecord);
            } else {
                lstpage = 
                        lst.subList(currentPage * this.count - this.count, currentPage * 
                                    this.count);
            }
            getPage(lstpage);
            gotopage=currentPage;
        }
    }

    //末尾页

    public void LastPageList() {
        List lstpage = new ArrayList();
        List lst = new ArrayList();

        lst = (ArrayList)model.getWrappedData();

        lstpage = 
                lst.subList(sumPage * count - count, Math.min(sumRecord, sumPage * 
                                                              count));

        this.setCurrentCount(lstpage.size());
        this.setCurrentPage(sumPage);
        getPage(lstpage);
        gotopage=currentPage;
    }

    //自定义页

    public void GotoPageList() {
        List lstpage = new ArrayList();
        List lst = new ArrayList();
        //gotopage=4;
        lst = (ArrayList)model.getWrappedData();
        if (gotopage > 0) {
            if (gotopage < sumPage) {
                lstpage = 
                        lst.subList(gotopage * count - count, gotopage * count);
                //currentPage = gotopage;
                this.setCurrentCount(lstpage.size());
                this.setCurrentPage(gotopage);
                this.getPage(lstpage);
            } else {
                this.setCurrentPage(sumPage);
                this.LastPageList();

            }

        } else {
            this.setCurrentPage(1);
            this.FirstPageList();

        }

        gotopage=currentPage;
    }

    //构造查询字符串

    public void searchString(String[] items) {
        findString = "";
        if ((null != items[0]) && !items[0].equals("0"))
            //countSaveKeys.get(0) + "='" + items[0] + "'";
            findString += 
                    "RTRIM(" + countSaveKeys.get(0) + ") =" + items[0] + "";
        for (int i = 1; i < countSaveKeys.size(); i++) {
            if ((null != items[i]) && !items[i].equals(""))
                findString += 
                        (findString.equals("") ? " " : " and ") + countSaveKeys.get(i) + 
                        " LIKE '%" + items[i] + "%'";
        }

        findString = " where " + findString;
        System.out.println(findString);
    }


    //刷新数据模型

    public void getAll() {
        model = new ListDataModel();
        List temp = getList();

        if (!temp.isEmpty()) {
            //非空
            System.out.println("^^^^this is getAll()查找到东西^^^^");
            model.setWrappedData(temp);
            maxRowIndex = model.getRowCount();
            currentPage--;
            FirstPageList();
        } else {
            //空
            model = null;
            modelPage = null;
            currentPage = 0;
            currentCount = 0;
            sumCount = 0;
            sumPage = 0;
            sumRecord = 0;
            rowIndex = -1;
            maxRowIndex = 0;
            System.out.println("^^^^this is getAll()没有查找到什么东西^^^^");

        }


    }
    //从数据库中取出数据

    private List getList() {
        OracleConn oracleconn = new OracleConn();
        String sql2 = "";
        List userlist = new ArrayList();
        int icount = 0;
        try {
            FacesContext facesContext = FacesContext.getCurrentInstance();
            CountSaveInfo emp = 
                (CountSaveInfo)facesContext.getExternalContext().getSessionMap().get("countSaveInfo");
            conn = oracleconn.getOracleConnection();
            Statement smt = conn.createStatement();
            System.out.println("^^^^开始查找^^^^");

            sql2 = 
    "select " + countSavestr + " from COUNTSAVE " + (emp.isQuery() ? findString :
                                                   "") + " ORDER BY " + 
    countSaveKeys.get(0);
            System.out.println("^^^^开始查找字符串：^^^^" + sql2);
            ResultSet result = smt.executeQuery(sql2);

            //查找结果

            String[] resultRow = new String[5];
            while (result.next()) {
                icount++;
                for (int i = 0; i < 5; i++) {
                    resultRow[i] = result.getString(i + 1);
                }
                userlist.add(new CountSaveInfo(Integer.parseInt(resultRow[0]),resultRow[1],resultRow[2],resultRow[3],resultRow[4] ));
            }

        } catch (SQLException e) {
            System.out.println("^^^^查找异常^^^^");

            e.printStackTrace();
        } finally {
            //关闭数据库连接
            conn = null;
            oracleconn.closeOracleConn();

            //刷新分页参数
            //sumRecord = icount;
            this.setSumRecord(icount);
            sumPage = (sumRecord + count - 1) / count;


            return userlist;
        }
    }
    //

    public void getPage(List lst) {
        modelPage = new ListDataModel();
        modelPage.setWrappedData(lst);
    }


    //删除

    public void deleteItem() {
        //从数据模型中取出选中的provider信息
        //CountSaveInfo providers = (CountSaveInfo)model.getRowData();
        CountSaveInfo providers = (CountSaveInfo)modelPage.getRowData();
        int providerKey = providers.getcountSave_ID();

        try {
            OracleConn oracleconn = new OracleConn();
            conn = oracleconn.getOracleConnection();
            //String a="DELETE inputvoucher WHERE inputvoucher_id='" + inputVoucher_id+ "'";
            //System.out.println(a);
            Statement smt = conn.createStatement();

            smt.executeUpdate("DELETE COUNTSAVE WHERE " + 
                              countSaveKeys.get(0) + "=" + providerKey);
            //刷新数据模型
            //model.getRowData();
            model.setWrappedData(getList());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        this.getAll();
        this.Reset();
    }

    //刷新

    public void updateItem() {
        FacesContext facesContext = FacesContext.getCurrentInstance();
        CountSaveInfo older = 
            (CountSaveInfo)facesContext.getExternalContext().getSessionMap().get("countSaveInfo");
        CountSaveInfo newer = (CountSaveInfo)modelPage.getRowData();

        older.setcountSave_ID(newer.getcountSave_ID());
        older.setmerchCode(newer.getmerchCode());
        older.setmerchName(newer.getmerchName());
        older.setUnit(newer.getUnit());
        older.setCount(newer.getCount());
       
        older.setSelect(true);
        //this.Reset();
    }

    //保存（插入和更新）

    public void insertItem() {
        try {
            FacesContext facesContext = FacesContext.getCurrentInstance();
            CountSaveInfo emp = 
                (CountSaveInfo)facesContext.getExternalContext().getSessionMap().get("countSaveInfo");

            OracleConn oracleconn = new OracleConn();
            conn = oracleconn.getOracleConnection();

            if (!emp.isSelect()) {
                //插入
                //emp.setSelect(false);
                Statement smtinsert = conn.createStatement();
                String sqll = 
                    "INSERT INTO COUNTSAVE ( " + countSavestr + " ) Values(" + 
                    emp.getcountSave_ID() + ",'" + emp.getmerchCode() + "','" + 
                    emp.getmerchName() + "','" + emp.getUnit() + "','" + 
                    emp.getCount() + "')";

                System.out.println(sqll);
                smtinsert.executeUpdate(sqll);
                System.out.println("完成插入操作");
            } else {
                //更新
                emp.setSelect(false);

                Statement smtupdate = conn.createStatement();
                String q = 
                    "UPDATE COUNTSAVE SET " + countSaveKeys.get(1) + " ='" + 
                    emp.getmerchCode() + "'," + countSaveKeys.get(2) + " ='" + 
                    emp.getmerchName()+ "'," + countSaveKeys.get(3) + " ='" + 
                    emp.getUnit() + "'," + countSaveKeys.get(4) + " ='" + 
                    emp.getCount()+ "' WHERE " + countSaveKeys.get(0) + 
                    " =" + emp.getcountSave_ID();
                System.out.println(q);
                smtupdate.executeUpdate(q);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //刷新数据模型
        this.getAll();
        this.Reset();
    }

    //查询

    public void queryItem() {

        FacesContext facesContext = FacesContext.getCurrentInstance();
        CountSaveInfo emp = 
            (CountSaveInfo)facesContext.getExternalContext().getSessionMap().get("countSaveInfo");
        emp.setQuery(true);
        String[] items = new String[5];
        items[0] = String.valueOf(emp.getcountSave_ID());
        items[1] = emp.getmerchCode();
        items[2] = emp.getmerchName();
        items[3] = emp.getUnit();
        items[4] = emp.getCount();
        
        //findString="inputVoucher_id LIKE '"+items[0]+"%'";
        searchString(items);
        //System.out.println(find+"find");
        // 构造查询串
        if (!findString.equals("")) {
            this.getAll();
        } else {
            System.out.println("需要查找条件");
        }

    }
    //清空

    public void Reset() {
        FacesContext facesContext = FacesContext.getCurrentInstance();

        CountSaveInfo provider = 
        (CountSaveInfo)facesContext.getExternalContext().getSessionMap().get("countSaveInfo");
        provider.setcountSave_ID(0);;
        provider.setmerchCode("");
        provider.setmerchName("");
        provider.setUnit("");
        provider.setCount("");
        
        provider = null;
    }
}