Skip to content

Commit d7aa982

Browse files
nsundaransundara
authored
Merge pull request oracle-samples#145 from oracle/nsundara-onpremquickstart
Adding CreateUser.java and QuickStart.java
2 parents 1d2ea23 + 4b4914b commit d7aa982

File tree

2 files changed

+223
-0
lines changed

2 files changed

+223
-0
lines changed

‎java/jdbc/ConnectionSamples/CreateUser.java

Lines changed: 90 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,90 @@
1+
/* Copyright (c) 2021, Oracle and/or its affiliates. All rights reserved.
2+
Licensed under the Universal Permissive License v 1.0
3+
as shown at http://oss.oracle.com/licenses/upl
4+
*/
5+
6+
/*
7+
DESCRIPTION
8+
The code sample creates a new database user and grants the required privileges.
9+
(a) Edit this file and update the connection URL along with the admin username and password.
10+
(b) Also, provide a new database user and password that you want to create.
11+
12+
NOTES Use JDK 1.8 and above
13+
14+
MODIFIED (MM/DD/YY)
15+
nbsundar 02/17/21 - Creation (Contributor - kmensah)
16+
*/
17+
import java.sql.Connection;
18+
import java.sql.SQLException;
19+
import java.sql.Statement;
20+
21+
import oracle.ucp.jdbc.PoolDataSourceFactory;
22+
import oracle.ucp.jdbc.PoolDataSource;
23+
24+
public class CreateUser {
25+
// The following connection string is pointing to Oracle XE database.
26+
// Change this URL to match your target database (Oracle XE or else).
27+
final static String DB_URL="jdbc:oracle:thin:@//localhost:1521/XEPDB1";
28+
// Enter the database admin user
29+
// It is usually "sys as sysdba" for Oracle XE database.
30+
final static String AdminUSER = "<DBAdminUser>";
31+
// Enter the password for the admin user
32+
final static String AdminPASSWORD = "<DBAdminPassword>";
33+
34+
// Enter the new database user that you want to create
35+
final static String newDBUser = "<db-new-username>";
36+
// Enter the password for the new database user that you want to create
37+
final static String newDBPassword = "<db-new-password>";
38+
final static String CONN_FACTORY_CLASS_NAME="oracle.jdbc.pool.OracleDataSource";
39+
40+
/*
41+
* Sample to create a new database user and password and grant the required privileges.
42+
* Requirement: database connection string, admin user and admin password
43+
*/
44+
public static void main(String args[]) throws Exception {
45+
// Get the PoolDataSource for UCP
46+
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
47+
// Set the connection factory
48+
pds.setConnectionFactoryClassName(CONN_FACTORY_CLASS_NAME);
49+
50+
pds.setURL(DB_URL);
51+
pds.setUser(AdminUSER);
52+
pds.setPassword(AdminPASSWORD);
53+
pds.setConnectionPoolName("JDBC_UCP_POOL");
54+
55+
// Create a new database user along with granting the required privileges.
56+
String createUserSQL = "BEGIN " +
57+
"EXECUTE IMMEDIATE ('CREATE USER " + newDBUser + " IDENTIFIED BY " + newDBPassword +
58+
" DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS'); " +
59+
"EXECUTE IMMEDIATE ('GRANT CREATE SESSION, CREATE VIEW, CREATE SEQUENCE, " +
60+
" CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, " +
61+
" CREATE MATERIALIZED VIEW TO " + newDBUser + "'); " +
62+
"END;";
63+
64+
// Set the connection pool properties
65+
pds.setInitialPoolSize(5);
66+
pds.setMinPoolSize(5);
67+
pds.setMaxPoolSize(20);
68+
pds.setTimeoutCheckInterval(5);
69+
pds.setInactiveConnectionTimeout(10);
70+
71+
// Get the database connection from UCP.
72+
try (Connection conn = pds.getConnection()) {
73+
conn.setAutoCommit(false);
74+
// Prepare a statement to execute the SQL Statement.
75+
Statement statement = conn.createStatement();
76+
// Create a new database user and grant privileges
77+
statement.executeUpdate(createUserSQL);
78+
System.out.println("New Database user " + newDBUser + " created");
79+
} catch (SQLException e) {
80+
System.out.println("CreateUser - "
81+
+ "CreateUser - SQLException occurred : " + e.getMessage());
82+
}
83+
} // End of main
84+
} // End of CreateUser
85+
86+
87+
88+
89+
90+

‎java/jdbc/ConnectionSamples/QuickStart.java

