This article describes the method of connecting to database based on JDBC in Java development. Share it for your reference, as follows:
Create a program that connects to the database with JDBC, which contains 7 steps:
1. Load the JDBC driver:
Before connecting to the database, you must first load the driver of the database you want to connect to to the JVM (Java virtual machine), which is implemented through the static method forName(String className) of the java.lang.Class class.
For example:
try{//Load MySql's driver class Class.forName("com.mysql.jdbc.Driver");}catch(ClassNotFoundException e){System.out.println("The driver class cannot be found, the driver loading failed! " );e.printStackTrace() ;}After successful loading, an instance of the Driver class will be registered in the DriverManager class.
2. Provide the URL for JDBC connection
The connection URL defines the protocol, subprotocol, and data source identifier when connecting to the database.
Writing form: Protocol: Subprotocol: Data source identification protocol: In JDBC, always start with jdbc. Subprotocol: is a bridge-connected driver or database management system name.
Data source identification: Tag the address and connection port where the database source is found.
For example: (MySql's connection URL)
jdbc:mysql:
//localhost:3306/test?useUnicode=true&characterEncoding=gbk;
useUnicode=true: means using Unicode character set. If characterEncoding is set to
gb2312 or GBK, this parameter must be set to true. characterEncoding=gbk: character encoding method.
3. Create a database connection
To connect to the database, you need to request the java.sql.DriverManager and obtain the Connection object, which represents the connection to a database.
Use the DriverManager's getConnectin(String url, String username, String password) method to pass in the specified database path, database username and password to connect to to obtain.
For example:
//Connect the MySql database, the username and password are rootString url = "jdbc:mysql://localhost:3306/test" ;String username = "root" ;String password = "root" ;try{Connecti on con = DriverManager.getConnection (url , username , password ) ;}catch(SQLException se){System.out.println("Database connection failed!");se.printStackTrace() ;}4. Create a Statement
To execute SQL statements, you must obtain a java.sql.Statement instance. The Statement instance is divided into the following 3
Type:
1. Execute static SQL statements. Usually implemented through Statement instances.
2. Execute dynamic SQL statements. Usually implemented through PreparedStatement instance.
3. Execute the database stored procedure. Usually implemented through CallableStatement instance.
Specific implementation methods:
Statement stmt = con.createStatement() ;PreparedStatement pstmt = con.prepareStatement(sql) ;CallableStatement cstmt = con.prepareCall("{CALL demoSp(? , ?)}") ;5. Execute SQL statements
The Statement interface provides three methods to execute SQL statements: executeQuery, executeUpdate and execute
1. ResultSet executeQuery(String sqlString): Execute SQL statements to query the database and return a result set (ResultSet) object.
2. int executeUpdate(String sqlString): used to execute INSERT, UPDATE or DELETE statements and SQL DDL statements, such as: CREATE TABLE and DROP TABLE, etc.
3. execute(sqlString): used to execute statements that return multiple result sets, multiple update counts, or a combination of both.
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 situations:
1. Execution of update returns the number of records affected by this operation.
2. The result returned by executing the query is a ResultSet object.
The ResultSet contains all rows that meet the conditions in the SQL statement, and it provides access to the data in these rows through a set of get methods.
Use the access method of the result set (ResultSet) object to get 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 from column 1)
7. Close JDBC object
After the operation is completed, all the used JDBC objects must be closed to release JDBC resources. The order of closing and declarations are reversed:
1. Close the record set
2. Close statement
3. Close the connection object
if(rs != null){ // Close the record set try{ rs.close() ;}catch(SQLException e){ e.printStackTrace() ;}}if(stmt != null){ // Close the declaration try{ stmt.close() ;}catch(SQLException e){ e.printStackTrace() ;}}if(conn != null){ // Close the connection object try{ conn.close() ; }catch(SQLException e){ e .printStackTrace() ; }}Supplement: JDBC connection to Oracle database example
import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;pu blic 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 the oracle database driver (Create middleware) String url = "jdbc :oracle:thin:@localhost:1521:oar92";//@localhost is the server name, sjzwish is the database instance name conn = DriverManager.getConnection(url, "guchao", "jimmy");//Connect the database, generation a Table Account, a represents the password stmt = conn.createStatement();//Submit the SQL statement and create a Statement object to send the SQL statement to the database//Query the data with executeQuery rs = stmt.executeQuery("select * from ruby") ; //Execute the query, (ruby) is the table name while (rs.next()) {//Locate the current record pointer to the first record in the record set 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. // Use executeUpdate to add data //stmt.executeUpdate("insert into ss values(7,'Jackie Chan')"); // Use executeUpdate to modify data //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 (ClassNot FoundException 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 will be helpful to everyone's Java programming.