Java note 03: MySQL database and JDBC programming 01

JDBC is a standard solution for accessing the database system using Java. It treats different APIs of different databases separately from standard SQL statements to implement database-independent Java operation interfaces. For developers, using the unified jdbc api and focusing on standard SQL statements can avoid directly processing underlying database drivers.ProgramDifferent from related operation interfaces, the main focus is on the application development itself, so as to speed up the development progress.

 

1. JDBC API

The JDBC specification adopts the idea of separation between interfaces and implementations, and designs the Java database programming framework. JDBC APIs provide a set of interfaces and classes in the standard Java language. Using these interfaces and classes, Java client programs can access different types of databases. 1.

Figure 1 Working Principle of JDBC

The jdbc api is provided by Sun and defines methods for establishing connections with databases, executing SQL statements, and processing results. These interfaces and classes are integrated in Java. SQL and javax. SQL package. The drivermanager class loads different JDBC drivers. The JDBC drivers of various databases are provided by different database vendors to implement methods in JDBC APIs for specific database products.

JDBC APIs mainly provide the following interfaces and classes: connection, drivermanager, statement, resultset, preparedstatement, and callablestatement.

The connection object indicates the connection to the database. The connection process includes the executed SQL statement and the result returned by the connection. An application can have one or more connections to a single database, or many databases.

The drivermanager class is the management layer of JDBC and acts between users and drivers. It tracks available drivers and establishes connections between the database and the corresponding drivers.

The statement object is used to send SQL statements to the database. There are actually three statement objects, which are used as the package containers for executing SQL statements on a given connection: Statement, preparedstatement, and callablestatement.

