环境:
eclipse+mysql
jar:mysql-connector-java-5.1.38-bin.jar
1、在mysql中的库test中建表emp(可以是任意库):
CREATE TABLE `emp` (
`empno` int(4) NOT NULL,
`ename` varchar(10) DEFAULT NULL,
`job` varchar(20) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` float(7,2) DEFAULT NULL,
PRIMARY KEY (`empno`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
INSERT INTO `emp` VALUES ('1', '张三', '老板', '2016-04-04', '100.00');
INSERT INTO `emp` VALUES ('32', '李四', '经理', '2016-01-31', '99.00');
INSERT INTO `emp` VALUES ('12', '王五', '清洁工', '2016-05-06', '10000.00');
2、新建Dynamic Web Project项目,testmysql
3、配置数据库驱动程序,将mysql-connector-java-5.1.38-bin.jar放到项目的WebContent/WEB-INF/lib文件夹内
右键项目,Bulid path->Configure Bulid path 选择 Libraries 点击Add JARs 找到mysql-connector-java-5.1.38-bin.jar将它添加到项目中。
4、在WebContent目录下新建页面list_emp.jsp,页面内容如下:
<%@ page language="java" contentType="text/html; charset=GBK"
pageEncoding="GBK"%>
<%@ page import="java.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>数据库测试</title>
</head>
<body>
<%!//定义数据库驱动程序
public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
//数据库连接地址
public static final String DBURL = "jdbc:mysql://localhost:3306/test";//数据库连接
public static final String DBUSER = "root";//用户名
public static final String DBPASS = "root";//密码%>
<%
Connection conn = null;//声明数据库连接对象
PreparedStatement pstmt = null;//声明数据库操作
ResultSet rs = null;//声明数据库结果集
%>
<%
try {//捕获数据库操作异常信息
Class.forName(DBDRIVER);//数据库驱动程序加载
conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);//初始化数据库连接
String sql = "select empno,ename,job,sal,hiredate from emp";//sql语句
pstmt = conn.prepareStatement(sql);//实例化prepareStatement对象
rs = pstmt.executeQuery();//执行查询操作
%>
<center>
<table border="1" width="80%">
<tr>
<td>雇员编号</td>
<td>雇员姓名</td>
<td>雇员工作</td>
<td>雇员工资</td>
<td>雇用日期</td>
</tr>
<%
while (rs.next()) {//循环emp表中的记录
int empno = rs.getInt(1);
String ename = rs.getString(2);
String job = rs.getString(3);
float sal = rs.getFloat(4);
Date hiredate = rs.getDate(5);
%>
<tr>
<!-- 输出雇员信息 -->
<td><%=empno%></td>
<td><%=ename%></td>
<td><%=job%></td>
<td><%=sal%></td>
<td><%=hiredate%></td>
</tr>
<%
}
%>
</table>
</center>
<%
} catch (Exception e) { //异常处理
System.out.println(e);
} finally { //关闭连接
rs.close();
pstmt.close();
conn.close();
}
%>
</body>
</html>
运行结果: