Connect to Oracle using JDBC

Implementation ideas:

Use a file named oracle.properties, which contains the Oracle URL, User, Password and driver classes. Through the input stream of ClassLoader, the input stream contains the Oracle connection initialization parameters of the input program. The input stream is loaded in the Properties load. Get the file information through getProperty(String key).

JdbcUtils.java

package exer.jdbcutils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @author mmengyiyu
 * @date 2019/11/15 16:47
 */
public class JdbcUtils {
    private static String url;
    private static String user;
    private static String pwd;
    private static String driverClass;
    // Load mysql.properties resource
    static {
        InputStream resource = JdbcUtils.class.getClassLoader().getResourceAsStream("exer\jdbcutils\oracle.properties");
        Properties prop = new Properties();
        try {
            prop.load(resource);
            url = prop.getProperty("jdbc.url");
            user = prop.getProperty("jdbc.user");
            pwd = prop.getProperty("jdbc.pwd");
            driverClass = prop.getProperty("jdbc.driverClass");
            Class.forName(driverClass);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * Release database connection and its JDBC resources
     * @author mmengyiyu
     * @date 2019-11-15 19:27
     * @param conn Resource references to release
    */
    public static void release(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Release precompiled statements and their JDBC resources
     * @author mmengyiyu
     * @date 2019-11-15 19:27
     * @param ps SQL statement to release
     */
    public static void release(PreparedStatement ps) {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Release statement and its JDBC resources
     * @author mmengyiyu
     * @date 2019-11-15 19:27
     * @param st SQL statement to release
     */
    public static void release(Statement st) {
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Release the result set and its JDBC resources
     * @author mmengyiyu
     * @date 2019-11-15 19:27
     * @param rs SQL result set to release
     */
    public static void release(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Return to MySQL database connection
     * @author mmengyiyu
     * @date 2019-11-15 19:28
     * @return Database connection
    */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url,user,pwd);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
}

oracle.properties

# oracle 11
jdbc.url = jdbc:oracle:thin:@//127.0.0.1:1521/orcl
jdbc.user = scott
jdbc.pwd = 123456
jdbc.driverClass = oracle.jdbc.driver.OracleDriver

FAQ - how to convert Java string to Oracle Date type

The way to solve this problem is as follows:

Oracle Date is different from Java String type. If Java wants to write an Oracle Date data, it needs to convert the string to java.sql.Date. String conversion to java.sql.Date requires the following steps:

1. Convert the string to java.time.LocalDate, or java.time.LocalTime or java.time.LocalDateTime type provided by JDK1.8.
2. Use java.sql.Date.valueOf() to convert java.time.LocalDate, or java.time.LocalTime or java.time.LocalDateTime type to java.sql.Date

Oracle Date type

The format of date value is NLS? Date? Format, and the specific format is as follows:

DD-MON-RR

Here we use java.sql.Date and java.time.LocalDate. The source code is as follows

import java.sql.Date;
import java.time.LocalDate;
...
System.out.println("Birthday(Format requirement:\"yyyy-MM-dd\"):");
                String birthday = sc.next();
                String[] split = birthday.split("-");
                Date date = Date.valueOf(LocalDate.of(Integer.parseInt(split[0]),Integer.parseInt(split[1]),Integer.parseInt(split[2])));

This translates the string entered from the screen into java.sql.Date.

FAQ - always call transaction commit method after Oracle DML operation with JDBC

Because Oracle database transactions are not automatically committed by default. Once the program involves adding, deleting and modifying the database (DML), make sure to conn.commit(), otherwise, the database results cannot be persisted!

The most intuitive feeling is: clearly my insert/update/delete records in the table, why there is no change in the table when using the database software to query the table.

Of course, if the DML operation fails in a transaction, the transaction needs to be rolled back when entering the catch block.

Some source codes are as follows:

Connection conn = null;
try {
    ...
    conn = JdbcUtils.getConnection();
    ...(Using connections for database operations)
    ...
    // If the database operation involves DML operation, be sure to commit the transaction, because Oracle does not automatically commit the transaction by default.
    conn.commit();
    conn.close();
} catch (SQLException e) {
    // This step can be reached because there is an error in the try block. According to the transaction ACID, rollback ends the transaction.
    try {
        conn.rollback();
     } catch (SQLException ex) {
        ex.printStackTrace();
    }
        e.printStackTrace();
    } finally {
        JdbcUtils.release(conn);
    }

What programming content needs to be mastered in Jdbc

• Write a Jdbc tool class, which involves the loading of database connection and the release operation related to Jdbc resources.
• How to CRUD a table
• Add a tag to the table (used to mark whether to DELETE, 0 - deleted, 1 - normal use, temporarily named as status). The operation of deleting the table is converted to the status tag in the UPDATE table instead of using DELETE
• How to delete multiple records based on multiple field values of the field where the primary key is located.

• Learn to use DBUtils.

  • For example, use QueryRunner.query() and QueryRunner.update() in DBUtils