jsp连接mysql

时间：2022-03-14 01:28

下载mysql-connector-java-5.1.26-bin.jar驱动程序。

复制粘贴到目录下。web-inf/lib下

数据库：

--删除数据库
DROP DATABASE IF EXISTS mldn ;
--创建数据库
CREATE DATABASE mldn CHARACTER SET UTF8 ;
--使用数据库
USE mldn ;
--删除数据表 
DROP TABLE IF EXISTS emp ;
--创建数据表
CREATE TABLE emp(
   empno            INT(4)            PRIMARY KEY,
   ename            VARCHAR(10),
   job                VARCHAR(9),
   hiredate            DATE,
   sal                FLOAT(7,2)
) ;
--插入测试数据
INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (6060,‘李‘,‘经理‘,‘2001-09-16‘,2000.30) ;
INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (7369,‘楠‘,‘销售‘,‘2003-10-09‘,1500.90) ;
INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (8964,‘祺‘,‘分析员‘,‘2003-10-01‘,3000) ;
INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (7698,‘惠‘,‘销售‘,‘2005-03-12‘,800) ;
INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (7782,‘军‘,‘分析员‘,‘2005-01-12‘,2500) ;
INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (7762,‘明‘,‘销售‘,‘2005-03-09‘,1000) ;
INSERT INTO emp (empno,ename,job,hiredate,sal) VALUES (7839,‘月‘,‘经理‘,‘2006-09-01‘,2500) ;

jsp页面

<%@ page contentType="text/html" pageEncoding="GBK"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><title>www.mldnjava.cn，MLDN高端Java培训</title></head>
<body>
<%!
    public static final String DBDRIVER = "com.mysql.jdbc.Driver" ;
    public static final String DBURL = "jdbc:mysql://localhost:3306/mldn" ;
    public static final String DBUSER = "root" ;
    public static final String DBPASS = "lcp8090" ;
%>
<%
    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" ;
    pstmt = conn.prepareStatement(sql) ;
    rs = pstmt.executeQuery() ;
%>
<center>
<table border="1" width="80%">
    <tr>
        <td>雇员编号</td>
        <td>雇员姓名</td>
        <td>雇员工作</td>
        <td>雇员工资</td>
        <td>雇佣日期</td>
    </tr>
<%
    while(rs.next()){
        int empno = rs.getInt(1) ;
        String ename = rs.getString(2) ;
        String job = rs.getString(3) ;
        float sal = rs.getFloat(4) ;
        java.util.Date date = rs.getDate(5) ;
%>
    <tr>
        <td><%=empno%></td>
        <td><%=ename%></td>
        <td><%=job%></td>
        <td><%=sal%></td>
        <td><%=date%></td>
    </tr>    
<%
    }
%>
</table>
</center>
<%
}catch(Exception e)    {
    System.out.println(e) ;
}finally{
    rs.close() ;
    pstmt.close() ;
    conn.close() ;    // 如果直接关闭连接也可以
}
%>
</body>
</html>