JDBC based MySQL

< data storage technology in a >java

In Java, database access technology can be divided into the following categories

1.JDBC direct access to the database

2.JDO Technology

3. Third-party O/R tools such as Hibernate, Ibatis, etc.

Note: JDBC is the cornerstone of Java's access to databases JDO, hibernate, etc. just better encapsulate JDBC.

< two >JDBC Foundation

1.JDBC (Java database Connectivity) is a common interface that is independent of a specific database management system, common SQL database access and operations a set of APIs defines the standard Java class library used to access the database using this class library can be used in a standard way, Easy access to database resources

2.JDBC provides a unified way to access different databases to shield developers from some of the details.

The goal of 3.JDBC is to enable Java programmers to use JDBC to connect to any database system that provides a JDBC driver so that the programmer does not have to be overly knowledgeable about the characteristics of a particular database system, greatly simplifying and speeding up the development process.

4.JDBC Architecture

(1) The JDBC Interface API consists of two levels

* Application-oriented Apijava API abstraction interface for application developers to use the connection database to execute SQL statements to obtain results.

* * Database-oriented Apijava Driver API for developers to develop database drivers.

5.JDBC Driver Classification

(1). JDBC Driver The class library of the JDBC implementation class made by each database vendor according to the specification of JDBC

(2). There are four types of JDBC drivers

*jdbc-odbc Bridge.

* * Part of the local API part of the Java driver.

JDBC Network pure Java driver.

Pure Java driver for local protocol.

Note (3) (4) Both types are pure Java drivers So for Java developers they have advantages in terms of performance, portability, functionality, and so on.

6.ODBC

(1) Early access to the database is a proprietary API that is called by the database vendor. In order to provide unified access under the Windows platform Microsoft introduced the ODBC (Open Database Connectivity) and provided ODBC API consumer in the program only need to call the ODBC API by the ODBC driver to convert the call to Request for a call to a specific database

(2) An ODBC-based application's operations on the database do not depend on any DBMS (Database manager System) that does not work directly with the DBMS. All of the databases operations are done by the corresponding DBMS's ODBC driver. This means that either FoxPro, Access, MySQL, or Oracle databases are accessible using the ODBC API. This shows that the biggest advantage of ODBC is the ability to handle all databases in a unified manner.

7.JDBC-ODBC Bridge

The Jdbc-odbc bridge itself is also a driver using this driver can use JDBC-API to access the database via ODBC. This mechanism actually translates the standard JDBC call into the corresponding ODBC call and accesses the database through ODBC

Access to the database using the Jdbc-odbc bridge is inefficient because it requires multiple calls

JDBC-ODBC Bridge Implementation Class (Sun.jdbc.odbc.JdbcOdbcDriver) is provided in the JDK

8. Part of the local API part Java driver

(1) This type of JDBC driver is written using Java to invoke the local API provided by the database vendor

(2) Accessing the database through this type of JDBC driver reduces the call link of ODBC to improve the efficiency of database access

(3) In this way the local JDBC driver and the vendor-specific local API need to be installed on the customer's machine

9.JDBC Network Pure Java Driver

(1) This driver uses the middleware application server to access the database. The application server acts as a gateway client to multiple databases through which it can connect to different database servers.

(2) The application server usually has its own network protocol the Java User program sends a JDBC call through the JDBC driver to the Application Server application server to use local program-driven access to the database to complete the request.

10.JDBC API

The JDBC API is a series of interfaces that enable an application to perform database joins to execute SQL statements and to get the results returned.

11.Driver interface

(1) The Java.sql.Driver interface is the interface that all JDBC drivers need to implement. This interface is provided for different implementations of different database vendors used by the database vendor

(2) In the program does not need to directly access the implementation of the Driver interface class, but by the Driver Manager class (Java.sql.DriverManager) to invoke these Driver implementation

12. Loading and registering the JDBC driver

(1) Loading the JDBC driver calls the class class's Static method Forname () to pass the JDBC driver's name to be loaded

(2) DriverManager class is the Driver manager class responsible for managing drivers

(3) It is often not necessary to explicitly call the DriverManager class's Registerdriver () method to register an instance of the driver class because the driver class for the Driver interface contains a static block of code that is called in this static block of code Drivermanager.registerdriver () method to register an instance of itself

13. Establish a connection

(1) You can call the Getconnection () method of the DriverManager class to establish a connection to the database

(2) The JDBC URL is used to identify a registered driver manager to establish a connection to the database by selecting the correct driver from this URL.

(3) The criteria for a JDBC URL are separated by colons by a three-part component.

(4) jdbc:< Sub-Protocol >:< sub-name >

Protocol in the protocol JDBC URL is always jdbc

The Sub-Protocol sub-Protocol is used to identify a database driver

