JDBC Programming theory knowledge (1)

1.SUN Company for the unified operation of the database, defined a set of Java Operational Database specification, called the JDBC

2.JDBC is all called: Java data Base Connectivity (Java database connection), which consists mainly of interfaces.
The 2 packages that make up JDBC:
(1) java.sql.*;
(2) javax.sql.*;

3.JDBC location in the program:

Six fixed steps for 4.JDBC
1, registered database driver [using reflection]
2, Get database Connection object connection
3, creating the SQL object
4, execute the SQL command, and return the result set
5, processing result sets
6, close the result set in turn

5.JDBC of DriverManager objects:

(1). The DriverManager in the JDBC program is used to load the driver and create a link to the database, the common method of this API: Drivermanager.registerdriver (new Driver ()), note: In actual development, It is not recommended to register the driver with this method. Looking at the source code of the driver can be seen, if in this way, will cause the driver to load two times, that is, there will be two driver objects in memory.

Java.sql.Driver (interface)-com.mysql.jdbc.driver (Implementation Class)
(Returns True first) Boolean acceptsurl (String URL)
Whether the query driver thinks it can open a connection to a given URL.
and then Connection Connect (String URL, Properties info)
An attempt was made to create a database connection to a given URL.

(2). Recommended Way: Class.forName ("Com.mysql.jdbc.Driver"); This approach does not cause the driver object to recur in memory, and in this way, the program only needs a string, no import-driven API, This allows the program to be independent of the specific driver, making the program more flexible.
Drivermanager.getconnection (URL, user, password), gets a link to the database based on the URL.

6. URL of the database

URLs are used to identify the location of the database, and the programmer tells the JDBC program which database to connect to by URL address.

(1). Common Database URL address:
Oracle-jdbc:oracle:thin: @localhost: 1521:sid
sqlserver-jdbc:microsoft:sqlserver://localhost:1433; Databasename=sid
Mysql-jdbc:mysql://localhost:3306/sid
(2). Short form of MySQL URL address: jdbc:mysql:///sid

7.JDBC of Connection objects:

Connection in the JDBC program, which is used to represent the link of the database, collection is the most important object in the database programming, the client and the database all interaction is done through the connection object, the common method of this object:
Createstatement (): Creates a statement object that sends SQL to the database.
Preparestatement (SQL): Creates a Preparesatement object that sends precompiled SQL to the database.
Preparecall (SQL): Creates the CallableStatement object that executes the stored procedure.
Setautocommit (Boolean autocommit): Sets whether the transaction is automatically committed.
Commit (): commits the transaction on the link.
Rollback (): Rolls back the transaction on this link.

8.JDBC of statement:

The statement object in the JDBC program is used to send SQL statements to the database, statement object common methods:
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 into one batch.
ExecuteBatch (): Sends a batch of SQL statement execution to the database.
Clearbatch (): Empty buffer

9.JDBC of ResultSet objects:

The resultset in the JDBC program is used to represent the execution result of the SQL statement. A table-like approach is used when the resultset encapsulates the execution result. The ResultSet object maintains a cursor to the table data row, and initially, the cursor calls the Resultset.next () method before the first row, allowing the cursor to point to a specific row of data and invoke the method to fetch the row's data.
(1) ResultSet since it is used to encapsulate execution results, the object provides a GET method for getting the data:
Get any type of data
GetObject (int index)
GetObject (String columnName)
Gets the data of the specified type, for example:
getString (int index)
GetString (String columnName)

(2). ResultSet also provides a way to scroll the result set:
Next (): Move to the next line
Previous (): Move to previous line
Absolute (int row): Move to the specified line
Beforefirst (): Moves the front of the resultset.
Afterlast (): Moves to the last face of the resultset.

Conversion of data types for 10.MySQL and Java

11. Code Demo:

Package Cn.wwh.www.java.jdbc;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.ResultSet; Import java.sql.statement;/** * Class role: Used to practice JDBC programming, connected database is mysql * * * * * @author Skiff * @version 1.0 *@ Created: 2014-8-7 pm 08   : 28:42 */public class Firstjdbc {/** * Build statement: Drop table if exists person; CREATE TABLE person (ID int primary key auto_increment, name char (n), password char (n), sex char (2));  INSERT into person values (1, "Wuhui", 123456, "male"); INSERT into person values (2, "Skiff", 123456, "male"); */private static string driverclass = "Com.mysql.jdbc.Driver";p rivate static string url = "jdbc:mysql://127.0.0.1:3306/ User ";p rivate static string user =" root ";p rivate static string password =" WWH ";p ublic static void Main (string[] args) th Rows Exception {////1. Load driver//(1). Mode 1://drivermanager.registerdriver (New Driver ());//(2). Mode 2: Use Reflection to Class.forName ( Driverclass);//Use DriverManager to get the data connection,//The connection that is returned represents the connection between the Java program and the database/** * @param URL * jdbc:subprotocol:su BnAme Database URL * @param user * Database users, connection is established for the user * @param password * Login database password */connection conn = Dri Vermanager.getconnection (URL, user, password); Statement Statement = Conn.createstatement (); String sql = "SELECT * from person;"; /executes the SQL command and returns the set of records that meet the criteria resultset result = statement.executequery (SQL);//Accept the result of the query in two ways while (Result.next ()) {// Gets the data for column 1th idint id = result.getint (1);//Gets the data result of the name of the field 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);} Shut down the database stream once Result.close (); Statement.close (); Conn.close ();}}

The test effect of the code: