JDBC programming theory (1)

1. SUN defines a set of Java Database Operation Specifications for database operations in a unified manner, called JDBC2.JDBC: JavaDataBaseConnectivity (java database connection), which is mainly composed of interfaces. The two packages that make up JDBC: (1) java. SQL. *; (2) javax. SQL. *; 3. Location of JDBC in the program: 4. Location of JDBC

1. SUN defines a set of Java Database Operation Specifications for unified database operations, called JDBC 2. JDBC is called Java Data Base Connectivity (java database connection). It is mainly composed of interfaces. Two JDBC packages: (1) java. SQL. *; (2) javax. SQL. *; 3. Location of JDBC in the program: 4. JDBC's

1. SUN defines a set of Java Database Operation Specifications for unified database operations, called JDBC

2. JDBC is called Java Data Base Connectivity (java database connection). It is mainly composed of interfaces.
Two JDBC packages:
(1) java. SQL .*;
(2) javax. SQL .*;

3. JDBC position in the program:

4. Six Fixed steps of JDBC
1. register the database driver [Using Reflection]
2. Obtain the Connection object of the database.
3. Create an SQL object
4. Execute the SQL command and return the result set.
5. process the result set
6. Disable the result set in sequence.

5. JDBC DriverManager object:

(1 ). the DriverManager In the Jdbc program is used to load the driver and create a connection to the database. The common method of this API is DriverManager. registerDriver (new Driver (). Note: in actual development, we do not recommend using this method to register the Driver. View the Driver source code. If this method is used, the Driver is loaded twice, that is, there are two Driver objects in the memory.

Java. SQL. Driver (Interface)-com. mysql. jdbc. Driver (Implementation class)
(Returns true first) boolean acceptsURL (String url)
Queries whether the driver thinks it can open a connection to a given URL.
(Then) Connection connect (String url, Properties info)
Try to create a database connection to the given URL.

(2 ). recommended method: Class. forName ("com. mysql. jdbc. "Driver"); using this method will not cause the Driver object to appear repeatedly in the memory, and using this method, the program only needs one string, and does not need the import-driven API, in this way, the program does not depend on the specific driver, so that the program is more flexible.
DriverManager. getConnection (url, user, password). Obtain the database link based on the url.

6. Database URL

The URL is used to identify the location of the database. The programmer uses the URL address to tell the JDBC program which database to connect,

(1). Common Database URL:
Oracle-jdbc: oracle: thin: @ localhost: 1521: sid
SqlServer-jdbc: microsoft: sqlserver: // localhost: 1433; DatabaseName = sid
MySql-jdbc: mysql: // localhost: 3306/sid
(2). the url of Mysql is abbreviated as jdbc: mysql: // sid.

7. JDBC Connection object:

The Connection in the Jdbc program is used to represent the connection of the database. Collection is the most important object in database programming. All the interactions between the client and the database are completed through the Connection object. The common method of this object is as follows:
CreateStatement (): Creates a statement object that sends SQL statements to the database.
PrepareStatement (SQL): Creates a PrepareSatement object that sends pre-compiled SQL statements to the database.
PrepareCall (SQL): Creates a callableStatement object that executes the stored procedure.
SetAutoCommit (boolean autoCommit): sets whether a transaction is automatically committed.
Commit (): Submit the transaction on the link.
Rollback (): rolls back the transaction on this link.

8. JDBC statement:

The Statement object in the Jdbc program is used to send SQL statements to the database. Common Methods of the Statement object are as follows:
Execute (String SQL): used to send arbitrary SQL statements to the database
ExecuteQuery (String SQL): Only select statements can be sent to Data.
ExecuteUpdate (String SQL): Only insert, update, or delete statements can be sent to the database.
AddBatch (String SQL): puts Multiple SQL statements in one batch.
ExecuteBatch (): sends a batch of SQL statements to the database for execution.
ClearBatch (): clears the buffer.

9. JDBC ResultSet object:

The ResultSet In the Jdbc program is used to represent the execution result of the SQL statement. When the Resultset encapsulates the execution result, it adopts a table-like approach. The ResultSet object maintains a cursor pointing to the table data row. Initially, the cursor is called before the first row. the next () method allows the cursor to point to a specific data row and call the method to obtain the data of the row.
(1) Since the ResultSet is used to encapsulate the execution result, the object provides the get method for getting data:
Obtain any type of data
GetObject (int index)
GetObject (string columnName)
Obtain data of the specified type, for example:
GetString (int index)
GetString (String columnName)

(2). ResultSet also provides a method to scroll the result set:
Next (): Move to the next row
Previous (): Move to the previous row
Absolute (int row): Move to the specified row
BeforeFirst (): Move the front of the resultSet.
AfterLast (): Move to the end of the resultSet.

10. Conversion of MySQL and java Data Types

11. Code Demonstration:

Package cn. wwh. www. java. jdbc; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. statement;/*** class: used to practice JDBC programming. The connected database is MySQL ***** @ author @ * @ version 1.0 * @ Creation Time: 08:28:42 */public class FirstJDBC {/*** table creation statement: drop table if exists person; create table person (id int primary key auto_increment, name char (12 ), password char (12), sex char (2); insert into person values (1, "wuhui", 123456, "male"); insert into person values (2, "Ye Binzhou", 123456, "male"); */private static String driverClass = "com. mysql. jdbc. driver "; private static String url =" jdbc: mysql: // 127.0.0.1: 3306/user "; private static String user =" root "; private static String password =" wwh "; public static void main (String [] args) throws Exception {// 1. load the driver // (1 ). method 1: // DriverManager. registerDriver (new Driver (); // (2 ). method 2: Use the reflection method Class. forName (driverClass); // use DriverManager to obtain data connections. // The returned Connection indicates the Connection between the java program and the database./*** @ param url * jdbc: subprotocol: database url * @ param user * In subname form. The Connection is the * @ param password * password for the user to log on to the database */Connection conn = DriverManager. getConnection (url, user, password); Statement statement = conn. createStatement (); String SQL = "select * from person;"; // run the SQL command and return the ResultSet result = statement.exe cuteQuery (SQL) set that meets the conditions ); // two methods are used to accept the query result while (result. next () {// obtain the idint id = result of column 1st. getInt (1); // obtain the data result of the field name. String name = result. getString ("name"); String password = result. getString (3); String sex = result. getNString ("sex"); System. out. println ("id =" + id + "\ tname =" + name + "\ tpassword =" + password + "\ tsex =" + sex );} // closes the database stream result at one time. close (); statement. close (); conn. close ();}}

Code Test Result: