Java Tutorial/Database/Oracle
Содержание
- 1 Connect with Oracle"s JDBC Thin Driver
- 2 Create a function named myfuncinout that returns a VARCHAR value
- 3 Create a function named myfuncin which returns a VARCHAR value; the function has an IN parameter named x
- 4 Create a function named myfuncout which returns a VARCHAR value;
- 5 Create a function named myfunc which returns a VARCHAR value; the function has no parameter
- 6 Create an Oracle Table to Store Java Types (Using Statement Objects)
- 7 Create procedure myprocinout with an IN/OUT parameter named x; x is an IN parameter and an OUT parameter
- 8 Create procedure myprocin with an IN parameter named x.
- 9 Create procedure myprocout with an OUT parameter named x
- 10 Creating an OBJECT Type in an Oracle Database
- 11 Creating an Oracle Table to Store Java Types
- 12 Creating a Stored Procedure or Function in an Oracle Database
- 13 Execute Method
- 14 Get list of stored procedure names
- 15 Inserting an OBJECT Value into an Oracle Table
- 16 Rollback a transaction
- 17 Store and retrieve an object from a table
Connect with Oracle"s JDBC Thin Driver
<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 = getConnection();
conn.setAutoCommit(false);
Statement st = conn.createStatement();
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");
rs.close();
st.close();
conn.close();
}
private static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:scorpian";
String username = "username";
String password = "password";
Class.forName(driver);
return DriverManager.getConnection(url, username, password);
}
}</source>
Create a function named myfuncinout that returns a VARCHAR value
<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();
String function = "CREATE OR REPLACE FUNCTION myfuncinout(x IN OUT VARCHAR) RETURN VARCHAR IS "
+ "BEGIN x:= x||"outvalue"; RETURN "a returned string"; END;";
stmt.executeUpdate(function);
}
}</source>
Create a function named myfuncin which returns a VARCHAR value; the function has an IN parameter named x
<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();
String function = "CREATE OR REPLACE FUNCTION myfuncin(x VARCHAR) RETURN VARCHAR IS "
+ "BEGIN RETURN "a return string"||x; END;";
stmt.executeUpdate(function);
}
}</source>
Create a function named myfuncout which returns a VARCHAR value;
<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();
String function = "CREATE OR REPLACE FUNCTION myfuncout(x OUT VARCHAR) RETURN VARCHAR IS "
+ "BEGIN " + "x:= "outvalue"; " + "RETURN "a returned string"; " + "END;";
stmt.executeUpdate(function);
}
}</source>
Create a function named myfunc which returns a VARCHAR value; the function has no parameter
<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();
String function = "CREATE OR REPLACE FUNCTION myfunc RETURN VARCHAR IS BEGIN RETURN "a returned string"; END;";
stmt.executeUpdate(function);
}
}</source>
Create an Oracle Table to Store Java Types (Using Statement Objects)
<source lang="java">
// Create a VARRAY type
stmt.execute("CREATE TYPE char_varray AS VARRAY(10) OF VARCHAR(20)");
// Create an OBJECT type
stmt.execute ("CREATE TYPE oracle_object AS OBJECT(column_string VARCHAR(128), column_integer INTEGER)");
StringBuffer sql = new StringBuffer("CREATE TABLE oracle_all_types(");
// Column Name Oracle Type Java Type
sql.append("column_short SMALLINT, "); // short
sql.append("column_int INTEGER, "); // int
sql.append("column_float REAL, "); // float
sql.append("column_double DOUBLE PRECISION,"); // double
sql.append("column_bigdecimal DECIMAL(13,2), "); // BigDecimal
sql.append("column_string VARCHAR2(254), "); // String
sql.append("column_charStream LONG, "); // CharacterStream
sql.append("column_bytes RAW(3000), "); // byte[]
sql.append("column_binarystream RAW(2000), "); // BinaryStream
sql.append("column_timestamp DATE, "); // Timestamp
sql.append("column_clob CLOB, "); // Clob
sql.append("column_blob BLOB, "); // Blob
sql.append("column_bfile BFILE, "); // oracle.sql.BFILE
sql.append("column_array char_varray, "); // oracle.sql.ARRAY
sql.append("column_object oracle_object)"); // oracle.sql.OBJECT
stmt.executeUpdate(sql.toString());</source>
//From Book: JDBC Recipes A Problem-Solution Approach
Create procedure myprocinout with an IN/OUT parameter named x; x is an IN parameter and an OUT parameter
<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);
String procedure = "CREATE OR REPLACE PROCEDURE myprocinout(x IN OUT VARCHAR) IS BEGIN "
+ "INSERT INTO oracle_table VALUES(x); " // Use x as IN parameter
+ "x := "outvalue"; " // Use x as OUT parameter
+ "END;";
Statement stmt = connection.createStatement();
stmt.executeUpdate(procedure);
}
}</source>
Create procedure myprocin with an IN parameter named x.
<source lang="java">
import java.sql.CallableStatement; 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();
// IN is the default mode for parameter, so both "x VARCHAR" and "x IN VARCHAR" are valid
String procedure = "CREATE OR REPLACE PROCEDURE myprocin(x VARCHAR) IS " + "BEGIN "
+ "INSERT INTO oracle_table VALUES(x); " + "END;";
stmt.executeUpdate(procedure);
}
}</source>
Create procedure myprocout with an OUT parameter named x
<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();
String procedure = "CREATE OR REPLACE PROCEDURE myprocout(x OUT VARCHAR) IS BEGIN "
+ "INSERT INTO oracle_table VALUES("string 2"); x := "outvalue"; END;";
stmt.executeUpdate(procedure);
}
}</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>
Creating a Stored Procedure or Function in an Oracle Database
<source lang="java">
import java.sql.CallableStatement; 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);
CallableStatement cs = connection.prepareCall("{? = call myfuncinout(?)}");
Statement stmt = connection.createStatement();
// Create procedure myproc with no parameters
String procedure = "CREATE OR REPLACE PROCEDURE myproc IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES("string 1"); "
+ "END;";
stmt.executeUpdate(procedure);
}
}</source>
Execute Method
<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 ExecuteMethod {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
boolean executeResult;
try {
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver).newInstance();
System.out.println("Connecting to database...");
String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:ORCL";
conn = DriverManager.getConnection(jdbcUrl, "test", "mypwd");
stmt = conn.createStatement();
String sql = "INSERT INTO Employees VALUES" + "(1,"G","4351",{d "1996-12-31"},500)";
executeResult = stmt.execute(sql);
processExecute(stmt, executeResult);
sql = "SELECT * FROM Employees ORDER BY hiredate";
executeResult = stmt.execute(sql);
processExecute(stmt, executeResult);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
public static void processExecute(Statement stmt, boolean executeResult) throws SQLException {
if (!executeResult) {
int updateCount = stmt.getUpdateCount();
System.out.println(updateCount + " row was " + "inserted into Employee table.");
} else {
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
System.out.println(rs.getInt("SSN") + rs.getString("Name")
+ rs.getDouble("Salary") + rs.getDate("Hiredate") + rs.getInt("Loc_id"));
}
}
}
}</source>
Get list of stored procedure names
<source lang="java">
import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; public class Main {
private static String url = "jdbc:oracle:thin:@localhost:1521:javaDemo";
private static String username = "java";
private static String password = "";
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, username, password);
DatabaseMetaData metadata = conn.getMetaData();
ResultSet result = metadata.getProcedures(null, "JAVA", "%");
while (result.next()) {
System.out.println(result.getString("PROCEDURE_CAT") + " - "
+ result.getString("PROCEDURE_SCHEM") + " - " + result.getString("PROCEDURE_NAME"));
}
}
}</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>
Rollback a transaction
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class Rollback {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
boolean executeResult;
try {
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver).newInstance();
System.out.println("Connecting to database...");
String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:ORCL";
conn = DriverManager.getConnection(jdbcUrl, "yourName", "mypwd");
stmt = conn.createStatement();
conn.setAutoCommit(false);
if (!conn.getAutoCommit())
System.out.println("Auto-commit is set to false");
String sql = "INSERT INTO Location VALUES(715,"Houston")";
stmt.executeUpdate(sql);
sql = "INSERT INTO Employees VALUES" + "(8,"K","4351",{d "2000-02-00"},715)";
stmt.executeUpdate(sql);
conn.rumit();
} catch (SQLException se) {
String msg = se.getMessage();
msg = "SQLException occured with message: " + msg;
System.out.println(msg);
System.out.println("Starting rollback operations...");
try {
conn.rollback();
} catch (SQLException se2) {
se2.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}</source>
Store and retrieve an object from a table
<source lang="java">
import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.ObjectInputStream; import java.io.ObjectOutputStream; import java.io.Serializable; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String args[]) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String URL = "jdbc:odbc:dbname";
Connection dbConn = DriverManager.getConnection(URL, "user", "passw");
Employee employee = new Employee(42, "AA", 9);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(baos);
oos.writeObject(employee);
byte[] employeeAsBytes = baos.toByteArray();
PreparedStatement pstmt = dbConn
.prepareStatement("INSERT INTO EMPLOYEE (emp) VALUES(?)");
ByteArrayInputStream bais = new ByteArrayInputStream(employeeAsBytes);
pstmt.setBinaryStream(1, bais, employeeAsBytes.length);
pstmt.executeUpdate();
pstmt.close();
Statement stmt = dbConn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT emp FROM Employee");
while (rs.next()) {
byte[] st = (byte[]) rs.getObject(1);
ByteArrayInputStream baip = new ByteArrayInputStream(st);
ObjectInputStream ois = new ObjectInputStream(baip);
Employee emp = (Employee) ois.readObject();
}
stmt.close();
rs.close();
dbConn.close();
}
} class Employee implements Serializable {
int ID;
String name;
double salary;
public Employee(int ID, String name, double salary) {
this.ID = ID;
this.name = name;
this.salary = salary;
}
}</source>
