4.2.3.6 Creating and Executing Oracle Statements
Executing any execution method we discussed in the previous sections is exactly the same as exe-cuting a string representing an Oracle statement. In fact, the Oracle statement and the JDBC rep-resentation are exactly the same thing from the point of view of the terminal execution results. However, in some cases, you have to modify the JDBC string to make sure that the database can receive the correct Oracle statement.
All Oracle statements can be divided into two categories:
- Data definition language (DDL) statements
- Data manipulation language (DML) statements
DDL statements are used to create and modify the structure of your database Tables and other objects related to the database. DML statements are used to work with and manipulate data in data-base Tables.
Let’s discuss the creation and execution of Oracle statements based on these two categories in the following sections.
4.2.3.6.1 Creating and Executing DDL Statements
Since DDL statements are mainly used for the creation and modification of the structure of database Tables and related objects, they do not perform any queries and do not affect any rows in database-related Tables. Of course, they will never return any ResultSet object, either. However, in order to keep DDL statements consistent with other types of Oracle statements, DDL statements always return a 0 in an actual application.
A standard DDL protocol used to create the structure of a Table is:
CREATE Table <Table name>
(<attribute name 1><data type 1>,
…….
<attribute name n><data type n>);
Figure 4.12 shows a piece of example code to illustrate how to create a LogIn Table using the JDBC statement.
First, the protocol used to create the Login Table is assigned to a JDBC statement string, oraString. The data type for both the user _ name and pass _ word columns isVARCHAR2, which is a variable-length char. The argument 10 is used to define the length of those chars. login _ ID is an integer. Then a Statement object is created, and the execute() method is called to perform the creation of this Table with oraString as the argument that is passed to the database.
To add data into a created Table, you need to use DML statements.
4.2.3.6.2 Creating and Executing DML Statements
DML statements are used to build and complete the body of database Tables. These statements include data query statements and insert, update and delete statements. All of these statements need to return execution results, either a ResultSet object or an integer.
A standard DML statement used to insert data into the created data Table looks like:
INSERT INTO <Table name>
VALUES (<value 1>, <value 2>, . . . <value n>);
A standard DML statement used to update data from a created data Table looks like:
UPDATE <Table name>
SET <attribute> = <expression>
WHERE <condition>;
Figure 4.13 shows a piece of example code to illustrate how to add data items to the created LogIn Table using the JDBC statement.
Figure 4.14 shows a piece of example code to illustrate how to perform a select query to retrieve the desired username and password from the LogIn Table.