JSP数据库连接操作

最新推荐文章于 2023-07-11 19:54:55 发布

硬骨头_children

最新推荐文章于 2023-07-11 19:54:55 发布

阅读量845

点赞数

分类专栏：前端文章标签：数据库 jsp insert string database sql

本文链接：https://blog.csdn.net/zhangze2/article/details/7841570

版权

前端专栏收录该内容

21 篇文章 0 订阅

订阅专栏

将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个多小时。