realo.java

通过jsp+servlet+bean实现数据库查询

package real;

import java.rmi.RemoteException;
import java.sql.*;
import java.util.ArrayList;
import bean.UserBean;
import shared.ConnPool;
import shared.MessageBean;
//import javax.sql.*;



public class RealO {
	private ConnPool objConnPool = null;
	
	public RealO()
	{		
		String url = "jdbc:oracle:thin:@192.168.0.80:1521:BCSP";
		String name = "BCSP";
		String Pwd = "BCSP";
		int maxCount = 20;
		System.out.println("==============ConnPool=============");
		objConnPool = new ConnPool(url,name,Pwd, maxCount);
	}

	
	public MessageBean Login( UserBean pUserBean) 
	{
		MessageBean objMessageBean = new MessageBean();
		String sql = "";
		sql ="select id,username from STUDENT_TEST where " 
			+"username = '"+pUserBean.getUserName()+"' and password = '"+pUserBean.getPassword()+"'";
			
		System.out.println(sql);
		try
		{
			Connection conn = objConnPool.getConnection();
			conn.setAutoCommit(false);
			PreparedStatement pStmt = conn.prepareStatement(sql);
			ResultSet rs = pStmt.executeQuery();
			if(rs.next())
			{
						UserBean objUserBean = new UserBean();
						objUserBean.setId(rs.getInt(1));
						objUserBean.setUserName(rs.getString(2));
						
						objMessageBean = new MessageBean(MessageBean.OK, objUserBean);
					}
					else
					{
						objMessageBean = new MessageBean(MessageBean.Failed, "用户名或者密码错误！");
					}
	
		rs.close();				
		pStmt.close();	
		conn.close();}
		catch(Exception Exp)
		{
			Exp.printStackTrace();
		}
		                                                            
		return objMessageBean;	
	}
					
	public MessageBean SelectUser()
	{
		MessageBean objMessageBean = new MessageBean();
		ArrayList alist = new ArrayList();
		String sql = "";
		
			sql = "select * from STUDENT_SCORE where name <> 'admin' order by id desc";
		
	  System.out.println(sql);
		try
		{
			Connection conn = objConnPool.getConnection();
			conn.setAutoCommit(false);
			PreparedStatement pStmt = conn.prepareStatement(sql);
			ResultSet rs= pStmt.executeQuery();		
			
				while(rs.next())
				{	
					UserBean objUserBean = new UserBean();
					objUserBean.setId(rs.getInt(1));
				    objUserBean.setUserName(rs.getString(2));
				    objUserBean.setChinese(rs.getInt(3));
					objUserBean.setMaths(rs.getInt(4));
					objUserBean.setEnglish(rs.getInt(5));
					
					
					System.out.println(rs.getInt(1));
					System.out.println(rs.getString(2));
					System.out.println(rs.getString(3));
					System.out.println(rs.getInt(4));
					System.out.println(rs.getInt(5));
					
					alist.add(objUserBean);
				}
				objMessageBean.setMsg(alist);
				objMessageBean.setStatus(MessageBean.OK);
			
			
		rs.close();				
		pStmt.close();	
		conn.close();
		}
		catch(Exception Exp)
		{
			Exp.printStackTrace();
		}
		
		return objMessageBean;	
	}

	public MessageBean ManagerUser(int pType,UserBean pUserBean) throws RemoteException
	{
		MessageBean objMessageBean = null;
		String Sql = "";
		switch(pType)
		{
			case 3: //修改用户
				Sql = "update STUDENT_SCORE set name= ?, chinese = ?, " +
			     "maths = ?, english = ?  where id = '"+pUserBean.getId()+"'";
				break;
			case 2://添加用户
				Sql = "insert into STUDENT_SCORE(id, name, chinese, maths, english) values(STUDENT_SCORE_SEQ.nextval,?,?,?,?)" ;
				break;
			case 4://删除用户
				Sql = "delete STUDENT_SCORE where id = '"+pUserBean.getId()+"'";
				break;
		}
		System.out.println(Sql);
		try
		{
			Connection conn = objConnPool.getConnection();
			conn.setAutoCommit(false);
			//SQL 语句被预编译并且存储在 PreparedStatement 对象中
			PreparedStatement pStmt = conn.prepareStatement(Sql);
			switch(pType)
			{
			  case 2://添加用户
				case 3://修改用户
					//将指定参数设置为给定 Java String 值,将此值发送给数据库
				  //pStmt.setInt(1,pUserBean.getId());  
				  pStmt.setString(1,pUserBean.getUserName());  
				  pStmt.setInt(2,pUserBean.getChinese());
				  pStmt.setInt(3,pUserBean.getMaths());	
				  pStmt.setInt(4,pUserBean.getEnglish());
				  

					if(pStmt.executeUpdate() > 0)    
					{		
						conn.commit();
						objMessageBean = new MessageBean(MessageBean.OK,null);
						System.out.println("=============");
					}
			    else
			    {
			    	objMessageBean = new MessageBean(MessageBean.Failed,null);
			    }			
					break;
			  case 4://删除用户
					if(pStmt.executeUpdate() > 0)    
					{		
						conn.commit();
						objMessageBean = new MessageBean(MessageBean.OK,null);
					}
					   
			 break;
			}
			conn.commit();
			pStmt.close();
			conn.close();
		}
		catch (Exception sqlExp)
		{
			sqlExp.printStackTrace();
		}		
		return objMessageBean;
	}


//测试





public static void main (String[] args)
{
RealO p =new RealO();
UserBean pUserBean=new UserBean();
pUserBean.setUserName("amao");
pUserBean.setPassword("1");
p.Login(pUserBean);

//p.SelectUser(2);

 

}
}