ORACLEコマンド大全(2)
53770 ワード
：
DBMS_OUTPUT PL/SQL
DBMS_LOB LOB
DBMS_XMLQUERY XML
DBMS_RANDOM
UTL_FILE
===============================================================
Create or REPLACE TRIGGER biu_emp_deptno
BEFORE Insert or Update OF deptno
ON emp
FOR EACH ROW
WHEN (New.deptno <> 40)
BEGIN
:New.comm := 0;
END;
/
===============================================================
----
Create VIEW ord_view AS
Select order_master.orderno, order_master.ostatus,
order_detail.qty_deld, order_detail.qty_ord
FROM order_master, order_detail
Where order_master.orderno = order_detail.orderno;
Create or REPLACE TRIGGER order_mast_insert
INSTEAD OF Insert ON ord_view
REFERENCING NEW AS n
FOR EACH ROW
DECLARE
CURSOR ecur IS Select * FROM order_master
Where order_master.orderno = :n.orderno;
CURSOR dcur IS Select * FROM order_detail
Where order_detail.orderno = :n.orderno;
a ecur%rowtype;
b dcur%rowtype;
BEGIN
OPEN ecur;
OPEN dcur;
FETCH ecur into a;
FETCH dcur into b;
IF dcur%notfound THEN
Insert INTO order_master(orderno,ostatus)
VALUES(:n.orderno, :n.ostatus);
ELSE
Update order_master SET order_master.ostatus = :n.ostatus
Where order_master.orderno = :n.orderno;
END IF;
IF ecur%notfound THEN
Insert INTO order_detail(qty_ord,qty_deld,orderno)
VALUES(:n.qty_ord, :n.qty_deld, :n.orderno);
ELSE
Update order_detail
SET order_detail.qty_ord = :n.qty_ord,
order_detail.qty_deld = :n.qty_deld
Where order_detail.orderno = :n.orderno;
END IF;
CLOSE ecur;
CLOSE dcur;
END;
/
===============================================================
Create TABLE dropped_obj
(
obj_name VARCHAR2(30),
obj_type VARCHAR2(20),
drop_date DATE
);
Create or REPLACE TRIGGER log_drop_obj
AFTER Drop ON SCHEMA
BEGIN
Insert INTO dropped_obj
VALUES (ORA_DICT_OBJ_NAME, orA_DICT_OBJ_TYPE, SYSDATE);
END;
/
Alter TRIGGER biu_emp_deptno DISABLE;
Alter TRIGGER biu_emp_deptno ENABLE;
Drop TRIGGER biu_emp_deptno;
DESC USER_TRIGGERS;
===============================================================
DECLARE
l_num NUMBER;
counter NUMBER;
BEGIN
counter:=1;
WHILE counter <= 10
LOOP
l_num := ABS((DBMS_RANDOM.RANDOM MOD 100)) + 1;
DBMS_OUTPUT.PUT_LINE(l_num);
counter := counter + 1;
END LOOP;
END;
/
40
4
35
52
68
5
94
38
49
51
PL/SQL 。
===============================================================
===============================================================
--- xml
-- SYSTEM
Create DIRECTORY TEST_DIR AS 'C:\DEVELOP';
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;
-- SCOTT
DECLARE
src CLOB;
xmlfile UTL_FILE.FILE_TYPE;
length INTEGER;
buffer VARCHAR2(16384);
BEGIN
src := DBMS_XMLQuery.getXml('select * from emp');
length := DBMS_LOB.GETLENGTH(src);
DBMS_LOB.READ(src, length, 1, buffer);
xmlfile := UTL_FILE.FOPEN('TEST_DIR', 'employees.xml', 'w');
UTL_FILE.PUT(xmlfile, buffer);
UTL_FILE.FCLOSE(xmlfile);
END;
/
===============================================================
------- xml
UTL_FILE
、 、
UTL_FILE DIRECTORY
1.Create DIRECTORY TEST_DIR AS 'C:\DEVELOP';
2.GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT
SET SERVEROUT ON FORMAT WRAPPED
DECLARE
input_file UTL_FILE.FILE_TYPE;
input_buffer VARCHAR2(4000);
BEGIN
input_file := UTL_FILE.FOPEN('TEST_DIR', 'employees.xml', 'r');
FOR I IN 1..11 LOOP
UTL_FILE.GET_LINE(input_file, input_buffer);
DBMS_OUTPUT.PUT_LINE(input_buffer);
END LOOP;
UTL_FILE.FCLOSE(input_file);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('------------------');
END;
===============================================================
oracle $rman
oracle :
===============================================================
oracle 4
1.
SQL 。 SQL ，
2.
。
3.
oracle
，
4.
，
===============================================================
oracle
1.
2.
3.
4.
===============================================================
USERID
BUFFER ，
FILE ， .dmp
FULL ，
OWNER
HELP
ROWS
TABLES ，
PARFILE
TABLESPACES ，
exp scott/tiger@tsinghua file=scott_back owner=scott
exp scott/tiger@tsinghua tables=(emp, dept) file=scott_back_tab
exp system/zl@tsinghua tablespaces=(users) file=tbs_users
exp system/zl parfile='C:\parameters.txt'
===============================================================
USERID
BUFFER ，
COMMIT （ BUFFER ）
FILE
FROMUSER
TOUSER 。FROMUSER TOUSER
FULL
TABLES
ROWS
PARFILE ，
IGNORE ， N
TABLESPACES ，
imp tsinghua/tsinghua@tsinghua file=item_back.dmp ignore=y full=y
scott martin
imp system/zl@tsinghua file=scott_back fromuser=scott touser=martin tables=(emp,dept)
imp system/oracle parfile='C:\parameters.txt'
===============================================================
:
===============================================================
：
， 。 ， .
， .
===============================================================
：
1. ，
2.
3. Oracle
4.
5. ，
===============================================================
oracle xml .txt
SQL> conn sys/sys as sysdba
。
SQL> drop directory test_dir
2 /
。
SQL> Create DIRECTORY TEST_DIR AS 'C:\';
。
SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO rbb;
。
SQL> conn rbb/rbb
。
SQL> ed
afiedt.buf
1 DECLARE
2 src CLOB;
3 xmlfile UTL_FILE.FILE_TYPE;
4 length INTEGER;
5 buffer VARCHAR2(16384);
6 BEGIN
7 src := DBMS_XMLQuery.getXml('select * from liuxing');
8 length := DBMS_LOB.GETLENGTH(src);
9 DBMS_LOB.READ(src, length, 1, buffer);
10 xmlfile := UTL_FILE.FOPEN('TEST_DIR', 'emp.xml', 'w');
11 UTL_FILE.PUT(xmlfile, buffer);
12 UTL_FILE.FCLOSE(xmlfile);
13* END;
SQL> /
PL/SQL 。
===============================================================
SQL> ed
afiedt.buf
1 declare
2 lname number;
3 counter number;
4 begin
5 counter:=1;
6 while counter<=10
7 loop
8 lname:=dbms_random.random;
9 dbms_output.put_line(lname);
10 counter:=counter+1;
11 end loop;
12* end;
SQL> /
277652640
-479979827
-1049652647
-1006595853
1252280346
196435204
466478280
-85782435
-1489036577
-927786638
PL/SQL 。
: 00: 00: 00.00
===============================================================
alter table old_table_name rename to new_table_name;
===============================================================
SQL I/O
SQL>SET AUTOTRACE ON ;
SQL>Select * FROM TABLE;
or
SQL>Select * FROM v$filestat ;
===============================================================
SQL>Select * FROM V$INSTANCE;
===============================================================
SQL>select * from all_tables;
===============================================================
SQL
SQL>set timing on ;
===============================================================
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
===============================================================
select name, waits, gets, waits/gets "Ratio"
from v$rollstat C, v$rollname D
where C.usn = D.usn;
===============================================================
I/O
select B.tablespace_name name,B.file_name "file",A.phyrds pyr,
A.phyblkrd pbr,A.phywrts pyw, A.phyblkwrt pbw
from v$filestat A, dba_data_files B
where A.file# = B.file_id
order by B.tablespace_name;
===============================================================
I/O
select substr(C.file#,1,2) "#", substr(C.name,1,30) "Name",
C.status, C.bytes, D.phyrds, D.phywrts
from v$datafile C, v$filestat D
where C.file# = D.file#;
===============================================================
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
===============================================================
SGA
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
===============================================================
SGA
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
===============================================================
SGA ， 1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
===============================================================
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
===============================================================
SGA ， 1%
Select name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch Where name IN ('redo allocation', 'redo copy');
===============================================================
， .10， sort_area_size
Select name, value FROM v$sysstat Where name IN ('sorts (memory)', 'sorts (disk)');
===============================================================
SQL
Select osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
===============================================================
Select (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
Select (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
Select SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
===============================================================
, 1%, 0% 。
Select SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
===============================================================
MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
0.5 ，
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater mts_max_servers ，
===============================================================
ID
SQL>SHOW USER;
or
SQL>select user from dual;
===============================================================
Select segment_name table_name , COUNT(*) extents
FROM dba_segments Where owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (Select MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
===============================================================
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
===============================================================
select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;
===============================================================
CPU session 11 cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc;
===============================================================
**************************************************SQLServer ***********************************************************
SQL ：
DDL— (Create，Alter，Drop，DECLARE)
DML— (Select，Delete，Update，Insert)
DCL— (GRANT，REVOKE，COMMIT，ROLLBACK)
, ：
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
1、 ：
Create DATABASE database-name
create database database
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
2、 ：
drop database database
drop database dbname
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
3、 ： sql server
--- device
user pubs
exec database 'disk'
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
---
backup database pubs to testback
BACKUP DATABASE pubs TO testBack
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
4、 ：
create table tabname(name nvchar(20) primary key,password nvchar(16))
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
：
A：create table tab_new like tab_old ( )
B：create table tab_new as select col1,col2… from tab_old definition only
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
5、 ：
：drop table tabname
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
6、 ：
：Alter table tabname add column col type
alter table tabanme add column col
： 。DB2 ， varchar 。
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
7、 ：
：Alter table tabname add primary key(col)
：
：Alter table tabname drop primary key(col)
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
8、 ：
：create [unique] index idxname on tabname(col….)
：drop index idxname
： ， 。
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
9、 ：
：create view viewname as select statement
：drop view viewname
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
10、 ： sql
：select * from table1 where
：insert into table1(field1,field2) values(value1,value2)
：delete from table1 where
：update table1 set field1=value1 where
：select * from table1 where field1 like ’%value1%’ ---like ， !
：select * from table1 order by field1,field2 [desc]
：select count * as totalcount from table1
：select sum(field1) as sumvalue from table1
：select avg(field1) as avgvalue from table1
：select max(field1) as maxvalue from table1
：select min(field1) as minvalue from table1
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
11、 ：
STAFF 20 ， 。
Create VIEW STAFF_ONLY
AS Select ID, NAME, DEPT, JOB, YEARS
FROM STAFF
Where JOB <> 'Mgr' AND DEPT=20
， :
Select * FROM STAFF_ONLY
A： UNION union
UNION （ TABLE1 TABLE2） 。 ALL UNION （ UNION ALL）， 。 ， TABLE1 TABLE2。
B： EXCEPT except
EXCEPT TABLE1 TABLE2 。 ALL EXCEPT (EXCEPT ALL)， 。
C： INTERSECT intersect
INTERSECT TABLE1 TABLE2 。 ALL INTERSECT (INTERSECT ALL)， 。
： 。
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
12、 ：
A、left outer join：
（ ）： ， 。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right outer join:
( )： ， 。
C：full outer join：
： ， 。
， sql
1、 ： ( , ：a ：b) (Access )
：select * into b from a where 1<>1
：select top 0 * into b from a
2、 ： ( , ：a ：b) (Access )
insert into b(a, b, c) select d,e,f from b;
3、 ： ( ) (Access )
insert into b(a, b, c) select d,e,f from b in ‘ ’ where
：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
4、 ： ( 1：a 2：b)
select a,b,c from a where a IN (select d from b ) : select a,b,c from a where a IN (1,2,3)
5、 ： 、
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、 ： ( 1：a 2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、 ： ( 1：a )
select * from (Select a,b,c FROM a) T where t.a > 1;
8、 ：between ,between ,not between
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 1 and 2
9、 ：in
select * from table1 where a [not] in (‘ 1’,’ 2’,’ 4’,’ 6’)
10、 ： ，
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、 ： ：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、 ：
SQL: select * from where datediff('minute',f ,getdate())>5
13、 ： sql
select top 10 b.* from (select top 20 , from order by desc) a, b where b. = a. order by a.
14、 ： 10
select top 10 * form table1 where
15、 ： b a ( , , , .)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、 ： TableA TableB TableC
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、 ： 10
select top 10 * from tablename order by newid()
18、 ：
select newid()
19、 ：
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、 ：
select name from sysobjects where type='U'
21、 ：
select name from syscolumns where id=object_id('TableName')
22、 ： type、vender、pcs ， type ，case ， select case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
：
type vender pcs
A 1
A 1
B 2
A 2
B 3
C 3
23、 ： table1
TRUNCATE TABLE table1
24、 ： 10 15
select top 5 * from (select top 15 * from table order by id asc) table_ order by id desc
（ Randomize ， SQL ）
， ， 。 ASP“ ” 。 ：
Randomize
RNumber = Int(Rnd*499) +1
While Not objRec.EOF
If objRec("ID") = RNumber THEN
... ...
end if
objRec.MoveNext
Wend
。 ， 1 500 （ 500 ）。 ， ID 、 RNumber。 THEN 。 RNumber 495， 。 500 ， ， 。 ？
SQL， recordset， ：
Randomize
RNumber = Int(Rnd*499) + 1
SQL = "Select * FROM Customers Where ID = " & RNumber
set objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email")
RNumber ID， 。 ， “ ” 。Recordset ， 。
Random ， 。 Random SQL 。
recordset ， ， ：
SQL = "Select * FROM Customers Where ID = " & RNumber & " or ID = " & RNumber2 & " or ID = " & RNumber3
10 （ 10 ）， BETWEEN 。 ， Select （ ID ）：
SQL = "Select * FROM Customers Where ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"
： 9 。
，
Access ：Select top 10 * From orDER BY Rnd(id)
Sql server:select top n * from order by newid()
mysql select * From order By rand() Limit n
Access ( ,Access , Access SQL , , )
select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where ...
SQL ...
：
SQL ：select case when len(field)>10 then left(field,10)+'...' else field end as news_name,news_id from tablename
Access ：Select iif(len(field)>2,left(field,2)+'...',field) FROM tablename;
Conn.Execute
Execute
SQL 。 SQL ， ：
1． SQL ， 。 ：
Set = .Execute("SQL ")
Execute ， ， ， Set ， ， 。
2． SQL ， 。 ：
.Execute "SQL " [, RecordAffected][, Option]
·RecordAffected ， ，SQL ， 。 ， SQL 。
·Option ， adCMDText， ADO， Execute 。 ， 。
·BeginTrans、RollbackTrans、CommitTrans
。BeginTrans ；RollbackTrans ；CommitTrans ， 。
， ， ； ， ， 。
BeginTrans CommitTrans ， ， 。 ， Error ， Error 0， ， 。Error Error ， 。
*************************************** **********************************************************************
==================TestSQLServer.java SQLServer====================================
import java.sql.*;
import java.util.*;
public class TestSQLServer{
public static void main (String args[]){
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=testdb";
String user ="sa";
String password = "sa";
Connection conn = DriverManager.getConnection(url,user,password);
Statement stat = conn.createStatement();
ResultSet result = stat.executeQuery("Select * FROM users");
result.next();
System.out.println(result.getString(1));
System.out.println(result.getString(2));
System.out.println(result.getString(3));
result.close();
stat.close();
conn.close();
}catch(ClassNotFoundException en){
System.out.println(" ！");
en.printStackTrace();
}catch(SQLException ex) {
while (ex != null) {
ex.printStackTrace();
ex = ex.getNextException();
}
}catch(Exception e){
System.out.println(" ！");
e.printStackTrace();
}
}
}
=================TestMysql.java mysql =======================================
package org.binbo.dom;
import java.sql.*;
public class TestMysql{
public static void main (String args[]){
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
String url = "jdbc:mysql://localhost:3306/binbo";
String user ="root";
String password = "binbo";
Connection conn = DriverManager.getConnection(url,user,password);
Statement stat = conn.createStatement();
ResultSet result = stat.executeQuery("Select * FROM testxml");
result.next();
System.out.println(result.getString(1));
System.out.println(result.getString(2));
System.out.println(result.getString(3));
result.close();
stat.close();
conn.close();
}catch(ClassNotFoundException en){
System.out.println(" ！");
en.printStackTrace();
}catch(SQLException ex) {
while (ex != null) {
ex.printStackTrace();
ex = ex.getNextException();
}
}catch(Exception e){
System.out.println(" ！");
e.printStackTrace();
}
}
}
=================TestOracle.java oracle ==================================
package org.binbo.dom;
import java.sql.*;
public class TestOracle{
public static void main (String args[]){
try{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
String url = "jdbc:oracle:thin:@localhost:1521:binbo";
String user ="scott";
String password = "tiger";
Connection conn = DriverManager.getConnection(url,user,password);
Statement stat = conn.createStatement();
ResultSet result = stat.executeQuery("Select * FROM test");
result.next();
System.out.println(result.getString(1));
System.out.println(result.getString(2));
System.out.println(result.getString(3));
result.close();
stat.close();
conn.close();
}catch(ClassNotFoundException en){
System.out.println(" ！");
en.printStackTrace();
}catch(SQLException ex) {
while (ex != null) {
ex.printStackTrace();
ex = ex.getNextException();
}
}catch(Exception e){
System.out.println(" ！");
e.printStackTrace();
}
}
}
===============XML =====================================================
da.xml
<?xml version="1.0" encoding="UTF-8"?>
<PEOPLE><!--
<PERSON PERSONID="E01">
<className>com.microsoft.jdbc.sqlserver.SQLServerDriver</className>
<url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=binbo</url>
<user>sa</user>
<password>sa</password>
</PERSON>
--><PERSON PERSONID="E02">
<className>com.mysql.jdbc.Driver</className>
<url>jdbc:mysql://localhost:3306/binbo</url>
<user>root</user>
<password>binbo</password>
</PERSON><!--
<PERSON PERSONID="E03">
<className>oracle.jdbc.driver.OracleDriver</className>
<url>jdbc:oracle:thin:@localhost:1521:binbo</url>
<user>scott</user>
<password>tiger</password>
</PERSON>
--></PEOPLE>
--------------------------------------------
content.java
package org.binbo.dom;
import java.sql.Connection;
import java.sql.DriverManager;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
public class Content {
public static Connection getConnection(){
Connection conn =null;
try {
DocumentBuilderFactory factory=DocumentBuilderFactory.newInstance();
DocumentBuilder builder=factory.newDocumentBuilder();
Document doc=builder.parse("da.xml");
NodeList nl=doc.getElementsByTagName("PERSON");
Element node=(Element) nl.item(0);
String className =node.getElementsByTagName("className").item(0).getFirstChild().getNodeValue();
String url =node.getElementsByTagName("url").item(0).getFirstChild().getNodeValue();
String user =node.getElementsByTagName("user").item(0).getFirstChild().getNodeValue();
String pwd =node.getElementsByTagName("password").item(0).getFirstChild().getNodeValue();
Class.forName(className);
conn =DriverManager.getConnection(url,user,pwd);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
--------------------------------------------
Domtest.java
package org.binbo.dom;
import java.io.FileOutputStream;
import java.sql.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
public class Domtest {
public static void main(String[] args){
try{
Connection conn =Content.getConnection();
PreparedStatement ps=conn.prepareStatement("select * from testxml");
ResultSet rs =ps.executeQuery();
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
Document doc = builder.newDocument();
Element Stu = doc.createElement("binboxml");
Element uname = doc.createElement("username");
Element nameid = doc.createElement("userid");
Element pass = doc.createElement("password");
while(rs.next()){
String id=rs.getString(1);
String name=rs.getString(2);
String pwd=rs.getString(3);
System.out.print(rs.getString(1));
System.out.print(rs.getString(2));
System.out.print(rs.getString(3));
System.out.print(" ！");
nameid.appendChild(doc.createTextNode(id));
uname.appendChild(doc.createTextNode(name));
pass.appendChild(doc.createTextNode(pwd));
}
Stu.appendChild(nameid);
Stu.appendChild(uname);
Stu.appendChild(pass);
doc.appendChild(Stu);
TransformerFactory tf = TransformerFactory.newInstance();
Transformer tr = tf.newTransformer();
tr.transform(new DOMSource(doc), new StreamResult(
new FileOutputStream("Binbo.xml")));
rs.close();
} catch(Exception e){
e.printStackTrace();
}
}
}
===================== =====================================
tomcat server.xml
<Logger className="org.apache.catalina.logger.FileLogger"
directory="logs" prefix="localhost_log." suffix=".txt"
timestamp="true"/>
<Context path="/myjsp" docBase="myjsp"
debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/myjsp"
auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/myjsp">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>100</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<parameter>
<name>username</name>
<value>sa</value>
</parameter>
<parameter>
<name>password</name>
<value>sa</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jsp</value>
</parameter>
</ResourceParams>
</Context>
-----------------------------------------------------------------------
DBPool.java
package com.binbo.dbo;
import java.sql.Connection;
import java.sql.DriverManager;
import javax.naming.Context;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBPool {
public static synchronized Connection getConnection()throws Exception{
DataSource ds = null;
try{
Context ininCtx = new javax.naming.InitialContext();
Context envCtx = (Context)ininCtx.lookup("java:comp/env");
ds = (DataSource)envCtx.lookup("jdbc/myjsp");
}catch(NamingException e){
e.printStackTrace();
}
Connection conn = ds.getConnection();
return conn;
}
}
-----------------------------------------------------------
package com.binbo.dbo;
import java.sql.*;
import com.binbo.javabean.BreakBean;
import com.binbo.javabean.OpenBean;
public class DataBaseClass {
private Statement sta = null;
private ResultSet rs = null;
Connection conn = null;
private int count;
public DataBaseClass() throws Exception {
//
conn = DBPool.getConnection();
sta = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
}
public void getExecute(String sql) {
try {
System.out.println(sql);
sta.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
/*
* sql
*/
public ResultSet getQuery(String sql) {
try {
System.out.println(sql);
rs = sta.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
*
public int getCount(ResultSet rs2) {
try {
rs2.next();
count = rs2.getRow();
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
// ！
public void getRegister(OpenBean open) {
String sql = "insert into f_info(nam,email,titl,content,tem,mid)values(?,?,?,?,?,?)";
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, open.getNam());
ps.setString(2, open.getEmail());
ps.setString(3, open.getTitl());
ps.setString(4, open.getContent());
ps.setString(5, open.getTem());
ps.setString(6, open.getDepa());
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
//
public void getRealys(OpenBean real) {
try {
String sql = "delete from departments where id=?";
PreparedStatement ppt = conn.prepareStatement(sql);
ppt.setString(1, real.getUnam());
ppt.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
//
public void getRealy(OpenBean hg) {
try {
String sql = "select * from departments where id=?";
PreparedStatement ppg = conn.prepareStatement(sql);
ppg.setString(1, hg.getUnam());
ppg.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
============================hibernate ====================================
package com.binbo.hibernate.xml;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class PersonOperate {
private Session session = null ;
public PersonOperate()
{
Configuration config = new Configuration().configure() ;
SessionFactory factory = config.buildSessionFactory() ;
this.session = factory.openSession() ;
}
//
public void insert(Person p)
{
Transaction tran = this.session.beginTransaction() ;
this.session.save(p) ;
tran.commit() ;
this.session.close() ;
}
------------------------------------------
LinOperate po = new LinOperate();
Lin p = new Lin();
p.setLname(lname);
p.setLmonery(lmonery);
p.setLmain(lmain);
p.setLtime(ltime);
LinOperate po = new LinOperate();
po.insert(p);
errors.add("success", new ActionMessage("xiangxi"));
request.setAttribute("org.apache.struts.action.ERROR", errors);
return mapping.findForward("jinru");
------------------------------------------
//
public void update(Person p)
{
Transaction tran = this.session.beginTransaction() ;
this.session.update(p) ;
tran.commit() ;
this.session.close() ;
}
//
public boolean queryById(Person person)
{
boolean flag = false;
String hql = "from Person as p where p.id=? and p.password=?" ;
Query q = this.session.createQuery(hql) ;
q.setString(0,person.getId()) ;
q.setString(1,person.getPassword());
Iterator iter = q.list().iterator();
if (iter.hasNext()) {
flag = true;
person.setName(((Person) iter.next()).getName());
}
this.session.close() ;
return flag;
}
//
public boolean queryC(Person person)
{
boolean flag = false;
String hql = "from Person as p where p.name=?" ;
Query q = this.session.createQuery(hql) ;
q.setString(0,person.getName()) ;
Iterator iter = q.list().iterator();
if (iter.hasNext()) {
flag = true;
}
this.session.close() ;
return flag;
}
//
public Person queryZliao(String id)
{
Person p = null ;
String hql = "from Person as p where p.id=?" ;
Query q = this.session.createQuery(hql) ;
q.setString(0,id) ;
List l = q.list() ;
Iterator iter = l.iterator() ;
if(iter.hasNext())
{
p = (Person)iter.next() ;
}
return p ;
}
//
public void delete(Person p)
{
Transaction tran = this.session.beginTransaction() ;
this.session.delete(p) ;
tran.commit() ;
}
//
public void delete(String name)
{
String hql = "delete Person where name=?" ;
Query q = this.session.createQuery(hql) ;
q.setString(0,name) ;
q.executeUpdate() ;
this.session.beginTransaction().commit() ;
}
//
public List queryAll()
{
List l = null ;
String hql = "from Person as p" ;
Query q = this.session.createQuery(hql) ;
l = q.list() ;
return l ;
}
-------------------------------
(
LinOperate po = new LinOperate();
List l = po.queryAll();
Iterator iter = l.iterator();
ArrayList lus = new ArrayList();
while (iter.hasNext()) {
Lin p = (Lin) iter.next();
Luser lu = new Luser();
lu.setName(p.getLname());
lu.setChange(p.getLmonery());
lu.setMainn(p.getLmain());
lu.setLtime(p.getLtime());
lus.add(lu);
System.out.print(p.getLname() + " ");
request.setAttribute("lus", lus);
)
------------------------
//
public List queryByLike(String cond)
{
List l = null ;
String hql = "from Person as p where p.name like ?" ;
Query q = this.session.createQuery(hql) ;
q.setString(0,"%"+cond+"%") ;
l = q.list() ;
return l ;
}
}