JSP数据库连接操作

来源：互联网发布：网站php源代码在哪编辑：程序博客网时间：2024/05/18 22:44

将mysql.jar包复制到Tomcat的lib文件夹中

数据库创建脚本：

/*===========删除数据库=============*/drop database if exists zz;/*===========创建数据库=============*/create database zz;/*===========使用数据库=============*/use zz;/*===========删除数据表=============*/drop table if exists emp;/*===========创建数据表=============*/create table emp(empno int(8) primary key,ename varchar(20),job varchar(9),hiredate date,sal float(11,2));/*===========插入测试数据=============*/insert into emp(empno,ename,job,hiredate,sal)values(1001,'张三','CEO','2001-10-08',2000.30);insert into emp(empno,ename,job,hiredate, sal)values(1002,'李四','销售','2002-09-06',1500.30);insert into emp(empno, ename, job, hiredate, sal)values(1003,'王五','销售',2001-09-06',800);insert into emp(empno, ename, job, hiredate, sal)values(1004,'赵六','销售','2001-09-06',1000);insert into emp(empno, ename, job, hiredate, sal)values(1005,'孙七','分析员','2001-09-06',2500);insert into emp(empno, ename, job, hiredate, sal)values(1006, '钱八','经理','2001-09-06',2500);

创建表成功后内容：

使用JSP列出emp表的数据：

<%@ page language = "java" contentType = "text/html" pageEncoding = "GBK"%><%@ page import = "java.sql.*"%><html><head><title>连接数据库</title></head><body><%!public static final String DBDRIVAER = "org.gjt.mm.mysql.Driver";public static final String DBURL = "jdbc:mysql://localhost:3306/zz";public static final String DBUSER = "root";public static final String DBPASSWORD = "mysql";%><%Connection conn = null;PreparedStatement pstmt = null;ResultSet rs = null;%><%try {conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);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>

开启Tomcat服务器,运行结果：

遇到问题：

1、数据库表创建：
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'inser
t into emp(empno, ename, job, hiredate, sal)values(1001, '张三'，'经理' at line 9
错误：输入法汉字空格！！！

2、JSP运行错误：
java.lang.NullPointerException
问题主要出在sql语句错误：
String sql = "SELECT empno,ename,job,sal,hiredateFORM emp"; 可笑的错误！好吧，让我问了自己到底错在哪了近1个多小时。