Java Study Notes-JDBC and Study Notes-jdbc

Java Study Notes-JDBC and Study Notes-jdbc
Introduction to JDBC

1. to simplify and unify database operations, SUN defines a set of Java database operation specifications, called JDBC.
2. database-driven
3. JDBC is called JAVA DataBase commecti.pdf (java DataBase connection)
4. Make up two JDBC packages (1 java. SQL, 2 javax. SQL ).
5. DriverManager-> Connection-> Statement (PreparedStatement, CallableStatement)-> ResultSet
6. JavaEE software is divided into four layers:
6.1 Client layer: DHTML (HTML, CSS, JS), Java Application
Starting from the second layer, on the server side
6.2. web layer (presentation layer): Servlet/JSP ---- receives client data, displays the data processing structure to the client, and schedules the business layer --- Struts
6.3 business layer (Middle Layer): EJB ----- process client submitted data according to system business flow --- Spring
6.4 data layer (persistence layer): JDBC interface ------- complete data persistence and obtain data from the database --- Hibernate/IBatis
Enterprise web layer, business layer, and data layer are collectively referred to as three-layer structure SSH: Struts + Spring + Hibernate

How to build a JDBC Program

1. Build the environment
1.1 create a database and table in (Mysql, SqlServer, Oracle...) and other databases
1.2 create a Java project and import the database Driver (maven) to different database programs)
2. How does JDBC perform database operations?
2.1 Step 1: load the driver in the program
2.1.1 DriverManager. registerDriver (new com. mysql. jdbc. Driver ());
2.1.2 Class. forName ("com. mysql. jdbc. Driver"); // recommended for development
2.2 Step 2: Obtain the database connection
String url = ""; // database access path, which uniquely identifies the database location
String user = ""; // user Name
String pass = ""; // Password
Connection conn = DriverManager. getConnection (url, user, pass );
2.3 Step 3: Create a Statement object used to send SQL statements to the database and send SQL statements
Statement stmt = conn. cteateStatement ();
Step 4: perform database operations through SQL
String SQL = "";
ResultSet rs1_stmt.exe cuteQuery (SQL); // obtain the data result set.
2.5 Step 5: release resources
Generally, the order of closing resources is: first open, then closed, and then closed first
ResultSet-> Statement-> Connection
3. The code for reading the configuration file (*. properties) is as follows:
ResourceBundle bundle = ResourceBundle. getBundle ("*");
String driver = bundle. getString ("driver ");

Program description-DriverManager

1. The DriverManager In the Jdbc program is used to load the driver and create a connection to the database. The common method of this API is as follows:
DriverManager. registerDriver (new Driver ())
DriverManager. getConnection (url, user, password ),
Note: The registerDriver method is not recommended in actual development. There are two reasons:
1.1 check the Driver source code. If this method is used, the Driver will be registered twice, that is, there will be two Driver objects in the memory.
1.2 The program depends on the mysql api and is out of the mysql jar package. The program cannot be compiled, and it will be very troublesome to switch the program to the underlying database in the future.
2. Recommended Driver Loading Method: Class. forName ("com. mysql. jdbc. Driver ");
Using this method will not cause the drive object to repeat in the memory, and using this method, the program only needs a string and does not need to depend on a specific driver, so that the program is more flexible.
3. Similarly, we do not recommend that you use a specific driver type to point to the connection object returned by the getConnection method during development.

Detailed Program description-Database URL

1. the URL is used to identify the location of the database. The programmer uses the URL address to tell the JDBC program which database to connect to. The URL is written as follows:
Jdbc: mysql: /// localhost: 3306/test? Parameter Name: Parameter Value
Protocol sub-Protocol HOST: Port Database Parameters
2. Common Database URL:
2.1 Oracle: jdbc: oracle: thin: @ localhost: 1521: sid
2.2 SqlServer: jdbc: microsoft: sqlserver: // localhost: 1433; DatabaseName = sid
2.3 MySql: jdbc: mysql: // localhost: 3306/sid
The url of Mysql is abbreviated as jdbc: mysql: // sid.
2.4 common attributes: useUnicode = true & characterEncoding = UTF-8

