providercontroller.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.event.*;
import javax.faces.context.FacesContext;
import javax.faces.model.DataModel;
import javax.faces.model.ListDataModel;


public class ProviderController {
    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 providerKeys = null;
    //provider字段
    private String providerstr = 
        "ProviderCode,Name,Category,Email,Address,PostCode,Tel,LinkMan,Mobile";

    private int gotopage;

    public ProviderController() {
        providerKeys = ProviderInfo.getProviderKeys();

    }


    //当前页

    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(""))
            //providerKeys.get(0) + "='" + items[0] + "'";
            findString += 
                    "RTRIM(" + providerKeys.get(0) + ") ='" + items[0] + "'";
        for (int i = 1; i < providerKeys.size(); i++) {
            if ((null != items[i]) && !items[i].equals(""))
                findString += 
                        (findString.equals("") ? " " : " and ") + providerKeys.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();
            ProviderInfo emp = 
                (ProviderInfo)facesContext.getExternalContext().getSessionMap().get("providerInfo");
            conn = oracleconn.getOracleConnection();
            Statement smt = conn.createStatement();
            System.out.println("^^^^开始查找^^^^");

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

            //查找结果

            String[] resultRow = new String[9];
            while (result.next()) {
                icount++;
                for (int i = 0; i < 9; i++) {
                    resultRow[i] = result.getString(i + 1);
                }
                userlist.add(new ProviderInfo(resultRow));
            }

        } 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信息
        //ProviderInfo providers = (ProviderInfo)model.getRowData();
        ProviderInfo providers = (ProviderInfo)modelPage.getRowData();
        String providerKey = providers.getProviderCode();

        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 ProviderInfo WHERE " + 
                              providerKeys.get(0) + "='" + providerKey + "'");
            //刷新数据模型
            //model.getRowData();
            model.setWrappedData(getList());
        } catch (SQLException e) {
            e.printStackTrace();
        }

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

    //刷新

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

        older.setProviderCode(newer.getProviderCode());
        older.setName(newer.getName());
        older.setCategory(newer.getCategory());
        older.setEmail(newer.getEmail());
        older.setAddress(newer.getAddress());
        older.setPostCode(newer.getPostCode());
        older.setTel(newer.getTel());
        older.setLinkMan(newer.getLinkMan());
        older.setMobile(newer.getMobile());

        older.setSelect(true);
        //this.Reset();
    }

    //保存（插入和更新）

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

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

            if (!emp.isSelect()) {
                //插入
                //emp.setSelect(false);
                Statement smtinsert = conn.createStatement();
                String sqll = 
                    "INSERT INTO ProviderInfo ( " + providerstr + " ) Values('" + 
                    emp.getProviderCode() + "','" + emp.getName() + "','" + 
                    emp.getCategory() + "','" + emp.getEmail() + "','" + 
                    emp.getAddress() + "','" + emp.getPostCode() + "','" + 
                    emp.getTel() + "','" + emp.getLinkMan() + "','" + 
                    emp.getMobile() + "')";

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

                Statement smtupdate = conn.createStatement();
                String q = 
                    "UPDATE ProviderInfo SET " + providerKeys.get(1) + " ='" + 
                    emp.getName() + "'," + providerKeys.get(2) + " ='" + 
                    emp.getCategory() + "'," + providerKeys.get(3) + " ='" + 
                    emp.getEmail() + "'," + providerKeys.get(4) + " ='" + 
                    emp.getAddress() + "'," + providerKeys.get(5) + " ='" + 
                    emp.getPostCode() + "'," + providerKeys.get(6) + " ='" + 
                    emp.getTel() + "'," + providerKeys.get(7) + " ='" + 
                    emp.getLinkMan() + "'," + providerKeys.get(8) + " ='" + 
                    emp.getMobile() + "' WHERE " + providerKeys.get(0) + 
                    " ='" + emp.getProviderCode() + "'";
                System.out.println(q);
                smtupdate.executeUpdate(q);

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

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

    //查询

    public void queryItem() {

        FacesContext facesContext = FacesContext.getCurrentInstance();
        ProviderInfo emp = 
            (ProviderInfo)facesContext.getExternalContext().getSessionMap().get("providerInfo");
        emp.setQuery(true);
        String[] items = new String[9];
        items[0] = emp.getProviderCode();
        items[1] = emp.getName();
        items[2] = emp.getCategory();
        items[3] = emp.getEmail();
        items[4] = emp.getAddress();
        items[5] = emp.getPostCode();
        items[6] = emp.getTel();
        items[7] = emp.getLinkMan();
        items[8] = emp.getMobile();
        //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();

        ProviderInfo provider = 
        (ProviderInfo)facesContext.getExternalContext().getSessionMap().get("providerInfo");
        provider.setProviderCode("");
        provider.setName("");
        provider.setCategory("");
        provider.setEmail("");
        provider.setAddress("");
        provider.setPostCode("");
        provider.setTel("");
        provider.setLinkMan("");
        provider.setMobile("");
        provider = null;
    }
    //public void selectAction(ValueChangeEvent e){
    //    this.updateItem();
    //}


}