defaultprojectdao.java~103~

些程序用java实现一个装机程序,不过是一个C/S架构,但是在数据库的设计方面与3层架构的构造方案我想对一些朋友还是否有一定的帮助吧

package pcdiysystem.Dao;

import javax.swing.JOptionPane;
import java.lang.StringBuffer;
import pcdiysystem.Beans.DefaultProjectBean;
import pcdiysystem.Panel.ProjectPanel;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.lang.Math;
import java.util.Calendar;
import javax.swing.table.TableModel;
import java.util.Vector;

/**
 * <p>Title: 自助装机系统</p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2006</p>
 *
 * <p>Company: </p>
 *
 * @author kamiiiyu
 * @version 1.0
 */
public class DefaultProjectDAO {

  ProjectPanel pPanObj;

  public DefaultProjectDAO() {

  }

  public DefaultProjectDAO(ProjectPanel pPanObj) {
    this.pPanObj = pPanObj;
  }

  public float calculate() {
    try {
      DefaultProjectBean dpb = new DefaultProjectBean();
      dpb.setDataTable(pPanObj.getTabProjectData());
      float total = 0;
      for (int i = 0; i < dpb.getDataTable().getRowCount(); i++) {
        String pTemp = String.valueOf(dpb.getDataTable().getValueAt(i, 3));
        String qTemp = String.valueOf(dpb.getDataTable().getValueAt(i, 2));
        if (!pTemp.equals("null") && !qTemp.equals("null")) {
          float price = Float.valueOf(pTemp.substring(1));
          float quantity = Integer.valueOf(qTemp);
          total += (price * quantity);
        }
      }
      pPanObj.getLblTotalValue().setText("￥" + String.valueOf(total) + "0元");
      return total;
    }
    catch (NumberFormatException ne) {
      JOptionPane.showMessageDialog(pPanObj, "请输入有效整数", "出错啦!",
                                    JOptionPane.ERROR_MESSAGE);
      pPanObj.getLblTotalValue().setText("无法计算!");
      return -1f;
    }
  }

  public float testCal(){//未完成
    float [] priceOfComponent={0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f};
    float [] totalPriceOfComponent={0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f,0.0f};
    int [] quantity={0,0,0,0,0,0,0,0,0,0,0,0,0,0};
    float total=0;
    return -1f;
  }

  public boolean creatSQLTable(String projectId) {
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet resultSet = null;
    try {
      dbConnection = ConnectionManager.getConnection();
      if (dbConnection != null)
        System.out.println("OK!");
      String ps = "CREATE TABLE [dbo].["+projectId+"] ([ID] [numeric](18, 0) IDENTITY (0, 1) NOT NULL ,[ProjectId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,[Component] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,[Type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,[Quantity] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,[Price] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]ALTER TABLE [dbo].["+projectId+"] ADD CONSTRAINT [PK_"+projectId+"] PRIMARY KEY  CLUSTERED ([ID])  ON [PRIMARY] ALTER TABLE [dbo].["+projectId+"] ADD CONSTRAINT [FK_"+projectId+"_ProjectManagement] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[ProjectManagement] ([ProjectId]) ON DELETE CASCADE  ON UPDATE CASCADE ";
      pStatement = dbConnection.prepareStatement(ps);
      pStatement.execute();
      return true;
    }
    catch (SQLException ex) {
      System.out.println("creatErr");
      JOptionPane.showMessageDialog(pPanObj,"对不起，系统随机创建的方案名重复，建议重复刚才步骤或者指定方案名","数据库出错了",JOptionPane.ERROR_MESSAGE);
      return false;
    }
    finally {
      ConnectionManager.closeResultSet(resultSet);
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(dbConnection);
    }
  }

  public void dropSQLTable(String projectId) {
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet resultSet = null;
    try {
      dbConnection = ConnectionManager.getConnection();
      if (dbConnection != null)
        System.out.println("OK!");
      String ps = "drop table "+projectId+"";
      pStatement = dbConnection.prepareStatement(ps);
      pStatement.execute();
    }
    catch (SQLException ex) {
      System.out.println("dropErr");
    }
    finally {
      ConnectionManager.closeResultSet(resultSet);
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(dbConnection);
    }
  }

