[JDBC] Applying SQL statements in Eclipse (DML, DDL, DCL, TCL)

First, in order to apply Oracle SQL statements in Eclipse, you must know the three interfaces: Connection, PrepareStatement, and ResultSet.

What is Connection?

This is an interface that allows you to execute SQL statements in Eclipse and retrieve the result values. In other words, for Java and Oracle DB, an interface object must first be created (initialized) as follows.

What is PrepareStatement?

The reason for using the preparestatment interface, unlike statement, is that it inherits statement and does not need to handle single quotation marks (' ') when entering sql statements. And it has the advantage of being able to compile sql statements in advance. (→ 자세한 설명)

Preparestatement is also created (initialized) first.

What is ResultSet?

When executeQuery() is executed, the result is put into ResultSet. It is entered when you want to know the value when executing the DDL statement select, and is used with the rs.next() method to check the result value or receive input. The details will be discussed again in the DDL statement description below. If ResultSet is also used, it is created (initialized) first in the same way.

All three interfaces above must return resources (object.close(); ) after use. However, when using SQL statements multiple times, the Connection object only needs to be returned once, so singletonpattern is used. (→ detailed explanation)

---

And let's learn about the database commands DML, DDL, DCL, and TCL.

The DML statement is

The abbreviations for Data Manipulation Language include INSERT, UPDATE, DELETE, and SELECT. As a data manipulation language, it can change data through the process of entering, selecting, deleting, and updating the necessary information in the data. Rollback is possible until commit.

The DDL statement is

It is an abbreviation for Data Definition Language and is a command that does not require separate commit of input values, such as CREATE, ALTER, DROP, RENAME, COMMENT, and TRUNCATE. In other words, it is completed through auto commit.

The DCL statement is

It is an abbreviation for Data Control Language and is a command that does not require separate commit of input values ​​like GRANT and REVOKE. In other words, it is completed through auto commit. This refers to a command that grants or revokes permission to another user to use the data manipulation language functions of sys or system.

The TCL statement is

It is an abbreviation for Transcation Control Language and includes COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION commands. It is used to define data security, integrity, recovery, and parallel execution control. When using SAVEPOINT, ROLLBACK is possible at a certain point in time.

---

★ Now, let’s learn specifically how to apply SQL statements in Eclipse. (→ Used when implementing console programs)

1. Initialization of the interface object to be used

Automatically import java.sql.*; when you enter import java.sql.*; This applies. If that doesn't work, import it.

2. Oracle driver loading

Loads OracleDriver into memory and creates an object. This refers to a series of processes that re-register this object to DriverManager.

Class.forName registers the class instance information in the ( ) part below forname when running the JVM, that is, oracle.jdbc.driver.OracleDriver in this case. (→ 자세한 설명)

+ After JDBC 4.0, it is said that the driver is automatically initialized without calling the method.

DriverManager is a class that manages a set of JDBC drivers and connects to the database. It can be used only when OracleDriver is registered in advance above. The parameter format is jdbc:subprotocol:subname, where the IP address to be connected is registered. Subprotocol is oracle, and the IP address below is entered as jdbc:oracle:thin:@127.0.0.1:1521:xe.

Here, the values ​​below @ can be entered as variables as IP addresses, or you can connect to a specific IP or your own IP, such as 127.0.0.1.

The second parameter value represents the Oracle user you wish to connect to, and enter the connection password in the next parameter. It is registered so that no input is needed when running, so it is not required.

Additionally, you can change auto commit details using Connection object.setAutoCommit(false); It can be changed by manual commit. The default setting in JDBC is auto commit.

3. Write SQL statements

Write with Oracle commands so that the SQL written in Eclipse can be reflected in the actual DB. At this time, one thing to keep in mind is that the DB cannot interpret the line break because the space is not used. For example " select name, msg|from tbl_test "; as | It is necessary to distinguish semantic units through spacing.

(Refer to the DDL, DML, DCL, TCL sections above)

4. Passing SQL statements to DB

Send SQL statements to DB using Connection and PreparedStatement.

If it is a DDL statement executeQuery(); method, otherwise executeUpdate(); Use methods.

pstmt.executeQuery(); The result is displayed in ResultSet format and is received in the variable rs that was initialized earlier. When this SQL is executed, rs.next is not a null value.

pstmt.executeUpdate(); The result value is displayed in integer int format, and has a value of 1 when executed.

5. Catching SQL Exception

When you enter SQL, Eclipse can detect if any syntax errors occur by using the try/catch statement. In Eclipse, it appears as a yellow warning sign, so just apply it.

When implementing an actual console program, it is easy to figure out where the problem occurred, so the catch statement includes e.printStackTrace(); Adds .

6. Return of resources

After the try/catch statement, finally is written to close each resource to ensure that the resource is returned.