oracledaoimpl.java

电信短信订购系统

package com.ouyang.ms.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import com.ouyang.ms.dao.Dao;
import com.ouyang.ms.entity.Admin;
import com.ouyang.ms.entity.CityInfo;
import com.ouyang.ms.entity.Operation;
import com.ouyang.ms.entity.Provision;
import com.ouyang.ms.entity.ServiceInfo;
import com.ouyang.ms.util.ConnectionFactory;

public class OracleDaoImpl implements Dao{

	public ServiceInfo getServiceInfo(int serviceId) {
		ServiceInfo sif=null;
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		con=ConnectionFactory.getConnection();
		System.out.println(con);
		String sql="select * from ServiceInfo where serviceId=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, serviceId);
			rs=ps.executeQuery();
			rs.next();
			sif=new ServiceInfo();
			sif.setServiceId(rs.getInt(1));
			sif.setName(rs.getString(2));
			sif.setPriceInfo(rs.getString(3));
			sif.setServiceDesc(rs.getString(4));
			sif.setFreqDesc(rs.getString(5));
			sif.setExample(rs.getString(6));
			sif.setHasCity(rs.getString(7));
			return sif;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return sif;
	}
    public int getProvisionId()
    {
    	Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		con=ConnectionFactory.getConnection();
		System.out.println(con);
		String sql="select * from (select provisionId from provision order by provisionId desc) where rownum=1";
		try {
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			System.out.println("表中是否有数据:"+rs.getRow());
			int provisionId=rs.getInt(1);
			
			return provisionId;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			ConnectionFactory.closeConnection(con, ps,rs);
		}
        return 0;
    }
	public Provision getProvision(int serviceId, String userMsisdn, Date provisionTime,String cityId) {
		  //调用获得provisionId序列的方法
		Provision psn=null;
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		con=ConnectionFactory.getConnection();
		System.out.println(con);
		String sql="insert into provision values(pid.nextval,?,?,?)";
		
		System.out.println("我获得了provisionId序列号");
		
		String sql2="select * from provision where provisionId=?";   //有问题
		String sql3="insert into forecastCity values(?,?)";
		try {
			ps=con.prepareStatement(sql);
			
			/*第一个sql语句的作用
			 * 将传入进来的信息(首先给该服务加一个用户订购标识，业务编号,用户电话号码,订购服务的日期)，
			 * 插入到provision表中
			 */
			ps.setInt(1, serviceId);
			ps.setString(2, userMsisdn);
			ps.setDate(3,new java.sql.Date(provisionTime.getTime()));
			ps.executeQuery();
			int provisionId=getProvisionId();
			/*第二个sql语句的作用
			 *  然后再从provision表中，查出该条信息
			 */
			con.setAutoCommit(false);
			ps=con.prepareStatement(sql2);
			ps.setInt(1,provisionId);
			rs=ps.executeQuery();
			rs.next();
			//给provision实例的属性设置值
			psn=new Provision();
		    psn.setProvisionId(rs.getInt(1));
		    psn.setServiceId(serviceId);
		    psn.setUserMsisdn(userMsisdn);
		    psn.setProvisionTime(provisionTime);
		    /*第3个sql语句的作用
		      向forecastCity表中插入数据,保存用户订购哪个城市的天气预报
		    */
		    System.out.println("城市id:"+cityId);
		    ps=con.prepareStatement(sql3);
		    ps.setInt(1,provisionId);
		    ps.setString(2,cityId);
		    ps.executeUpdate();
	
		    con.commit();
			return psn;
			
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return psn;
	}
	public boolean isOrder(int serviceId,String phone)
	{
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		boolean b=false;
		con=ConnectionFactory.getConnection();
		System.out.println(con);
		String sql="select * from provision where serviceId=? and usermsisdn=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1,serviceId);
			ps.setString(2,phone);
			rs=ps.executeQuery();
			rs.next();
			if(rs.getRow()==1)
			{
				b=true;
				System.out.println("我在判断是否已经订购!");
				return b;
				
			}
			else
			{
				return b;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b;
	}
	public boolean isOrder(int serviceId,String phone,String cityId)
	{
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		ResultSet rs2=null;
		boolean b=false;
		con=ConnectionFactory.getConnection();
		System.out.println(con);
		String sql="select provisionId from provision where serviceId=? and usermsisdn=?";
		String sql2="select cityId from forecastCity where provisionId=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1,serviceId);
			ps.setString(2,phone);
			rs=ps.executeQuery();
			rs.next();
			
			if(rs.getRow()>=1)
			{
				System.out.println("我进入了城市判断");
				do
				{
					System.out.println("先循环一下");
					ps=con.prepareStatement(sql2);
					ps.setInt(1,rs.getInt(1));
					rs2=ps.executeQuery();
					
					rs2.next();
					if(cityId.equals(rs2.getString(1)))
					{
  					    b=true;
						System.out.println("我在判断是否已经订购!");
						return b;
					}
				}while(rs.next());
			}
			else
			{
				return b;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			ConnectionFactory.closeConnection(con, ps,rs);
		}
		return b;
	}
	public Collection getCitys()
	{
		System.out.println("获得城市对象");
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Collection col=new ArrayList();
		con=ConnectionFactory.getConnection();
		System.out.println(con);
		String sql="select * from cityInfo";
		try {
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next())
			{
				CityInfo cif=new CityInfo();
				cif.setCityId(rs.getString(1));
				cif.setCityName(rs.getString(2));
				col.add(cif);
			}
			System.out.println("一切ok");
			return col;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	    return col;  	
	}
	public Collection getOperations(String phone)
	{ 
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Collection col=new ArrayList();
		con=ConnectionFactory.getConnection();
		System.out.println(con);
		//注意，一定要注意，折磨了我一天,有城市和没有城市（没有城市的，将cityId,cityName赋值为"无"），
		String sql="select p.*,s.name,c.* from serviceInfo s,provision p,forecastCity f,cityInfo c where p.usermsisdn=? and p.provisionId=f.provisionId and p.serviceId=s.serviceId and c.cityId=f.cityId";
		String sql2="select p.*,s.name from serviceInfo s,provision p,forecastCity f where p.usermsisdn=? and p.provisionId=f.provisionId and p.serviceId=s.serviceId and p.serviceId!=1";
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1,phone);
			 rs=ps.executeQuery();
			 rs.next();
			 if(rs.getRow()>=1)
			 {
				 do
				 {
					Operation operation=new Operation();
					operation.setProvisionId(rs.getInt(1));
					operation.setServiceId(rs.getInt(2));
					operation.setUsermsisdn(rs.getString(3));
					operation.setProvisionTime(rs.getString(4));
					operation.setName(rs.getString(5));
					operation.setCityId(rs.getString(6));
					operation.setCityName(rs.getString(7));
					col.add(operation);
				 }while(rs.next());
			 }
			 ps=con.prepareStatement(sql2);
			 ps.setString(1,phone);
			 rs=ps.executeQuery();
			 rs.next();
			 if(rs.getRow()>=1)
			 {
				 do
				 {
					 Operation operation=new Operation();
					 operation.setProvisionId(rs.getInt(1));
					 operation.setServiceId(rs.getInt(2));
					 operation.setUsermsisdn(rs.getString(3));
					 operation.setProvisionTime(rs.getString(4));
					 operation.setName(rs.getString(5));
					 operation.setCityId("无");
					 operation.setCityName("无");
					 col.add(operation);
				 }while(rs.next());
			 }
			 return col;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			ConnectionFactory.closeConnection(con, ps,rs);
		}
	    return col;  	
	}
	public boolean cancelOperation(int provisionId)
	{
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		con=ConnectionFactory.getConnection();
		
		System.out.println(con);
		String sql="delete from forecastCity where provisionId=?";
		String sql2="delete from provision where provisionId=?";
		try {
			con.setAutoCommit(false);
			ps=con.prepareStatement(sql);
			ps.setInt(1,provisionId);
			ps.executeUpdate();
			ps=con.prepareStatement(sql2);
			ps.setInt(1,provisionId);
			 ps.executeUpdate();
			 con.commit();
		    return true;
			
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			ConnectionFactory.closeConnection(con, ps,rs);
		}
		return false;
	}
    public Admin loginResult(String AName,String APass)
    {
    	Admin admin=new Admin();
    	Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		con=ConnectionFactory.getConnection();
		
		System.out.println(con);
		String sql="select * from Admin where AName=? and APass=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, AName);
			ps.setString(2, APass);
			rs=ps.executeQuery();
			rs.next();
			if(rs.getRow()>=1)
			{
				do
				{
					admin.setAId(rs.getInt(1));
					admin.setAName(rs.getString(2));
					admin.setAPass(rs.getString(3));
				}while(rs.next());
				return admin;
			}
		} catch (SQLException e) {
	
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			ConnectionFactory.closeConnection(con, ps,rs);
		}
    	return null;
    }
    public int updatePass(int AId,String newPass)
    {
    	int result;
    	Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		con=ConnectionFactory.getConnection();
		System.out.println(con);
		String sql="update Admin set APass=? where AId=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, newPass);
			ps.setInt(2, AId);
			result=ps.executeUpdate();
			System.out.println("更新的结果:"+result);
			return result;
		} catch (SQLException e) {
	
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			ConnectionFactory.closeConnection(con, ps,rs);
		}
        return 0;	
    }
    public Collection getServiceInfos(int point)
    {
    	System.out.println("获得所有服务");
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Collection col=new ArrayList();
		con=ConnectionFactory.getConnection();
		System.out.println(con);
		String sql="select * from (select rownum r, s.* from serviceInfo s) where r>? and r<?";
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1,point);
			ps.setInt(2,point+2);
			rs=ps.executeQuery();
			while(rs.next())
			{
				ServiceInfo sif=new ServiceInfo();
				sif.setServiceId(rs.getInt(2));   //由于返回的结果集多了一个rownum的字段,所以要从第2个字段开始接受数据
				sif.setName(rs.getString(3));
				sif.setPriceInfo(rs.getString(4));
				sif.setServiceDesc(rs.getString(5));
				sif.setFreqDesc(rs.getString(6));
				sif.setExample(rs.getString(7));
				sif.setHasCity(rs.getString(8));
				col.add(sif);
			}
			return col;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	    return col;  	
    }
    public int findAllTotal()
    {
    	Connection con=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		Collection cols=new ArrayList();
		int total=0;
		try{
			con=ConnectionFactory.getConnection();
			String sql="select * from serviceInfo";
			pst=con.prepareStatement(sql);
			rs=pst.executeQuery();
			
			while(rs.next()){
				ServiceInfo sif=new ServiceInfo();
				cols.add(sif);
			}
			total=cols.size();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			ConnectionFactory.closeConnection(con,pst,rs);
		}
		return total;
    }
    public int addService(ServiceInfo sif)
    {
    	Connection con=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		int addResult=0;
		try{
			con=ConnectionFactory.getConnection();
			con.setAutoCommit(false);
			String sql="insert into serviceInfo values(?,?,?,?,?,?,?)";
			pst=con.prepareStatement(sql);
			pst.setInt(1,sif.getServiceId());
			pst.setString(2,sif.getName());
			pst.setString(3,sif.getPriceInfo());
			pst.setString(4,sif.getServiceDesc());
			pst.setString(5,sif.getFreqDesc());
			pst.setString(6,sif.getExample());
			pst.setString(7,sif.getHasCity());
			addResult=pst.executeUpdate();
			con.commit();
			return addResult;
		}catch(Exception e){
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			ConnectionFactory.closeConnection(con,pst,rs);
		}
    	return addResult;
    }
}