stockdb.java

股票投资管理系统

package db;

import java.sql.*;

import bean.*;

public class StockDB {
	
	private Connection con;
	private Statement stm;
	private PreparedStatement ps;
	private ResultSet rs;
	
	public StockDB(){		
		con = null;
		stm = null;
		ps = null;
		rs = null;
	}
	
	public ResultSet getResult(StockBean sb){
		
		con = ConDB.getCon();
		try{
			String sql = "select stock_code,stock_name,stock_num,stock_pre from stock where in_id="+sb.getIn_id();
			stm = con.createStatement();
			rs = stm.executeQuery(sql);
		}catch(SQLException e){
			e.toString();
		}
		return rs;
	}
	
	
	public boolean checkStock(StockBean sb){
		con = ConDB.getCon();
		boolean flag = false;
		String sql = "select * from stock where in_id=? and stock_code=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, sb.getIn_id());
			ps.setString(2, sb.getStock_code());
			rs = ps.executeQuery();
			if(!rs.next())
				flag = true;
		} catch (SQLException e) {
			flag = false;
			e.printStackTrace();
		}
		
		try {
			rs.close();
			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return flag;
	}
	
	public int insertStock(StockBean sb){
		con = ConDB.getCon();
		int result = 0;	
		String sql = "insert into stock values(default,?,?,?,?,?,?)";		
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, sb.getIn_id());
			ps.setString(2,sb.getStock_name());
			ps.setString(3, sb.getStock_code());
			ps.setLong(4, sb.getStock_num());
			ps.setFloat(5, sb.getStock_pre());
			ps.setFloat(6, sb.getStock_total());
			result = ps.executeUpdate();			
		} catch (SQLException e) {
			result = 0;
			e.printStackTrace();
		}
		
		try {
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
		
	}
	
	
	public int deleteStock(StockBean sb){
		con = ConDB.getCon();
		String sql = "delete from stock where in_id=? and stock_code=?";
		int result = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, sb.getIn_id());
			ps.setString(2, sb.getStock_code());
			result = ps.executeUpdate();
		} catch (SQLException e) {
            result = 0;
			e.printStackTrace();
		}
		
		try {
			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	
	public int updateStock(StockBean sb){
		con = ConDB.getCon();
		int result = 0;
		String sql = "update stock set stock_num=?,stock_pre=?,stock_total=? where stock_code=? and in_id=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, sb.getStock_num());
			ps.setFloat(2, sb.getStock_pre());
			ps.setFloat(3, sb.getStock_total());
			ps.setString(4, sb.getStock_code());
			ps.setInt(5, sb.getIn_id());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			result = 0;
			e.printStackTrace();
		}
		
		try {
			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	
	
	public ResultSet sortStock(StockBean sb){
		con = ConDB.getCon();
		try{
			String sql = "select stock_code,stock_name,stock_num,stock_pre from stock where in_id=? order by stock_pre desc";
			ps = con.prepareStatement(sql);
			ps.setInt(1,sb.getIn_id());
			rs = ps.executeQuery();
		}catch(SQLException e){
			e.toString();
		}
		return rs;
	}

}