JDBC connection database
• 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).
This is achieved 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 it from java.sql.DriverManager and get the Connection object.
This object represents a connection to a database.
• Use DriverManager's getConnectin(String url, String username,
The String password ) method passes the specified path, database username and password of the database to be connected to to obtain.
For example:
//Connect the MySql database, the username and password are root String url = "jdbc:mysql://localhost:3306/test" ; String username = "root" ; String password = "root" ; try{ Connect ion 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. Statement instances are 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() ; } }Example code:
package com.b510; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import j ava.sql.SQLException; /** * * @author Hongten</ br> * @date 2012-7-16 * */public class JDBCTest { public static void main(String[] args) { String driver = "com.mysql.jdbc.Driver"; String dbName = "sp ring"; String password = "root"; String userName = "root"; String url = "jdbc:mysql://localhost:3308/" + dbName; String sql = "select * from users"; try { Class.f orName(driver); Connection conn = DriverManager.getConnection(url, userName, password); PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); wh ile (rs.next()) { System.out.println("id: " + rs.getInt(1) + " name : " + rs.getString(2) + " password : " + rs.getString(3)); } // Close the record set if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } // Close the declaration if (ps != null) { try { ps.close(); } catch (SQLException e) { e. printStackTrace(); } } // Close the link object if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } catch (Exception e ) { e .printStackTrace(); } } } Running effect:
id: 3 name: hongten password: 123