jsp接続データベース


<%@ page language="java" import="com.mchange.v2.c3p0.*"
	import="java.sql.*" contentType="text/html; charset=gbk"
	pageEncoding="gbk"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=gbk">
		<title>        c3p0   </title>
	</head>
	<body>
	<%
		Connection con=null;
    	Statement stmt=null;
    	ResultSet rs=null;
    	try{
		//       
		ComboPooledDataSource cpds=new ComboPooledDataSource();
		cpds.setDriverClass("com.mysql.jdbc.Driver");
		cpds.setJdbcUrl("jdbc:mysql://localhost:3306/bookdb");
		cpds.setUser("root");
		cpds.setPassword("888");
		
		//           
		con=cpds.getConnection();
		
		//      
		stmt=con.createStatement();
		
		//      
		rs=stmt.executeQuery("select * from books");
		
		//      
		out.print("<table border=1 align='center'>");
		out.print("<tr><td>  </td><td>  </td><td>  </td><td>  </td><td>    </td><td>    </td><td>  </td></tr>");
		while(rs.next()){
    			String col1=rs.getString(1);
    			String col2=rs.getString(2);
    			String col3=rs.getString(3);
    			float col4=rs.getFloat(4);
    			int col5=rs.getInt(5);
    			String col6=rs.getString(6);
    			int col7=rs.getInt(7);
    			
    			//        
    			out.print("<tr><td>"+col1+"</td><td>"+col2+"</td><td>"+col3+"</td><td>"+col4+"</td><td>"+col5+"</td><td>"+col6+"</td><td>"+col7+"</td></tr>");
    		}
    		out.print("</table>");
    	}catch(Exception e){
    		//            
    		out.print(e.getMessage());
    	}
	 %>
	</body>
</html>

 
 
c3p0jstlReSourceTest.jsp
<%@ page language="java" contentType="text/html; charset=gbk" pageEncoding="gbk"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gbk">
<title>  c3p0   jstl   sql</title>
</head>
<body>
	<sql:query var="rs" dataSource="c3p0/bookdb">select * from books</sql:query>
	
	<table border=1 align='center'>
    <tr><td>  </td><td>  </td><td>  </td><td>  </td><td>    </td><td>    </td><td>  </td></tr>
    	<c:forEach var="row" items="${rs.rows}">
    		<tr>
    		<td>${row.id}</td>
    		<td>${row.name}</td>
    		<td>${row.title}</td>
    		<td>${row.price}</td>
    		<td>${row.yr}</td>
    		<td>${row.description}</td>
    		<td>${row.saleAmount}</td>
    		</tr>
    	</c:forEach>
    </table>
</body>
</html>

 
 
MySqlデータベースへの接続のテスト
<%@ page language="java" import="java.util.*" import="java.io.*" import="java.sql.*" pageEncoding="gbk"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>    MySql   </title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
  </head>
  
  <body>
    <%	
    	Connection con=null;
    	Statement stmt=null;
    	ResultSet rs=null;
    	try{
    		//  MySql    
    		Class.forName("com.mysql.jdbc.Driver");
    		
    		//        
    		String dbUrl="jdbc:mysql://localhost:3306/bookdb";
    		String dbUser="root";
    		String dbPwd="888";
    		
    		//       
    		con=DriverManager.getConnection(dbUrl,dbUser,dbPwd);
    		
    		//      
    		stmt=con.createStatement();
    		
    		//       
    		rs=stmt.executeQuery("select * from books");
    		
    		//      
    		out.print("<table border=1 align='center'>");
    		out.print("<tr><td>  </td><td>  </td><td>  </td><td>  </td><td>    </td><td>    </td><td>  </td></tr>");
    		while(rs.next()){
    			String col1=rs.getString(1);
    			String col2=rs.getString(2);
    			String col3=rs.getString(3);
    			float col4=rs.getFloat(4);
    			int col5=rs.getInt(5);
    			String col6=rs.getString(6);
    			int col7=rs.getInt(7);
    			
    			//        
    			out.print("<tr><td>"+col1+"</td><td>"+col2+"</td><td>"+col3+"</td><td>"+col4+"</td><td>"+col5+"</td><td>"+col6+"</td><td>"+col7+"</td></tr>");
    		}
    		out.print("</table>");
    	}catch(Exception e){
    		//            
    		out.print(e.getMessage());
    	}finally{
    		rs.close();
    		stmt.close();
    		con.close();
    	}
     %>
  </body>
</html>

 
Oracleデータベースへの接続のテスト
 
<%@ page language="java" import="java.util.*" import="java.io.*" import="java.sql.*" pageEncoding="gbk"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>    oracle   </title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->
  </head>
  
  <body>
    <%	
    	Connection con=null;
    	Statement stmt=null;
    	ResultSet rs=null;
    	try{
    		//  MySql    
    		Class.forName("oracle.jdbc.driver.OracleDriver");
    		
    		//        
    		String dbUrl="jdbc:oracle:thin:@127.0.0.1:1521:fengniao";
    		String dbUser="system";
    		String dbPwd="";
    		
    		//       
    		con=DriverManager.getConnection(dbUrl,dbUser,dbPwd);
    		
    		//      
    		stmt=con.createStatement();
    		
    		//       
    		rs=stmt.executeQuery("select books.*, books.rowid from books");
    		
    		//      
    		out.print("<table border=1 align='center'>");
    		out.print("<tr><td>  </td><td>  </td><td>  </td><td>  </td><td>    </td><td>    </td><td>  </td></tr>");
    		while(rs.next()){
    			String col1=rs.getString(1);
    			String col2=rs.getString(2);
    			String col3=rs.getString(3);
    			float col4=rs.getFloat(4);
    			int col5=rs.getInt(5);
    			String col6=rs.getString(6);
    			int col7=rs.getInt(7);
    			
    			//        
    			out.print("<tr><td>"+col1+"</td><td>"+col2+"</td><td>"+col3+"</td><td>"+col4+"</td><td>"+col5+"</td><td>"+col6+"</td><td>"+col7+"</td></tr>");
    		}
    		out.print("</table>");
    	}catch(Exception e){
    		//            
    		out.print(e.getMessage());
    	}finally{
    		//rs.close();
    		stmt.close();
    		con.close();
    	}
     %>
  </body>
</html>

 
ReSourceTest.jsp
<%@ page language="java" import="java.sql.*" import="javax.naming.*" import="javax.sql.*"  contentType="text/html; charset=gbk"pageEncoding="gbk"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gbk">
<title>Insert title here</title>
</head>
<body>
	<%	
    	Connection con=null;
    	Statement stmt=null;
    	ResultSet rs=null;
    	try{
    		Context context=new InitialContext();
    		DataSource ds=(DataSource)context.lookup("java:comp/env/jdbc/bookdb");
    		
    		//       
    		con=ds.getConnection();
    		
    		//      
    		stmt=con.createStatement();
    		
    		//       
    		rs=stmt.executeQuery("select * from books");
    		
    		//      
    		out.print("<table border=1 align='center'>");
    		out.print("<tr><td>  </td><td>  </td><td>  </td><td>  </td><td>    </td><td>    </td><td>  </td></tr>");
    		while(rs.next()){
    			String col1=rs.getString(1);
    			String col2=rs.getString(2);
    			String col3=rs.getString(3);
    			float col4=rs.getFloat(4);
    			int col5=rs.getInt(5);
    			String col6=rs.getString(6);
    			int col7=rs.getInt(7);
    			
    			//        
    			out.print("<tr><td>"+col1+"</td><td>"+col2+"</td><td>"+col3+"</td><td>"+col4+"</td><td>"+col5+"</td><td>"+col6+"</td><td>"+col7+"</td></tr>");
    		}
    		out.print("</table>");
    	}catch(Exception e){
    		//            
    		out.print(e.getMessage());
    	}finally{
    		//rs.close();
    		//stmt.close();
    		//con.close();
    	}
     %>
</body>
</html>

 
apache-tomcat-6.0.18/conf/comtext.xmlでのデータソースの構成
に参加
 <Resource auth="Container" 
                description="DB Connection" 
                driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver"
    	          maxPoolSize="100" 
    	          minPoolSize="2" 
    	          acquireIncrement="1" 
    	          name="jdbc/Examination" 
    	          user="sa" 
    	          password="" 
    	          factory="org.apache.naming.factory.BeanFactory" 
    	          type="com.mchange.v2.c3p0.ComboPooledDataSource" 
    	          jdbcUrl="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=bookdb" /> 

 
 
関連ソース