  public String creatProjectId(){
    Calendar date=Calendar.getInstance();
    String month;
    String day;
    if(date.get(date.MONTH)<10){
      month="0"+String.valueOf(date.get(date.MONTH)+1);
    }else{
      month=String.valueOf(date.get(date.MONTH)+1);
    }
    if(date.get(date.DATE)<10){
      day="0"+String.valueOf(date.get(date.DATE));
    }else{
      day=String.valueOf(date.get(date.DATE));
    }
    String random=String.valueOf(Math.random()).substring(2,6);
    String projectId="Pro"+month+day+random;
    return projectId;
  }

  public void insertDataToProjectManagement(String id,String name,String price){
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet resultSet = null;
    if(name.equals("") || name.equals("请为您的方案命名（可缺省）")){
      name=id;
    }
    if(this.insertToProjectName(id,name)){
      try {
        dbConnection = ConnectionManager.getConnection();
        if (dbConnection != null)
          System.out.println("OK!");
        String ps = "insert into projectManagement(projectid,projectname,projectcost) values(?,?,?)";
        pStatement = dbConnection.prepareStatement(ps);
        pStatement.setString(1, id);
        pStatement.setString(2, name);
        pStatement.setString(3, price);
        int rowCount = pStatement.executeUpdate();
      }
      catch (SQLException ex) {
        System.out.println("Err");
      }
      finally {
        ConnectionManager.closeResultSet(resultSet);
        ConnectionManager.closeStatement(pStatement);
        ConnectionManager.closeConnection(dbConnection);
      }
    }
  }

  public void insertDataToDefaultProject(Vector dataSource,String targetTable){
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet resultSet = null;
    int rowCount=dataSource.size();
    int columnCount=((Vector)dataSource.get(0)).size();
    try {
      dbConnection = ConnectionManager.getConnection();
      if (dbConnection != null)
        System.out.println("OK!");
      String ps = "insert into "+targetTable+"(projectId,Component,Type,Quantity,Price) values('"+targetTable+"',?,?,?,?)";
      for(int i=0;i<rowCount;i++){
        pStatement = dbConnection.prepareStatement(ps);
        String [] item=new String[4];
        for(int j=0;j<columnCount;j++){
          item[j]=String.valueOf(((Vector)dataSource.get(i)).get(j));
          pStatement.setString(j+1,item[j]);
        }
        int recordCount = pStatement.executeUpdate();
      }
      JOptionPane.showMessageDialog(this.pPanObj, "已经成功提交方案");
    }
    catch (SQLException ex) {
      System.out.println("sqlErr");
    }
    finally {
      ConnectionManager.closeResultSet(resultSet);
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(dbConnection);
    }
  }

  public Vector getDataFromTableMOdel(TableModel dtm){
    DefaultProjectBean dpb=new DefaultProjectBean();
    for(int i=0;i<13;i++){
      Vector tdTemp=new Vector();
      for(int j=0;j<4;j++){
        Object td=dtm.getValueAt(i,j);
        tdTemp.add(td);
      }
      dpb.setFile(tdTemp);
    }
    return dpb.getFile();
  }

  private boolean insertToProjectName(String id,String name){
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet resultSet = null;
    try {
      dbConnection = ConnectionManager.getConnection();
      if (dbConnection != null)
        System.out.println("OK!");
      String ps = "insert into projectName(projectname) values(?)";
      pStatement = dbConnection.prepareStatement(ps);
      pStatement.setString(1,name);
      int rowCount=pStatement.executeUpdate();
      return true;
    }
    catch (SQLException ex) {
      System.out.println("NameErr");
      JOptionPane.showMessageDialog(pPanObj,"对不起，方案名重复，请重新指定方案名","数据库出错了",JOptionPane.ERROR_MESSAGE);
      this.dropSQLTable(id);
      return false;
    }
    finally {
      ConnectionManager.closeResultSet(resultSet);
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(dbConnection);
    }

  }

