Summary of JDBC connection database instances in Java development

This article illustrates the method of connecting to a database based on JDBC in Java development. Share with you for your reference, as follows:

Creating a program to connect to a database using JDBC consists of seven steps:  

1. Load the JDBC driver:

Before connecting to the database, the driver for the database you want to connect to is first loaded into the JVM (Java virtual machine) through the static method forName of the java.lang.class Class (String ; The className) implementation.

Such as:

try{
//Load the driver class for MySql
Class.forName("com.mysql.jdbc.Driver") ;
}catch(ClassNotFoundException e){
System.out.println(" The driver class could not be found   , load driver failed! ");
e.printStackTrace() ;
}

After a successful load, an instance of the Driver class is registered with the DriverManager class.

2. Provide the URL for the JDBC connection

The connection URL defines the protocol, subprotocol, and data source identity for connecting to the database.    

Written form: protocol: subprotocol: data source identifier    
Protocol: in JDBC always start with JDBC    
Subprotocol: is the name of the driver or database management system for the bridge connection.    
Data source identity: marks the address and connection port where the database source was found.

For example :(MySql connection URL)

JDBC: mysql:
/ / localhost: 3306 / test & # 63; UseUnicode = true&characterEncoding = GBK;

UseUnicode =true: indicates the use of the Unicode character set. If characterEncoding is set to
For gb2312 or GBK, this parameter must be set to true. CharacterEncoding = GBK: characterEncoding method.

3. Create a connection to the database

To connect to a database, you need to request and obtain a Connection object from java.sql.DriverManager, which represents a Connection to a database.

Use DriverManager's getConnectin(String url, String username, String password) method to pass in the specified path to the database to which you want to connect, the database username, and the password.

Such as:

//Connect to the MySql database with the username and password as root
String url = "jdbc:mysql://localhost:3306/test" ;
String username = "root" ;
String password = "root" ;
try{
Connection con = DriverManager.getConnection(url , username , password ) ;
}catch(SQLException se){
System.out.println(" Database connection failed! ");
se.printStackTrace() ;
}

4. Create a Statement

To execute an SQL Statement, you must obtain a java.sql.statement instance, which is divided into the following 3& PI;
Type:

1. Execute static SQL statements. This is typically implemented with a Statement instance.
2. Execute dynamic SQL statements. It is usually implemented through a PreparedStatement instance.
3. Execute database stored procedures. This is typically implemented through a CallableStatement instance.

Specific implementation method:

Statement stmt = con.createStatement() ;
PreparedStatement pstmt = con.prepareStatement(sql) ;
CallableStatement cstmt = con.prepareCall("{CALL demoSp(? , ?)}") ;

5. Execute the SQL statement

The Statement interface provides three methods for executing SQL statements: executeQuery, executeUpdate, and execute

ResultSet executeQuery(String sqlString) : execute the SQL statement to query the database and return a ResultSet (ResultSet) object.

Int executeUpdate(String sqlString) : used to execute INSERT, UPDATE, or DELETE statements, as well as SQL DDL statements, such as CREATE TABLE, DROP TABLE, etc

Execute (sqlString): a statement that returns multiple result sets, multiple update counts, or a combination of the two.

Specific implementation code:

ResultSet rs = stmt.executeQuery("SELECT * FROM ...") ;
int rows = stmt.executeUpdate("INSERT INTO ...") ;
boolean flag = stmt.execute(String sql) ;

6. Processing results

Two cases:

1. The number of records affected by this operation is returned when the update is performed.
2. The result returned by executing the query is a ResultSet object.

The ResultSet contains all the rows that meet the criteria in the SQL statement, and it provides this & PI through a set of get methods;  
Access to data in a row.    

Using the access method of a ResultSet object to obtain data:

while(rs.next()){
 String name = rs.getString("name") ;
String pass = rs.getString(1) ; //This method is more efficient
}

(columns are numbered from left to right and start at column 1)

Close the JDBC object

When the operation is complete, close all the JDBC objects in use to free the JDBC resources in the reverse order of declaration:

1. Close the recordset
2. Closing statement
Close the connection object

if(rs != null){ //Close recordset
try{
  rs.close() ;
}catch(SQLException e){
  e.printStackTrace() ;
}
}
if(stmt != null){ //Closing statement
try{
  stmt.close() ;
}catch(SQLException e){
  e.printStackTrace() ;
}
}
if(conn != null){ //Close the connection object
 try{
  conn.close() ;
 }catch(SQLException e){
  e.printStackTrace() ;
 }
}

Supplement: sample JDBC connection to Oracle database

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestOracle {
 public static void main(String[] args) {
 Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");//Instantiate oracle database driver (build middleware)
   String url = "jdbc:oracle:thin:@localhost:1521:oar92";//@localhost is the server name and sjzwish is the database instance name
   conn = DriverManager.getConnection(url, "guchao", "jimmy");//Connect to database,a for account,a for password
   stmt = conn.createStatement();//Submit the SQL Statement and create a Statement object to send the SQL Statement to the database
   //Query data with executeQuery
   rs = stmt.executeQuery("select * from ruby");//Execute the query,(ruby) for the table name
   while (rs.next()) {//Position the current record pointer to the first record in the recordset
   System.out.println(rs.getString("sid") +" "+ rs.getString("sname"));
   }//1 represents the value of the first field of the current record and can be written as the field name.
   //2 represents the value of the second field of the current record and can be written as the field name.
   //Add data with executeUpdate
   //Stmt.executeupdate (" insert into ss values (7, 'Jacky cheung') ");
   //Modify the data with executeUpdate
   //Stmt.executeupdate (" update ss set name = 'maggie cheung' where id = 5 ");
   //Delete data with executeUpdate
   //stmt.executeUpdate("delete from ss where id = 6");
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }finally{
   try {
   //Close the database and end the process
   rs.close();
   stmt.close();
   conn.close();
   } catch (SQLException e) {
   e.printStackTrace();
   }
  }
 }
}

I hope this article has been helpful to you in Java programming.