JSP--JDBC Technology

JDBC Technology
1. Introduction to JDBC
Large Web sites are database-linked, using JDBC technology to link databases
Jdbc:java data Base Connectivity, database Add/delete/change/check and batch operations after using JDBC to connect to the database
JDBC is the standard API for Java programs to communicate with database systems, and is defined in the JDK API, where JDBC bridges the Java program and database system.
Java programs <-------&GT;JDBC api<---------> Database Drivers <---------> Database systems

2, the JDBC Connection database process
1) Register Database driver
Connecting the database requires registering the database driver classes provided by the database vendor into the JDBC Driver Manager, typically loading the database-driven class into the JVM virtual machine.
Class.forName ("Com.mysql.jdbc.Driver");//Load Database driver, register to drive manager
2) Building the database connection URL
This URL is usually set by the database vendor, different database URLs are different, but the basic format is the JDBC protocol +IP address or domain + port + database name.
The string for the URL of the MySQL database is: "Jdbc:mysql://localhost:3306/db_admin"; Db_admin is a database name created locally
3) Get Connection object
After the first two steps have been completed, the connection object of the database can be obtained through the drive manager, which is a JDBC encapsulated database connection object that is created to perform related operations on the database
Drivermanager.getconnection (Url,username,password);
The DriverManager object is required to get the connection object, and the Getconnection () method of the object provides the database connection URL, the database user name, and the password to create the connection object

Tips:The JDK does not contain a database driver, using the JDBC operation database requires the database vendor to download the driver package, due to the use of MySQL database, it is added is the MySQL official database driver package
is: Mysql-connector-java-5.1.20-bin.jar//Database driver package
JDBC-------> Jar Package (Driver package)---------> Operations Database
try{
Class.forName ("Com.mysql.jdbc.Driver");
String URL = "Jdbc:mysql://localhost:3306/db_admin";
String userName = "root";
String Password = "123";
Connection Connection = drivermanager.getconnection (Url,username,password);
if (connection!=null) {
SYSTEM.OUT.PRINTLN ("Database connection succeeded");
}else{
SYSTEM.OUT.PRINTLN ("Database connection failed");
}
}catch (Exception ee) {
SYSTEM.OUT.PRINTLN (EE);
}

3. JDBC Core API
The JDBC Core API mainly consists of 5 interfaces:
Database Connection Interface Connection
Driver Management class DriverManager
Statement interface for executing SQL statements: executing static SQL statements
PreparedStatement interface for precompiled SQL statements: executing dynamic SQL statements
Result set ResultSet interface

Drivermanager.getconnection ()-----------> Connection-------> Connection.createstatement ()----> Statement Object
1) DriverManager class
The basic service for managing the JDBC driver is the management layer of JDBC, which acts between the user and the driver, is responsible for tracking the available drivers, and establishes a connection between the database and the driver.
The class also handles such things as the driver logon time limit, as well as displaying login and trace messages, and so on, when the driver class is successfully loaded and registered in the DriverManager class, the DriverManager class can establish a database connection
When the Getcoonnection () method of the DriverManager class is called to make a database connection, the class will attempt to locate an appropriate driver class and check if the driver class to which it is located can establish a connection if it can
The connection is established and returned. Otherwise throws a SqlException exception.
The main methods of this class are:
Getconnection (String url,string user,string password)
Establish a database connection based on the URL and user name and password of the specified database connection
Getdrivers ()
Gets all the drivers that have been loaded in the current DriverManager, and the return value is: enumeration type
Deregisterdriver (Driver Driver)
Remove a driver from the DriverManager management list.
Registerdriver (Driver Driver)
Registers a drive object with the DriverManager parameter driver the driver to be registered

2) connection interface representing the database connection
The connection interface is used to create a linked session of a database that can access the database only after the connection object is acquired and manipulate the database
Method:
Close () Closes the connection and frees up system resources
Createstatement () Create a statement object
IsClosed () Retrieves whether this connection object has been closed
PreparedStatement (String sql) creates a PreparedStatement object to send a parameterized SQL statement to the database

3) Statement interface for executing SQL statements
The statement interface is used to execute static SQL statements and return an object that produces results. Use this interface object to send a static SQL statement to the database compilation execution, and then return the database processing results
Method:
Close () releases the database and JDBC resources for this statement object
Execute (String SQL) executes the given SQL statement, which may return multiple results
Addbatch (String SQL) adds a pending SQL statement to batch
ExecuteBatch () submits a batch of commands to the database for execution, and returns an array of update counts if all the commands are executed successfully
ExecuteQuery (String SQL) executes the given SQL statement, which returns a single ResultSet object
Executeupdate (String SQL) executes the given SQL statement, which may be an insert update delete

4) PreparedStatement interface for precompiled SQL statements
Use placeholder "?" Instead of parameters, assign values to the parameters of the SQL statement by SETXX ()
Execute ()
ExecuteQuery ()
Executeupdate ()
SetBoolean (int parameterindex,boolean x) sets the specified parameter to the given Java Boolean value
SetByte (int parameterindex,byte x)
setDate (int parameterindex,date x)
Setint (int parameterindex,int x)//assign x to the Parameterindex parameter of the SQL statement
setString (int parameterindex,string x)

5) result set ResultSet interface
The ResultSet interface holds the results returned by the JDBC query when executing. The result set corresponds to a database field. Consists of rows and columns, and pointers are provided on the rows of the resultset result set, initially pointing to the first row of the result set
The next () method is called to move the pointer to the next row, False if there is no data on the next line, and generally loops through while () for the result traversal
Method:
Afterlast () Moves the pointer to the end of this ResultSet object, after the last row
Beforefirst () Moves the pointer to the beginning of this ResultSet object, before the first row
Close () Immediately releases the database and JDBC resources for this ResultSet object
First () Moves the pointer to the top row of this ResultSet object
getfloat (int columnindex) Retrieves the value of the specified column in the current row of the ResultSet object in the form of float
getInt (int columnindex) Retrieves the value of the specified column in the current row of the ResultSet object in the form of int
getString (int columnindex) Retrieves the value of the specified column in the current row of the ResultSet object in string form
Next () Moves the pointer down one line from the current position, and the iteration condition of the common while loop
Absolute (int row) moves the pointer to the given line number for this ResultSet object
Tips: The indexes inside are all starting from 1, unlike arrays
Does not synchronize operations to the database while executing the DeleteRow () method, but synchronizes to the database when the resultset result set is closed
It is recommended that each time the database is completed, turn off resultset Statement Connection
Although shutting down the statement instance directly also shuts down the associated resultset instance, and the garbage collection mechanism of the JVM virtual machine periodically cleans up the cache and shuts down the database connection for a long time, but if the shutdown is not timely
A certain number of database connections, will seriously affect the speed of the database and computer, and even paralysis

6) JSP Connection database operation
A: Install MySQL database, download the driver package: Mysql-connector-java-5.1.20-bin.jar, download the corresponding driver package according to the database version
B: Put the Mysql-connector-java-5.1.20-bin.jar under the Lib folder of the Tomcat server
C: The package that must be imported when accessing the database using a JSP program: java.sql.*
D: Using the server's own jar package and the Jrejar package

JSP--JDBC Technology