Connect Oracle Database "Go" with JDBC

1. JDBC is a technology developed by Sun to connect databases in the Java language.

   First, the basic knowledge of JDBC

   JDBC (Java Data Base Connectivity,java database connection) is a Java API for executing SQL statements that provides unified access to a variety of relational databases, consisting of a set of classes and interfaces written in the Java language. JDBC provides a standard API for database developers to build more advanced tools and interfaces that enable database developers to write database applications with pure Java APIs, and to run across platforms and not subject to database vendor constraints.

   1, cross-platform operation: This is the Java language inherits the "once compiled, run Everywhere" feature;

   2, is not limited by the database vendor: The ingenious is that JDBC has two interfaces, one for the application layer, the role is to enable developers to call the database through SQL and processing results, without having to consider the database provider, the other is the driver layer, processing and specific driver interaction, The JDBC driver can use the JDBC API to create bridges between Java programs and data sources. The application needs to be written once and can be moved to a variety of drivers to run. Sun provides a driver manager, a database vendor-such as MySQL, Oracle, which provides drivers that meet the requirements of the driver manager and can be identified to work properly. Therefore, JDBC is not limited by the database vendor.

   
   The JDBC API can be used as a link between Java applications and a variety of relational databases, at the same time bring convenience and negative impact, the following are the advantages and disadvantages of JDBC. The advantages are as follows:

   
   Easy to operate: JDBC makes it unnecessary for developers to use complex drive invocation commands and functions;
   Strong portability: JDBC supports different relational databases, so you can enable the same application to support multiple database accesses as long as the appropriate driver is loaded;
   Versatility good: JDBC-ODBC bridging drives replace the JDBC function with ODBC;
   Object-oriented: a common JDBC database connection can be encapsulated into a class that is invoked directly when used.

   Disadvantages are as follows:

   
   The speed of accessing data records is affected by some degree;
   Changing the data source is difficult: JDBC supports multiple databases, and the operations between the various databases are different, which can cause a lot of trouble in changing the data source.

   
   Ii. the process and principle of JDBC Connection database

   1. Load the driver for the specified database in the development environment. For example, in the next experiment, the database used is Oracle, so you need to download the Oracle JDBC-enabled driver (in fact this place does not need to download the JDBC driver on the official web, there is a Ojdbc14.jar file on the locally installed Oracle) While the development environment is myeclipse, loading the downloaded drivers into the development environment (for example, explains how to load).

   
   2. Load the driver in the Java program. In a Java program, you can load drivers that are added to the development environment by means of "class.forname (" Specify the Database Driver "), such as the code for loading Oracle's data drivers: Class.forName (" Oracle.jdbc.driver.OracleDriver ")

   
   3. Create a data Connection object: Create a database Connection object connection through the DriverManager class. The DriverManager class acts between the Java program and the JDBC driver to check whether the loaded driver can establish a connection, and then, through its Getconnection method, creates a JDBC based on the URL, user name, and password of the database. The Connection object. such as: Connection Connection = drivermanager.geiconnection ("URL of the connection database", "username", "password"). where Url= protocol name +IP address (domain name) + port + database name, user name and password refers to the user name and password used to log in to the database. A concrete example of creating Oracle's database Connection code is as follows:

   Conn=drivermanager.getconnection (URL, user, password);

   4. Create a Statement object: The Statement class is primarily used to execute a static SQL statement and return the object to which it produces results. You can create a statement object by using the Createstatement () method of the Connection object. For example: Statement statament = Connection.createstatement (); The specific example creates the statement object code as follows:

   Statement statamentmysql =connectmysql.createstatement ();

   5. Call the relevant method of the statement object to execute the corresponding SQL statement: Use the Execuupdate () method to update the data, including insertions and deletions, such as code to insert a piece of data into the staff table:

   Statement.excuteupdate ("INSERT into the staff (name, age, Sex,address, Depart, Worklen,wage)" + "VALUES (' Tom1 ', 321, ' M ', ' C Hina ', ' personnel ', ' 3 ', ' 3000 ');

   Queries for data are made by calling the ExecuteQuery () method of the Statement object, and the query results are Resulset objects, Resulset represents the collection of data returned after the query database is executed, and the Resulset object has pointers that can point to the current data row. Through the object's next () method, the pointer points to the next line, and then the data is taken out with the column number or field name. If the next () method returns NULL, no data exists in the next row. Use the sample code as follows:

   ResultSet Resultsel = Statement.executequery ("SELECT * from staff");

   
   6. Close the database connection: When you are finished using the database or do not need to access the database, close the data connection in a timely manner through the connection close () method.

   
   Third, the JDBC application example experiment

   Experimental steps:
   S1, download the Ojdbc14.jar driver file, and put the file in your project;
   S2, add the Oracle driver to the project in MyEclipse: On the project name, right-click Build Path->add External archiver and select the file you just placed in the project, click OK.
   S3, open Oracle's services, and build a table in Oracle.
   S4, write the MyEclipse and Oracle Connection program:
   [CPP] View Plaincopyprint?<span style= "font-size:16px" >import java.sql.Connection;
   Import Java.sql.DriverManager;
   Import java.sql.PreparedStatement;
   Import Java.sql.ResultSet;
   Import java.sql.Statement;
   Import java.sql.*;
   
   public class Jdbc_test {
   ORCL is the database name in the Oracle database, and localhost represents the Oracle database that connects to the native
   1521 port number for the connection
   private static String url= "Jdbc:oracle:thin: @localhost: 1521:ORCL";
   System is the user name for logging into Oracle database
   private static String user= "System";
   Manager is the password for user name System
   private static String password= "manager";
   public static Connection Conn;
   public static PreparedStatement PS;
   public static ResultSet RS;
   public static Statement St;
   Ways to connect to a database
   public void getconnection () {
   try {
   Initializing the driver package
   Class.forName ("Oracle.jdbc.driver.OracleDriver");
   Assign a value to conn based on the database connection character, name, and password
   Conn=drivermanager.getconnection (URL, user, password);
   
   } catch (Exception e) {
   Todo:handle exception
   E.printstacktrace ();
   }
   }
   Test whether the connection to the Oracle database is successful
   public static void Main (string[] args) {
   Jdbc_test basedao=new jdbc_test ();
   Basedao.getconnection ();
   if (conn==null) {
   SYSTEM.OUT.PRINTLN ("Connection to Oracle database failed! ");
   }else{
   SYSTEM.OUT.PRINTLN ("Connection to Oracle database was successful! ");
   }
   }
   }
   </SPAN>

   Import java.sql.Connection;
   Import Java.sql.DriverManager;
   Import java.sql.PreparedStatement;
   Import Java.sql.ResultSet;
   Import java.sql.Statement;
   Import java.sql.*;

   public class Jdbc_test {
   ORCL is the database name in the Oracle database, and localhost represents the Oracle database that connects to the native
   1521 port number for the connection
   private static String url= "Jdbc:oracle:thin: @localhost: 1521:ORCL";
   System is the user name for logging into Oracle database
   private static String user= "System";
   Manager is the password for user name System
   private static String password= "manager";
   public static Connection Conn;
   public static PreparedStatement PS;
   public static ResultSet RS;
   public static Statement St;
   Ways to connect to a database
   public void getconnection () {
   try {
   Initializing the driver package
   Class.forName ("Oracle.jdbc.driver.OracleDriver");
   Assign a value to conn based on the database connection character, name, and password
   Conn=drivermanager.getconnection (URL, user, password);
   
   } catch (Exception e) {
   Todo:handle exception
   E.printstacktrace ();
   }
   }
   Test whether the connection to the Oracle database is successful
   public static void Main (string[] args) {
   Jdbc_test basedao=new jdbc_test ();
   Basedao.getconnection ();
   if (conn==null) {
   SYSTEM.OUT.PRINTLN ("Connection to Oracle database failed! ");
   }else{
   SYSTEM.OUT.PRINTLN ("Connection to Oracle database was successful! ");
   }
   }
   }

   S5, if the above connection has been established, you can use the Java API in JDBC to operate the database, the specific query, insert, delete, update operation is as follows:
   [CPP] View Plaincopyprint?<span style= "font-size:16px" > Code reprinted from: http://blog.csdn.net/cxwen78/article/details/6863696
   
   Import java.sql.Connection;
   Import Java.sql.DriverManager;
   Import Java.sql.ResultSet;
   Import java.sql.SQLException;
   Import java.sql.Statement;
   
   public class Jdbc_test {
   Create a static global variable
   Static Connection Conn;
   
   Static Statement St;
   
   public static void Main (string[] args) {
   Insert (); Insert Add record
   Update (); Update record Data
   Delete (); Deleting records
   Query (); Querying records and displaying
   }
   
   /* Insert data record and output the number of data records inserted */
   public static void Insert () {
   
   conn = getconnection (); To get the connection first, connect to the database
   
   try {
   String sql = "INSERT into the staff (name, age, Sex,address, Depart, Worklen,wage)"
   + "VALUES" (' Tom1 ', +, ' M ', ' China ', ' personnel ', ' 3 ', ' 3000 ') "; SQL statement to insert data
   
   St = (Statement) conn.createstatement (); Create a statement object to execute a static SQL statement
   
   int count = st.executeupdate (SQL); The SQL statement that performs the insert operation and returns the number of inserted data
   
   System.out.println ("Insert" + Count + "bar data" into the staff table); Processing results for output insert operations
   
   Conn.close (); To close a database connection
   
   } catch (SQLException e) {
   System.out.println ("Insert data Failed" + e.getmessage ());
   }
   }
   
   /* Update the records that meet the requirements and return the updated number of records */
   public static void Update () {
   conn = getconnection (); Also, to get the connection first, connect to the database
   try {
   String sql = "Update staff set wage= ' 2200 ' WHERE name = ' Lucy '";//SQL statement to update data
   
   St = (Statement) conn.createstatement (); Create a statement object for executing static SQL statements, ST local variables
   
   int count = st.executeupdate (SQL);//SQL statement that performs the update operation, returns the number of update data
   
   SYSTEM.OUT.PRINTLN (update in "Staff table" + Count + "bar data"); Processing results for output update operations
   
   Conn.close (); To close a database connection
   
   } catch (SQLException e) {
   System.out.println ("Failed to update data");
   }
   }
   
   /* Query the database to output records that match the requirements */
   public static void query () {
   
   conn = getconnection (); Also, to get the connection first, connect to the database
   try {
   String sql = "SELECT * from staff"; SQL statement for querying data
   St = (Statement) conn.createstatement (); Create a statement object for executing static SQL statements, ST local variables
   
   ResultSet rs = st.executequery (SQL); Executes a SQL query statement that returns the result set of the query data
   System.out.println ("The final query results are:");
   while (Rs.next ()) {//To determine if there is still the next data
   
   Get the corresponding value based on the field name
   String name = rs.getstring ("name");
   int age = Rs.getint ("Age");
   String sex = rs.getstring ("Sex");
   String address = rs.getstring ("Address");
   String depart = rs.getstring ("Depart");
   String Worklen = rs.getstring ("Worklen");
   String wage = rs.getstring ("wage");
   
   Outputs the values of the individual fields of the record being traced
   SYSTEM.OUT.PRINTLN (name + "+ Age +" "+ sex +" + address
   + "+ Depart +" "+ Worklen +" "+ wage);
   
   }
   Conn.close (); To close a database connection
   
   } catch (SQLException e) {
   SYSTEM.OUT.PRINTLN ("Query data Failed");
   }
   }
   
   /* Delete compliant records, output status */
   public static void Delete () {
   
   conn = getconnection (); Also, to get the connection first, connect to the database
   try {
   String sql = "Delete from the staff where name = ' Lili '";//SQL statement to delete data
   St = (Statement) conn.createstatement (); Create a statement object for executing static SQL statements, ST local variables
   
   int count = st.executeupdate (SQL);//Execute SQL DELETE statement, return the number of deleted data
   
   System.out.println ("delete" + Count + "bar data \ n" in the "Staff Table"); Processing results of output Delete operations
   
   Conn.close (); To close a database connection
   
   } catch (SQLException e) {
   System.out.println ("Failed to delete data");
   }
   
   }
   
   /* function to get database connections */
   public static Connection getconnection () {
   Connection con = null; Create a Connection object to connect to the database
   try {
   Class.forName ("Com.mysql.jdbc.Driver");//load MySQL data-driven
   
   con = drivermanager.getconnection (
   "Jdbc:mysql://localhost:3306/myuser", "root", "root");//Create a data connection
   
   } catch (Exception e) {
   SYSTEM.OUT.PRINTLN ("Database connection Failed" + e.getmessage ());
   }
   return con; Returns the database connection that was established
   }
   }
   </SPAN>

Connect Oracle Database "Go" with JDBC