A child name is a way to identify a database. Sub-names can vary according to different sub-protocols the purpose of the sub-name is to locate the database to provide sufficient information

14. JDBC URLs for several common databases

(1) The following form is used for Oracle database connection

Jdbc:oracle:thin: @localhost: 1521:sid

(2) The following form is used for SQL Server database connection

jdbc:microsoft:sqlserver//localhost:1433; Databasename=sid

(3) for MYSQL database connection in the following form

Jdbc:mysql://localhost:3306/sid

15. Accessing the database

(1) database connections are used to send commands to the database server and SQL statements require access to the database to execute SQL statements after the connection is established

(2) There are 3 interfaces in the java.sql package that define different ways to call the database, respectively.

Statement

Prepatedstatement

CallableStatement

16.Statement

(1) Create the object by calling the Createstatement method of the Connection object

(2) The object is used to execute a static SQL statement and return the execution result

(3) The following methods are defined in the Statement interface for executing SQL statements

ResultSet excutequery (String sql)

int excuteupdate (String sql)

17.ResultSet

Create the object by calling the Excutequery () method of the Statement object

The ResultSet object encapsulates the result set that performs the database operation in the form of a logical table ResultSet interface is implemented by the database vendor

The ResultSet object maintains a pointer to the current row of data when the cursor is initially moved to the next line by the next () method of the ResultSet object before the first row

Common methods of ResultSet interface

Boolean Next ()

GetString ()

...

18.JDBC API Summary 1

The Java.sql.DriverManager is used to mount the driver to obtain a database connection.

Java.sql.Connection completion of a join to a specified database

Java.sql.Statement as a container for SQL execution declarations in a given connection he contains two important sub-types.

Java.sql.PreparedSatement SQL declaration used to perform precompiled

Java.sql.CallableStatement used to perform a call to a stored procedure in the database

Java.sql.ResultSet ways to get results for a given claim

19.SQL Injection Attack

(1) SQL injection is the practice of using the system's SQL engine to complete malicious behavior by injecting illegal SQL statement segments or commands into user input data without adequate checking of data entered by the user.

(2) for Java to prevent SQL injection as long as you replace Statement with PreparedStatement.

20.PreparedStatement

(1) The PreparedStatement object can be obtained by invoking the PreparedStatement () method of the Connection object

(2) The PreparedStatement interface is a sub-interface of Statement it represents a pre-compiled SQL statement

(3) The PreparedStatement object represents a parameter in the SQL statement with a question mark (?). To represent the Setxxx () method that invokes the PreparedStatement object to set these parameters. The Setxxx () method has two parameters the first parameter is the index of the parameter in the SQL statement to set (starting from 1) and the second is the value of the parameter in the SQL statement that is set

21.PreparedStatement vs Statement

The readability and maintainability of the code.

(1) PreparedStatement can maximize performance

DBServer provides performance optimizations for pre-compiled statements. Because precompiled statements are likely to be called repeatedly, the statements are cached after the compiled code of the DBServer compiler, so that the next call will not need to compile as long as the same precompiled statement is executed as long as the parameters are passed directly into the compiled statement execution code.

(2) In the statement statement, even if the same operation but because the data content is not the same, so the entire statement itself does not match, there is no meaning of the cached statement. The fact is that no database executes code caches after the normal statement is compiled. This will be compiled once for each execution of the incoming statement.

(3) (Syntax check semantic check translated into binary command cache)

PreparedStatement can prevent SQL injection

22. Database connection Pooling Connection pool

(1) In order to solve the problem of database connection in traditional development, database connection pool technology can be adopted.

(2) The basic idea of database connection pool is to establish a "buffer pool" for database connection. Pre-placing a certain number of connections in the buffer pool when you need to establish a database connection, simply remove one from the buffer pool and put it back.

(3) Database connection pooling is responsible for allocating, managing, and releasing database connections it allows applications to reuse an existing database connection instead of re-establishing one.

(4) Database connection pooling creates a certain number of database connections to the connection pool at initialization the number of these database connections is set by the minimum number of database connections. Regardless of whether these database connections are used, the connection pool will always guarantee that you have at least so many connections. The maximum number of database connections for a connection pool limits the maximum number of connections that this pool can occupy when the application requests more connections to the connection pool than the maximum number of connections, the requests are added to the wait queue.

23. Advantages of database connection pooling technology

(1) Resource reuse

The large amount of performance overhead caused by frequent creation of free connections is avoided because of the reuse of database connections. On the other hand, the stability of the operating environment of the system is increased on the basis of reducing the system consumption.

(2) Faster system reaction speed

Database connection pooling has often created several database connections to be placed in the connection pool for backup during initialization. The initialization of the connection is now complete. Reduces system response time by directly leveraging existing available connections for business request processing to avoid time overhead for database connection initialization and release processes

(3) New means of resource allocation

