salesdb.java

JSP+oracle开发实例精粹》随书光盘

package qyxx;

import java.sql.*;
import java.util.*;

public class salesDB
{
	String DBDriver =  "oracle.jdbc.driver.OracleDriver";
	String DBUrl = "jdbc:oracle:thin:@localhost:1521:ORACLE";
	String DBUser = "jerry";
	String DBPsw = "123456";
	Connection conn = null;
	PreparedStatement stmt = null;
	ResultSet rs = null;

	private Connection initDB()
	{
		try
		{
			Class.forName(DBDriver);
			return DriverManager.getConnection(DBUrl,DBUser,DBPsw);
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
	}


	public ArrayList getMoneyInfo()
	{
		ArrayList list = new ArrayList();
		try
		{
			String sql = "select * from QYXX_MONEY order by money_time";
			this.conn = null;
			conn = this.initDB();
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			rs = this.stmt.executeQuery();
			while(rs.next())
			{
//				moneyTable mt = new moneyTable();
//				mt.setMoneytime(rs.getString("money_time"));
//				mt.setMount(rs.getString("mount"));
//				mt.setJiedai(rs.getString("jiedai"));
//				mt.setMoneyman(rs.getString("money_men"));
//				mt.setMoneypriority(rs.getString("money_priority"));
//				mt.setDepartid(String.valueOf(rs.getInt("depart_id")));
//				list.add(mt);
			}
			return list;
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return list;
		}
		finally
		{
			try
			{
				conn.close();
				stmt.close();
				rs.close();
			}
			catch(Exception e)
			{

			}
		}
	}


	public int addSales(qyxx.salesTable stable)
	{
		try
		{
			this.conn = null;
			conn = this.initDB();

			String sql = "select MAX(id) from QYXX_MONEY";
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			int d = 0;
			if(rs.next())
			{
				d  = rs.getInt(1) + 1;
			}
			rs = null;
			int pp = Integer.parseInt(stable.getSalesmount()) * Integer.parseInt(stable.getSalesprice());

			sql = "insert into QYXX_MONEY(id,mount,jiedai,depart_id,money_men,money_time,money_priority) values(?,?,?,?,?,?,?)";
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			stmt.setInt(1,d);
			stmt.setInt(2,pp);
			stmt.setString(3,"1");
			stmt.setString(4,"3");
			stmt.setString(5,stable.getSalesmen());
			stmt.setString(6,stable.getSalestime());
			stmt.setString(7,stable.getPriority());

			stmt.executeUpdate();

			sql = "select MAX(id) from QYXX_SALES";
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			int dd = 0;
			rs = stmt.executeQuery();
			if(rs.next())
			{
				dd = rs.getInt(1) + 1;
			}
			sql ="insert into QYXX_SALES(id,money_id,good_id,sales_mount,sales_price,sales_men,sales_time,priority) values(?,?,?,?,?,?,?,?)";
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);

			stmt.setInt(1,dd);
			stmt.setInt(2,d);
			stmt.setInt(3,Integer.parseInt(stable.getGoodid()));
			stmt.setString(4,stable.getSalesmount());
			stmt.setString(5,stable.getSalesprice());
			stmt.setString(6,stable.getSalesmen());
			stmt.setString(7,stable.getSalestime());
			stmt.setString(8,stable.getPriority());

			this.stmt.executeUpdate();
			return 1;
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			try
			{
				rs.close();
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{

			}
		}
	}

	public int editSales(qyxx.salesTable stable)
	{
		try
		{
			this.conn = null;
			conn = this.initDB();
			String sql = "update QYXX_SALES set sales_mount = ?,sales_price = ?,good_id = ? where id = ?";
			this.stmt = null;
			this.rs = null;
			this.stmt = conn.prepareStatement(sql);

			stmt.setInt(1,Integer.parseInt(stable.getSalesmount()));
			stmt.setInt(2,Integer.parseInt(stable.getSalesprice()));
			stmt.setInt(3,Integer.parseInt(stable.getGoodid()));
			stmt.setInt(4,Integer.parseInt(stable.getId()));

			stmt.executeUpdate();

			sql = "select money_id from QYXX_SALES where id = "+stable.getId();
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();

			if(rs.next())
			{
				int idd = rs.getInt(1);
				int pp = Integer.parseInt(stable.getSalesmount()) * Integer.parseInt(stable.getSalesprice());
				sql = "update QYXX_MONEY set mount = "+String.valueOf(pp)+ "where id = "+idd;
				this.stmt = null;
				this.stmt = conn.prepareStatement(sql);
				stmt.executeUpdate();
			}


			return 1;
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			try
			{
				conn.close();
				stmt.close();
				rs.close();
			}
			catch(Exception e)
			{

			}
		}
	}

	public int deleteSales(String [] id)
	{
		try
		{
			this.conn = null;
			conn = this.initDB();

			for(int i=0; i<id.length; i++)
			{
				String sql = new String("select money_id from QYXX_SALES where id = "+id[i]);
				this.stmt = null;
				this.stmt = conn.prepareStatement(sql);
				this.rs = null;
				rs = this.stmt.executeQuery();

				if(rs.next())
				{
					sql = "delete from QYXX_MONEY where id = "+String.valueOf(rs.getInt(1));
					this.stmt = null;
					this.stmt = conn.prepareStatement(sql);
					this.stmt.executeUpdate();
				}

				sql = "delete from QYXX_Sales where id = "+id[i]+"";
				this.stmt = null;
				this.stmt = conn.prepareStatement(sql);
				this.stmt.executeUpdate();
			}

			return 1;
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		finally
		{
			try
			{
				conn.close();
				stmt.close();
			}
			catch(Exception e)
			{

			}
		}
	}
}