Basic usage of JDBC (Java Data Base Connectivity)

first, what is JDBC
JDBC (Java Database Connection) provides a unified programming interface for Java developers to use a database, which consists of a set of Java classes and interfaces. is the standard APL for Java programs to communicate with database systems. The JDBC API allows developers to connect to a database and perform operations in a pure Java manner.
Sun is unable to write its own code to connect to each database because it does not know the program code for each mainstream commercial database. As a result, Sun decided to provide itself with a set of APIs that the database vendor must implement in its own JDBC interface if the database wanted to connect with Java. and the database vendor JDBC implementation, we call it the database driver of this database.

Ii. accessing the database process using JDBC

Third, JDBC Common interface

1. Driver Interface
The driver interface is provided by the database manufacturer, and for Java developers it is only necessary to use the driver interface.
In programming to connect to a database, you must first load a specific vendor's database driver. Different databases have different loading methods.
The driver is the JDBC interface proposed by Sun Company, which is implemented by each database vendor, which is the jar file of the implementation class of interface such as connection.

Load MySQL Driver
Class.forName("com.mysql.jdbc.Driver");
Load Oracle Drivers
Class.forName("oracle.jdbc.driver.OracleDriver");

2. Drivermanage interface
A drivermanager is a JDBC management layer that acts between the user and the driver.
A DriverManager tracks the available drivers and establishes a connection between the database and the appropriate driver.

3. Connection Interface
Connection the connection (session) to a particular database, executes the SQL statement in the connection context, and returns the result.
The DriverManager getconnection () method establishes the database connection connection defined in the JDBC URL.

To connect to the MYSQL database:
Connection connection = DriverManager.getConnection("jdbc:mysql://host:port/database","user","password");

To connect to an ORACLE database:
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@host:port:database","user","password");

4. Statement Interface
The object used to execute a static SQL statement and return the result it produces.

Three types of statement:
Statement:
Created by Createstatement to send a simple SQL statement. (without parameters)

preparedstatement:**
Inherits from the statement interface, created by preparestatement, for sending SQL statements that contain one or more input parameters. PreparedStatement objects, which are precompiled, are more efficient than statement objects, and can prevent SQL injection . We generally use PreparedStatement.

CallableStatement:
Inherited from PreparedStatement, created by method Preparecall, to invoke stored procedures .

Common methods of statement:
Execute (): Runs the statement, returning whether there is a result set.
ExecuteQuery (): Runs the select statement, returning the resultset result set.
Executeupdate (): Runs the insert/update/delete operation, returning the number of rows updated .

5. ResultSet interface
Statement The resultset result set is returned when the SQL statement is executed.

ResultSet provides methods for retrieving different types of fields, which are commonly used:
GetString (): Gets the object of a data type such as varchar, char, and so on in the database.
GetFloat (): Gets an object of type float in the database.
GetDate (): Gets the date type data in the database.
Getboolean (): Gets the Boolean type of data inside the database.

