JSP链接mysql数据库
一、jsp开发环境
1.JDK安装（参考http://blog.csdn.net/cnlht/article/details/7911664）
2.安装MySQL（安装包，可以到oracle网站下载）
3.安装Tomcat（参考：http://wenku.baidu.com/view/6683b97102768e9951e73894.html）
4.下载Eclipse（下载路径：http://www.eclipse.org/downloads/，推荐JavaEE 64位），并配置，开发web程序
二、具备mysql的链接包
1.下载包（见资源：http://download.csdn.net/detail/cnlht/5631917）
三、创建一个web程序，链接数据库，并读取数据表
1.新建web项目
2.在项目文件中建立lib文件夹，拷贝二中下载的mysql包到该目录。
3.设置（添加包到该项目中）
4.编写读取数据库的代码，显示在页面上。
1）编写的连接数据库的类DBConnection.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 数据库连接与设置
* @author Administrator
*
*/
public class DBConnection {
private Connection conn;
private String MySqlDriver ;
private String MySqlURL ;
public DBConnection(){
try{
MySqlDriver = "org.gjt.mm.mysql.Driver";
MySqlURL = "jdbc:mysql://localhost:3306/game?useUnicode=true&characterEncoding=utf-8";
Class.forName(MySqlDriver).newInstance();
this.conn = DriverManager.getConnection(MySqlURL,"root","123");
}catch(Exception e){
e.printStackTrace();
}
}
public String getMySqlDriver() {
return MySqlDriver;
}
public void setMySqlDriver(String mySqlDriver) {
MySqlDriver = mySqlDriver;
}
public String getMySqlURL() {
return MySqlURL;
}
public void setMySqlURL(String mySqlURL) {
MySqlURL = mySqlURL;
}
public Connection getConn() {
return conn;
}
public void setConn(Connection conn) {
this.conn = conn;
}
public static void main(String[] argc){
DBConnection DBConn = new DBConnection();
Connection conn;
ResultSet rs;
Statement stmt;
conn = DBConn.getConn();
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from chess");
while(rs.next()){
System.out.println(rs.getString(2));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("-------Exception!-----------");
//e.printStackTrace();
}
}
}
2）编写的查询和更新的类
package org.estong.dao;
import java.sql.*;
public class QueryBean {
private Connection conn;
private ResultSet rs=null;
public Statement stmt=null;
public QueryBean(){
DBConnection DBConn = new DBConnection();
conn = DBConn.getConn();
}
public ResultSet getQuery(String sqlstr){
try {
stmt = conn.createStatement(1004,1007);
rs = stmt.executeQuery(sqlstr);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
public boolean setModify(String sqlstr){
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
stmt.executeUpdate(sqlstr);
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
public static void main(String[] argc){
QueryBean q = new QueryBean();
ResultSet rs;
try {
//stmt = conn.createStatement(1004,1007);
rs = q.getQuery("select * from chess");
while (rs.next()){
System.out.println(rs.getString(2));
System.out.println("--------------------");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
3）jsp页面
package org.estong.dao;
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="org.estong.dao.DBConnection" %>
<%@ page import="org.estong.dao.QueryBean" %>
<!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>Game Management System - Show Chess</title>
<body>
<table width="100%">
<thead>
<tr>
<th width="20%"><a href="#">ID<img src="img/icons/arrow_down_mini.gif" width="16" height="16" align="absmiddle" /></a></th>
<th>Chess Name</th>
<th width="60px"><a href="#">Action</a></th>
</tr>
</thead>
<tbody>
<!-- 此处为读取game数据库，Chess表的内容 -->
<%
try{
rs = q.getQuery("select * from chess");
while(rs.next()) {
%>
<tr>
<td class="a-center"><%=rs.getInt("cId") %></td>
<td><a href="showChess.jsp?cId=<%=rs.getInt("cId")%>"><%=rs.getString("cName") %></a></td>
<td><a href="showChess.jsp?cId=<%=rs.getInt("cId")%>"><img src="img/icons/user.png" title="Show profile" width="16" height="16" /></a><a href="editChess.jsp?cId=<%=rs.getInt("cId")%>"><img src="img/icons/user_edit.png" title="Edit user" width="16" height="16" /></a><a href="#"><img src="img/icons/user_delete.png" title="Delete user" width="16" height="16" /></a></td>
</tr>
<%
}
rs.close();
}catch(Exception e){
}
%>
</tbody>
</table>
</body>
</html>