For systems with multiple applications sharing the same database, you can implement a limit on the maximum number of available database connections in an application layer through the configuration of the database connection pool avoid an application exclusive of all database resources

(4) Unified connection management to avoid database connection leakage

In a more complete database connection pool implementation, it is possible to forcibly reclaim occupied connections based on pre-occupied timeout settings, thus avoiding resource leaks that may occur in regular database connection operations

24. Two types of open source database connection pools

(1) The JDBC database connection pool uses Javax.sql.DataSource to represent that DataSource is just an interface that interface is typically provided by the server (Weblogic, WebSphere, Tomcat) and some open source organizations provide implementations

*DBCP Database Connection Pool

**C3P0 Database Connection Pool

(2) DataSource is often referred to as a data source, it contains connection pooling and connection pooling management two parts of the habit also often referred to DataSource as connection pooling

25.DBCP Data source

(1) DBCP is an open source connection pool under the Apache Software Foundation that enables the connection pool to rely on another open source system Common-pool under the organization. To use this connection pool implementation should add the following two jar files to the system

Implementation of the Commons-dbcp.jar connection pool

Commons-pool.jar dependency libraries implemented by connection pooling

(2) The connection pool for Tomcat is implemented with this connection pool. The database connection pool can be used either in combination with the application server or independently by the application.

Example of 26.DBCP data source usage

(1) Data source and database connection different data sources do not need to create multiple it is the factory that produces the database connection so the entire application needs only one data source.

(2) When the database access is finished, the program closes the database connection as before Conn.close (); But the code above does not close the physical connection to the database it simply returns the database connection to the database connection pool.

27.apache-dbutils Introduction

Commons-dbutils is an open source JDBC Tool class library provided by the Apache organization. It is a simple encapsulation of JDBC with minimal learning costs and the effort to use dbutils to greatly simplify JDBC coding without compromising program performance.

API Introduction

Org.apache.commons.dbutils.QueryRunner

Org.apache.commons.dbutils.ResultSetHandler

Tool class

Org.apache.commons.dbutils.DbUtils,.

28.DbUtils class

Dbutils provides all the methods in a tool class such as closing a connection, loading a JDBC driver, and so on, all of which are static. The main methods are as follows

public static void Close (...) throws Java.sql.SQLException Dbutils class provides three overloads for the shutdown method. These methods check that the supplied arguments are null if they are not, and they close connection, statement, and ResultSet.

public static void Closequietly (...): This kind of method can not only avoid closing in connection, statement and ResultSet, but also hide some sqleeception thrown in the program.

public static void commitandclosequietly (Connection conn) is used to submit the connection and then closes the connection and does not throw a SQL exception when the connection is closed.

public static Boolean loaddriver (Java.lang.String driverclassname) This side loads and registers the JDBC driver if successful returns true. Using this method you do not need to catch this exception classnotfoundexception.

29.QueryRunner class

(1) This class simplifies the SQL query which is combined with Resultsethandler to accomplish most of the database operations can greatly reduce the amount of coding.

(2) The Queryrunner class provides two construction methods

* Default method of construction

* * A javax.sql.DataSource is required to construct a parameter.

The main methods of the 30.QueryRunner class

(1) Public Object query (Connection conn, String sql, object[] params, Resultsethandler rsh) throws SqlException perform a query operation in this Each element value in the object array in the query is used as the replacement parameter for the query statement. This method handles the creation and shutdown of PreparedStatement and ResultSet itself.

(2) Public Object query (String sql, object[] params, Resultsethandler rsh) throws SQLException: Almost as unique as the first method is that it does not provide a database connection to the method and it is re-obtained Connection from the data source (DataSource) provided to the constructor method or the Setdatasource method used.

(3) Public Object query (Connection conn, String sql, Resultsethandler rsh) throws SQLException: Executes a query operation that does not require a replacement parameter.

(4) public int update (Connection conn, String sql, object[] params) throws SQLException: Used to perform an update insert, UPDATE, or delete operation.

(5) public int update (Connection conn, String sql) throws SqlException is used to perform an update operation that does not require displacement parameters.

31.ResultSetHandler interface

(1) This interface is used to process java.sql.ResultSet to convert data to another form as required.

(2) The Resultsethandler interface provides a separate method for object handle (Java.sql.ResultSet. rs).

Implementation classes for 32.ResultSetHandler interfaces

(1) Arrayhandler turns the first row of data in the result set into an array of objects.

(2) Arraylisthandler turns each row of data in the result set into an array and stores it in the list.

(3) Beanhandler encapsulates the first row of data in the result set into a corresponding JavaBean instance.

(4) Beanlisthandler encapsulates each row of data in the result set into a list of corresponding JavaBean instances.

This article from "I Am Princes" blog, declined reprint!

JDBC based MySQL