Java/Database SQL JDBC/Oracle JDBC
Содержание
- 1 All data types for Oracle
- 2 Check JDBC Installation for Oracle
- 3 Connect to an Oracle database with JDBC
- 4 Count row in Oracle
- 5 Create a table in database
- 6 Create Employee Table Oracle
- 7 Creating an OBJECT Type in an Oracle Database
- 8 Creating an Oracle Table to Store Java Types
- 9 Demo ResultSet Oracle
- 10 Get Column Names From ResultSet for Oracle
- 11 Get Object From Oracle Database Using STRUCT
- 12 Get Oracle Table Names
- 13 Get Parameter MetaData From Oracle JDBC Driver
- 14 Insert BLOG(Picture or Photo) Data Type Into Oracle Database
- 15 Insert custom type to Oracle
- 16 Inserting an OBJECT Value into an Oracle Table
- 17 OracleDataSource Demo
- 18 Register custome type to Oracle
- 19 Serialized And Deserialize Object Oracle
- 20 Test Data Encryption Integrity
- 21 Test DataSource LookUp
- 22 Test OCINet 8 App
- 23 Test Oracle JDBC Driver Installation
- 24 Test SSL
All data types for Oracle
<source lang="java">
/** Code revised from Database Metadata with JDBC A sample from JDBC Metadata, MySQL, and Oracle Recipes by Mahmoud Parsian
- /
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement;
public class CreateTableAllTypesInOracle {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "scott";
String password = "tiger";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) {
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement("CREATE TYPE varray_type is VARRAY(5) OF VARCHAR(10)");
pstmt.executeUpdate();
// Create an OBJECT type
pstmt = conn.prepareStatement("CREATE TYPE oracle_object is OBJECT(column_string VARCHAR(128), column_integer INTEGER)");
pstmt.executeUpdate();
StringBuffer allTypesTable = new StringBuffer("CREATE TABLE oracle_all_types(");
// Column Name Oracle Type Java Type
allTypesTable.append("column_short SMALLINT, "); // short
allTypesTable.append("column_int INTEGER, "); // int
allTypesTable.append("column_float REAL, "); // float; can also be NUMBER
allTypesTable.append("column_double DOUBLE PRECISION, "); // double; can also be FLOAT or NUMBER
allTypesTable.append("column_bigdecimal DECIMAL(13,0), "); // BigDecimal
allTypesTable.append("column_string VARCHAR2(254), "); // String; can also be CHAR(n)
allTypesTable.append("column_characterstream LONG, "); // CharacterStream or AsciiStream
allTypesTable.append("column_bytes RAW(2000), "); // byte[]; can also be LONG RAW(n)
allTypesTable.append("column_binarystream RAW(2000), "); // BinaryStream; can also be LONG RAW(n)
allTypesTable.append("column_timestamp DATE, "); // Timestamp
allTypesTable.append("column_clob CLOB, "); // Clob
allTypesTable.append("column_blob BLOB, "); // Blob; can also be BFILE
allTypesTable.append("column_bfile BFILE, "); // oracle.sql.BFILE
allTypesTable.append("column_array varray_type, "); // oracle.sql.ARRAY
allTypesTable.append("column_object oracle_object)"); // oracle.sql.OBJECT
pstmt.executeUpdate(allTypesTable.toString());
} catch (Exception e) {
// creation of table failed.
// handle the exception
e.printStackTrace();
}
}
}
</source>
Check JDBC Installation for Oracle
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class CheckJDBCInstallation_Oracle {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "name";
String password = "pass";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
/**
* Test Validity of JDBC Installation
*
* @param conn
* a JDBC connection object
* @return true if a given connection object is a valid one; otherwise return
* false.
* @throws Exception
* Failed to determine if a given connection is valid.
*/
public static boolean isValidConnection(Connection conn) throws Exception {
if (conn == null) {
// null connection object is not valid
return false;
}
if (conn.isClosed()) {
// closed connection object is not valid
return false;
}
// for Oracle database:
// you may use the connection object
// with query of "select 1 from dual";
// if the query returns the result, then
// it is a valid connection object.
return testConnection(conn, "select 1 from dual");
}
/**
* Test Validity of a Connection
*
* @param conn
* a JDBC connection object
* @param query
* a sql query to test against database connection
* @return true if a given connection object is a valid one; otherwise return
* false.
*/
public static boolean testConnection(Connection conn, String query) {
ResultSet rs = null;
Statement stmt = null;
try {
stmt = conn.createStatement();
if (stmt == null) {
return false;
}
rs = stmt.executeQuery(query);
if (rs == null) {
return false;
}
if (rs.next()) {
// connection object is valid: we were able to
// connect to the database and return something useful.
return true;
}
// there is no hope any more for the validity
// of the connection object
return false;
} catch (Exception e) {
return false;
} finally {
// close database resources
try {
rs.close();
stmt.close();
} catch (Exception e) {
// ignore
}
}
}
public static void main(String[] args) {
Connection conn = null;
try {
conn = getConnection();
System.out.println("conn=" + conn);
System.out.println("valid connection = " + isValidConnection(conn));
} catch (Exception e) {
// handle the exception
e.printStackTrace();
System.exit(1);
} finally {
// release database resources
try {
conn.close();
} catch (Exception e) {
// ignore
}
}
}
}
</source>
Connect to an Oracle database with JDBC
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@//server.local:1521/prod", "scott", "tiger");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select * from employee");
while (rset.next()) {
System.out.println(rset.getString(1));
}
stmt.close();
}
}
</source>
Count row in Oracle
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class CountRows_Oracle {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "userName";
String password = "pass";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static int countRows(Connection conn, String tableName) throws SQLException {
// select the number of rows in the table
Statement stmt = null;
ResultSet rs = null;
int rowCount = -1;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
// get the number of rows from the result set
rs.next();
rowCount = rs.getInt(1);
} finally {
rs.close();
stmt.close();
}
return rowCount;
}
public static void main(String[] args) {
Connection conn = null;
try {
conn = getConnection();
String tableName = "myTable";
System.out.println("tableName=" + tableName);
System.out.println("conn=" + conn);
System.out.println("rowCount=" + countRows(conn, tableName));
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
} finally {
// release database resources
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</source>
Create a table in database
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:javaDemo";
String username = "username";
String password = "welcome";
String sql = "CREATE TABLE books (id NUMBER(11), title VARCHAR2(64))";
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
statement.execute(sql);
connection.close();
}
}
</source>
Create Employee Table Oracle
<source lang="java">
import java.sql.Connection; import java.sql.Statement; import java.sql.DriverManager; import java.sql.SQLException; public class MainClass {
private static final String EMPLOYEE_TABLE = "create table MyEmployees3 ( "
+ " id INT PRIMARY KEY, firstName VARCHAR(20), lastName VARCHAR(20), "
+ " title VARCHAR(20), salary INT " + ")";
public static Connection getConnection() throws ClassNotFoundException, SQLException {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:database";
String username = "name";
String password = "pass";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String args[]) {
Connection conn = null;
Statement stmt = null;
try {
conn = getConnection();
stmt = conn.createStatement();
stmt.executeUpdate(EMPLOYEE_TABLE);
stmt.executeUpdate("insert into MyEmployees3(id, firstName) values(100, "A")");
stmt.executeUpdate("insert into MyEmployees3(id, firstName) values(200, "B")");
System.out.println("CreateEmployeeTableOracle: main(): table created.");
} catch (ClassNotFoundException e) {
System.out.println("error: failed to load Oracle driver.");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("error: failed to create a connection object.");
e.printStackTrace();
} catch (Exception e) {
System.out.println("other error:");
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();
} catch (Exception e) {
}
}
}
}
</source>
Creating an OBJECT Type in an Oracle Database
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1521";
String sid = "mydatabase";
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
Statement stmt = connection.createStatement();
stmt.execute("CREATE TYPE object2 AS OBJECT(col_string2 VARCHAR(30), col_integer2 NUMBER)");
stmt.execute("CREATE TYPE object1 AS OBJECT(col_string1 VARCHAR(30), col_integer2 object2)");
stmt.execute("CREATE TABLE object1_table(col_integer NUMBER, col_object1 object1)");
}
}
</source>
Creating an Oracle Table to Store Java Types
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1521";
String sid = "mydatabase";
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
Statement stmt = connection.createStatement();
stmt.execute("CREATE TYPE number_varray AS VARRAY(10) OF NUMBER(12, 2)");
stmt.execute ("CREATE TYPE my_object AS OBJECT(col_string2 VARCHAR(30), col_int2 INTEGER)");
// Column Name Oracle Type Java Type
String sql = "CREATE TABLE oracle_all_table("
+ "col_short SMALLINT, " // short
+ "col_int INTEGER, " // int
+ "col_float REAL, " // float; can also be NUMBER
+ "col_double DOUBLE PRECISION, " // double; can also be FLOAT or NUMBER
+ "col_bigdecimal DECIMAL(13,0), " // BigDecimal
+ "col_string VARCHAR2(254), " // String; can also be CHAR(n)
+ "col_characterstream LONG, " // CharacterStream or AsciiStream
+ "col_bytes RAW(2000), " // byte[]; can also be LONG RAW(n)
+ "col_binarystream RAW(2000), " // BinaryStream; can also be LONG RAW(n)
+ "col_timestamp DATE, " // Timestamp
+ "col_clob CLOB, " // Clob
+ "col_blob BLOB, " // Blob; can also be BFILE
+ "col_array number_varray, " // oracle.sql.ARRAY
+ "col_object my_object)"; // oracle.sql.OBJECT
stmt.executeUpdate(sql);
}
}
</source>
Demo ResultSet Oracle
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DemoResultSetOracle {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "username";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
System.out.println("conn=" + conn);
// prepare query
String query = "select id, name, age from employees";
// create a statement
stmt = conn.createStatement();
// execute query and return result as a ResultSet
rs = stmt.executeQuery(query);
// extract data from the ResultSet
while (rs.next()) {
String id = rs.getString(1);
String name = rs.getString(2);
int age = rs.getInt(3);
System.out.println("id=" + id);
System.out.println("name=" + name);
System.out.println("age=" + age);
System.out.println("---------------");
}
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
} finally {
// release database resources
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</source>
Get Column Names From ResultSet for Oracle
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; public class GetColumnNamesFromResultSet_Oracle {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "username";
String password = "pass";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void getColumnNames(ResultSet rs) throws SQLException {
if (rs == null) {
return;
}
// get result set meta data
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
// get the column names; column indexes start from 1
for (int i = 1; i < numberOfColumns + 1; i++) {
String columnName = rsMetaData.getColumnName(i);
// Get the name of the column"s table name
String tableName = rsMetaData.getTableName(i);
System.out.println("column name=" + columnName + " table=" + tableName + "");
}
}
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
// prepare query
String query = "select id, name, age from employees";
// create a statement
stmt = conn.createStatement();
// execute query and return result as a ResultSet
rs = stmt.executeQuery(query);
// get the column names from the ResultSet
getColumnNames(rs);
} catch (Exception e) {
e.printStackTrace();
} finally {
// release database resources
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</source>
Get Object From Oracle Database Using STRUCT
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
conn = getOracleConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT emp, age FROM employee");
while (rs.next()) {
oracle.sql.STRUCT emp = (oracle.sql.STRUCT) rs.getObject(1);
Object[] empValues = emp.getAttributes();
String name = (String) empValues[0];
java.math.BigDecimal badgeNumber = (java.math.BigDecimal) empValues[1];
int age = rs.getInt(2);
System.out.println("name=" + name);
System.out.println("badgeNumber=" + badgeNumber);
System.out.println("age=" + age);
}
rs.close();
stmt.close();
conn.close();
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "userName";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
</source>
Get Oracle Table Names
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getOracleConnection();
Statement stmt = null;
ResultSet rs = null;
stmt = conn.createStatement();
//only for Oracle
rs = stmt.executeQuery("select object_name from user_objects where object_type = "TABLE"");
while (rs.next()) {
String tableName = rs.getString(1);
System.out.println("tableName=" + tableName);
}
stmt.close();
conn.close();
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:caspian";
String username = "mp";
String password = "mp2";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
</source>
Get Parameter MetaData From Oracle JDBC Driver
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ParameterMetaData; import java.sql.PreparedStatement; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getOracleConnection();
Statement st = conn.createStatement();
st.executeUpdate("drop table survey;");
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
PreparedStatement pstmt = null;
ParameterMetaData paramMetaData = null;
String query = "select * from survey where id > ? ";
System.out.println("conn=" + conn);
pstmt = conn.prepareStatement(query);
paramMetaData = pstmt.getParameterMetaData();
if (paramMetaData == null) {
System.out.println("db vendor does NOT support ParameterMetaData");
} else {
System.out.println("db vendor supports ParameterMetaData");
// find out the number of dynamic parameters
int paramCount = paramMetaData.getParameterCount();
System.out.println("paramCount=" + paramCount);
}
pstmt.close();
conn.close();
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:caspian";
String username = "mp";
String password = "mp2";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
</source>
Insert BLOG(Picture or Photo) Data Type Into Oracle Database
<source lang="java">
/* Defining the Table: Oracle and MySql create table MyPictures (
id INT PRIMARY KEY, name VARCHAR(0), photo BLOB
);
- /
import java.io.File; import java.io.FileInputStream; import java.io.OutputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import oracle.jdbc.OracleResultSet; public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getOracleConnection();
int rows = 0;
FileInputStream fin = null;
OutputStream out = null;
ResultSet rs = null;
Statement stmt = null;
oracle.sql.BLOB photo = null;
conn.setAutoCommit(false);
stmt = conn.createStatement();
String id = "001";
String binaryFileName = "fileName.dat";
rows = stmt.executeUpdate("insert into my_pictures(id, photo ) values ("" + id
+ "", empty_blob() )");
System.out.println(rows + " rows inserted");
rs = stmt.executeQuery("select photo from my_pictures where id = "" + id
+ "" for update nowait");
rs.next();
photo = ((OracleResultSet) rs).getBLOB(1);
fin = new FileInputStream(new File(binaryFileName));
out = photo.getBinaryOutputStream();
// Get the optimal buffer size from the BLOB
byte[] buffer = new byte[photo.getBufferSize()];
int length = 0;
while ((length = fin.read(buffer)) != -1) {
out.write(buffer, 0, length);
}
conn.rumit();
out.close();
fin.close();
rs.close();
stmt.close();
conn.close();
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "userName";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
</source>
Insert custom type to Oracle
<source lang="java">
import java.io.Serializable; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLData; import java.sql.SQLException; import java.sql.SQLInput; import java.sql.SQLOutput; public class InsertCustomType_Oracle {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "userName";
String password = "pass";
Class.forName(driver); // load Oracle driver
return DriverManager.getConnection(url, username, password);
}
public static void main(String[] args) {
String id = "001";
String isbn = "1234567890";
String title = "java demo";
String author = "jexp";
int edition = 1;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
String insert = "insert into book_table values(?, BOOK(?, ?, ?, ?))";
pstmt = conn.prepareStatement(insert);
pstmt.setString(1, id);
pstmt.setString(2, isbn);
pstmt.setString(3, title);
pstmt.setString(4, author);
pstmt.setInt(5, edition);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
} finally {
try {
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
} /**
* A class to hold a copy of "BOOK" data type */
class Book implements SQLData, Serializable {
public static final String SQL_TYPE_NAME = "BOOK";
public String isbn;
public String title;
public String author;
public int edition;
public Book() {
}
public Book(String isbn, String title, String author, int edition) {
this.isbn = isbn;
this.title = title;
this.author = author;
this.edition = edition;
}
// retrieves the fully qualified name of the SQL
// user-defined type that this object represents.
public String getSQLTypeName() {
return SQL_TYPE_NAME;
}
// populates this object with data it reads from stream
public void readSQL(SQLInput stream, String sqlType) throws SQLException {
this.isbn = stream.readString();
this.title = stream.readString();
this.author = stream.readString();
this.edition = stream.readInt();
}
// writes this object to stream
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeString(this.isbn);
stream.writeString(this.title);
stream.writeString(this.author);
stream.writeInt(this.edition);
}
public void print() {
System.out.println("isbn=" + isbn);
System.out.println("title=" + title);
System.out.println("author=" + author);
System.out.println("edition=" + edition);
}
}
</source>
Inserting an OBJECT Value into an Oracle Table
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1521";
String sid = "mydatabase";
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
Statement stmt = connection.createStatement();
stmt.execute("CREATE TYPE object2 AS OBJECT(col_string2 VARCHAR(30), col_integer2 NUMBER)");
stmt.execute("CREATE TYPE object1 AS OBJECT(col_string1 VARCHAR(30), col_integer2 object2)");
stmt.execute("CREATE TABLE object1_table(col_integer NUMBER, col_object1 object1)");
stmt.execute("INSERT INTO object1_table VALUES(1, object1("str1", object2("obj2str1", 123)))");
}
}
</source>
OracleDataSource Demo
<source lang="java">
import java.sql.*; import oracle.jdbc.pool.*; public class TestThinDSApp {
public static void main(String args[]) throws ClassNotFoundException,
SQLException {
// These settings are typically configured in JNDI
// so they a implementation specific
OracleDataSource ds = new OracleDataSource();
ds.setDriverType("thin");
ds.setServerName("dssw2k01");
ds.setPortNumber(1521);
ds.setDatabaseName("orcl"); // sid
ds.setUser("scott");
ds.setPassword("tiger");
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rset = stmt
.executeQuery("select "Hello Thin driver data source tester "||"
+ "initcap(USER)||"!" result from dual");
if (rset.next())
System.out.println(rset.getString(1));
rset.close();
stmt.close();
conn.close();
}
}
</source>
Register custome type to Oracle
<source lang="java">
import java.util.*; import java.io.*; import java.sql.*; public class InsertCustomType2_Oracle {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "name";
String password = "pass";
Class.forName(driver); // load Oracle driver
return DriverManager.getConnection(url, username, password);
}
public static void main(String[] args) {
String id = "001";
String isbn = "1234567890";
String title = "Java Oracle";
String author = "jexp";
int edition = 1;
// create the Book object
Book book = new Book(isbn, title, author, edition);
book.print();
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
// create type map
java.util.Map map = conn.getTypeMap();
System.out.println("map=" + map);
map.put("BOOK", Class.forName("Book"));
System.out.println("map=" + map);
String insert = "insert into book_table(ID, BOOK) values(?, ?)";
pstmt = conn.prepareStatement(insert);
pstmt.setString(1, id);
pstmt.setObject(2, book);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
} finally {
try {
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
} /**
* A class to hold a copy of "BOOK" data type */
class Book implements SQLData, Serializable {
public static final String SQL_TYPE_NAME = "BOOK";
public String isbn;
public String title;
public String author;
public int edition;
public Book() {
}
public Book(String isbn, String title, String author, int edition) {
this.isbn = isbn;
this.title = title;
this.author = author;
this.edition = edition;
}
// retrieves the fully qualified name of the SQL
// user-defined type that this object represents.
public String getSQLTypeName() {
return SQL_TYPE_NAME;
}
// populates this object with data it reads from stream
public void readSQL(SQLInput stream, String sqlType) throws SQLException {
this.isbn = stream.readString();
this.title = stream.readString();
this.author = stream.readString();
this.edition = stream.readInt();
}
// writes this object to stream
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeString(this.isbn);
stream.writeString(this.title);
stream.writeString(this.author);
stream.writeInt(this.edition);
}
public void print() {
System.out.println("isbn=" + isbn);
System.out.println("title=" + title);
System.out.println("author=" + author);
System.out.println("edition=" + edition);
}
}
</source>
Serialized And Deserialize Object Oracle
<source lang="java">
/* Code revised from JDBC Recipes: A Problem-Solution Approach
- By Mahmoud Parsian
- ISBN: 1-59059-520-3
- 664 pp.
- Published: Sep 2005
publisher: apress.ru
- /
import java.io.InputStream; import java.io.ObjectInputStream; import java.io.ObjectOutputStream; import java.io.OutputStream; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import oracle.sql.BLOB; /**** CREATE TABLE java_objects (object_id NUMBER, object_name varchar(128), object_value BLOB DEFAULT empty_blob(), primary key (object_id)); SQL> desc java_objects;
Name Null? Type ----------------------------------------- -------- ---------------------------- OBJECT_ID NOT NULL NUMBER OBJECT_NAME VARCHAR2(128) OBJECT_VALUE BLOB
SQL> select SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, LAST_NUMBER from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
---------- ---------- ------------ -----------
ID_SEQ 1 1.0000E+27 1 21 JAVA_OBJECT_SEQUENCE 1 1.0000E+27 1 1
- /
public class Main {
public static void main(String[] args) throws Exception {
String WRITE_OBJECT_SQL = "BEGIN "
+ " INSERT INTO java_objects(object_id, object_name, object_value) "
+ " VALUES (?, ?, empty_blob()) " + " RETURN object_value INTO ?; " + "END;";
String READ_OBJECT_SQL = "SELECT object_value FROM java_objects WHERE object_id = ?";
Connection conn = getOracleConnection();
conn.setAutoCommit(false);
List<Object> list = new ArrayList<Object>();
list.add("This is a short string.");
list.add(new Integer(1234));
list.add(new java.util.Date());
// write object to Oracle
long id = 0001;
String className = list.getClass().getName();
CallableStatement cstmt = conn.prepareCall(WRITE_OBJECT_SQL);
cstmt.setLong(1, id);
cstmt.setString(2, className);
cstmt.registerOutParameter(3, java.sql.Types.BLOB);
cstmt.executeUpdate();
BLOB blob = (BLOB) cstmt.getBlob(3);
OutputStream os = blob.getBinaryOutputStream();
ObjectOutputStream oop = new ObjectOutputStream(os);
oop.writeObject(list);
oop.flush();
oop.close();
os.close();
// Read object from oracle
PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL);
pstmt.setLong(1, id);
ResultSet rs = pstmt.executeQuery();
rs.next();
InputStream is = rs.getBlob(1).getBinaryStream();
ObjectInputStream oip = new ObjectInputStream(is);
Object object = oip.readObject();
className = object.getClass().getName();
oip.close();
is.close();
rs.close();
pstmt.close();
conn.rumit();
// de-serialize list a java object from a given objectID
List listFromDatabase = (List) object;
System.out.println("[After De-Serialization] list=" + listFromDatabase);
conn.close();
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "userName";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
</source>
Test Data Encryption Integrity
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.Properties; public class TestDataEncryptionIntegrity {
public static void main(String[] argv) throws Exception {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Properties prop = new Properties();
prop.setProperty("user", "scott");
prop.setProperty("password", "tiger");
prop.setProperty("oracle.net.encryption_client", "REQUIRED");
prop.setProperty("oracle.net.encryption_types_client", "( RC4_40 )");
prop.setProperty("oracle.net.crypto_checksum_client", "REQUIRED");
prop.setProperty("oracle.net.crypto_checksum_types_client", "( MD5 )");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@dssw2k01:1521:orcl", prop);
Statement stmt = conn.createStatement();
ResultSet rset = stmt
.executeQuery("select "Hello Thin driver Encryption & Integrity "
+ "tester "||USER||"!" result from dual");
while (rset.next())
System.out.println(rset.getString(1));
rset.close();
stmt.close();
conn.close();
}
}
</source>
Test DataSource LookUp
<source lang="java">
/* Java Programming with Oracle JDBC by Donald Bales ISBN: 059600088X Publisher: O"Reilly
- /
import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; public class TestDSLookUp {
public static void main(String[] args) throws SQLException, NamingException {
Context ctx = null;
try {
Properties prop = new Properties();
prop.setProperty(Context.INITIAL_CONTEXT_FACTORY,
"com.sun.jndi.fscontext.RefFSContextFactory");
prop.setProperty(Context.PROVIDER_URL, "file:/JNDI/JDBC");
ctx = new InitialContext(prop);
} catch (NamingException ne) {
System.err.println(ne.getMessage());
}
DataSource ds = (DataSource) ctx.lookup("joe");
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rset = stmt
.executeQuery("select "Hello Thin driver data source tester "||"
+ "initcap(USER)||"!" result from dual");
if (rset.next())
System.out.println(rset.getString(1));
rset.close();
stmt.close();
conn.close();
}
}
</source>
Test OCINet 8 App
<source lang="java">
/* Java Programming with Oracle JDBC by Donald Bales ISBN: 059600088X Publisher: O"Reilly
- /
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestOCINet8App {
// Test the Net8 syntax
public static void main(String args[]) throws ClassNotFoundException,
SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
// or you can use:
// DriverManager.registerDriver(
// new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(
"jdbc:oracle:oci8:@(DESCRIPTION = "
+ "(ADDRESS_LIST = (ADDRESS = "
+ "(PROTOCOL = TCP)(HOST = dssw2k01)(PORT = 1521)))"
+ "(CONNECT_DATA = (SERVICE_NAME = dssw2k01)))",
"scott", "tiger");
Statement stmt = conn.createStatement();
ResultSet rset = stmt
.executeQuery("select "Hello OCI driver tester "||USER||"!" result from dual");
while (rset.next())
System.out.println(rset.getString(1));
rset.close();
stmt.close();
conn.close();
}
}
</source>
Test Oracle JDBC Driver Installation
<source lang="java">
public class TestJDBCDriverInstallation_Oracle {
public static void main(String[] args) {
System.out.println("TestJDBCDriverInstallation_Oracle begin");
try {
String className = "oracle.jdbc.driver.OracleDriver";
Class driverObject = Class.forName(className);
System.out.println("driverObject=" + driverObject);
System.out.println("your installation of JDBC Driver OK.");
} catch (Exception e) {
// your installation of JDBC Driver Failed
System.out.println("Failed: JDBC Driver Error: " + e.getMessage());
}
System.out.println("TestJDBCDriverInstallation_Oracle end.");
}
}
</source>
Test SSL
<source lang="java">
/* Java Programming with Oracle JDBC by Donald Bales ISBN: 059600088X Publisher: O"Reilly
- /
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.Properties; public class TestSSL {
public static void main(String[] argv) throws Exception {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Properties prop = new Properties();
prop.setProperty("user", "scott");
prop.setProperty("password", "tiger");
// THIS DOES NOT WORK YET
prop
.setProperty("oracle.net.ssl_cipher_suites",
"(ssl_rsa_export_with_rc4_40_md5, ssl_rsa_export_with_des40_cbc_sha)");
prop.setProperty("oracle.net.ssl_client_authentication", "false");
prop.setProperty("oracle.net.ssl_version", "3.0");
prop.setProperty("oracle.net.encryption_client", "REJECTED");
prop.setProperty("oracle.net.crypto_checksum_client", "REJECTED");
Connection conn = DriverManager
.getConnection(
"jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = dssw2k01)(PORT = 2484))) (CONNECT_DATA = (SERVICE_NAME = DSSW2K01)))",
prop);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select "Hello Thin driver SSL "
+ "tester "||USER||"!" result from dual");
while (rset.next())
System.out.println(rset.getString(1));
rset.close();
stmt.close();
conn.close();
}
}
</source>
