Summary of JDBC connection database instances in Java development
- 2020-04-01 04:20:40
- OfStack
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.