JDBC Connection MySQL Database

preparatory work , well jdk,myeclipse.

Download the JDBC Driver Mysql-connector-java-5.0.5-bin.jar import into the project

1. Engineering (right-click)--buildpath--configure build Path--add external jars.

2, can also be added to the Classpath, as follows: "My Computer", "Properties", "Advanced", "Environment variables", in the system variables to edit classpath, will D:\mysql-connector-java-5.0.5\ Mysql-connector-java-5.0.5-bin.jar add to the end, add ";" before adding the string, to separate from the previous Classpath area, and then OK.

Test code: The drive Mysql-connector-java-5.0-nightly-20071116-bin.jar used

The necessary packages are referenced in the program: Import java.sql.*; It contains the various classes and interfaces that operate the database

1 , loading the appropriate JDBC Driver Program

Class.forName (Driver);

MySQL Driver: com.mysql.jdbc.Driver

Oracle Driver: Oracle.jdbc.driver.OracleDriver

SQL Server Driver:

Com. Microsoft.jdbc.sqlserver.SQLServerDriver

2 , establishing a database connection

How to connect to the MySQL database:

Connection con=drivermanager.getconnection ("jdbc:mysql:// host IP or host name : 3306/ database Name ", user name, password );

Java.sql.DriveManager is used to process the load driver and provides support for creating a new database connection;

–java.sql.connection is used to complete the connection to a specific database;

–java.sql.statement is used to execute SQL statements against a particular database, and Java.sql.Statement contains the following two important subtypes:

Java.sql.PreparedStatement The SQL statement used to execute the precompiled;

Java.sql.CallableStatement is used to perform calls to procedures stored in the database;

--java.sql.resultset The result set used to hold the query

3 , creating Statement Object

Statement st=con.createstatement (); Can not be closed at last, but recommended to close

Static SQL statements can be executed with statement objects, and static SQL statements may be SELECT statements, DELETE statements, UPDATE statements, and INSERT statements.

Execute SQL statement

The statement interface provides three ways to execute SQL statements: ExecuteQuery (), executeupdate (), and execute (). The exact method used is determined by the SQL statement itself.

The ExecuteQuery method uses statements that produce a single result set, such as a SELECT statement.

The Executeupdate method is used to execute INSERT, UPDATE, or DELETE statements, as well as SQL DDL (data definition language) statements, such as CREATE table and DROP table. The effect of an INSERT, UPDATE, or DELETE statement is to modify one or more columns in 0 or more rows in a table. The return value of Executeupdate is an integer that indicates the number of rows affected (that is, the update count). For statements that do not manipulate rows such as CREATE table or drop table, the return value of executeupdate is always zero.

The Execute method executes a statement that returns multiple result sets, multiple update counts, or a combination of the two. This advanced functionality is generally not required.

①JDBC does not make any checks on the SQL query statement that will be executed at compile time, just as a string class object until the driver executes the SQL query statement to know if it is correct. For an incorrect SQL query statement, SQLException will be generated at execution time.

② A statement object can only open one result set at a time, and opening to the second result set implies closing the first result set.

③ if you want to operate on multiple result sets at the same time, you must create multiple statement objects and Execute SQL query statements on each statement object to obtain the corresponding result set.

④ If you do not need to work with multiple result sets at the same time, you can sequentially execute multiple SQL query statements on one statement object and sequentially manipulate the resulting set of results.

Str= "Insertinto Customer values (' Wangyang ',", ' Beijing ', ' [email protected] ') ";

int recordnumber=st.executeupdate (str); the Execute executeupdate () method returns the number of records involved in the SQL statement.

Str= "select* from MyCustomer"; Record in query table

ResultSet rs=st.executequery (str); Because the SQL statement is select, the ExecuteQuery () method is used to return a result set stored in the ResultSet object Rs.

4 , Analysis ResultSet Object

