opendatabaseclass.java

用java写的固定资产管理系统原代码 这可是自己小组做的哦

/*
 * OpendatabaseClass.java
 *
 * Created on 2007年4月28日, 下午1:59
 *
 *此类是用来操作数据库
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package com.cn.szhaccp.set;

import java.sql.*;

/**
 *
 * @author Administrator
 */
public class OpendatabaseClass {
    public Connection con;
    public Statement statement;
    public ResultSet resultset;
    
    /** Creates a new instance of OpendatabaseClass */
    public OpendatabaseClass(Connection con) {
        this.con = con;
    }
    
    /*
  *查询类别
  */
  public ResultSet lookup_sort() throws SQLException {
    statement = con.createStatement();
    String sql = "select sort from assetinfo group by sort";
    resultset = statement.executeQuery(sql);
    return resultset;
  }
  
  /*
  *查询这个类别下的所有可以借用设备名称
  */
  public ResultSet lookup_name(String sort) throws SQLException {
    SetgetClass sg = new SetgetClass();
    sg.setSort(sort);

    statement = con.createStatement();
    String sql = "select Name from assetinfo where sort = '"+sg.getSort()+"' and state = 1";
    resultset = statement.executeQuery(sql);
    return resultset;
  }
  
  /*
   *查询这个类别下的所有设备名称
   */
  public ResultSet lookup_name2(String sort) throws SQLException{
      SetgetClass sg = new SetgetClass();
      sg.setSort(sort);
      
      statement = con.createStatement();
      String sql = "select Name from assetinfo where sort = '"+sg.getSort()+"'";
      resultset = statement.executeQuery(sql);
      return resultset;
  }
  
  /*
   *获得设备编号
   */
  public ResultSet lookup_assetid(String sort,String Name) throws SQLException{
      SetgetClass sg = new SetgetClass();
      sg.setSort(sort);
      sg.setName(Name);
      
      statement = con.createStatement();
      String sql = "select code from assetinfo where sort = '"+sg.getSort()+"' and name = '"+sg.getName()+"'";
      resultset = statement.executeQuery(sql);
      return resultset;
  }
  
  /*
   *查询这个类别下这个设备的状态
   */
  public ResultSet lookup_state(String sort,String Name) throws SQLException{
      SetgetClass sg = new SetgetClass();
      sg.setSort(sort);
      sg.setname(Name);
      
      statement = con.createStatement();
      String sql = "select * from assetinfo where sort = '"+sg.getSort()+"' and name = '"+
              sg.getname()+"'";
      resultset = statement.executeQuery(sql);
      return resultset;
  }
  
   /*
  *借用人员的验证
  */

  public boolean incumbency_attest(int id ,String name) throws SQLException {
    SetgetClass sg = new SetgetClass();
    sg.setId(id);
    sg.setName(name);

    statement = con.createStatement();
    String sql = "select * from Pinfo where code = '"+sg.getId()+"' and name = '"+
        sg.getName()+"'";
    resultset = statement.executeQuery(sql);
    if(resultset.next()){
      return true;
    }
    return false;
  }
  
   /*
  *获得管理员的姓名
  */
  public String lookup_username() throws SQLException {
      statement = con.createStatement();
      String sql = "select name from users where state = 1";
      resultset = statement.executeQuery(sql);
      String username = "";
      while(resultset.next()){
        username = resultset.getString("name");
      }
      return username;
  }
  
   /*
  *把借用时的记录加到数据库里
  */
  public void accretion_database(int assetid,String Name,String time,int id,String name,String username,
                          String purpose,String annotation) throws SQLException {
    SetgetClass sg = new SetgetClass();
    sg.setassetid(assetid);
    sg.setname(Name);
    sg.setTime(time);
    sg.setId(id);
    sg.setName(name);
    sg.setUsername(username);
    sg.setPurpose(purpose);
    sg.setAnnotation(annotation);

    statement = con.createStatement();
    String sql = "insert into assetborrow values('"+sg.getassetid()+"','"+sg.getname()+"','"+
        sg.getTime()+"','"+sg.getId()+"','"+sg.getName()+"','"+sg.getUsername()+"','"+
        sg.getPurpose()+"','"+sg.getAnnotation()+"')";
        statement.executeUpdate(sql);
  }
  
  /*
  *修改借用设备后的相关状态
  */

  public int amend_record(int assetid,String Name,String name) throws SQLException {
    SetgetClass sg = new SetgetClass();
    sg.setassetid(assetid);
    sg.setname(Name);
    sg.setName(name);

    statement = con.createStatement();
    String sql = "update assetinfo set state = 0,username = '"+sg.getName()+"' where name = '"+
        sg.getname()+"' and code = '"+sg.getassetid()+"'";
    int fahui = 0;
    fahui = statement.executeUpdate(sql);
    return fahui;
  }
  
  /*
  *获得借出人员的姓名
  */
  public ResultSet lookup_incumbency_name() throws SQLException {
    statement = con.createStatement();
    String sql = "select BorrowName from assetborrow group by borrowname";
    resultset = statement.executeQuery(sql);
    return resultset;
  }
  
  /*
  *获得设备名称
  */
  public ResultSet lookup_set_name(String username) throws SQLException {
    SetgetClass sg = new SetgetClass();
    sg.setUsername(username);

    statement = con.createStatement();
    String sql = "select code from assetborrow where BorrowName = '"+sg.getUsername()+"'";
    resultset = statement.executeQuery(sql);
    return resultset;
  }
  
  /*
  *获得设备编号
  */
  public ResultSet lookup_set_assetid(String username,String Name) throws SQLException {
    SetgetClass sg = new SetgetClass();
    sg.setUsername(username);
    sg.setName(Name);

    statement = con.createStatement();
    String sql = "select assetid from assetborrow where BorrowName = '"+sg.getUsername()+"' and code = '"+
            sg.getName()+"'";
    resultset = statement.executeQuery(sql);
    return resultset;
  }
  
  /*
  *归还验证
  */
  public boolean return_attest(int id,String name) throws SQLException {
    SetgetClass sg = new SetgetClass();
    sg.setId(id);
    sg.setName(name);

    statement = con.createStatement();
    String sql = "select * from assetborrow where borrowNo = "+sg.getId()
        +" and borrowname = '"+sg.getName()+"'";
    resultset = statement.executeQuery(sql);
    if(resultset.next()){
      return true;
    }
    return false;
  }
  
  /*
  *对于归还后一些状态进行处理
   *删除借用记录
  */
  public void delete_return(int assetid,String Name) throws SQLException {
    SetgetClass sg = new SetgetClass();
    sg.setassetid(assetid);
    sg.setname(Name);

    statement = con.createStatement();
    String sql = "delete from assetborrow where code = '"+sg.getname()+"' and assetid = '"+
            sg.getassetid()+"'";
    statement.executeUpdate(sql);
  }
  /*
   *进行归还处理
   */
  public int return_disposal(int assetid) throws SQLException {
    SetgetClass sg = new SetgetClass();
    sg.setassetid(assetid);

    statement = con.createStatement();
    String sql = "update assetinfo set state = 1,username = null where code = '"+
        sg.getassetid()+"'";
    int fahui = 0;
    fahui = statement.executeUpdate(sql);
    return fahui;
  }
  /*
   *添加归还记录
   */
  public void return_record(int assetid,String Name,String time,
                          String name,String username) throws SQLException {
   SetgetClass sg = new SetgetClass();
   sg.setassetid(assetid);
   sg.setname(Name);
   sg.setTime(time);
   sg.setName(name);
   sg.setUsername(username);

   statement = con.createStatement();
   String sql = "insert into AssetRestore(assetid,Code,endtime,RestoreName,UserName) values('"+
           sg.getassetid()+"','"+sg.getname()+"','"+sg.getTime()+"','"+
           sg.getName()+"','"+sg.getUsername()+"')";
   statement.executeUpdate(sql);
 }
  
 /*
 *添加设备记录
 */
 public void accretion_record(String name,String sort,String model,double merit,
                               String buydate,String state,String purpose) throws
      SQLException {
   SetgetClass sg = new SetgetClass();
   sg.setName(name);
   sg.setSort(sort);
   sg.setModel(model);
   sg.setMerit(merit);
   sg.setBuydate(buydate);
   sg.setState(state);
   sg.setPurpose(purpose);

   statement = con.createStatement();
   String sql = "insert into assetinfo(Name,Sort,Model,Merit,BuyDate,State,UserName,Remark) values('"+sg.getName()+"','"+
       sg.getSort()+"','"+sg.getModel()+"',"+sg.getMerit()+",'"+
       sg.getBuydate()+"','"+sg.getState()+"',"+ null +",'"+sg.getPurpose()+"')";
   statement.executeUpdate(sql);
 }

 /*
 *删除设备记录
 */
 public void update_record(String name,String sort) throws SQLException {
   SetgetClass sg = new SetgetClass();
   sg.setName(name);
   sg.setSort(sort);

   statement = con.createStatement();
   String sql = "delete from assetinfo where name = '"+sg.getName()+"' and sort = '"+
       sg.getSort()+"'";
   statement.executeUpdate(sql);
 }
 
 /*
  *添加职员记录
  */
 public void add_workman(String code,String name,String duty,String annotation) throws SQLException{
     SetgetClass sg = new SetgetClass();
     sg.setCode(code);
     sg.setName(name);
     sg.setduty(duty);
     sg.setAnnotation(annotation);
     
     statement = con.createStatement();
     String sql = "insert into pinfo values('"+sg.getCode()+"','"+sg.getName()+"','"+sg.getduty()+"','"+
             sg.getAnnotation()+"')";
     statement.executeUpdate(sql);
 }
 
 /*
  *获得职员姓名
  */
 public ResultSet lookup_workman_name() throws SQLException{
     statement = con.createStatement();
     String sql = "select * from pinfo";
     resultset = statement.executeQuery(sql);
     return resultset;
 }
 
 /*
  *获得职员编号
  */
 public ResultSet lookup_workman_code(String name) throws SQLException{
     SetgetClass sg = new SetgetClass();
     sg.setName(name);
     
     statement = con.createStatement();
     String sql = "select code from pinfo where name = '"+sg.getName()+"'";
     resultset = statement.executeQuery(sql);
     return resultset;
 }
 
 /*
  *获得职员记录
  */
 public ResultSet lookup_workman(String code, String name) throws SQLException{
     SetgetClass sg = new SetgetClass();
     sg.setCode(code);
     sg.setName(name);
     
     statement = con.createStatement();
     String sql = "select * from pinfo where code = '"+sg.getCode()+"' and name = '"+sg.getName()+"'";
     resultset = statement.executeQuery(sql);
     return resultset;
 }
 
 /*
  *删除职员记录
  */
 public void delete_workman(String code,String name) throws SQLException{
     SetgetClass sg = new SetgetClass();
     sg.setCode(code);
     sg.setName(name);
     
     statement = con.createStatement();
     String sql = "delete from pinfo where code = '"+sg.getCode()+"' and name = '"+sg.getName()+"'";
     statement.executeUpdate(sql);
 }
    
}