1, Chuangku

    creating a database in oracle is usually called creating a tablespace, and users of the tablespace need to be created

– create tablespaces

create tablespace jwxt datafile 'jwxt.ora' size 500m 
autoextend on --Automatic extension 
 next 50m maxsize 20480m --Each automatic expansion 50 M，Up to 20480 M 
extent management local;

– create tablespace default user

create user jwxt identified by 888 default tablespace jwxt quota 500m on jwxt;

– give it all permissions

grant all privileges to jwxt;

– users with connect permission can only log in to Oracle; Users with resource permission can only create entities, not database structures; Having all privileges is the highest privilege of the system. Only DBA s can create database structures.

2, Create table

   oracle database uses sequence to complete the database autoincrement operation. Usually, a sequence is created for each table

create sequence USER_SEQ
    maxvalue 10000000

Create table

create table "user"
(
    id int
        constraint USER_PK
        primary key,
    name varchar2(5)
)

3, Insert data

    in oracle, the table name and field name cannot use the ~ (floating) above TAB, but use double quotation marks, and the value must use single quotation marks. At the same time, the next value method of the sequence is used to obtain the next value. Each time this method is used, the next value of the sequence will automatically increase in steps.

insert into "user"(ID,NAME) values (USER_SEQ.nextval,'Zhang San')

4, Paging query

   Oracle paging query must use row number rownum. The row number of each query changes, so it is generally completed by sub query. The SQL is as follows:

select * from ( select rownum r_, row_.* from ( select * from student order by id ) row_ where rownum <=Y ) where r_>=X ;

10: Start index position.

Y: End index position.

5, jdbc operation

   there are two driving methods before the url during jdbc operation, which are described below. The last sid is called the instance name. Generally, each database service will have an instance name. Each user has its own database. The query is also for the database.

At the same time, you need to import the corresponding dependencies:

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>12.2.0.1</version>
</dependency>

Case:

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.75.128:1521:xe", "jwxt", "888");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from \"user\"");
while (resultSet.next()) {
    System.out.println(resultSet.getString(2));
}
resultSet.close();
statement.close();
connection.close();

Differences in url writing between thin and oci:

1) In terms of use, oci must install oracle client or on the client to connect, but thin is not required. Therefore, thin is more convenient to use, which is also a common reason for thin.

2) In principle, thin is a pure java implementation of tcp/ip c/s communication; In the oci mode, the client accesses the server by calling c library through the native java method, and this c library is the oci(oracle called interface), so this oci always needs to be installed with the oracle client (starting from oracle 10.1.0, OCI Instant Client is provided separately, and there is no need to install the client completely)

3) They are different types of drivers, oci is the second type of driver, thin is the fourth type of driver, but they have no difference in function.

4) Although many people say that oci is faster than thin, they haven't found the relevant test report for a long time.