After ① executes the SQL statement, it returns an object of the ResultSet class that contains all the query results. However, the way the object of the ResultSet class depends on the cursor type, and the individual columns in each row can be processed in any order (of course, if the columns are processed in left-to-right order, the execution efficiency can be higher);

The main ways of course in resultset class are:

Resultset.type_forward_only (default): The cursor can only go forward and not back, that is, it can only move from the first one to the last.

Resultset.type_scroll_sensitive: Allows the cursor to move forward or backward and sense the movement of other ResultSet cursors.

Resultset.type_scroll_insensitive: Allows the cursor to move forward or backward without sensing the movement of other ResultSet cursors.

Whether the data in the ResultSet class is allowed to be modified is mainly:

Resultset.concur_read_only (default setting): Indicates that data can only be read-only and cannot be changed.

Resultset.concur_updatable: Indicates that data is allowed to be modified.

You can specify these two attributes of resultset when you create a statement or PreparedStatement object.

Statement stmt=con.createstatement (resultset.type_forward_only,resultset.concur_read_only);

Or

PreparedStatement Pstmt=con. Preparestatement ("INSERT into booktable values (?,?,?)", Resultset.type_scroll_insensitive,resultset.concur_ updatable);

The object of the ②resultset class maintains a pointer to the current line, which can be moved to the next line using the next () method of the ResultSet class (in JDBC, the Java program can see only one row of data at a time), and if the return value of next () is false, The end of the recordset is indicated. In addition, JDBC does not have a method similar to the ODBC bookmark function.

③ uses the GetXXX () method of the ResultSet class to obtain the results of a column where xxx represents the Java data type in JDBC, such as getInt (), getString (), GetDate (), and so on. You need to specify the columns you want to retrieve (you can use the int value as the column number (starting from 1) or specify the column (field) name, but the field name does not distinguish the case of the letter).

Example:

while (Rs.next ()) {

int Id=rs.getint ("id");

String name=rs.getstring ("CName");

int Age=rs.getint ("CAge");

String address=rs.getstring ("caddress");

String email=rs.getstring ("Cemail");

System.out.println (id+ "" +name+ "" +age+ "" +address+ "" +email ");

}

Get the structure information in the result set: Use the GetMetaData () method of the ResultSet class to get some structure information in the result set, which is mainly provided to describe the number of columns, the name of the column, and the data type of the column. Use the methods in the Resulsetmetadata Class).

ResultSetMetaData Rsmd=rs.getmetadata ();

Rsmd.getcolumncount (); Returns the number of columns in the result set

Rsmd.getcolumnlabel (1); Returns the column name (field name) of the first column

For example:

Statement stmt=con.createstatement ();

ResultSet rs=stmt.executequery ("SELECT * from TableName");

for (int i=1; I<=rs.getmetadata (). getColumnCount (); i++)//trace displays the names of individual columns

{System.out.print (Rs. getcolumnname (i) + "\ T");

}

while (Rs.next ())

{//trace displays values for individual columns

for (int j=1; J<=rs.getmetadata (). getColumnCount (); j + +)

{System.out.print (Rs.getobject (j) + "\ T");

}

}

5 , close the connection

( Note the order of closing) example:

Rs.close ();

St.close ();

Con.close ()

6 , JDBC the common API

First, Connection Interface:

1.createStatement (): Create a database connection

2.prepareStatement (Stringsql): Creating a preprocessing statement

3.prepareCall (stringsql): Create callable statements

4.getAutoCommit (): Get auto-commit mode

5.setAutoCommit (): Set auto-commit mode

6.commit (): Commit the executed SQL statement

7.rollback (): Rollback of executed SQL statement

8.getMetaData (): Gets a DatabaseMetaData object that contains basic information about the database

9.close (): Close database connection

10.isClose (): Determines whether the database connection timed out or is displayed off

Second, Statement Interface:

1.execute (stringsql): Execute SQL statement, true if the return value is a result set, False otherwise

2.executeQuery (stringsql): Execute SQL statement with a return value of resultset

3.executeUpdate (stringsql): Execute SQL statement, return value is the number of rows affected

4.addBatch (Stringsql): Adds a new batch SQL statement to the list of commands for the current statement object

5.clearBatch (): Clears the list of commands for the current statement object

6.executeBatch (): Executes a batch statement of the current statement object, with the return value being an array of functions affected by each statement

7.getConnection (): Returns the Connection object that created the statement object

8.getQueryTimeout (): Gets the time to wait for processing results

9.setQueryTimeout (): Sets the time to wait for processing results

Third, ResultSet Interface:

1.first ()/beforefirst (): Moves the cursor to the first record in resultset (front)

2.last ()/afterlast (): Moves the cursor to the last record in ResultSet (after)

3.absolute (Intcolumn): Moves the cursor to the specified row relative to the first row, negative is relative to the last record

4.relative (introws): Moves the cursor to the first row relative to the current row, just down, negative to up

5.next (): Moves the cursor down one line

6.previous (): Moves the cursor up one line

7.insertRow (): Inserts a record into the current resultset and in the database where rows are inserted

8.deleteRow (): Deletes the current row in the current resultset and the corresponding record in the database

9.updateRow (): Update the corresponding record in the database with the updated records in the current resultset

10.cancelUpdate (): Cancels the current operation on resultset and the database

11.findColumn (Stringcolumnname): Returns the index in the current resultset that corresponds to the specified column name

12.getRow (): Returns the current line number in resultset

13.refreshRow (): Update all records in the current resultset

14.getMetaData (): Returns the ResultSetMetaData object that describes resultset

15.isAfterLast (): Whether to the end

16.isBeforeFirst (): Whether it is at the beginning

17.isFirst (): Whether the first record

18.isLast (): Whether the last record

19.wasNull (): Checks if the column value is a null value, if the type of the column is the base type, and the value in the database is 0, then

This check is very important. Because the database null also returns 0, the value of 0 and the null of the database cannot be distinguished. If the type of the column is an object, you can simply compare the return value to NULL

20.close (): Close current resultset

Four, ResultSetMetaData Interface:

1.getColumnCount (): Returns the number of columns in resultset

2.getColumnName (): Returns the name of the column in the database

3.getColumnType (): Returns the SQL type of the column

4.isReadOnly (): Indicates whether the data item is a read-only value

5.isNullable (): Indicates whether the column can store null

Instance:

Import java.sql.*;p Ublic class Jdbctest {public static void main (string[] args) {//driver name String Drive           r = "Com.mysql.jdbc.Driver";           The URL points to the database name to access Scutcs String URL = "Jdbc:mysql://127.0.0.1:3306/test";            MySQL configuration username String user = "root";           MySQL configuration when the password String password = "123456";            try {//Load driver Class.forName (driver);            Continuous database Connection conn = drivermanager.getconnection (URL, user, password);            if (!conn.isclosed ()) System.out.println ("succeeded connecting to the database!");            Statement used to execute SQL statements Statement Statement = Conn.createstatement ();            SQL statement to execute String sql = "SELECT * from Pet";            Result set ResultSet rs = statement.executequery (SQL);                       System.out.println ("-----------------");            String name = NULL;     while (Rs.next ()) {           Select sname This column of data name = rs.getstring ("name");             First, use the iso-8859-1 character set to decode name into a sequence of bytes and store the result in a new byte array.             Then use the GB2312 character set to decode the specified byte array name = new String (name.getbytes ("iso-8859-1"), "GB2312");            Output result System.out.println (rs.getstring ("ID") + "\ T" + name);            } rs.close ();           Conn.close ();            } catch (ClassNotFoundException e) {System.out.println ("Sorry,can ' t find the driver!");           E.printstacktrace ();           } catch (SQLException e) {e.printstacktrace ();           } catch (Exception e) {e.printstacktrace (); }}}

JDBC Connection MySQL Database