First stage: Front-end development _jdbc Simple Introduction

2018-06-25

A brief introduction to JDBC

First, what is JDBC

JDBC (Java database Connectivity) is the connection of Java databases, in other words, the Java language to operate the database.

It turns out that we operate the database using SQL statements in the console to manipulate the database, and JDBC sends SQL statements to the database in the Java language.

Second, the JDBC principle

Early Sun's geniuses wanted to write a set of APIs that could connect all the world's databases, but when they started they found it was an unfinished task,

Because the database servers differ too much for each vendor. Then Sun began to discuss with the database vendors, and the final conclusion was that

Sun provides a set of specifications to access the database (that is, a set of interfaces) and provides protocol standards for connecting to the database.

Each database vendor will then follow Sun's specifications to provide a set of APIs to access their company's database servers.

The specifications that Sun provides are named JDBC, and the APIs that each vendor provides that follow the JDBC specification, which can access their own databases, are called Drivers!

JDBC is the interface, and the JDBC driver is the implementation of the interface, no driver can not complete the database connection! Each database vendor has its own driver to connect to its own company's database.

Of course, there are third-party companies specifically for a database to provide drivers, such drivers are often not open source free!

Third, the JDBC Core Class (interface) Introduction

The core classes in JDBC are: DriverManager, Connection, Statement, and resultset!

The Drivermanger (drive manager) has two functions:

• • Registration driver: This allows JDBC to know which driver to use;
  • Get connection: If you can get to connection, then the description is already connected to the database.

The Connection object represents the connection, and the communication to the database is carried out through this object:

• • One of the most important methods of connection is to acquire statement objects;
  • Statement is used to send SQL statements to the database so that the database executes the SQL statement sent over
  • void executeupdate (String sql): Perform update operations (INSERT, UPDATE, delete, etc.);
  • ResultSet executeQuery (String sql): Execute the query operation, the database will be executed after the query results, query results are ResultSet;

The ResultSet object represents the query result set, and only the result set will be produced after the query operation has been performed. The result set is a two-dimensional table with rows and columns. The action result set learns to move the "row cursor" inside the resultset and to get the data on each column on the current line:

• • Boolean next (): Moves the row cursor to the next line and returns whether the moved row exists;
  • XXX GetXXX (int col): Gets the value on the specified column of the current row, which is the number of columns and the number of columns starts at 1 instead of 0.

Four, Hello JDBC

Start writing the first JDBC program below

4.1 Importing the Drive jar package for the MySQL database:

Mysql-connector-java-5.1.39-bin.jar;

4.2 Registration Drive

See clearly, the registration drive is only a sentence: Class.forName ("Com.mysql.jdbc.Driver"), the following is the explanation of this code. In our code, this is the only sentence in the code that is related to registering the driver.

Registerdriver of the DriverManager class () The parameter of the method is Java.sql.Driver, but Java.sql.Driver is an interface, the implementation class is provided by the MySQL driver, and the implementation class of the Java.sql.Driver interface in the MySQL driver is com.mysql.jdbc.driver! Then the registered driver code is as follows:

Drivermanager.registerdriver (New Com.mysql.jdbc.Driver ());

Although the above code can register the driver, but there is hard coding (the code depends on the MySQL driver jar package), if you want to connect the Oracle database in the future, then you must modify the code. And in fact, this registration-driven way is registered two times the driver!

In JDBC, the driver class needs to be "active" to register itself in the Drivermanger when it is loaded, so let's take a look at the source code of the Com.mysql.jdbc.Driver class:

Com.mysql.jdbc.Driver.java

1  Public classDriverextendsNonregisteringdriverImplementsJava.sql.Driver {2     Static {3         Try {4Java.sql.DriverManager.registerDriver (NewDriver ());5}Catch(SQLException E) {6             Throw NewRuntimeException ("Can ' t register driver!");7         }8     }9 ...Ten}

The static blocks in the Com.mysql.jdbc.Driver class create this class of objects and register them in DriverManager. This means that as long as the Com.mysql.jdbc.Driver class is loaded, the static block is executed and the Com.mysql.jdbc.Driver is registered to DriverManager, so the registration driver can be the code is modified to load the driver class .

Class.forName ("Com.mysql.jdbc.Driver");

4.3 Getting the connection

Getting a connection takes two steps, one using DriverManager to register the driver, and the other using DriverManager to get the connection object.

There is only one line of code to get the connection:

Drivermanager.getconnection (Url,username,password),

Where username and password are login database username and password, if I am not wrong, your MySQL database username and password are: root, 123.

URL check is a bit more complicated, it is used to find to connect to the database "url", just like you want to find Baidu in the browser, also need to provide a URL. Here is the URL for MySQL:

Jdbc:mysql://localhost:3306/mydb1

JDBC Specifies that the format of a URL consists of three parts, each separated by a colon.

• • The first part is JDBC, which is fixed;
  • The second part is the database name, then connect the MySQL database, the second part of course is MySQL;
  • The third part is stipulated by the database manufacturers, we need to understand the requirements of each database manufacturer, MySQL third part is the database server's IP address (localhost), port number (3306), as well as databases name (MYDB1) composition.

Here is the statement that gets the connection:

Connection con = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/web08", "root", "root");

You can also provide parameters in the URL:

Jdbc:mysql://localhost:3306/web08? Useunicode=true&characterencoding=utf8

The Useunicode parameter specifies the set of Unicode bytes used in the process of connecting to the database;

The characherencoding parameter specifies that the byte set encoded as UTF-8 encoding is used during the connection to the database. Note that the specified UTF-8 encoding in MySQL is given by UTF8, not UTF-8. Be careful!

4.4 Get Statement

After getting connectoin, the instructions are already connected to the database, and the following is the code to get the statement object through connection:

Statement stmt = Con.createstatement ();

Statement is used to send SQL statements to the database for execution!

4.5 Sending SQL query statements

String sql = "SELECT * from user";

ResultSet rs = stmt.executequery (SQL);

Note that the Execute query is not using the executeupdate () method, but rather the ExecuteQuery () method. The ExecuteQuery () method returns Resultset,resultset encapsulates the query result, which we call the result set.

4.6 reading data from the result set

ResultSet is a two-dimensional table, it has a "line cursor", the cursor default position in the "first row above", we can call the RS object's next () method to move the "line cursor" down one line, when the first call to the next () method, the "row cursor" On the first row of records, you can use the GetXXX (int col) method provided by ResultSet to get the data for the specified column:

Rs.next ();//cursor moves to the first line

Rs.getint (1);//Get data for the first column of the first row

When you use the Rs.getint (1) method, you must be sure that the data type of the 1th column is the int type, and if you are not sure, then it is best to use Rs.getobject (1). A series of getxxx () methods are provided in the ResultSet class, and the more commonly used methods are:

Object getObject (int col)

String getString (int col)

int getInt (int col)

Double getdouble (int col)

4.7 Close, Release resources

As with the IO stream, everything after use needs to be closed! The order of closing is first obtained after closing, and then gets closed first.

Rs.close ();

Stmt.close ();

Con.close ();

First stage: Front-end development _jdbc Simple Introduction