Program description-Connection

1. the Connection in the Jdbc program is used to represent the connection of the database. Collection is the most important object in database programming. All the interactions between the client and the database are completed through the Connection object. The common method of this object is as follows:
CreateStatement (): Creates a statement object that sends SQL statements to the database.
PrepareStatement (SQL): Creates a PrepareSatement object that sends pre-compiled SQL statements to the database.
Stored Procedure
PrepareCall (SQL): Creates a callableStatement object that executes the stored procedure.
Transactions
SetAutoCommit (boolean autoCommit): sets whether a transaction is automatically committed.
Commit (): Submit the transaction on the link.
Rollback (): roll back the transaction at this link

Program description-Statement

1. The Statement object in the Jdbc program is used to send SQL statements to the database. Common Methods of the Statement object are as follows:
1.1 Execute SQL statements
ExecuteQuery (String SQL): used to send query statements to data and return the ResultSet result set.
ExecuteUpdate (String SQL): used to send insert, update, or delete statements to the database. int and affected records are returned.
Execute (String SQL): used to send any SQL statement to the database, return bool
1.2 batch processing is supported and multiple SQL statements are executed at a time
AddBatch (String SQL): puts Multiple SQL statements in one batch.
ExecuteBatch (): sends a batch of SQL statements to the database for execution.

Program description-PreparedStatement

1. PreperedStatement is a subclass of Statement. Its instance object can be obtained by calling the Connection. preparedStatement () method, relative to the Statement object:
2. PreperedStatement can avoid SQL Injection problems.
3. Statement: the database frequently compiles SQL statements, which may cause database Buffer Overflow. PreparedStatement can pre-compile the SQL statement to improve the execution efficiency of the database. In addition, PreperedStatement allows replacement of parameters in the SQL statement in the form of placeholders to simplify the preparation of SQL statements.
For example:
String SQL = "select * from users where name =? And pwd =? ";
Try {
PreparedStatement pstmt = conn. prepareStatement (SQL );
Pstmt. setString (1, userName );
Pstmt. setString (1, password );
ResultSet rs1_pstmt.exe cuteQuery ();
}
Note:
(1). When obtaining the PrepareStatement object, pass in SQL for pre-Compilation
(2). Pass the value. The index starts from 1.
(3) When pstmt executes the SQL statement, do not pass in the pre-compiled SQL statement; otherwise, an error will occur.

Detailed Program description-ResultSet

1. The ResultSet In the Jdbc program is used to represent the execution result of the SQL statement. When the Resultset encapsulates the execution result, it adopts a table-like approach. The ResultSet object maintains a cursor pointing to the table data row. Initially, the cursor is called before the first row. the next () method can point the cursor to a specific data row, and then call the method to obtain the data of this row.
2. Since ResultSet is used to encapsulate execution results, most of the methods provided by this object are used to get data:
2.1 obtain data of any type
GetObject (int index)
GetObject (string columnName)
2.2 obtain data of the specified type, for example:
GetString (int index)
GetString (String columnName)
3. if rs returns a row of data, in development, if (rs. next () is often used to replace while (rs. next ());
4. ResultSet also provides a method to scroll the result set:
4.1 next (): Move to the next row
4.2 previous (): Move to the previous row
4.3 absolute (int row): Move to the specified row
4.4 beforeFirst (): Move the front of the resultSet.
4.5 afterLast (): Move to the end of the resultSet.

Program description-release resources

1. After the Jdbc program is run, remember to release the objects that interact with the database created during the program running. These objects are generally ResultSet, Statement, and Connection objects.
2. Especially the Connection object, which is a very rare resource, must be released immediately after use. If the Connection cannot be closed in a timely and correct manner, it can easily lead to system downtime. The usage principle of Connection is to create a Connection as late as possible and release it as early as possible.
3. To ensure that the resource release code can run, the resource release code must also be placed in the finally statement.

JDBC operation database Demo

Development Environment MyEclipse
MySql database
: