環境:
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> 中加入下列的參數
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();
}
}
}
}
沒有留言:
張貼留言