<%@ page contentType="text/htmlcharset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/htmlcharset=gb2312">
<title>JSP连接Access数据库</title>
<style type="text/css">
<!--
.style1 {
font-size: 20px
font-weight: bold
}
-->
</style>
</head><body>
<div align="center" class="style1">JSP连接Access数据库</div>
<br>
<hr>
<p><%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")//载入驱动程序类别
Connection con = DriverManager.getConnection("jdbc:odbc:jspdata")//建立数据库链接,jspdata为ODBC数据源名称
//建立Statement对象
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY)
ResultSet rs = stmt.executeQuery("select * from lyb")//建立ResultSet(结果集)对象，并执行SQL语句
%>
</p>
<p align="center">NUMB1数据表中记录如下</p>
<table width="640" border="1" align="center" bordercolor="#7188e0">
<tr bgcolor="d1d1ff">
<th width="49">编号</th>
<th width="90">姓名</th>
<th width="126">E-mail</th>
<th width="221">网站</th>
<th width="80">QQ</th>
</tr>
<%
while(rs.next())
{
%>
<tr bgcolor="#f8f8f8">
<th><%= rs.getString(1) %></th>
<th><%= rs.getString(2) %></th>
<th><%= rs.getString(3) %></th>
<th bgcolor="#f6f6f8"><%= rs.getString(4) %></th>
<th><%= rs.getString(5) %></th>
</tr>
<%
}
rs.close()
stmt.close()
con.close()
%>
</table>
<p align="center"><br>
如果您能看到表格中的数据，说明连接数据库成功！</p>
</body>
</html>
1.安装所需要的数据库。2.导入sql语句。包含表结构,数据等。
3.导入所需要的jar包。
4.准备容器部署项目，如tomcat。
5.将项目部署到tomcat中，运行项目。
我给你我原来写过的吧。很久以前的了。只给你连接数据库这些，其他的你自己看着写吧。下面是我当初写的其中一个dao类，有注释。你看看，模仿着写吧，肯定能写出来。.
package com.dao
import java.sql.Connection
import java.sql.Date
import java.sql.DriverManager
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.SQLException
import java.sql.Statement
import java.util.List
import javax.servlet.jsp.jstl.sql.Result
import javax.servlet.jsp.jstl.sql.ResultSupport
import com.tool.BaseTool
public class BillDAO {
// 数据库连接
private Connection con
// 数据库SQL语句执行者对象
private Statement stmt
// 数据库SQL语句执行者对象
private PreparedStatement pstmt
// 数据库SQL查询结果集
private ResultSet rs
// 打开数据库连接
private void openConnection() {
try {
// 加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver")
// 获得数据库连接
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcllib", "zxl", "zxl")
} catch (ClassNotFoundException e) {
e.printStackTrace()
} catch (SQLException e) {
e.printStackTrace()
}
}
// 关闭数据库连接及资源
private void closeConnection() {
if (rs != null) {
try {
rs.close()
} catch (SQLException e) {
e.printStackTrace()
}
}
if (stmt != null) {
try {
stmt.close()
} catch (SQLException e) {
e.printStackTrace()
}
}
if (pstmt != null) {
try {
pstmt.close()
} catch (SQLException e) {
e.printStackTrace()
}
}
if (con != null) {
try {
con.close()
} catch (SQLException e) {
e.printStackTrace()
}
}
}
//检查账单编号是否存在的方法
@SuppressWarnings("finally")
public boolean checkBillNumber(long bill_number) {
boolean result = false
openConnection()
// 编写SQL语句
String sql = "select b.bill_number from bill b"
try {
// 实例化执行SQL语句的对象preparedstatement
stmt = con.createStatement()
// 执行查询语句并返回结果集
rs = stmt.executeQuery(sql)
while (rs.next()) {
if (bill_number == rs.getLong("bill_number")) {
return result
}
}
result = true
} catch (SQLException e) {
e.printStackTrace()
} finally {
// 关闭数据库连接及资源
closeConnection()
return result
}
}
//分页显示账单列表的方法
@SuppressWarnings("finally")
public Result getPagingBill(int page_number, int page_data_number) {
Result result = null
// 打开数据库连接
openConnection()
// 编写SQL语句
String sql = "select r.bill_number,r.goods_name,r.goods_number,r.transaction_amount," +
"r.value_name,r.supplier_name,r.goods_description,r.bill_date from " +
"(select rownum as rn,b.* from bill_list_view b) r where r.rn between "+
BaseTool.getPagingString(page_number, page_data_number)
try {
// 实例化执行SQL语句的对象preparedstatement
stmt = con.createStatement()
// 执行查询语句并返回结果集
rs = stmt.executeQuery(sql)
// 将结果集储存在Result对象当中
result = ResultSupport.toResult(rs)
} catch (SQLException e) {
e.printStackTrace()
} finally {
// 关闭数据库连接及资源
closeConnection()
return result
}
}
//获得账单总数的方法
@SuppressWarnings("finally")
public int getBillCount() {
int result = 0
// 打开数据库连接
openConnection()
// 编写SQL语句
String sql = "select count(b.bill_number) from bill_list_view b "
try {
// 实例化执行SQL语句的对象preparedstatement
stmt = con.createStatement()
// 执行查询语句并返回结果集
rs = stmt.executeQuery(sql)
// 取得账单总数
while (rs.next()) {
result = rs.getInt(1)
}
} catch (SQLException e) {
e.printStackTrace()
} finally {
// 关闭数据库连接及资源
closeConnection()
return result
}
}
//将用户组合查询的账单列表分页
@SuppressWarnings("finally")
public Result getFilterPagingBill(String goods_name, int pay_status ,
int page_number , int page_data_number){
Result result = null
// 打开数据库连接
openConnection()
// 编写SQL语句
String sql = "select r.bill_number,r.goods_name,r.goods_number,r.transaction_amount," +
"r.value_name,r.supplier_name,r.goods_description,r.bill_date " +
" from (select rownum as rn,b.* from bill_list_view b where "
String sql_last = ") r where r.rn between " + BaseTool.getPagingString(page_number, page_data_number)
// 将参数初始化
String value_name = ""
if (pay_status == 3)
value_name = "已付款"
else if (pay_status == 4)
value_name = "未付款"
else
value_name = null
if ("".equals(goods_name.trim()))
goods_name = null
// 按照用户查询的条件处理
try {
if (value_name != null &&goods_name == null) {
sql = sql + "b.value_name=?" + sql_last
pstmt = con.prepareStatement(sql)
pstmt.setString(1, value_name)
} else if (value_name == null &&goods_name != null) {
goods_name = "%"+goods_name+"%"
sql = sql + "b.goods_name like ?" + sql_last
pstmt = con.prepareStatement(sql)
pstmt.setString(1, goods_name)
} else {
goods_name = "%"+goods_name+"%"
sql = sql + "b.value_name=? and b.goods_name like ?" + sql_last
pstmt = con.prepareStatement(sql)
pstmt.setString(1, value_name)
pstmt.setString(2, goods_name)
}
rs = pstmt.executeQuery()
result = ResultSupport.toResult(rs)
} catch (SQLException e) {
e.printStackTrace()
} finally {
closeConnection()
return result
}
}
//获得用户组合查询的账单总数
@SuppressWarnings("finally")
public int getFilterBillCount(String goods_name, int pay_status) {
int result = 0
// 打开数据库连接
openConnection()
// 编写SQL语句
String sql = "select count(b.bill_number) from bill_list_view b where "
// 将参数初始化
String value_name = ""
if (pay_status == 3)
value_name = "已付款"
else if (pay_status == 4)
value_name = "未付款"
else
value_name = null
if ("".equals(goods_name.trim()))
goods_name = null
// 按照用户查询的条件处理
try {
if (value_name != null &&goods_name == null) {
sql = sql + "b.value_name=? "
pstmt = con.prepareStatement(sql)
pstmt.setString(1, value_name)
} else if (value_name == null &&goods_name != null) {
goods_name="%"+goods_name+"%"
sql = sql + "b.goods_name like ? "
pstmt = con.prepareStatement(sql)
pstmt.setString(1, goods_name)
} else {
goods_name="%"+goods_name+"%"
sql = sql + "b.value_name=? and b.goods_name like ? "
pstmt = con.prepareStatement(sql)
pstmt.setString(1, value_name)
pstmt.setString(2, goods_name)
}
rs = pstmt.executeQuery()
while(rs.next()){
result = rs.getInt(1)
}
} catch (SQLException e) {
e.printStackTrace()
} finally {
closeConnection()
return result
}
}
//根据账单编号获得账单详细信息的方法
@SuppressWarnings("finally")
public Result getBillForBillNumber(long bill_number){
Result result = null
// 打开数据库连接
openConnection()
// 编写SQL语句
String sql = "select b.* from bill_list_view b where b.bill_number="+bill_number
try {
// 实例化执行SQL语句的对象preparedstatement
stmt = con.createStatement()
// 执行查询语句并返回结果集
rs = stmt.executeQuery(sql)
// 将结果集储存在Result对象当中
result = ResultSupport.toResult(rs)
} catch (SQLException e) {
e.printStackTrace()
} finally {
// 关闭数据库连接及资源
closeConnection()
return result
}
}
//为账单增加数据的方法
@SuppressWarnings("finally")
public int updateBill(List<Object>values){
//用于返回执行结果
int result = 0
// 打开数据库连接
openConnection()
// 编写SQL语句
String sql = "insert into bill values (?,?,?,?,?,?,?,?)"
try {
// 实例化执行SQL语句的对象preparedstatement
pstmt = con.prepareStatement(sql)
//设置参数
for(int i=0,j=1i<values.size()i++,j++){
if(values.get(i) instanceof Integer){
pstmt.setInt(j, (Integer)values.get(i))
}else if(values.get(i) instanceof Long){
pstmt.setLong(j, (Long)values.get(i))
} else if(values.get(i) instanceof String){
pstmt.setString(j, (String)values.get(i))
}else{
pstmt.setDate(j, (Date)values.get(i))
}
}
//获得执行结果
result = pstmt.executeUpdate()
} catch (SQLException e) {
e.printStackTrace()
} finally {
// 关闭数据库连接及资源
closeConnection()
//返回执行结果
return result
}
}
//为账单修改数据的方法
@SuppressWarnings("finally")
public int saveBill(List<Object>values){
//用于返回执行结果
int result = 0
// 打开数据库连接
openConnection()
// 编写SQL语句
String sql = "update bill b set b.goods_name=?,b.goods_number=?,b.transaction_amount=?," +
"b.pay_status=?,b.supplier_number=?,b.goods_description=?,b.bill_date=? where b.bill_number=?"
try {
// 实例化执行SQL语句的对象preparedstatement
pstmt = con.prepareStatement(sql)
//设置参数
for(int i=0,j=1i<values.size()i++,j++){
if(values.get(i) instanceof Integer){
pstmt.setInt(j, (Integer)values.get(i))
}else if(values.get(i) instanceof Long){
pstmt.setLong(j, (Long)values.get(i))
}else if(values.get(i) instanceof String){
pstmt.setString(j, (String)values.get(i))
}else{
pstmt.setDate(j, (Date)values.get(i))
}
}
//获得执行结果
result = pstmt.executeUpdate()
} catch (SQLException e) {
e.printStackTrace()
} finally {
// 关闭数据库连接及资源
closeConnection()
//返回执行结果
return result
}
}
//删除账单中数据的方法
@SuppressWarnings("finally")
public int deleteBill(long bill_number){
//用于返回执行结果
int result = 0
// 打开数据库连接
openConnection()
// 编写SQL语句
String sql = "delete bill b where b.bill_number=?"
try {
// 实例化执行SQL语句的对象preparedstatement
pstmt = con.prepareStatement(sql)
//设置参数
pstmt.setLong(1, bill_number)
//获得执行结果
result = pstmt.executeUpdate()
} catch (SQLException e) {
e.printStackTrace()
} finally {
// 关闭数据库连接及资源
closeConnection()
//返回执行结果
return result
}
}
}
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）