Lines changed: 133 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,133 @@
1+
/* Copyright (c) 2021, Oracle and/or its affiliates. All rights reserved.
2+
Licensed under the Universal Permissive License v 1.0
3+
as shown at http://oss.oracle.com/licenses/upl
4+
*/
5+
6+
/*
7+
DESCRIPTION
8+
The code sample connects to the Oracle Database and creates a table 'todoitem'.
9+
It inserts few records into this table and displays the list of tasks and task completion status.
10+
Edit this file and update the connection URL along with the database username and password
11+
that point to your database.
12+
13+
NOTES
14+
Use JDK 1.8 and above
15+
16+
MODIFIED (MM/DD/YY)
17+
nbsundar 02/17/21 - Creation (Contributor - kmensah)
18+
*/
19+
20+
package com.oracle.jdbctest;
21+
22+
import java.sql.Connection;
23+
import java.sql.ResultSet;
24+
import java.sql.SQLException;
25+
import java.sql.Statement;
26+
import java.sql.PreparedStatement;
27+
import java.util.Properties;
28+
29+
import oracle.ucp.jdbc.PoolDataSourceFactory;
30+
import oracle.ucp.jdbc.PoolDataSource;
31+
32+
public class QuickStart {
33+
// The following connection string is pointing to Oracle XE database.
34+
// Change this URL to match your target Oracle database (XE or else)
35+
final static String DB_URL="jdbc:oracle:thin:@//localhost:1521/XEPDB1";
36+
// Enter the database user
37+
final static String DB_USER = "<db-user>";
38+
// Enter the database password
39+
final static String DB_PASSWORD = "<db-password>";
40+
final static String CONN_FACTORY_CLASS_NAME="oracle.jdbc.pool.OracleDataSource";
41+
42+
/*
43+
* The sample creates a table 'todoitem' that lists tasks and task completion status.
44+
* Requirement: database connection string, database user and database password
45+
*/
46+
public static void main(String args[]) throws Exception {
47+
48+
// Get a pooled connection
49+
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
50+
// Set the connection factory
51+
pds.setConnectionFactoryClassName(CONN_FACTORY_CLASS_NAME);
52+
pds.setURL(DB_URL);
53+
pds.setUser(DB_USER);
54+
pds.setPassword(DB_PASSWORD);
55+
pds.setConnectionPoolName("JDBC_UCP_POOL");
56+
57+
// Set the connection pool properties
58+
pds.setInitialPoolSize(5);
59+
pds.setMinPoolSize(5);
60+
pds.setMaxPoolSize(20);
61+
pds.setTimeoutCheckInterval(5);
62+
pds.setInactiveConnectionTimeout(10);
63+
64+
// Get the database connection from UCP
65+
try (Connection conn = pds.getConnection()) {
66+
doSQLWork(conn);
67+
}
68+
}
69+
70+
/*
71+
* Creates a 'todoitem' table, insert few rows, and select the data from
72+
* the table created. Remove the table after verifying the data.
73+
*/
74+
public static void doSQLWork(Connection conn) {
75+
try {
76+
conn.setAutoCommit(false);
77+
// Prepare a statement to execute the SQL Statement.
78+
Statement statement = conn.createStatement();
79+
80+
// Create a table 'todoitem'
81+
String createSQL = "CREATE TABLE todoitem "
82+
+ "(id NUMBER GENERATED ALWAYS AS IDENTITY,"
83+
+ " description VARCHAR2(4000), "
84+
+ " creation_ts TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,"
85+
+ " done NUMBER(1, 0), PRIMARY KEY(id))";
86+
87+
statement.executeUpdate(createSQL);
88+
System.out.println("New table 'todoitem' is created");
89+
90+
//Sample data for populating the 'todoitem'
91+
String[] description = { "Task 1", "Task 2", "Task 3", "Task 4", "Task 5" };
92+
int[] done = { 0, 0, 1, 0, 1 };
93+
94+
// Insert some records into the table 'todoitem'
95+
PreparedStatement prepStatement = conn.prepareStatement("INSERT INTO "
96+
+ " todoitem (description, done) VALUES(?, ?)");
97+
for(int row = 0; row < description.length; row++) {
98+
prepStatement.setString(1, description[row]);
99+
prepStatement.setInt(2, done[row]);
100+
prepStatement.addBatch();
101+
}
102+
prepStatement.executeBatch();
103+
104+
System.out.println("New records are inserted");
105+
106+
// Query the 'todoitem' table to list tasks and task completion status
107+
ResultSet resultSet = statement.executeQuery("SELECT DESCRIPTION, DONE FROM TODOITEM");
108+
System.out.println("\nNew table 'todoitem' contains:");
109+
System.out.println("DESCRIPTION" + "\t" + "DONE");
110+
System.out.println("--------------------------");
111+
while (resultSet.next()) {
112+
System.out.println(resultSet.getString(1) + "\t\t" + resultSet.getInt(2));
113+
}
114+
System.out.println("\nCongratulations!! You have successfully used Oracle Database");
115+
}
116+
catch (SQLException e) {
117+
System.out.println("QuickStart - "
118+
+ "doSQLWork()- SQLException occurred : " + e.getMessage());
119+
}
120+
finally {
121+
// Clean-up after everything
122+
try (Statement statement = conn.createStatement()) {
123+
statement.execute("DROP TABLE TODOITEM");
124+
}
125+
catch (SQLException e) {
126+
System.out.println("QuickStart - "
127+
+ "doSQLWork()- SQLException occurred : " + e.getMessage());
128+
}
129+
}
130+
}
131+
}
132+
133+

0 commit comments

Comments
 (0)