Connect Java to Oracle and javaoracle

Connect Java to Oracle and javaoracle

1. Create a user after Oracle Installation

1 -- create user chenh identified by chenh; 3 -- unlock user 4 alter user chenh account unlock; 5 alter user scott account unlock; 6 -- Change Password 7 alter user chenh identified by chenh; 8 -- grant permission 9 grant sysdba to chenh; 10 -- grant the chenh user the permission to create a session, that is, the login permission 11 grant create session to chenh; 12 -- grant the chenh user permission to use the tablespace 13 grant unlimited session to chenh; 14 -- grant the table creation permission 15 grant the create table to chenh; 16 -- grant table deletion permission 17 grant drop table to chenh; 18 -- insert table permission 19 grant insert table to chenh; 20 -- Modify table permission 21 grant update table to chenh; 22 -- view the current database instance name 23 select name from v $ database; 24 -- submit transaction 25 commit;

 

2. After a user is created, a user may be prompted that the user has no permissions on the users.

1 -- solve the problem that user XX has no permissions on users tablespace 2 alter user chenh quota unlimited on users;

 

 

3. Create a new test table and insert several data records to it.

1 -- delete table 2 drop table a; 3 -- create table 4 create table a (5 col1 varchar2 (10), 6 col2 varchar2 (10), 7 col3 varchar2 (10 ), 8 col4 varchar2 (10) 9); 10 -- Query 11 select * from a; 12 -- insert statement multiple times because there is no primary key limit, therefore, 13 insert into a (col1, col2, col3, col4) values ('a1', 'B', 'C', 'D') can be executed multiple times '); 14 15 commit;

4. Create a New java project in eclipse

Open eclipse (myeclipse)> File> New> Project> Java Project)

Project Name: The Project Name is followed by "Next" or "Finish)

5. Download the JAR package for connecting to the database driver from ojdbc6.jar in advance.

Available here: https://files.cnblogs.com/files/1187163927ch/blog.rar

Unzip password: 1187163927

6. Import ojdbc6.jar to the project created in eclipse (myeclipse). Remember to build path.

Open the tree chart of the project, drag the downloaded ojdbc6.jar file to the project, select the file with the left mouse click, and right click Build Path.

7. Create a java class to connect to the database

7.1 database connection driver

1 import java. SQL. connection; 2 import java. SQL. driverManager; 3 import java. SQL. SQLException; 4 5 public class DBConnection {6 public static Connection dbConn (String name, String pass) {7 Connection c = null; 8 try {9 Class. forName ("oracle. jdbc. driver. oracleDriver "); 10 // classnotfoundException will occur if the import driver fails. let's see if something is wrong, such as classpath and these settings 11} catch (ClassNotFoundException e) {12 e. printStackTrace (); 13} 14 try {15 c = DriverManager. getConnection (16 "jdbc: oracle: thin: @ localhost: 1521: chenh", name, pass); 17 // There are four data connection methods, which are the simplest, in general, the web page program chenh is the name of your database instance, In the downloaded file test. in SQL, you can run the following statement to view 18 // "jdbc: oracle: thin: @ computer name: listening port: System Instance name", username, password, 19 // computer name, if you do not know, you can check the computer properties. 20 // The listening port is generally 1521 by default. If you change the port, you can view the listener file listener. ora21 // The system Instance name is generally orcl by default. If yes, use select name from v $ database to check the current instance name. in this program, I changed the instance to chenh2/username and password, which is the username and password used to log on to the database. 23 24 25} catch (SQLException e) {26 e. printStackTrace (); 27} 28 return c; 29} 30}

7.2 query a database

1 import java. SQL. connection; 2 import java. SQL. resultSet; 3 import java. SQL. SQLException; 4 import java. SQL. statement; 5 6 public class DB extends DBConnection {7 private static Connection con = null; 8 private static Statement SQL = null; 9 private static ResultSet rs = null; 10 11 12 public static void main (String [] args) throws SQLException {13 String COL1; 14 String COL2; 15 String COL3; 16 String COL4; 17 try {18 con = dbConn ("chenh", "chenh"); 19 if (con = null) {20 System. out. print ("connection failed"); 21 System. exit (0); 22} 23 SQL = con. createStatement (); 24 rs = SQL .exe cuteQuery ("select * from a"); 25 System. out. println ("COL1" + "" + "COL2" + "" + "COL3" + "" + "COL4"); 26 while (rs. next () {27 COL1 = rs. getString (1); 28 COL2 = rs. getString (2); 29 COL3 = rs. getString (3); 30 COL4 = rs. getString (4); 31 System. out. println (COL1 + "" + COL2 + "" + COL3 + "" + COL4); 32} 33} catch (Exception e) {34 e. printStackTrace (); 35 36 37} finally {38 con. close (); 39} 40} 41}

After checking that no error is reported, Run the button and right-click the project and choose Run As> Java Application to view the following results: