JSP database operations

JSP uses database operations. In JSP, Java JDBC technology can be used to query, modify, and delete table records in the database. JDBC technology plays an important role in JSP development. JDBC (JavaDataBaseConnectivity) is a Java Database Connection

JSP database operations
In JSP, Java JDBC technology can be used to query, modify, and delete table records in the database. JDBC technology plays an important role in JSP development.
JDBC (Java DataBase Connectivity) is a Java DataBase connection API. Simply put,
JDBC can accomplish three things:
(1) Establish a connection with a database,
(2) Send SQL statements to the database,
(3) process the results returned by the database.
A common method for establishing a connection between JDBC and a database is to establish a JDBC-ODBC bridge. Since ODBC drivers are widely used, after the bridge is established, JDBC has the ability to access almost all
There is a type of database. JDBC can also directly load the database driver to access the database,
If you use the JDBC-ODBC bridge to access the database, you must set the data source in advance.
1 Data Source
Use the system to create an ODBC data source.
2. JDBC-ODBC Bridge
Create a JDBC-ODBC bridge, that is, load the bridge driver.
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");
Class is a Class in the java. lang Package, which can be used to build a JDBC-ODBC bridge by calling its static method forName.
3. query records
(1) connect to the database
First, declare an object using the Connection class in the java. SQL package, and then use the class DriverManager to call its static method getConnection to create this Connection object:
Connection con = DriverManager. getConnection ("jdbc: odbc: Data Source name", "login name", "password ");
(2) Send SQL statements to the database.
First, declare an SQL Statement object using Statement, and then create this SQL Statement object using the con call method createStatment () created to connect to the database.
Statement SQL = con. createStatement ();
(3) process query results
With the SQL statement object, this object can call corresponding methods to query and modify tables in the database. And store the query results in an object declared by the ResultSet class,
That is to say, the SQL statement will return a ResultSet object for the database query operation:
ResultSet rs= SQL .exe cuteQuery ("SELECT * FROM orders table ");
The ResultSet object is composed of data rows organized by columns in a unified form. The ResultSet object can only see one data row at a time, and uses the next () method to go to the next data row. After obtaining a row of data,
The ResultSet object can use the getxxxx method to obtain the field value, and pass the location index (the first column uses 1, the second column uses 2, and so on) or field name to the parameter of the getxxxx method.
You can use the next () method of the Result set to query results in sequence. A result set first locates the cursor before the first row. The next () method is called for the first time to move the cursor to the first row.
The next () method returns a boolean type data, and false is returned when the cursor moves to the last row.
Scrolling result set:
Statement stmt = con. createStatement (int type, int concurrency );
Based on the value of type and concurrency, stmt returns the result set of the corresponding type:
The value of type determines the scroll mode. The value can be:
ResultSet. TYPE_FORWORD_ONLY: The cursor of the result set can only scroll down.
ResultSet. TYPE_SCROLL_INSENSITIVE: The cursor of the result set can be moved up or down. When the database changes, the current result set remains unchanged.
ResultSet. TYPE_SCROLL_SENSITIVE: returns a scrollable result set. When the database changes, the current result set changes synchronously.
The value of Concurrency determines whether the database can be updated using the result set. The value of Concurrency is as follows:
ResultSet. CONCUR_READ_ONLY: tables in the database cannot be updated using the result set.
ResultSet. CONCUR_UPDATETABLE: Use the result set to update Tables in the database.
The following ResultSet method is often used for Rolling queries:
Public boolean previous (): move the cursor up. This method returns boolean data and returns false if it is before the first row of the result set.
Public void beforeFirst (): move the cursor to the initial position of the result set, that is, before the first line.
Public void afterLast (): move the cursor to the last row of the result set.
Public void first (): move the cursor to the first row of the result set.
Public void last (): move the cursor to the last row of the result set.
Public boolean isAfterLast (): determines whether the cursor is behind the last row.
Public boolean isBeforeFirst (): determines whether the cursor is before the first row.
Public boolean ifFirst (): determines whether the cursor points to the first row of the result set.
Public boolean isLast (): determines whether the cursor points to the last row of the result set.
Public int getRow (): Get the row number of the row pointed by the current cursor. The row number starts from 1. If no row exists in the result set, 0 is returned.
Public boolean absolute (int row): move the cursor to the row specified by the row parameter.
Note: If the row value is negative, it indicates the number of rows to be counted. absolute (-1) indicates moving to the last row, while absolute (-2) indicates moving to the last 2nd rows. When moving to the front of the first line or
This method returns false when the last row is followed.
4. update records
Use SQL statements to update the field values in a record
Statement object call method: public int executeUpdate (String sqlStatement );
5. Add record
Add new records using SQL statements
Statement object call method: public int executeUpdate (String sqlStatement );
6. delete records
Delete records using SQL statements
Statement object call method:
Public int executeUpdate (String sqlStatement );
Note: You can use a Statement object to add, modify, delete, and query results. However, when a query Statement returns a result set, no records of the result set are output immediately,
Then the add statement is executed, so the result set cannot output records. To output a record, you must return the result set again.
7. other methods for connecting to the database
7.1 connect to the Oracle database
(1) load the driver:
Class. forName ("oracle. jdbc. driver. OracleDriver ");
(2) establish a connection:
Connection conn = DriverManager. getConnection ("jdbc: oracle: thin: @ host: Port Number: Database Name", "User Name", "password ");
7.2 connect to the MySql database
(1) load the driver:
Class. forName ("org. gjt. mm. mysql. Driver"). newInstance ();
(2) establish a connection:
Connection conn = DriverManager. getConnection ("jdbc: mysql: // host: Port: Database Name", "User Name", "password ");