moneydb.java

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

package qyxx;

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

public class moneyDB
{
	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())
			{
				qyxx.moneyTable mt = new qyxx.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 addMoney(qyxx.moneyTable mtable)
	{
		try
		{
			this.conn = null;
			conn = this.initDB();
			String sql = "select MAX(id) from QYXX_MONEY";
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			int d = 0;
			rs = stmt.executeQuery();
			if(rs.next())
			{
				d = rs.getInt(1) + 1;
			}
			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.setString(2,mtable.getMount());
			stmt.setString(3,mtable.getJiedai());
			stmt.setInt(4,Integer.parseInt(mtable.getDepartid()));
			stmt.setString(5,mtable.getMoneyman());
			stmt.setString(6,mtable.getMoneytime());
			stmt.setString(7,mtable.getMoneypriority());
			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 editMoney(qyxx.moneyTable mtable)
	{
		try
		{
			this.conn = null;
			conn = this.initDB();
			String sql = "update QYXX_MONEY set mount = ?, depart_id = ?, jiedai = ? where id = ?";
			this.stmt = null;
			this.rs = null;
			this.stmt = conn.prepareStatement(sql);
			stmt.setString(1,mtable.getMount());
			stmt.setInt(2,Integer.parseInt(mtable.getDepartid()));
			stmt.setString(3,mtable.getJiedai());
			stmt.setInt(4,Integer.parseInt(mtable.getId()));

			stmt.executeUpdate();

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

			}
		}
	}

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

			for(int i=0; i<id.length; i++)
			{
				String sql = "delete from QYXX_MONEY 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)
			{

			}
		}
	}
}