Java Development Engineer (web direction)-03. Database Development-1th chapter. Jdbc

1th Chapter--JDBCJDBC Foundation

Java database Connectivity enables Java programs to access back-end databases

A complete database deployment architecture, typically consisting of both client and server-side components

The client encapsulates the database request and sends it to the server side, and returns the result to the client after the server finishes execution.

Common Client Tools:

MySQL Workbench (graphical chemical tools are simple to operate but can only be implemented with simple queries)

JDBC (Driver jar package)

Command-line tools (directly typed into SQL statements for querying)

Different databases on the market, high learning costs, portability of database migration

--JDBC (Common Java Class Library): Applications can access any database through a unified interface.

For database manufacturers, JDBC is a set of interface specifications, each database needs to implement a JDBC-defined interface, the user through the interface to access the database.

JDBC Advantage: Easy access to the database, fast development, time-saving, strong portability for different databases, customizable powerful frameworks on JDBC (e.g. MyBatis)

System Architecture:

The upper API layer is responsible for communication with the Java Web program

JDBC API:

Driver & DriverManager:

Driver is the abstraction of the driver, the operation of the various drivers can be achieved by manipulating the driver interface.

DriverManager is the management class of the driver, the user registers a driver with DriverManager through Class.forName (drivername),

The driver is then called through the DriverManager getconnection method to establish a physical connection to the backend database.

Drivermanager.getconnection (Db_url, USER, PASS));

User and pass are available when deploying the database

Db_url is a unique identifier for the back-end DB instance

i.e. Jdbc:mysql://10.164.172.20:3306/cloud_study

Protocol Sub-Protocol sub-name (host Port database)

(Sub-protocols differ, and the format of sub-names is slightly different.) ）

Connection: Physical connection to the back-end database through the DriverManager getconnection method

A physical connection of a Java application to a back-end database

With these connections, you can execute some SQL statements:

Statement stmt = Conn.createstatement (); SQL statement

The container for the Statement:sql statement, which is used to host the SQL statement, in which the operations such as the increase and deletion can be performed

Through the ExecuteQuery method, the database query is executed and a collection of the returned results is obtained, represented by the object of the ResultSet class:

ResultSet rs = stmt.executequery ("Select UserName from user");

Perform database update, DELETE statement by executeupdate method, return an int value object, represent the number of database records affected

The ResultSet object represents the result of an SQL statement query.

Relational database: Two meta table-the ResultSet object is also made up of rows and columns

There is a pointer inside the ResultSet object that points to the current row record (default to the first row of records)

. Next (): Move the pointer to the next line

. Previous (): Move the pointer to the next line

. Absolute (): Position the pointer in a row

. Beforefirst (): Moves the pointer before the first line (via. Next () to the first row)

. Afterlast (): After you move the pointer over the last line

. GetString (Columnname/index): (Index starting from 0) Gets the value of the corresponding column

. GetInt (Columnname/index):

. GetObject (Columnname/index):

SQLException: MySQL may throw some exceptions during execution

Handling exceptions by capturing SqlException objects

The underlying driver API layer is responsible for establishing a connection to a specific database, and generally the underlying driver is provided by the database vendor.

To install JDBC:

JDBC is already integrated into the JDK and can be referenced directly without installation.

You need to install a driver for the database

For MySQL database:

Log in to your Oracle account, download the jar package, and add it to Java Web project.

https://dev.mysql.com/downloads/connector/Download connector/j 5.1.43

To build a complete Java Web program:

Build steps:

Load driver: Register a driver with DriverManager driver

Establish database connection: Drivermanager.getconnection ()

Execute SQL statement: Statement.execute ... ()

Get execution Result: ResultSet object

Clean up the environment:

Before using JDBC, you need to prepare a back-end instance of a database to create a user table

Java Development Engineer (web direction)-03. Database Development-1th chapter. Jdbc