  public Vector getProjectName(){
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet resultSet = null;
    try {
      dbConnection = ConnectionManager.getConnection();
      if (dbConnection != null)
        System.out.println("OK!");
      String ps = "select projectname from projectmanagement";
      pStatement = dbConnection.prepareStatement(ps);
      resultSet=pStatement.executeQuery();
      DefaultProjectBean dpb=new DefaultProjectBean();
      while(resultSet.next()){
        dpb.setRecord(resultSet.getString(1));
      }
      return dpb.getRecord();
    }
    catch (SQLException ex) {
      System.out.println("gpErr");
      return new Vector();
    }
    finally {
      ConnectionManager.closeResultSet(resultSet);
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(dbConnection);
    }
  }

  public Vector getProjectData(String projectName){
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet resultSet = null;
    String tableName=this.getProIdfromProName(projectName);
    try {
      dbConnection = ConnectionManager.getConnection();
      if (dbConnection != null)
        System.out.println("OK!");
      String ps = "select * from "+tableName;
      pStatement = dbConnection.prepareStatement(ps);
      resultSet=pStatement.executeQuery();
      DefaultProjectBean dpb=new DefaultProjectBean();
      while(resultSet.next()){
        DefaultProjectBean temp=new DefaultProjectBean();
        /*
        temp.setRecord(resultSet.getObject(3));
        temp.setRecord(resultSet.getObject(4));
        temp.setRecord(resultSet.getObject(5));
        temp.setRecord(resultSet.getObject(6));*///已过时，有可能造成界面混乱
        for(int i=3;i<=6;i++){
          Object tempItem=resultSet.getString(i);
          if(tempItem.equals("null")){
            tempItem=null;
          }
          temp.setRecord(tempItem);
        }
        dpb.setFile(temp.getRecord());
      }
      return dpb.getFile();
    }
    catch (SQLException ex) {
      System.out.println("gpErr");
      return new Vector();
    }
    finally {
      ConnectionManager.closeResultSet(resultSet);
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(dbConnection);
    }
  }

  public String getProIdfromProName(String name){
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet resultSet = null;
    try {
      dbConnection = ConnectionManager.getConnection();
      if (dbConnection != null)
        System.out.println("OK!");
      String ps = "select projectId from projectmanagement where projectname='"+name+"'";
      pStatement = dbConnection.prepareStatement(ps);
      resultSet=pStatement.executeQuery();
      resultSet.next();
      String proId=resultSet.getString(1);
      return proId;
    }
    catch (SQLException ex) {
      System.out.println("gpErr");
      return "null";
    }
    finally {
      ConnectionManager.closeResultSet(resultSet);
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(dbConnection);
    }
  }

  public String getProjectCost(String projectName){
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet resultSet = null;
    try {
      dbConnection = ConnectionManager.getConnection();
      if (dbConnection != null)
        System.out.println("OK!");
      String ps = "select projectcost from projectmanagement where projectname='"+projectName+"'";
      pStatement = dbConnection.prepareStatement(ps);
      resultSet=pStatement.executeQuery();
      DefaultProjectBean dpb=new DefaultProjectBean();
      while(resultSet.next()){
        dpb.setRecord(resultSet.getString(1));
      }
      String cost=String.valueOf(dpb.getRecord().get(0));
      pPanObj.getLblTotalValue().setText(cost + "元");
      return cost;
    }
    catch (SQLException ex) {
      System.out.println("gpErr");
      return "null";
    }
    finally {
      ConnectionManager.closeResultSet(resultSet);
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(dbConnection);
    }
  }

  public boolean isTableEmpty(TableModel dtm){
    DefaultProjectBean dpb=new DefaultProjectBean();
    for(int i=0;i<13;i++){
      Object td=dtm.getValueAt(i,1);
      if(td!=null){
        return false;
      }
    }
    return true;
  }
}