l. Methods to connect to the database: 1) ODBC (Open Database Connectivity)
An interface based on C language to access SQL-based database engine. It provides a consistent interface for communicating with the database and accessing data.
2) JDBC
Java version of ODBC
2. JDBC application programming interface Answer: The JDBC application programming interface is:
1) Standard data access interface, which can be connected to different databases;
2) A set of classes and interfaces in the JAVA programming language.
The JDBC application programming interface can:
1) Connect to the database;
2) Send the SQL query string to the database;
3) Process the results.
The JDBC application programming interface has two main parts:
1) The JAVA application development interface is 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 provided by ODBC driver
2) Local API
Some Java drivers convert JDBC calls into local client APIs
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 function of JDBC under the java 2 platform, standard version (J2SE)
2) javax.sql--JDBC enhanced functions under the Java 2 platform, Enterprise Edition (J2EE)
6. Use URL to confirm the database Answer: 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 transactions;
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) The JDBC application programming 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: When DriverManager calls the getConnection(urlString) method, it actually calls the driver's connect(urlString) method;
1) When a driver definitely corresponds to a database URL, 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:<DB>
2) Oracle: jdbc:oracle:oci:@<sid> or jdbc:oracle:thin:@<SID>
3) Weblogic MS-SQL: jdbc:weblogic:mssqlserver4:<DB>@<HOST>:<PORT>
4) DB2: jdbc:db2:MyTest or jdbc.db2://localhost:6789/MyTest (requires user name 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 3)
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 interface: | Statement | PreparedStatement | CallableStatement
-------------------------------------------------- ----------------------------
Writing code location | Client | Client | Server
-------------------------------------------------- ----------------------------
Writing code location | client | server side | server side
-------------------------------------------------- ----------------------------
Coding technology | Java, SQL operations | Java, SQL operations | Database programming languages, such as PL/SQL
-------------------------------------------------- ----------------------------
Configurability | High | High first time, low thereafter | Low
-------------------------------------------------- ----------------------------
Portability | High | High assuming PreparedStatement is supported
-------------------------------------------------- ----------------------------
Transmission efficiency | Low | Low for the first time, high thereafter | High