2021-04-15 Java 链接oracle数据库

DemoOracleApplication.java 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
import java.util.logging.Logger;

public class DemoOracleApplication {

	private static final Logger log;

    static {
        System.setProperty("java.util.logging.SimpleFormatter.format", "[%4$-7s] %5$s %n");
        log =Logger.getLogger(DemoPostgresApplication.class.getName());
    }
    
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		log.info("Loading application properties");
		Properties properties = new Properties();
        properties.load(DemoSqlServerApplication.class.getClassLoader().getResourceAsStream("oracle.properties"));
        
        log.info("Connecting to the database");
        Connection connection = DriverManager.getConnection(properties.getProperty("jdbc.url"), properties.getProperty("jdbc.username"), properties.getProperty("jdbc.password"));
        log.info("Database connection test: " + connection.getCatalog());
        
        log.info("Create database schema");
        Scanner scanner = new Scanner(DemoPostgresApplication.class.getClassLoader().getResourceAsStream("./SQL/Oracle.sql"));
        Statement statement = connection.createStatement();
        while (scanner.hasNextLine()) {
            statement.execute(scanner.nextLine());
        }
        
        Todo1 todo = new Todo1(1L, "configuration", "congratulations, you have set up JDBC correctly!", 0);
        insertData(todo, connection);
        todo = readData(connection);
        todo.setDetails("congratulations, you have updated data!");
        updateData(todo, connection);
        deleteData(todo, connection);
        
        log.info("Closing database connection");
        connection.close();
        
        System.out.println("####################################################################");
        System.out.println("Oracle DB link CRUD successful!");
        System.out.println("####################################################################");
	}

	private static void deleteData(Todo1 todo, Connection connection) throws SQLException {
		// TODO Auto-generated method stub
	    log.info("Delete data");
	    PreparedStatement deleteStatement = connection.prepareStatement("DELETE FROM todo WHERE id = ?");
	    deleteStatement.setLong(1, todo.getId());
	    deleteStatement.executeUpdate();
	    readData(connection);		
	}

	private static void updateData(Todo1 todo, Connection connection) throws SQLException {
		// TODO Auto-generated method stub
	    log.info("Update data");
	    PreparedStatement updateStatement = connection
	            .prepareStatement("UPDATE todo SET description = ?, details = ?, done = ? WHERE id = ?");

	    updateStatement.setString(1, todo.getDescription());
	    updateStatement.setString(2, todo.getDetails());
	    updateStatement.setInt(3, todo.isDone());
	    updateStatement.setLong(4, todo.getId());
	    updateStatement.executeUpdate();
	    readData(connection);
	}

	private static Todo1 readData(Connection connection) throws SQLException {
		// TODO Auto-generated method stub
	    log.info("Read data");
	    PreparedStatement readStatement = connection.prepareStatement("SELECT * FROM todo");
	    ResultSet resultSet = readStatement.executeQuery();
	    if (!resultSet.next()) {
	        log.info("There is no data in the database!");
	        return null;
	    }
	    Todo1 todo = new Todo1();
	    todo.setId(resultSet.getLong("id"));
	    todo.setDescription(resultSet.getString("description"));
	    todo.setDetails(resultSet.getString("details"));
	    todo.setDone(resultSet.getInt("done"));
	    log.info("Data read from the database: " + todo.toString());
	    return todo;
	}

	private static void insertData(Todo1 todo, Connection connection) throws SQLException {
		// TODO Auto-generated method stub
	    log.info("Insert data");
	    PreparedStatement insertStatement = connection
	            .prepareStatement("INSERT INTO todo (id, description, details, done) VALUES (?, ?, ?, ?)");

	    insertStatement.setLong(1, todo.getId());
	    insertStatement.setString(2, todo.getDescription());
	    insertStatement.setString(3, todo.getDetails());
	    insertStatement.setInt(4, todo.isDone());
	    insertStatement.executeUpdate();
	}

}

Todo.java

package Application;

public class Todo {
	private Long id;
	private String description;
	private String details;
	private boolean done;
	
	public Todo() {
	}

	public Todo(Long id, String description, String details, boolean done) {
		this.id = id;
		this.description = description;
		this.details = details;
		this.done = done;
	}
	
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public String getDetails() {
		return details;
	}
	public void setDetails(String details) {
		this.details = details;
	}
	public boolean isDone() {
		return done;
	}
	public void setDone(boolean done) {
		this.done = done;
	}
	
	@Override
	public String toString() {
		return "Todo{" + "id=" + id + ", description='" + description + '\'' + ", details='" + details + '\''
				+ ", done=" + done + '}';
	}
}

 

oracle.properties 

jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl
jdbc.username=xxxxxx
jdbc.password=xxxxxx
initialSize=0
maxActive=20 
maxIdle=20
minIdle=1
maxWait=60000

 Oracle.sql

CREATE TABLE todo (id Number(4) NOT NULL PRIMARY KEY, description varchar(100), details varchar(100), done Number(1) default '0' NOT NULL)