JDBC study notes_PHP tutorial-PHP Tutorial-php.cn

l. Method of connecting to the database
Answer: 1) ODBC (Open Database Connectivity)
An interface based on C language to access SQL-based database engine. It provides a consistent interface for and Database communication and access to data.
2) JDBC
Java version of ODBC

2. JDBC application programming interface
Answer: The JDBC application programming interface is:
1) Standard data access interface that can be connected to Different databases;
2) A set of classes and interfaces in the JAVA programming language.
JDBC application programming interface can:
1) Connect to the database;
2) Send SQL query string to the database;
3) Process the results.
The JDBC application programming interface has two main parts:
1) JAVA application development interface for JAVA application developers;
2) JDBC driver development interface

3. JDBC Driver
Answer: 1) A large number of classes that implement JDBC classes and interfaces;
2) Provides a class that implements the java.sql.Driver interface.

4. Four types of JDBC Driver
Answer: 1) JDBC-ODBC Bridge
JDBC access is provided by the ODBC driver
2) Local API
Some Java drivers call JDBC Convert to local client API
3) JDBC-net
A pure Java driver that transfers JDBC calls to DBMS and has nothing to do with network protocols. The call is then converted to the DBMS protocol through the server.
4) Local protocol
Pure java driver, converts JDBC calls directly into the network protocol used by DBMS

5. JDBC developer interface
Answer: 1) java.sql-- The main functions of JDBC under the Java 2 platform, Standard Edition (J2SE)
2) javax.sql--JDBC enhanced functions under the Java 2 platform, Enterprise Edition (J2EE)

6. Confirm the database using URL
A: We use URL to determine a database (correct Driver, correct host, correct protocol, correct protocol, correct username and password);
Syntax: protocol:subprotocol:subname
Example: jdbc:db2:MyTest
jdbc:db2://localhost:6789/MyTest

7. Enhanced functions of javax.sql package JDBC2.0
Answer: 1) Data source interface;
2) Connection pool;
3) Distributed transaction;
4) Rowset;

8. Create a basic JDBC application
Answer: 1) Step 1: Register a driver ;
2) Step 2: Establish a connection to the database;
3) Step 3: Create a statement;
4) Step 4: Execute the SQL statement;
5) Step 5: Process the results ;
6) Step 6: Close the JDBC object

9. Register a Driver (Step 1)
Answer: 1) The driver is used to connect to the database;
2) JDBC application programming The interface uses the first driver that can successfully connect to the given URL;
3) Multiple drivers can be loaded at the same time

10. How to register a driver:
Answer: 1) Use Class loader (loading; instantiation; registration into DriverManager)
a. Class.forName("Com.ibm.db2.jdbc.app.DB2Driver");
b. Class.forName("Com.ibm. db2.jdbc.net.DB2Driver");
c. Class.forName("Com.microsoft.jdbc.sqlServer.SQLServerDriver);
d. Class.forName("oracl.jdbc.driver.OracleDriver") ;
e. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
2) Instantiate a Driver
a. Driver drv = new COM.cloudscape.core.RmiJdbcDriver();

1. Establish a connection to the database (step 2)
Answer: DriverManager calls the getConnection(urlString) method, which actually calls the driver’s connect(urlString) method;
1) When a The driver definitely corresponds to a database URL, and the DriverManager establishes a connection;
2) When no driver matches, null is returned and the next driver is checked;
3) If the connection is not established, a SQLExcepiton exception is thrown

2. Some commonly used JDBC URLs
Answer: 1) JDBC-ODBC: jdbc:odbc:
2) Oracle: jdbc:oracle:oci:@ or jdbc:oracle:thin:@
3) Weblogic MS-SQL: jdbc:weblogic:mssqlserver4:@:
4) DB2: jdbc: db2:MyTest or jdbc.db2://localhost:6789/MyTest (requires username and password)

3. Driver connection method
Answer: 1) Create a direct call to the specified Driver instance;
2) Avoid general access problems
Driver drv = new COM.ibm.db2.jdbc.app.DB2Driver();
Connection con = null;
try {con = drv.connect(" jdbc:db2:MyTest",new Properties())}
catch(SQLException e){}

4. Create a Statement (step three)
Answer: 1) Three interfaces of Statement :
a. Statement;
b. PreparedStatement (inherited from Statement);
c. CallableStatement (inherited from PreparedStatement);
2) Use the method Connection.createStatement() to get a Statement object

5. PreparedStatement object
Answer: 1) Calling ProparedStatement is more efficient than statement;
2) Inherited from Statement;
3) Syntax: PreparedStatement pstm = connection.prepareStatement(sqlString);

6. CallableStatement object
Answer: 1) Call the stored procedure in the database through CallableStatement;
2) Inherited from PreparedStatement;
3) CallableStatement cstm = connection.prepareCall("{call return_student [?,?]}");
cstm.setString(1,"8623034");
cstm.registerOutparameter(2, Types.REAL);
cstm.execute();
float gpa = cstm.getFloat( 2);

7. Comparison of Statement interfaces
Answer: -------------------------------------------------- -------
Code writing location | Client | Client | Server side
-------------------------- -------------------------------------------------- ---
Code writing location | Client | Server side | Server side
-------------------------------- --------------------------------------------------
Coding technology | Java, SQL operations | Java, SQL operations | Database programming language, such as PL/SQL
-------------------------- -------------------------------------------------- ------
Configurability | High | High for the first time, low thereafter | Low
----------------------- -------------------------------------------------- -----
Portability | High | High if PreparedStatement is supported
--------------------------- --------------------------------------------------
Transmission efficiency | Low | Low for the first time, high thereafter | High

8. Execute SQL Statement (Step 4)
Answer: Transmit the SQL statement to the recognized database connection through the interface method , the returned result may be a data table, which can be accessed through java.sql.ResultSet.
1) Statement interface method:
a. executeQuery(sqlString): Execute the given SQL statement and return a result set (ResultSet) object;
b. executeUpdate(sqlString): Execute the given SQL statement, which can be an INSERT, UPDATE or DELETE statement, or a SQL DDL statement;
c. execute(sqlString): Execute the given SQL statement.

9. Processing results (step 5)
Answer: 1) Use the access method of the ResultSet object to obtain data;
a. next(): next record
b . first(): the first record
c. last(): the last record
d. previous(): the previous record
2) Obtain data through field name or index
3) The result set maintains a pointer to the current row, initialized before the first record.

10. Close the JDBC object (step 6)
Answer: 1) First close the record set;
2) Secondly close the statement;
3) Finally close the connection object.

11. Three relationships between data tables and classes:
Answer: 1) One table corresponds to one class;
2) One table corresponds to related classes;