Turn off the objects and connections used:
Resultset–>statement–>connection

 PackageCOM.LGD.JDBC;ImportJava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.SQLException;ImportJava.sql.Statement; Public  class Demo01 {     Public Static void Main(string[] args) {Connection Connection =NULL; Statement Statement =NULL;Try{///1, Load driver classClass.forName ("Com.mysql.jdbc.Driver");///2, establish a connection to the database            The inside of the connection object actually contains the socket object, which is a remote connection. Time-consuming, this is a key point of connection object management!             //In real development, connection pooling is used to manage connection objects in order to improve efficiency. Connection = Drivermanager.getconnection ("Jdbc:mysql://localhost:3306/testjdbc","Root","Liguodong"); System.out.println (connection);///3, test the specified SQL statement and SQL injection issues            //Less in practical applications            //1. Handling Parameters Inconvenient            //2. Risk of SQL injection pronestatement = Connection.createstatement ();/*string sqlone = "INSERT into user (Username,pwd,regtime) VALUES (' Susan ', 54423,now ())"; Statement.execute (Sqlone); * *            /*string namestring = "Song Eight";            String sqltwo = "INSERT into user (Username,pwd,regtime) VALUES ('" +namestring+ "', 54ds23,now ())"; Statement.execute (sqltwo); * *            //test SQL injectionString idstring ="5 or 1=1"; String SQL1 ="Delete from user where id="+idstring; Statement.execute (SQL1);//This will delete all elements}Catch(ClassNotFoundException e)        {E.printstacktrace (); }Catch(SQLException e)        {E.printstacktrace (); }finally{if(statement!=NULL){Try{Statement.close (); }Catch(SQLException e) {//TODO auto-generated catch blockE.printstacktrace (); }            }if(connection!=NULL){Try{Connection.close (); }Catch(SQLException e) {//TODO auto-generated catch blockE.printstacktrace (); }            }        }    }}

 PackageCOM.LGD.JDBC;ImportJava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.PreparedStatement;ImportJava.sql.SQLException;ImportJava.sql.Statement; Public  class Demo03 {     Public Static void Main(string[] args) {Connection Connection =NULL; PreparedStatement statement =NULL;Try{///1, Load driver classClass.forName ("Com.mysql.jdbc.Driver");///2, establish a connection to the databaseConnection = Drivermanager.getconnection ("Jdbc:mysql://localhost:3306/testjdbc","Root","Liguodong");///3, Basic usage of test PreparedStatementString SQL1 =INSERT INTO User (Username,pwd,regtime) VALUES (?,?,?); statement = Connection.preparestatement (SQL1);/*statement.setstring (1, "Jay Chou");//The parameter index is calculated starting from 1, not 0 statement.setstring (2, "fdsf323"); Statement.execute (); */            /*//You can also use the SetObject method to process parameter Statement.setobject (1, "James");                                   Statement.setobject (2, "fdf323"); Statement.execute (); */Statement.setstring (1,"Demacia"); Statement.setstring (2,"FDSFDS3"); Statement.setdate (3,NewJava.sql.Date (System.currenttimemillis ()));        Statement.execute (); }Catch(ClassNotFoundException e)        {E.printstacktrace (); }Catch(SQLException e)        {E.printstacktrace (); }finally{if(statement!=NULL){Try{Statement.close (); }Catch(SQLException e) {//TODO auto-generated catch blockE.printstacktrace (); }            }if(connection!=NULL){Try{Connection.close (); }Catch(SQLException e) {//TODO auto-generated catch blockE.printstacktrace (); }            }        }    }}

 PackageCOM.LGD.JDBC;ImportJava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.sql.PreparedStatement;ImportJava.sql.ResultSet;ImportJava.sql.SQLException; Public  class Demo04 {     Public Static void Main(string[] args) {Connection Connection =NULL; PreparedStatement statement =NULL; ResultSet rs1 =NULL;Try{///1, Load driver classClass.forName ("Com.mysql.jdbc.Driver");///2, establish a connection to the databaseConnection = Drivermanager.getconnection ("Jdbc:mysql://localhost:3306/testjdbc","Root","Liguodong");///3, test the basic usage of preparedstatement? PlaceholderString SQL1 ="SELECT * from user where id>?";            statement = Connection.preparestatement (SQL1); Statement.setobject (1,2);//Take out records that are greater than 2Rs1 = Statement.executequery (); while(Rs1.next ()) {System.out.println (Rs1.getint (1)+"---"+rs1.getstring (2)+"---"+rs1.getstring (3)); }        }Catch(ClassNotFoundException e)        {E.printstacktrace (); }Catch(SQLException e)        {E.printstacktrace (); }finally{//execution order resultset-->statement-->connection such a close order! Be sure to write three pieces of Try-catch separately!             if(rs1!=NULL){Try{Rs1.close (); }Catch(SQLException e) {//TODO auto-generated catch blockE.printstacktrace (); }            }if(statement!=NULL){Try{Statement.close (); }Catch(SQLException e) {//TODO auto-generated catch blockE.printstacktrace (); }            }if(connection!=NULL){Try{Connection.close (); }Catch(SQLException e) {//TODO auto-generated catch blockE.printstacktrace (); }            }        }    }}

Basic usage of JDBC (Java Data Base Connectivity)