connectdb.java

jsp+Oracle的网上求职源码

package qiuzhi;

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

public class connectDB
{
	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;
	public connectDB()
	{
		try
		{
			Class.forName(DBDriver);
			conn = DriverManager.getConnection(DBUrl,DBUser,DBPsw);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
	public ResultSet executeQuery(String sql) throws Exception
	{
		try
		{
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			return this.stmt.executeQuery();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return null;
		}
	}
	public int executeUpdate(String sql) throws Exception
	{
		try
		{
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			return this.stmt.executeUpdate();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return 0;
		}
		
	}
	public void closeDB()
	{
		try
		{
			conn.close();
			stmt.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public ArrayList getJobInfo(String user_id)
	{
		ArrayList list = new ArrayList();
		if(user_id == null || user_id.length() == 0)
		{
			return null;	
		}
		else
		{
			ResultSet rs = null;
			try
			{
				String sql = "select a.* from JOBS a, COMPANY b where b.user_id = "+user_id+" and a.cop_id = b.id";
				this.stmt = null;
				this.stmt = conn.prepareStatement(sql);
				rs = stmt.executeQuery();
				while(rs.next())
				{
					jobTable jtable = new jobTable();
					
					jtable.setId(String.valueOf(rs.getInt("id")));
					jtable.setCopid(String.valueOf(rs.getInt("cop_id")));
					jtable.setJobname(rs.getString("job_name"));
					jtable.setJobmann(String.valueOf(rs.getInt("jop_mann")));
					jtable.setJobaddr(rs.getString("job_addr"));
					jtable.setJobnum(String.valueOf(rs.getString("job_num")));
					jtable.setJobget(rs.getString("job_get"));
					jtable.setJobsta(String.valueOf(rs.getInt("job_sta")));
					jtable.setJobend(String.valueOf(rs.getInt("job_end")));
					jtable.setJobgrad(String.valueOf(rs.getInt("job_grad")));
					jtable.setJobage1(String.valueOf(rs.getInt("job_age1")));
					jtable.setJobage2(String.valueOf(rs.getInt("job_age2")));
					jtable.setJobexpe(String.valueOf(rs.getInt("job_expe")));
					jtable.setJoblang(String.valueOf(rs.getInt("job_lang")));
										
					jtable.setJobodem(rs.getString("job_odem"));
					jtable.setJoboget(rs.getString("job_oget"));
					
					list.add(jtable);
				}
				stmt.close();
				rs.close();
				return list;
			}
			catch(Exception e)
			{
				e.printStackTrace();
				return null;	
			}
		}	
	}	
	
	/*
	public jobTable getJob(String id)
	{
		jobTable jt = new jobTable();
		ResultSet rs = null;
		try
		{
			String sql = "select * from JOBS where id ="+id;
			stmt = this.conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if(rs.next())
			{
				jtable.setId(String.valueOf(rs.getInt("id")));
				jtable.setCopid(String.valueOf(rs.getInt("cop_id")));
				jtable.setJobname(rs.getString("job_name"));
				jtable.setJobmann(String.valueOf(rs.getInt("jop_mann")));
				jtable.setJobaddr(rs.getString("job_addr"));
				jtable.setJobnum(String.valueOf(rs.getString("job_num")));
				jtable.setJobget(rs.getString("job_get"));
				jtable.setJobsta(String.valueOf(rs.getInt("job_sta")));
				jtable.setJobend(String.valueOf(rs.getInt("job_end")));
				jtable.setJobgrad(String.valueOf(rs.getInt("job_grad")));
				jtable.setJobage1(String.valueOf(rs.getInt("job_age1")));
				jtable.setJobage2(String.valueOf(rs.getInt("job_age2")));
				jtable.setJobexpe(String.valueOf(rs.getInt("job_expe")));
				jtable.setJoblang(String.valueOf(rs.getInt("job_lang")));
									
				jtable.setJobodem(rs.getString("job_odem"));
				jtable.setJoboget(rs.getString("job_oget"));		
			}
			
			
			return jt;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
	}
	
	public copTable getCop(String id)
	{
		
	}
	*/
	
	
	public int copBind(String id)
	{
		try
		{
			ResultSet rs = null;
			String sql = "select * from COMPANY where user_id = "+id;
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();	
			if(rs.next())
			{
				
				stmt.close();
				rs.close();
				return 1;
			}
			else
			{
				stmt.close();
				rs.close();
				return 0;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;	
		}
	}
	
	public ResultSet getCopInfo(String id)
	{
		try
		{
			ResultSet rs = null;
			String sql = "select a.*,b.name from COMPANY a,USERS b where user_id = "+id+" and b.id = a.user_id";
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			return stmt.executeQuery();
		}
		catch(Exception e)
		{
			return null;
		}
	}
	
	public int editCop(copTable ctable)
	{
		try
		{
			String sql = "update COMPANY set cop_name=?,cop_mann=?,cop_tel=?,cop_mail=?,cop_tex=?,cop_addr=?,cop_zip=?,cop_www=?,cop_desc=?  where id=?";
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			
			stmt.setString(1,ctable.getCopname());
			stmt.setInt(2,Integer.parseInt(ctable.getCopmann()));
			stmt.setString(3,ctable.getCoptel());
			stmt.setString(4,ctable.getCopmail());
			stmt.setString(5,ctable.getCoptex());
			stmt.setString(6,ctable.getCopaddr());
			stmt.setString(7,ctable.getCopzip());
			stmt.setString(8,ctable.getCopwww());
			stmt.setString(9,ctable.getCopdesc());
			stmt.setInt(10,Integer.parseInt(ctable.getId()));
			stmt.executeUpdate();
			
			stmt.close();
			return 1;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
	}
	
	public int insertJob(jobTable jtable)
	{
		try
		{
			ResultSet rs = null;
			String sql = "select MAX(id) from JOBS";
			this.stmt = null;
			this.stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			int id = 0;
			if(rs.next())
			{
				id = rs.getInt(1) + 1;
			}
			rs.close();
			this.stmt = null;
			sql = "insert into JOBS(id,job_name,jop_mann,job_addr,job_num,job_get,job_sta,job_end,job_grad,job_age1,job_age2,job_expe,job_lang,job_odem,job_oget,cop_id)";
			sql += "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			this.stmt = conn.prepareStatement(sql);
			stmt.setInt(1,id);
			stmt.setString(2,jtable.getJobname());
			stmt.setInt(3,Integer.parseInt(jtable.getJobmann()));
			stmt.setString(4,jtable.getJobaddr());
			stmt.setInt(5,Integer.parseInt(jtable.getJobnum()));
			stmt.setString(6,jtable.getJobget());
			stmt.setInt(7,Integer.parseInt(jtable.getJobsta()));
			stmt.setInt(8,Integer.parseInt(jtable.getJobend()));
			stmt.setInt(9,Integer.parseInt(jtable.getJobgrad()));
			stmt.setInt(10,Integer.parseInt(jtable.getJobage1()));
			stmt.setInt(11,Integer.parseInt(jtable.getJobage2()));
			stmt.setInt(12,Integer.parseInt(jtable.getJobexpe()));
			stmt.setInt(13,Integer.parseInt(jtable.getJoblang()));
			stmt.setString(14,jtable.getJobodem());
			stmt.setString(15,jtable.getJoboget());
			stmt.setInt(16,Integer.parseInt(jtable.getCopid()));
			stmt.executeUpdate();
			stmt.close();
			return 1;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return 0;
		}
	}
	
	public int ifReply(String job_id,String user_id)
	{
		ResultSet rs = null;
		Statement st = null;
		try
		{
			String sql = "select * from REPLY a where a.job_id = "+job_id+" and a.user_id = "+user_id;
			st = this.conn.createStatement();
			rs = st.executeQuery(sql);
			if(rs.next())
			{
				st.close();
				rs.close();
				return 1;
			}
			else
			{
				st.close();
				rs.close();
				return 0;
			}
		}
		catch(Exception e)
		{
			return 0;
		}
	}
}