java连数据库_JNDI方式

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.phome.servlet.mapper.IBeanMapper;

/**
 * 连接数据库并封装数据
 * 服务器配置信息:<conf/context.xml>
 * <Resource auth="Container"
 *  driverClassName="com.mysql.jdbc.Driver" maxActive="250" maxIdle="40"
 *  maxWait="4000" name="db_struts" password="root"
 *  type="javax.sql.DataSource"
 *  url="jdbc:mysql://localhost:3306/db_struts" username="root"/>
 * @author lenovo39
 *
 */
public class BaseDAO 
{
	//获取数据库连接
	protected Connection getConnection()
	{
		//JNDI方式获取数据库中的数据源
		
		try
		{
			Context context = new InitialContext();
			DataSource ds = (DataSource) context.lookup
					("java:comp/env/db_servlet");
			
			return ds.getConnection();
		} 
		catch (NamingException e)
		{
			e.printStackTrace();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		return null;
	}
	
	/**
	 * 封装表单查询数据
	 * @param sql
	 * 		查询的sql语句
	 * @param oList
	 * 		sql语句中?的值
	 * @param clazz
	 * 		封装数据的对象
	 * @return
	 * @throws SQLException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 */
	public <T> List<T> select(String sql,List<Object> oList,Class<T> clazz)
			throws SQLException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException
	{
		//1.创建查询的List集合
		List<T> list = new ArrayList<T>();
		
		//2.获取连接对象
		Connection conn = this.getConnection();
		
		//3.创建PS对象
		PreparedStatement ps = conn.prepareStatement(sql);
		
		//4.判断并给?赋值
		for(int i = 0;oList != null && i < oList.size();i++)
		{
			ps.setObject(i + 1, oList.get(i));
		}
		
		//5.执行查询,获取ResultSet对象
		ResultSet rs = ps.executeQuery();
		
		//6.获取结果集的元数据(即数据库中字段名)
		ResultSetMetaData rsmd = rs.getMetaData();
		
		//7.遍历并保存列名
		List<String> columnList = new ArrayList<String>();
		for(int i = 0;i < rsmd.getColumnCount();i++)
		{
			columnList.add(rsmd.getColumnName(i + 1));
		}
		
		//8.循环遍历记录
		while(rs.next())
		{
			//9.创建封装记录的对象
			T obj = clazz.newInstance();
			
			//10.遍历一条记录
			for(int i = 0;i < columnList.size();i++)
			{
				//获取列名
				String column = columnList.get(i);
				
				//根据列名创建对应的set方法
				String setMethod = "set" + column.substring(0, 1).toUpperCase()
						+ column.substring(1);
				
				//创建clazz中所有方法对应的method对象
				Method[] ms = clazz.getMethods();
				
				//遍历ms
				for(int j = 0;j < ms.length;j++)
				{
					//获取每一个method对象
					Method m = ms[j];
					
					//判断m中的方法名和用数据库中列名创建的方法名是否相同
					if(m.getName().equals(setMethod))
					{
						//调用set方法封装数据
						m.invoke(obj, rs.getObject(column));
						
						break;
					}
				}
			}
			
			list.add(obj);
	
		}
		this.close(rs,ps,conn);
		return list;
	}
	
	/**
	 * 可封装多条记录
	 * @param sql
	 * @param oList
	 * @param mapper
	 * 		servlet.mapper.IBeanMapper
	 * @return
	 * @throws Exception
	 */
	public <T> List<T> select(String sql,List<Object> oList,
			IBeanMapper<T> mapper) throws Exception
	{
		List<T> list = new ArrayList<T>();
		
		Connection conn = this.getConnection();
		
		PreparedStatement ps = conn.prepareStatement(sql);
		
		for(int i = 0;oList != null && i < oList.size();i++)
		{
			ps.setObject(i + 1, oList.get(i));
		}
		
		ResultSet rs = ps.executeQuery();
		
		while(rs.next())
		{
			T obj = mapper.rowMapper(rs);
			
			list.add(obj);
		}
		
		this.close(rs, ps, conn);
		
		return list;
	}
	
	/**
	 * 增删改的方法
	 * @param sql
	 * @param oList
	 * @return
	 * @throws Exception 
	 */
	public int executeUpdate(String sql,List<Object> oList) throws Exception
	{
		int rowCount = -1;
		
		Connection conn = this.getConnection();
		
		PreparedStatement ps = conn.prepareStatement(sql);
		
		for(int i = 0; oList != null && i < oList.size();i++)
		{
			ps.setObject(i + 1, oList.get(i));
		}
		
		rowCount = ps.executeUpdate();
		
		return rowCount;
	}
	
	/**
	 * 关闭资源的方法
	 * @param rs
	 * @param ps
	 * @param conn
	 */
	public void close(ResultSet rs,PreparedStatement ps,Connection conn)
	{
		try
		{
			if(rs != null)
			{
				rs.close();
				rs = null;
			}
			
			if(ps != null)
			{
				ps.close();
				ps = null;
			}
			
			if(conn != null)
			{
				conn.close();
				conn = null;
			}
			
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
}