java操作oracle
java操作oracle的两种方式:桥连接方式和jdbc方式
1.桥接方式连接oracle
try{
//1.加载驱动
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//2.得到连接,testsp为配置的数据源名,配置方法在后面将补充
Connection ct = DriverManager.getConnection("jdbc.odbc.testsp");
//3.创建Statement
Statement sm = ct.CreateStatement();
//4.执行
ResultSet rs = sm.executeQuery("select * from emp");
//5.导出数据
while(rs.next()){
System.out.printIn("用户名: "+rs.getString(2););
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//2.得到连接,testsp为配置的数据源名,配置方法在后面将补充
Connection ct = DriverManager.getConnection("jdbc.odbc.testsp");
//3.创建Statement
Statement sm = ct.CreateStatement();
//4.执行
ResultSet rs = sm.executeQuery("select * from emp");
//5.导出数据
while(rs.next()){
System.out.printIn("用户名: "+rs.getString(2););
}
}catch(Exception e){
e.printStackTrace();
}
e.printStackTrace();
}
数据源的配置方法:
打开目录:控制面板\所有控制面板项\管理工具\数据源(ODBC)
选择驱动:在用户DSN中点“添加”,选择驱动程序oracle in OraDb10g_home1
驱动命名:填写Data Source Name 以上命名为testsp
测试驱动:Test Connect
完成配置:点击OK完成
选择驱动:在用户DSN中点“添加”,选择驱动程序oracle in OraDb10g_home1
驱动命名:填写Data Source Name 以上命名为testsp
测试驱动:Test Connect
完成配置:点击OK完成
2.用jdbc连接oracle
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connect ct = DriverManager.getConnect("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","test","test");
Statement sm = ct.CreateStatement();
ResultSet rs = sm.executeQuery("select * from emp");
while(rs.next()){
System.out.printIn("用户名: "+getString(2));
}
}catch(Exception e){
e.printStackTrace();
}
System.out.printIn("用户名: "+getString(2));
}
}catch(Exception e){
e.printStackTrace();
}
注意需加载驱动包:
在安装目录D:\oracle\product\10.2.0\db_1\jdbc\lib中的classes12.jar包
在安装目录D:\oracle\product\10.2.0\db_1\jdbc\lib中的classes12.jar包
3.Jsp分页实例
创建web object: oracleFenye
创建jsp file: MyJsp,jsp
创建jsp file: MyJsp,jsp
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'MyJsp.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'MyJsp.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
This is my JSP page. <br>
<h2>oracle分页案例</h2>
<table>
<tr><td>用户名</td><td>薪水</td></tr>
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
<body>
This is my JSP page. <br>
<h2>oracle分页案例</h2>
<table>
<tr><td>用户名</td><td>薪水</td></tr>
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","SCOTT","SCOTT");
Statement sm = ct.createStatement();
//接收pageNow
String s_pageNow = (String)request.getParameter("pageNow");
int pageNow = 1;
if (s_pageNow != null){
pageNow = Integer.parseInt(s_pageNow);
}
//查询总页数
int pageSize = 4;
int rowCount = 0;
int pageCount =0;
ResultSet rs = sm.executeQuery("select count(*) from emp");
if(rs.next()){
rowCount = rs.getInt(1);
if(rowCount%pageSize == 0){
pageCount = rowCount/pageSize;
}
else{
pageCount = rowCount/pageSize + 1;
}
}
//接收pageNow
String s_pageNow = (String)request.getParameter("pageNow");
int pageNow = 1;
if (s_pageNow != null){
pageNow = Integer.parseInt(s_pageNow);
}
//查询总页数
int pageSize = 4;
int rowCount = 0;
int pageCount =0;
ResultSet rs = sm.executeQuery("select count(*) from emp");
if(rs.next()){
rowCount = rs.getInt(1);
if(rowCount%pageSize == 0){
pageCount = rowCount/pageSize;
}
else{
pageCount = rowCount/pageSize + 1;
}
}
rs = sm.executeQuery("select * from (select t1.*,rownum rn from
(select * from emp)t1 where rownum <="+pageSize*pageNow+")
where rn >="+(pageSize*(pageNow-1)+1)+"");
(select * from emp)t1 where rownum <="+pageSize*pageNow+")
where rn >="+(pageSize*(pageNow-1)+1)+"");
while(rs.next()){
out.println("<tr>");
out.println("<td>"+rs.getString(2)+"</td>");
out.println("<td>"+rs.getString(6)+"</td>");
out.println("</tr>");
}
//打印总页数
for (int i=1;i<=pageCount;i++){
out.print("<a href = MyJsp.jsp?pageNow="+i+">["+i+"] </a>");
}
rs.close();
sm.close();
ct.close();
%>
</table>
</body>
</html>
out.println("<tr>");
out.println("<td>"+rs.getString(2)+"</td>");
out.println("<td>"+rs.getString(6)+"</td>");
out.println("</tr>");
}
//打印总页数
for (int i=1;i<=pageCount;i++){
out.print("<a href = MyJsp.jsp?pageNow="+i+">["+i+"] </a>");
}
rs.close();
sm.close();
ct.close();
%>
</table>
</body>
</html>
4.to_date函数的使用
例如:‘11-11月-1988’等效于 to_date('1988-11-11','yyyy-mm-dd’)
等效与 to_date('1988/11/11','yyyy/mm/dd’)
等效与 to_date('1988/11/11','yyyy/mm/dd’)