01. Getting started with JDBC

01. Getting started with JDBC
I. Getting started with JDBC 1. JDBC IntroductionJDBC is Java Database Connectivity. JDBC provides a standard API for tools/Database developers to write Database applications using pure Java APIs. Java database programming interfaces are interfaces and classes in a set of standard Java languages,Java client programs can access different types of databasesFor example, you can create a database connection and execute SQL statements to access data. The JDBC specification adopts the idea of interface and implementation separation to design the Java database programming framework. Interfaces are included in the java. SQL and javax. SQL packages. java. SQL belongs to JavaSE and javax. SQL belongs to JavaEE. The implementation classes of these interfaces are called database drivers, which are provided by the database vendor or other vendor or individual. To make the client program independent from a specific database driver, we recommend that developers use an interface-based programming method in the JDBC specification, that is, to make the application only dependent on java. SQL and javax. SQL interfaces and classes.
2. JDBC driverJDBC drivers (Implementation classes for accessing database programming interfaces) are JDBC implementation classes created by database manufacturers according to JDBC specifications. JDBC class structure: ◆ DriverManager: is an implementation class, which is a factory class (the structure design mode of the class is the factory method), used to produce the database Driver object; ◆ Driver: the interface of the driver object. It points to a real database driver object. The database object obtains the driver object by calling the getDriver (String URL) of DriverManager, this method is implemented statically in the classes in the database driver package designed by each database vendor according to JDBC specifications; ◆ Connection: the object connected to the database is implemented through this interface, obtained through the getConnection (String URL) method of the DriverManager factory; ◆ Statement: interface used to execute static SQL statements, obtained through the createStatement method in the Connection ◆ Resultset: this interface is used to point to the result set object. The result set object is obtained through the execute method in Statement.
2. Steps for Java to access the database using JDBCCreate a program to connect to the database using JDBC, which contains seven steps:

1. Load the database driver to JVMBefore connecting to the database, load the driver of the database to be connected to JVM (java Virtual Machine. lang. the static method forName (String className) of the Class is implemented. After Successful loading, the Driver Class instance is registered to the DriverManager Class. For example:
Try {// load the MySql Driver Class
Class. forName ("com. mysql. jdbc. Driver");} catch (ClassNotFoundException e) {System. out. println ("unable to find the Driver Class, failed to load the Driver! "); E. printStackTrace ();} Note: com. mysql. jdbc. driver, which is the MySQL database Driver Class, located in the mysql-connector-java-3.1.11-bin.jar com. mysql. driver in the jdbc package. class ).

2. Provide the URL of the JDBC connectionThe connection URL defines the protocol, sub-protocol, and data source identification used to connect to the database. Writing format: Protocol: Sub-Protocol: Data Source Identification Protocol: Always starting with JDBC in jdbc; Sub-Protocol: the name of the Bridge Connection driver or database management system; data source identification: mark the address and connection port of the database source. For example: (MySql connection URL) jdbc: mysql: // localhost: 3306/test? UseUnicode = true & characterEncoding = gbk;
Here, test is the database name; useUnicode = true indicates that the Unicode character set is used. If characterEncoding is set to gb2312 or GBK, this parameter must be set to true; characterEncoding = gbk is the character encoding method.

3. Create a database connectionTo connect to the database, you need to request to java. SQL. DriverManager and obtain the Connection object. This object represents the Connection to a database. Use the getConnectin (String url, String username, String password) method of DriverManager to input the path of the database to be connected, and the username and password of the database. For example:
// Connect to the MySql database. The username and password are both root String url = "jdbc: mysql: // localhost: 3306/test"; String username = "root "; string password = "root"; try {
Connection con =
DriverManager. getConnection (url, username, password);} catch (SQLException se) {System. out. println ("database connection failed! "); Se. printStackTrace ();}

4. Create a StatementTo execute an SQL statement, you must obtain the java. SQL. statement instances are divided into three types: (1) execute static SQL statements, which are usually implemented through the Statement instance; (2) execute dynamic SQL statements, which are usually implemented through the PreparedStatement instance; (3) execute the database Stored Procedure, usually through the CallableStatement instance. Specific implementation methods:
Statement stmt = con. createStatement (); // execute a static SQL Statement
PreparedStatement pstmt = con. prepareStatement (SQL); // execute dynamic SQL statements
CallableStatement cstmt = con. prepareCall ("{CALL demoSp (? ,?)} "); // Execute the Stored Procedure

5. Execute SQL statementsThe Statement interface provides three methods to execute SQL statements: executeQuery, executeUpdate, and execute (1) ResultSet executeQuery (String sqlString): execute the SQL Statement to query the database and return a result set (ResultSet) (2) int executeUpdate (String sqlString): used to execute INSERT, UPDATE, or DELETE statements and SQL DDL statements such as CREATE TABLE and DROP TABLE (3) execute (sqlString ): it is used to execute statements that return multiple result sets, multiple update counts, or a combination of the two. Specific implementation code:
ResultSet rs = stmt.exe cuteQuery ("SELECT * FROM ...");
Int rows = stmt.exe cuteUpdate ("insert ...");
Boolean flag = stmt.exe cute (String SQL );

6. processing resultTwo cases: (1) the number of records affected by this operation is returned when an update is executed. (2) The result returned by executing the query is a ResultSet object. ? 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 :? Use the access method of the result set object to obtain data:
While (rs. next () {// The returned result set is a series of row (record) data.
String name = rs. getString ("name ");
String pass = rs. getString (1); // This method is more efficient.
} (Columns are numbered from left to right and start from column 1)

7. Disable JDBC objectsAfter the operation is completed, all the used JDBC objects should be closed to release the JDBC resources. The order of closure is the opposite to the declared order: (1) closing the record set (2) closing the declaration (3) close connection object
If (rs! = Null) {// close record set try {
Rs. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (stmt! = Null) {// close the Declaration try {
Stmt. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (conn! = Null) {// close the connection object try {
Conn. close ();} catch (SQLException e) {e. printStackTrace () ;}}conclusion 1: Load different database drivers to JMV code (1) Oracle Database: Class. forName ("oracle. jdbc. driver. oracleDriver "); (2) SQL Server database: Class. forName ("com. microsoft. jdbc. sqlserver. SQLServerDriver "); (3) MySQL database: Class. forName ("com. mysql. jdbc. driver "); (4) Access Database: Class. forName ("sun. jdbc. odbc. jdbcOdbcDriver "); Conclusion 2: Create a database Connection string in the format of Connection con = DriverManager. getConnection ("database jdbc url", "database username", "Database Password ");

3. Drivers and JDBC URLs of common databases

1. Oracle Database(1) driver package name: ojdbc14.jar (2) Name of the driver class: oracle. jdbc. driver. oracleDriver (3) jdbc url: jdbc: oracle: thin: @ dbip: port: databasename Description: The driver package name may change to the Black font part of the jdbc url, which must be kept intact, the URL format recognized by the driver. The red font must be filled in according to the database installation. The meaning of each part is as follows: dbip-the IP address of the database server. If it is a local IP address, it can be written as localhost or 127.0.0.1. Port-indicates the listening port of the database. You need to check the configuration during installation. The default value is 1521. Databasename-is the SID of the database, usually the name of the global database. For example, if you want to access the local database allandb, port 1521, the URL is written as follows: jdbc: oracle: thin: @ localhost: 1521: allandb

2. SQL Server database(1) driver package name: msbase. jar mssqlserver. jar msutil. jar (2) Driver Class Name: com. microsoft. jdbc. sqlserver. SQLServerDriver (3) jdbc url: jdbc: microsoft: sqlserver: // dbip: port; DatabaseName = databasename description: the driver package name may change to the URL format recognized by the driver because the Black font in the jdbc url must be kept intact. The red font must be filled in according to the database installation. The meaning of each part is as follows: dbip-the IP address of the database server. If it is a local IP address, it can be written as localhost or 127.0.0.1. Port-indicates the listening port of the database. You need to check the configuration during installation. The default value is 1433. Databasename-database name. For example, if you want to access the local database allandb, port 1433, write the URL as follows: jdbc: microsoft: sqlserver: @ localhost: 1433; DatabaseName = allandb

3. MySQL database(1) driver package name: mysql-connector-java-3.1.11-bin.jar (2) Name of the driver class: com. mysql. jdbc. driver (3) jdbc url: jdbc: mysql: // dbip: port/databasename Description: The Driver package name may change to the Black font part of the jdbc url, which must be kept intact, the URL format recognized by the driver. The red font must be filled in according to the database installation. The meaning of each part is as follows: dbip-the IP address of the database server. If it is a local IP address, it can be written as localhost or 127.0.0.1. Port-indicates the listening port of the database. You need to check the configuration during installation. The default value is 3306. Databasename-database name. For example, if you want to access the local database allandb, port 1433, the URL is written as follows: jdbc: mysql: // localhost: 3306/allandb

4. Access Database(1) driver package name: this driver is included in JavaSE and does not require additional installation. (2) name of the driver class: sun. jdbc. odbc. jdbcOdbcDriver (3) jdbc url: jdbc: odbc: datasourcename Description: The driver can only work in Windows, first, you need to create a local data source (ODBC) that can Access the Access database in the operating system. If the name is allandb, the URL is written as follows: jdbc: odbc: allandb