JNDI连接mysql实例

1.优点：

  通过jndi访问抽象的资源。这样程序不至于与访问的资源耦合。比如用jndi数据库密码变了与程序无关。不需要改代码

2.步骤：

a.jar:

mySQL导入：mysql-connector-java-5.0.8.jar包

b.context.xml文件：

在WEB项目中的的META-INF中创建一个context.xml文件。

<?xml version='1.0' encoding='utf-8'?>

<Context reloadable="true" crossContext="true">

 <!-- Default set of monitored resources -->
 <WatchedResource>WEB-INF/web.xml</WatchedResource>

 <Resource 
 name="jdbc/sysadmin1" 
 auth="Container" 
 type="javax.sql.DataSource" 
 driverClassName="com.mysql.jdbc.Driver" 
 url="jdbc:mysql://dbserver:3306/theuser" 
 username="root" 
 password="root1234" 
 maxActive="100" 
 maxIdle="20" 
 maxWait="10000" 
 validationQuery="select 1" 
 removeAbandoned="true" 
 removeAbandonedTimeout="3600" />

</Context>

c.非spring项目中：

public class DbConnection {
	
	static Logger logger = Logger.getLogger("theuser.dbconnection");
	
	//连接池:
	public static Connection getDbConnection() {
		 Connection conn = null;
		 try {
			InitialContext ctx=new InitialContext();
			Context envCtx = (Context)ctx.lookup("java:comp/env");
			DataSource ds=(DataSource)envCtx.lookup("jdbc/theuser");
			conn = ds.getConnection();
			
		 }catch (Exception e) {
			 logger.info(e.getMessage());
		 }
		 
		return conn;
	 }

	//关闭数据库连接:
	public static void CloseCon(Connection con,PreparedStatement ps,ResultSet rs) {
		 //rs:
		 if(rs != null) {
			try {
				 rs.close(); rs = null;
			} catch (Exception e) {
				logger.info(e.getMessage());
			}
		 }
		 //ps:
		 if(ps != null) {
			 try {
				 ps.close(); ps = null;
			} catch (Exception e2) {
				logger.info(e2.getMessage());
			}
		 }
		 //con:
		 if(con != null) {
			try {
				con.close(); con = null;
			} catch (Exception e3) {
				logger.info(e3.getMessage());
			}
		 }
	}
}

d.外部调用：

	//搜索记录数量:
	public int SearchHyInfoNum(Connection conn,String osql) throws Exception {
		int res = 0;
		String sql = "select count(n.id) from ws_notice n,news_sort s " +
					 "where n.ntype=s.id and s.sort_pid=(select id from news_sort where sort_flag='hyzixun')"+osql;
		
		ps = conn.prepareStatement(sql);
		rs = ps.executeQuery();
		if(rs.next()) {
			res = rs.getInt(1);
		}
		return res;
	}

	//获取大图信息(eg:资讯首页):
	public Vector GetBigPicShow_conn(Connection conn,String sortflag,int topnum)
	{
		String sql = "select n.id,n.ntitle,n.npic from ws_notice n,news_sort s " +
		 			 "where n.ntype=s.id and s.sort_pid = (select id from news_sort where sort_flag=?) and " +
		 			 "n.bigshow = 1 and n.npic != '' " +
		 			 "order by n.ifding desc,n.nsendate desc limit "+topnum;
		
		Vector vec = new Vector();
		NoticeBean nb = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sortflag);
			rs = ps.executeQuery();
			while(rs.next()) {
				nb = new NoticeBean();
				nb.setId(rs.getInt("id"));
				nb.setNtitle(rs.getString("ntitle"));
				nb.setNpic(Constants.zx_pic + rs.getString("npic"));				
				vec.add(nb);
			}
		}catch(Exception ex) {
			logger.info(ex.toString());
		}finally {
			DbConnection.CloseCon(null, ps, rs);
		}
		return vec;
	}