JDBC Database operations

1. How to connect the database:

① establishing JDBC-ODBC Bridge Connector

The application simply establishes a connection between JDBC and ODBC , and the database connection is completed by ODBC;

Pros: Gives JDBC the ability to access almost any type of database.

Cons: Dependency on ODBC, poor portability.

Try {    class.forname ("Sun.jdbc.odbc.JdbcOdbcDriver");} Catch (ClassNotFoundException e) {    System.out.println (e);}

② loading a pure Java database driver

Pros: Not dependent on the platform.

Drivers are provided by the database manufacturer

2. Establish the connection:

Use the java.sql.Connection class to declare an object, and then use class DriverManager to invoke its static method getconnection Create the Connection object:

Try {    Connection con=drivermanager.getconnection ("JDBC:ODBC: Data Source Name", "LoginName", "password");} Catch (SQLException e) {}

3. Query operation:

Using Statement to declare an SQL statement object, let the connection object con call method createstatment () to create the SQL statement object:

Try {    Statement sql=con.createstatement ();} Catch (SQLException e) {}

The results of the query are stored in the object declared by the ResultSet class:

ResultSet rs=sql.executequery (SQL);

Methods for ResultSet objects:

Boolean Next ()

byte getbyte (int columnindex or string columnindex)

Date getDate (int columnindex or string columnindex)

Double getdouble (int columnindex or string columnindex)

float getfloat (int columnindex or string columnindex)

int getInt (int columnindex or string columnindex)

Long Getlong (int columnindex or string columnindex)

String getString (int columnindex or string columnindex)

(1) Sequential query:

① Connection object con calls GetMetaData () to return a DatabaseMetaData object:

DatabaseMetaData Metadata=con.getmetadata ();

The ②metadata object is then called GetColumns to store the table's field information as a row and column in a ResultSet object:

ResultSet tablemessage=metadata.getcolumns (database name, database extension, table name, field name);

③ Cursors Get information:

Cursor initial position before line 1th

Tablemessage object calls next () moves the cursor down one line (sequential query)

④ Control Cursors:

The Statement object uses the createstatement (int type,int concurrency) method of the Connection object to return a scrollable result set;

The value of type determines the scrolling method:

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 and down (when the database changes, the current result set is unchanged);

resultset.type_scroll_sensitive //The cursor of the result set can be moved up and down (when the database changes, the current result set changes synchronously);

The value of the concurrency determines whether the database can be updated with the result set:

resultset.concur_read_only //The table in the database cannot be updated with the result set;

resultset.concur_updatable //can update the tables in the database with the result set;

Methods for ResultSet objects:

public Boolean Previous ()

Moves the cursor up and returns False when it moves to the first row of the result set;

public void Beforefirst ()

Moves the cursor to the initial position of the result set, before the first row;

public void Afterlast ()

Moves the cursor after the last row of the result set;

public void First ()

Moves the cursor to the first row of the result set;

public void Last ()

Moves the cursor to the last row of the result set;

public boolean isafterlast ()

Determines whether the cursor is after the last row;

public boolean Isbeforefirst ()

Determines whether the cursor precedes the first line;

public boolean IsFirst ()

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 ()

Gets the line number of the row that the current cursor refers to, and the line number starts at 1, and returns 0 if the result set has no rows;

public boolean absolute (int row)

Moves the cursor to the row specified by the parameter row (negative values are the number of rows that are reciprocal);

(2) Sort query:

SQL statement: SELECT * from  table name ORDER  by  field name

(3) Fuzzy query:

SQL statement: SELECT *  from table name WHERE field name like ' [contains character]% '//% represents 0 or more characters

4. Update, add, and delete operations:

Update:

SQL statement: Update < table name > SET < field name > = new value where < conditional clause > such as: Update  goods= 3009 WHERE name= ' Haier TV '

Add to:

SQL statements: INSERT into table (field list) values (corresponding specific records) such as: INSERT INTO goods (Number,name,madetime,price) VALUES (' A009 ', ' Phone ', ' 2010-12-20 ', 3976)

Delete:

SQL statement: Delete from < table name > where < conditional clause > such as: delete from goods wherenumber = ' B002 '

5. Preprocessing statements:

The SQL statement is interpreted as the underlying internal command of the database, allowing the database to execute the command, increasing the speed of accessing the database.

The Connection object con invokes the preparestatement (String sql) method to pre-compile the SQL statement specified by the parameter SQL;

The method that the preparestatement object calls:

ResultSet ExecuteQuery ()

Boolean Execute ()

int executeupdate ()

6. Transactions (a set of SQL statements):

Transaction processing: The application guarantees that the SQL statements in the transaction are either all executed or none of them executed.

The ① Connection object con invokes the setautocommit (Boolean Autocommite) method, and the parameter autocommit evaluates to False to turn off autocommit mode;

The ② Connection object con calls the commit () method to make the SQL statements in the transaction take full effect;

③ Connection object con calls the rollback () method to handle a transaction failure, that is, if any of the SQL statements in the transaction fail to take effect, throw a SQLException exception, call rollback () METHOD to revoke the statement of the firm;

7. Batch processing:

The statement object calls the ExecuteBatch () method to execute multiple SQL statements at a time;

Let the statement object call the addbatch (String sql) method in advance to add the SQL statement that will be executed to the object;

8.CachedRowSetImol class

Resolve data loss in ResultSet object after Connection object con is closed (avoid consuming database connection resources for a long time)

Com.sun.rowset.CachedRowSetImol Class (implements the CachedRowSet interface)

The Cachedrowsetimpl object calls the populate (Resultset Rs) method to save the data in the Resultset object:

such as: ResultSet rs=sql.executequery (SQL);　　Cachedrowsetimpl RowSet=new  Cachedrowsetimpl;　　Rowset.populate (RS); Con.close ();

JDBC Database operations