JDBC principle, JDBC Basics programming __ Programming

JDBC WHAT is JDBC

Java Database Connectivity:java The solution for accessing the databases.
JDBC is a landmark solution for Java applications to access databases. Java developers want to access different databases in the same way to implement a Java interface that is not related to a specific database.
JDBC defines a set of standard interfaces, that is, a common API for accessing databases, and different database vendors implement these interfaces according to the characteristics of their respective databases. JDBC Interface and database vendor implementation

Some interfaces are defined in JDBC:
1, Drive Management:
DriverManager
2. Connection interface
Connection
DatabaseMetaData
3, the Statement object interface
Statement
PreparedStatement
CallableStatement
4. Result set interface
ResultSet
ResultSetMetaData JDBC Working principle

JDBC defines interfaces only, and is implemented by individual database vendors.
Programmers use only to invoke the interface, the actual call is the implementation of the bottom of the database manufacturer part.

JDBC access to database work process:
Load driver, establish connection
To create a statement object
Execute SQL statement
Working with result sets
Turn off connection driver interface and driver class loading

To use the JDBC interface, you need to load the implementation part (driver) of the corresponding database first.
Driver class loading mode (Oracle):

Class.forName ("Oracle.jdbc.driver.OracleDriver");

The meaning of this statement is: Load the driver class, the driver class through the static block implementation in DriverManager "autoenrollment." Connection Interface

The connection interface is responsible for the application's connection to the database and, after loading the driver, creates a connection to the specific database using the URL, username, password three parameters.

Class.forName ("Oracle.jdbc.OracleDriver")
///According to the URL connection parameters, find the matching driver object, call its method to get the connection
Connection conn = Drivermanager.getconnection (
"Jdbc:oracle:thin:@192.168.0.26:1521:tarena",
"OpenLab", "open123");

Note that the connection is just an interface, and the real implementation is done by a driver package provided by the database vendor. Statement Interface

The statement interface is used to process SQL statement objects that are sent to the database, created by Connection objects. There are mainly three common methods:

Statement stmt=conn.createstatement ();
1.execute method that returns true if the executed SQL is a query and has a result set, and returns False
Boolean flag = Stmt.execute (SQL) If it is a query-only statement or no result set;
2. Execute Query statement, return result set
resultsetrs = stmt.executequery (sql);
3. Executes the DML statement, returns the number of affected records
int flag = stmt.executeupdate (SQL);

ResultSet Interface

The result set returned after the query SQL statement is executed is received by the ResultSet interface.
Common processing methods: traversal/Judge whether there is a result (login).

