一.Java程序如何操作oracle
1.配odbc数据源,用jdbc_odbc桥连接方式连接数据库
注意:odbc只能本地连接,不能远程连接,即java程和Oracle数据库必须在同一台机器上。
控制面板->管理工具->数据源[ODBC],添加->选择Oracle in OraHome90->完成
Data Source Name:test
TNS Service Name:选择Oracle的服务
建议点击Test Connection弹出弹出框,输入用户名、密码测试连接是否OK。
Java代码:
package com.bijian.study;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class TestOra {
public static void main(String[] args) {
try {
//1.加载驱动
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//2.得到连接
Connection ct = DriverManager.getConnection("jdbc:odbc:test","scott","tigger");
Statement sm = ct.createStatement();
ResultSet rs = sm.executeQuery("select * from emp");
while(rs.next()) {
//打印用户名
System.out.println("用户名:" + rs.getString(2));
}
//关闭打开的资源
rs.close();
sm.close();
ct.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
2.jdbc连接oracle
首先需将oracle驱动包加至工程中。
package com.bijian.study;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class TestOra2 {
public static void main(String[] args) {
try {
//1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到连接
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","scott","tigger");
Statement sm = ct.createStatement();
ResultSet rs = sm.executeQuery("select * from emp");
while(rs.next()) {
//打印用户名
System.out.println("用户名:" + rs.getString(2));
}
//关闭打开的资源
rs.close();
sm.close();
ct.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
3.oracle分页案例
分页显示emp表的用户信息
如果Tomcat默认的端口被占用,则打开apache-tomcat\conf\server.xml文件,将8080端口改为其它端口即可。
代码如下所示:
<%@ page language="java" import="java.util.*,java.sql.*" contentType="text/html; charset=gbk" pageEncoding="gbk"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>Insert title here</title> </head> <body> <h2>oracle分页案例</h2> <table> <tr> <td>用户名</td> <td>薪水</td> </tr> <% //1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //2.得到连接 Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","scott","tigger"); Statement sm = ct.createStatement(); int pageNow = 1; //接收pageNow String s_pageNow = (String)request.getParameter("pageNow"); if(s_pageNow != null) { pageNow = Integer.parseInt(s_pageNow); } //查询总页数 int pageCount = 0; //记录总条数 int rowCount = 0; //每页显示几条记录 int pageSize = 3; 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 a1.*,rownum rn from (select * from emp) a1 where rownum<=" + pageNow*pageSize + ") where rn>=" + ((pageNow-1)* pageSize + 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=MyTest.jsp?pageNow=" + i + ">[" + i + "] </a>"); } //关闭打开的资源 rs.close(); sm.close(); ct.close(); %> </table> </body> </html>
二.如何在oracle中操作数据
1.使用特定格式插入日期值
使用to_date函数
insert into emp values(9998,’小红’,’MANAGER’,7782,’11-11月-1988’,78.9,55.33,10);
insert into emp values(9997,’小红2’,’MANAGER’,7782,to_date(’1988-12-12’,’yyyy-mm-dd’),78.9,55.33,10);
insert into emp values(9996,’小红3’,’MANAGER’,7782,to_date(’1988/12/12’,’yyyy/mm/dd’),78.9,55.33,10);
2.使用子查询插入数据
当使用values子句时,一次只能插入一行数据,当使用子查询插入数据时,一条insert语句可以插入大量的数据。当处理行迁移或者装载外部表的数据到数据库时,可以使用子查询来插入数据。
create table kkk(myId nuber(4),myname varchar2(50),myDept number(5));
insert into kkk(myId,myname,myDept) select empno,ename,deptno from emp where deptno=10;
3.使用子查询更新数据
使用update语句更新数据时,既可以使用表达式或者数值直接修改数据,也可以使用子查询修改数据。
希望员工scott的岗位、工资、补助与SMITH员工一样。
update emp set job=(select job from emp where ename=’SMITH’), sal=(select sal from emp where ename=’SMITH’), comm.=(select comm. from emp where ename=’SMITH’);
应该这样写:
update emp set (job,sal,comm) = (select job,sal,comm. from emp where ename=’SMITH’) where ename=’SCOTT’;
三.oracle事务处理
1.事务用于保证数据的一致性,它由一组相关的dml语句组成,该组的dml语句要么全部成功,要么全部失败。如网上转账就是典型的要用事务来处理,用以保证数据的一致性。
2.当执行事务操作时(dml语句),oracle会在被作用的表上加锁,防止其它用户改表的结构。
3.当执行使用commit语句可以提交事务。当执行了commit语句后,会确认事务的变化、结束事务、删除保存点、释放锁,当使用commit语句结束事务后,其它会话将可以查看到事务变化后的新数据。
设置保存点:savepoint a1; 删除操作:delete from emp where empno=9996; 再设置保存点:savepoint a2; 误删除操作:delete from emp where empno=9999; 回滚至a2:rollback to a2; 回滚至a1:rollback to a1;
4.事务的几个重要操作
a.设置保存点:savepint a;
b.取消部分事务:rollback to a;
c.取消全部事务:rollback
5.java程序中如何使用事务
ct.setAutoCommit(false);
ct.commit();
ct.rollback();
6.只读事务
只读事务是指只允许执行查询的操作,而不允许执行任何其它dml操作的事务,使用只读事务可以确保用户只能取得某时间点的数据。假定机票代售点每天18点开始统计今天的销售情况,这时可以使用只读事务。在设置了只读事务后,尽管其它会话可能会提交新的事务,但是只读事务将不会取得最新数据的变化,从而可以保证取得特定时间点的数据信息。
设置只读事务:set transaction read only
四.sql函数的使用
1.字符函数
字符函数是oracle中最常用的函数。
lower(char):将字符串转化为小写的格式 upper(char):将字符串转化为大写的格式 length(char):返回字符串的长度 substr(char,m,n):取字符串的子串 replace(char1,search_string,replace_string) instr(char1,char2,[,n[,m]]):取子串在字符串的位置
1)将所有员工的名字按小写的方式显示
select lower(ename),sal from emp;
2)将所有员工的名字按大写的方式显示
select upper(ename),sal from emp;
3)显示正好为5个字符的员工的姓名
select * from emp where length(ename)=5;
4)显示所有员工姓名的前三个字符
select substr(ename,1,3) from emp;
说明:substr(列名,从第几位取,取多少个)
5)以首字母大写的方式显示所有员工的姓名
select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;
6)以首字线小写的方式显示所有员工的姓名
select lower(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;
replace(char1,search_string,replace_string)
instr(char1,char2,[,n[,m]]取子串在字符串的位置
7)显示所有员工的姓名,用a替换所有”A”
select replace(ename,’A’,’a’) from emp;
2.数学函数
数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round。
round(n,[m]):用于执行四舍五入,如果省掉m,则四舍五入到整数;如果m是正数,则四舍五入到小数点的m位后;如果m是负数,则四舍五入到小数点的m位前。 trunc(n,[m]):用于截取数字,如果省掉m,就截去小数部分,如果m是正数就截取到小数点的m位后,如果m是负数,则截取到小数点的前m位 mod(m,n) floor(n):返回小于或是等于n的最大整数 ceil(n):返回大于或是等于n的最小整数 abs(n):返回数字n的绝对值 acos(n):返回数字的反余弦值 asin(n):返回数字的反正弦值 atan(n):返回数字的反正切 cos(n) exp(n):返回e的n次幂 log(m,n):返回对数值 power(m,n):返回m的n次幂
1)显示在一个月为30天的情况所有员工的日薪金,忽略余数
select trunc(sal/30),ename from emp;
select floor(sal/30),ename from emp;
2)实例
select round(sal) from emp where ename=’SMITH’;
select round(comm,1),comm from emp where ename=’SMITH’;
select trunc(comm,1),comm. from emp where ename=’SMITH’;
select trunc(comm),trunk(comm,-1),comm. from emp where ename=’SMITH’;
select floor(comm),comm from emp where ename=’SMITH’;
select ceil(comm),comm from emp where ename=’SMITH’;
select mod(10,2) from dual;--在做oracle测试时,可以用dual(哑原表)
3)select abs(-13) from dual;
3.日期函数
日期函数用于处理date类型的数据,默认情况下日期格式是dd-mon-yy,即12-7月-78
sysdate:返回系统时间select sysdate from dual;
add_months(d,n)
last_day(d):返回指定日期所在月份的最后一天
1)查询已经入职8个多月的员工
select * from emp where sysdate>add_months(hiredate,8);
2)显示满10年服务年限的员工的姓名和受雇日期
select * from emp where sysdate>=add_months(hiredate,12*10);
3)对于每个员工,显示加入公司的天数
select trunc(sysdate-hiredate) “入职天数”,ename from emp;
4)找出各月倒数第3天受雇的所有员工
select hiredate,ename from emp where last_day(hiredate)-2=hiredate;
4.转换函数
转换函数用于将数据类型从一种转为另外一种,在某此情况下,oracle server允许值的数据类型和实际的不一样,这时oracle server会隐含的转化数据类型
比如:
1)create table t1(id int);
insert into t1 values(‘10’)--这样oracle会自动的将’10’转为10
2)create table t2(id varchar2(10));
insert into t2 (id varchar2(10));--这样oracle就会自动的将1转为’1’
尽管oracle可以进行隐含的数据类型的转换,但是它并不适应所有的情况,为了提高程序的可靠性,我们应该使用转换函数进行转换。
3)to_char
可以使用select ename,hiredate,sal from emp where deptnoo=10;显示信息,可是,在某些情况下,这个并不能满足你的需求。
a.日期是否可以显示时分秒?
select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’) from emp;
yy:两位数字的年份2004->04 yyyy:四位数字的年份2004年 mm:两位数字的月份8月->08 dd:2位数字的天,30号->30 hh24:8点->20 hh12:8点->08 mi、ss:显示分钟、秒
b. 薪水是否可以显示指定的货币符号?
9:显示数字,并忽略前面0 0:显示数字,如位数不足,则用0补齐 .:在指定位置显示小数点 ,:在指定位置显示逗号 $:在数字前加美元 L:在数字前加本地货币符号 C:在数字前加国际货币符号 G:在指定位置显示组分隔符 D:在指定位置显示小数点符号(.)
select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’),to_char(sal,’L99999.99’) from emp;
select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’),to_char(sal,’L99,999.99’) from emp;
select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’),to_char(sal,$99,999.99’) from emp;
显示1980年入职的所有员工
select * from emp where to_char(hiredate,’yyyy’)=1980;
显示所有12月份入职的员工
select * from emp where to_char(hiredate,’mm’)=12;
5.系统函数
sys_context
1)terminal:当前会话客户所对应的终端的标识符
2)language:语言
3)db_name:当前数据库名称
4)nls_date_format:当前会话客户所对应的日期格式
5)session_user:当前会话客户所对应的数据库用户名
6)current_schema:当前会话客户所对应的默认方案名
7)host:返回数据库所在主机的名称
通过该函数,可以查询一些重要信息,比如你怎在使用哪个数据库?
select sys_context(‘userenv’,’db_name’) from dual;
select sys_context(‘userenv’,’language’) from dual;
select sys_context(‘userenv’,’session_user’) from dual;
select sys_context(‘userenv’,’current_schema’) from dual;