The resultset contains all rows that meet the conditions in the SQL statement, and provides access to the data in these rows through a set of get methods (these get methods can access different columns in the current row.

The preparedstatement instance contains compiled SQL statements. The SQL statement contained in the preparedstatement object can have one or more parameters.

The callablestatement object provides a standard method for calling stored procedures for all DBMS systems. Stored procedures are stored in the database. The call to a stored procedure is the content contained in the callablestatement object.

For a detailed description of jdbc api can see the Java Database Interface JDBC basic introduction lecture (http://www.yesky.com/zhuanti/443/1865943.shtml), explain in detail.

 

2. Establish the environment

The following describes how to install the MySQL database and the navicat for MySQL tool.

MySQL database download and installation can see MySQL installation diagram (http://www.jb51.net/article/23876.htm), here will not go into detail.

Navicat for MySQL is a user management interface tool tailored for MySQL databases based on the Windows platform. With a graphical user interface (GUI), you can quickly create, organize, access, and share data in a secure and easier way.

Navicat for MySQL is: http://www.cr173.com/soft/26935.html

Navicat for MySQL installation and use can see navicat MySQL installation and use instructions (http://wenku.baidu.com/view/6938c04b2b160b4e767fcf6b.html), here will not go into detail.

Run the navicat for MySQL tool to create a connection to the MySQL database we installed. Here, the connection name I created is "myconnection ". Open the connection and create a database. Here I create a database named "mydb. In this database, we created a table named "userinfo.

After all this is done, we can design the table to specify the storage structure of the data. In this table, the ID, username, and password contents are stored. Table 2 is designed.

Figure 2 Table Design

 

3. Use JDBC to access the MySQL database

Follow these steps to access a database using JDBC:

3.1 load the JDBC driver

Different databases correspond to different drivers, to access the MySQL database using jdbc api, You need to import the MySQL database driver package mysql-connector-java-5.1.7-bin under the libs directory of the project.

Different database manufacturers have different driver classes, specifically:

Oracle10g: Oracle. JDBC. Driver. oracledriver

Mysql5: COM. MySQL. JDBC. Driver

Sqlserver2005: COM. Microsoft. sqlserver. JDBC. sqlserverdriver

3.2 provide connection Parameters

Different database products have different connection URLs, specifically:

Oracle10g: JDBC: oracle: thin: @ host name: Port: Database Sid

For example: JDBC: oracle: thin: @ localhost: 1521: orcl

Mysql5: JDBC: mysql: // host name: Port/Database Name

For example: JDBC: mysql: // localhost: 3306/test

Sqlserver2005: JDBC: sqlserver: // host name: Port: databasename = Database Name

For example: JDBC: sqlserver: // localhost: 1433: databasename = bookdb

When you access a database using JDBC, the provided connection parameters include the database URL and the database username and password.

3.3 establish a database connection

A connection object is a specific instance of a database connection. A connection object represents a database connection. You can use the getconneciton () method of drivermanager to pass in the connection URL, user name, and password of the specified database.

Get the connection object from drivermanager, and the driver will automatically register through the drivermanager. registerdriver () method, so that the drivermanager can communicate with the vendor's driver.

When connecting to the database, you need to check the exception object java. SQL. sqlexception.

The following describes how to establish a database connection:

 1  Private   Final String username = "root "; //  Define the Database User Name  2       Private   Final String Password = "******"; //  Define Database Password  3       Private  Final String driver = "com. MySQL. JDBC. Driver "; //  Define database driver information  4       Private   Final String url = "JDBC: mysql: // localhost: 3306/mydb "; //  Define the address for accessing the database  5       6  /*  7   * Function: Obtain the database connection.  8  * Author: blog Park-still indifferent  9        */  10       Public  Connection getconnection (){  11           Try  {  12 Mconnection = Drivermanager. getconnection (URL, username, password );  13 } Catch  (Exception e ){  14 System. Out. println ("failed to connect to the database! " );  15   }  16           Return  Mconnection;  17 }

3.4 create a statement object.

The preparedstatement object is used to execute dynamic SQL statements. Which of the following SQL statements can be used? As a placeholder parameter.

3.5 Execute SQL statements

You can add, delete, and modify common database operations. Sometimes we also need to query the information in the database table, then we can use the following method to achieve:

 1  /*  2   * Function: Query returns a single record.  3   * Author: blog Park-still indifferent  4        */  5       Public Map <string, Object> findsimpleresult (string SQL, list <Object> Params)  6               Throws  Sqlexception {  7 Map <string, Object> map =New Hashmap <string, Object> ();  8           Int Index = 1 ;  9 Mpreparedstatement = Mconnection. preparestatement (SQL );  10           If (Params! = Null )&&(! Params. isempty ())){  11               For ( Int I = 0; I <Params. Size (); I ++ ){  12 Mpreparedstatement. setobject (index ++ , Params. Get (I ));  13   }  14   }  15 Mresultset = mpreparedstatement.exe cutequery (); //  Return query results  16 Resultsetmetadata resultsetmatedata = mresultset. getmetadata (); // Obtain related column information  17           Int Cols_len = resultsetmatedata. getcolumncount (); //  Obtain the column length.  18           While  (Mresultset. Next ()){  19               For ( Int I = 0; I <cols_len; I ++ ){  20 String cols_name = resultsetmatedata. getcolumnname (I + 1 ); 21 Object cols_value = Mresultset. GetObject (cols_name );  22                   If (Cols_value = Null  ){  23 Cols_value = "" ;  24   }  25   Map. Put (cols_name, cols_value );  26   } 27   }  28           Return  Map;  29 }

3.6 handling results

The preceding steps have been used to access the database. In this step, you can process the accessed data.

3.7 close connection

After the operation is completed, all the JDBC objects used should be closed to release the JDBC resources. The specific implementation method is as follows:

 1  /*  2   * Function: disconnect 3   * Author: blog Park-still indifferent  4        */  5       Public   Void  Releaseconnection (){  6           If (Mresultset! = Null ){ //  Disable the resultset object  7               Try  { 8   Mresultset. Close ();  9 } Catch  (Sqlexception e ){  10   E. printstacktrace ();  11   }  12   }  13           If (Mpreparedstatement! = Null ){ // Disable the preparedstatement object  14               Try  {  15   Mpreparedstatement. Close ();  16 } Catch  (Sqlexception e ){  17   E. printstacktrace ();  18   }  19   } 20           If (Mconnection! = Null ){ //  Close connection object  21               Try  {  22   Mconnection. Close ();  23 } Catch  (Sqlexception e ){  24   E. printstacktrace (); 25   }  26   }  27 }