String sql = "SELECT * from emp";
resultsetrs = stmt.executequery (sql);
while (Rs.next ()) {
    System.out.println (rs.getint ("empno") + ","
       +rs.getstring ("ename"));

processing result set resultset

ResultSet represents DQL query results, which are 2-D results. It maintains a cursor that reads data internally, by default, when the cursor moves down in the first row of data before the next () method is called, and returns True if the containing data is included in the result set. The result set also provides a good getxxx method for getting the result set cursor to point to the current row data.

Principle:

Case:

/** * Execute DQL statement */public class Demo03 {public static void main (string[) args) throws exception{/
        /Register Drive String driver= "Oracle.jdbc.OracleDriver";;
        Class.forName (driver);
        Connect database String url= "JDBC:ORACLE:THIN:@192.168.201.227:1521:ORCL";
        String user= "OpenLab";
        String pwd= "open123";
        Connection conn=drivermanager.getconnection (URL, user, pwd);
        Create Statement Statement st=conn.createstatement ();
        Execute SQL (DQL) String sql= "SELECT ID, name" + "from Robin_demo";
        ResultSet rs=st.executequery (SQL); Processing results ... the//RS result set contains a cursor, which defaults to the first row of the result set////rs.next (): Moves the result set cursor to the next line//checks for data, and if so returns True,
            Otherwise false while (Rs.next ()) {//getxxx (column name): Returns data for the specified column name in the current row of the result set.
            int id = rs.getint ("id");
            String name=rs.getstring ("name"); Output Query Results SYSTEM.OUT.PRIntln (id+ "," +name);
    }//Close connection conn.close ();
 }
}

using properties to read a configuration file

Properties are APIs in Java that are specifically used to read configuration files. The bottom is that the text file IO properties itself implements the map interface, and the interior is a hash table properties that define the key and value are string types.

Properties Common API Method: Load (Stream) read a configuration file String GetProperty (key) read a property value

Using steps: Creating a Properties Object reading a configuration file using the Load method using GetProperty query property file contents

Case, read the configuration file:

Add a profile in the Resource folder db.properties:

# db.properties
jdbc.driver=oracle.jdbc.oracledriver
jdbc.url=jdbc:oracle:thin:@192.168.201.227:1521:o RCL
Jdbc.username=openlab
jdbc.password=open123

To read the configuration file contents using properties:

 public class Demo05 {public static void main (string[] args) throws ioexception{//Properties is The API designed to read//*.properties files is the text file IO//properties itself implementing the Map interface//interior is a hash table, defining the key and

        Value is//String type. Method: Load (stream) reads the file as a hash list//string GetProperty (key) query value//Use steps//1 Create Properties object Pr
        Operties cfg = new Properties ();
        System.out.println (CFG);
        System.out.println (Cfg.size ());
        System.out.println (Cfg.isempty ()); 2. Use the Load method to read files InputStream in= Demo05.class.getClassLoader (). getResourceAsStream ("Db.proper
        Ties "); 
        After execution, the contents of the file are read into the hash table cfg.load (in);
        System.out.println (CFG);

        System.out.println (Cfg.size ()); 3.
        Find the contents of a file, that is, read the contents of the file String s= cfg.getproperty ("Jdbc.driver"); 
    System.out.println (s); }
}

Using a configuration file, you can save parameters in a program to a configuration file, and modify the parameters of the program only to modify the configuration file. Managing database Connections

In the Software database connection is used very frequently, if each time creates the connection, can cause the code the massive redundancy, the general practice is establishes the database connection tool class, encapsulates the database connection process, unifies the database connection process, the use time can simplify the code.

Implementation step: Create database connection parameter file Db.properties create Dbutils.java Encapsulate Database connection method
Using properties to read the database connection parameter creation method in the configuration folder getconnection encapsulating the database connection process uses the Getconnection method to Create the configuration file Db.properties

# db.properties
jdbc.driver=oracle.jdbc.oracledriver
jdbc.url=jdbc:oracle:thin:@192.168.201.227:1521:o RCL
Jdbc.username=openlab
jdbc.password=open123

Create Dbutils.java

public class Dbutils {static String driver;
    static String URL;
    static String username;

    static String password;
            Read the database connection parameters in the file static{//Initialize the static property//1. Read the configuration file//2 using properties. Find the corresponding parameter values from the configuration file try{
            Properties Cfg=new properties ();
            InputStream in= DbUtils.class.getClassLoader (). getResourceAsStream ("db.properties");
            Cfg.load (in); 
            System.out.println (CFG);
            Initialize the connection parameter Driver=cfg.getproperty ("Jdbc.driver");
            Url=cfg.getproperty ("Jdbc.url");
            Username=cfg.getproperty ("Jdbc.username");
            Password=cfg.getproperty ("Jdbc.password");
        In.close ();
            }catch (Exception e) {e.printstacktrace ();
        throw new RuntimeException (e);
            /** * Encapsulates the process of creating a database connection * simplifies database connections/public static Connection getconnection () {try{ Class.fornAme (driver);
            Connection conn= drivermanager.getconnection (URL, username, password);
        Return conn;
            }catch (Exception e) {e.printstacktrace ();
        throw new RuntimeException (e);
        }//dbutils.java/* Closes the connection method of the database, encapsulates the complex shutdown process * * public static void Close (Connection conn) {
            if (conn!=null) {try {conn.close (); 
            catch (Exception e) {e.printstacktrace ();
 }
        }
    }
}

Description: Driver URL username password is a 4 database connection parameter because only one copy is required, then it is defined as a static variable. The purpose of a static code block is to read the values of 4 database connection parameters from the configuration file. The Getconnection method encapsulates the database connection procedure Close method encapsulates the use of a database connection shutdown process dbutils:

public class Demo06 {public
    static void Main (string[] args) {
        Connection conn=null;
        try{
            conn=dbutils.getconnection ();
            Statement st=conn.createstatement ();
            String sql= "SELECT * from Robin_demo";
            ResultSet rs=st.executequery (SQL);
            while (Rs.next ()) {
                int id=rs.getint ("id");
                String name=rs.getstring ("name");
                System.out.println (id+ "," +name);
            }
            Rs.close ()//Release query result
            st.close ()//Release Statement object
        }catch (Exception e) {
            e.printstacktrace ();
        } finally {
            dbutils.close (conn);
        }
    }
}

Obviously: using dbutils can simplify the writing of JDBC code.
This code closes the database connection in finally, with the benefit of reliably shutting down the connection.