Initial knowledge of JDBC

I. Introduction to JDBC

JDBC Full name Java Date Base Connectivity (Java database connection) is a Java API for executing SQL statements that provides unified access to a variety of databases ("Write once, run Everywhere").

In simple terms, JDBC is a bridge between server applications and databases.

Two. Database-driven

After the database is installed, our application cannot access the database directly, but must communicate with the actual database through the driver through the corresponding database driver.

Three. Common interface

1.Driver interface

The driver interface is implemented by the database vendor, and Java developers only need to use this interface.

To connect to a database in programming, you need to load a specific vendor's database driver, and different databases have different loading methods.

Load MySQL driver: class.forname ("Com.mysql.jdbc.Driver");

Load Oracle Driver: Class.forName ("Com.jdbc.driver.OracleDriver");

2.DriverManager interface

DriverManager manages a set of basic services for a JDBC driver.

It tracks the available drivers and establishes a connection between the database and the corresponding driver.

3.Connection interface

The connection interface represents a connection (session) to a particular database, and to manipulate the data in the data table, you first get the database connection. The connection implementation is like opening a channel between the application and the database.

The connection instance is obtained through the getconnection () method of the DriverManager class.

Connect MySQL database: Connection con = drivermanager.getconnection ("Jdbc:mysql://host:port/datebase", USERNAME, PASSWORD);

Connecting the Oracle database: connetion con = drivermanager.getconnection ("Jdbc:oralce:thin: @host:p ort:datebase");

4.Statement interface

Statement is an important interface for Java performing Database operations to send SQL static statements to the database and return the resulting object, based on the database connection already established.

Three types of statement:

-Statement: Created by createstatement to send a simple SQL statement (without parameters).

-PreparedStatement: Inherited from the statement interface, created by PreparedStatement, to send an SQL statement with one or more parameters. PreparedStatement objects are more efficient than statement objects and can prevent SQL injection, so we generally use preparedstatement.

-CallableStatement: Inherits from the PreparedStatement interface, created by method Preparecall, to invoke the stored procedure.

Common methods:

-Excute (String SQL): Executes the given SQL statement, which is used to reflect the success of the SQL statement when it returns a Boolean value.

-Excutequery (String SQL): Executes a SELECT statement that returns a single ResultSet object.

-Executeupdate (String SQL): Runs the insert/update/delete operation, returning the number of rows updated.

-Addbatch (String sql): Adds the given SQL command to the current list of commands for this Statement object.

-ExecuteBatch (): Sends a batch of SQL statement execution to the database, and returns an array of update counts if all the commands are executed successfully.

5.ResultSet interface

A data table for a database result set, typically generated by executing a statement that queries the database.

ResultSet provides methods for retrieving different types of fields, which are commonly used:

-getString (int index), getString (String columnName): Gets a data object of type varchar, char, and so on in the database.

-getfloat (int index), getfloat (String columnName): Gets a data object of type float in the database.

-getDate (int index), getDate (String columnName): Gets the data in the database that is of date type.

-Getboolean (int index), Getboolean (String columnName): Gets the data in the database that is of type Boolean.

-GetObject (int index), getObject (String columnName): Gets any type of data in the database.

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.

Close objects and connections in turn after use: resultset→statement→connection

Four. Using the JDBC Step

Loading the JDBC driver → establishing a database connection connection→ creating a statement that executes SQL statement→ processing execution Results resultset→ freeing resources

1. Registration driver (only once)

Way One : Class.forName ("com. MySQL.jdbc.Driver ");
It is recommended that this approach does not rely on specific driver classes.
mode two : Drivermanager.registerdriver (Com.mysql.jdbc.Driver);
Will cause two of the same drivers in the DriverManager, and will be dependent on the specific driver class.

2. Establish a connection

Connection con = drivermanager.getconnection (URL, username, password);

3. Create a statement that executes the SQL statement

Statement  String id = "5"; String sql = "Delete from table where id=" +  ID; Statement st = Conn.createstatement ();  St.executequery (SQL);  There is a risk of SQL injection//If the user has passed in an ID of "5 or 1=1", all records in the table will be deleted

4. Processing execution Results (RESULTSET)

1 ResultSet rs = Ps.executequery ();  2 while (Rs.next ()) {  3     rs.getstring ("Col_name");  4     rs.getint (1);  5     //... 6}  

5. Releasing Resources

Database connection (Connection) is very resource-intensive, created as late as possible, as early as possible release//To add a try catch in case of a previous shutdown error, followed by 1 try {    if (rs! = null) {        rs.close ();    }} catch (SQLException e) {    e.printstacktrace ();} finally {    try {        if (st! = null) {            st.close ()        }    } catch (SQLException e) {        e.printstacktrace ();    } finally {        try {            if (conn! = null) {                CONN.C Lose ();            }        } catch (SQLException e) {            e.printstacktrace ();}}    }

Five. Installation tutorials (including Myeclipse,mysql,mysql Connector) (pro-Test active)

myeclipse-2017 Link: https://pan.baidu.com/s/13kcZ7l3XIGD7oQGtPa38jw Password: LTMW

MyEclipse hack file Link: https://pan.baidu.com/s/1u2CXKverCgFdWedyFk1yFg Password: n05h

MyEclipse installation tutorial from Friends Blog Link: https://www.cnblogs.com/qianjun2017/p/8108253.html

Windows version mysql:https://dev.mysql.com/downloads/file/?id=479669

MySQL download post install configuration from CSDN Blog link: 80636843

MySQL Connector Link: https://pan.baidu.com/s/1poBOudWobnxIrpSFLKs9Og Password: HKFJ

-About Java Web Project Import Database Jar Package

Copy the Mysql-connector-java file directory under the two jar package, go to the project file directory under Webroot, web_inf-> lib right-click Paste, and then respectively to the two jar package right-click Build path Select Add to Build Path.

---accumulate a little every day, the draft does not forget to dig the well man---

Initial knowledge of JDBC