BloggerAds

2008年11月14日 星期五

[JNDI] Java + Tomcat + MySQL 的連結設定

From: Polin Wei

環境:
Web Server: apache-tomcat-6.0.14
DB Version: MySQL 5.0.45
DB : test
Table: users(username varchar(30),password varchar(30))
JDBC Driver: mysql-connector-java-5.1.7-bin.jar
JAVA: jdk1.6.0_07


在Web步驟如下:
Step 1: Copy MySQL Connector/J 的 JAR 檔
mysql-connector-java-5.1.7-bin.jar 複製到 $TOMCAT_HOME/lib

Step 2:在 $TOMCAT_HOME/conf/server.xml 的 <Context></Context> 中加入下列的參數
<Resource name="jdbc/MySQLDS01" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/test" username="username" password="password" maxActive="20" maxIdle="10" maxWait="-1" />




Step 3: 撰寫一個網頁 MySQLConnTest.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import="com.mysql.*,java.util.*,javax.naming.*,java.sql.*,javax.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=UTF-8">
<title>Insert title here</title>
</head>
<body>
<%    
    Context ctx = new InitialContext();     
    String strLookup = "java:comp/env/jdbc/MySQLDS01";
    DataSource ds =(DataSource) ctx.lookup(strLookup);
    Connection con = ds.getConnection();
    Statement smt = con.createStatement();
    String sql = "";
    ResultSet rs = null;   
     
    sql = "SELECT * FROM users";
    rs = smt.executeQuery(sql);
    while(rs.next())
    {
         out.println(rs.getString(1));
         out.println(rs.getString(2));        
    }


%>


</body>
</html>




這樣就可以利用 JNDI 的方式連到 MySQL , 並在網頁中顯示資料庫test中表格users裡的資料.

若是純以 Java Code 來執行連結MySQL , 則程式碼如下:

package com.demos;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class MySQLConnTest {


 /**
  * @param args
  */
 public static void main(String[] args) {
  // TODO Auto-generated method stub
  Connection conn = null;
  String Server_IP = "127.0.0.1";


  try {
   Class.forName("com.mysql.jdbc.Driver");
   String sql = "";
   conn = DriverManager.getConnection("jdbc:mysql://" + Server_IP
     + ":3306/test", "username", "password");
   sql = "select * from users";
   // Statement stmt =
   // conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
   // ResultSet.CONCUR_READ_ONLY);
   Statement stmt = conn.createStatement();
   ResultSet rs = stmt.executeQuery(sql);
   if (rs.next()) {
    System.out.println(rs.getString("userName"));
    System.out.println(rs.getString("password"));
   } else {
    System.out.print("Cound not find data in test of MySQL");
   }
  } catch (Exception e) {
   // TODO: handle exception
   e.printStackTrace();
   try {
    conn.rollback();
   } catch (SQLException e1) {
    e1.printStackTrace();
   }
  } finally {
   try {
    if (conn != null && !conn.isClosed()) {
     conn.close();
    }
   } catch (Exception e) {
    e.printStackTrace();
   }
  }


 }


}



沒有留言:

張貼留言