JDBC Learning Notes

This article for Bo Master original, allow reprint, but please declare the original address: http://www.coselding.cn/blog/5/5-125.html

1. Load Driver: Load implementation class that implements the database interface
String Driver = "Com.mysql.jdbc.Driver";
Class.forName (driver);
2. Instantiate the database Connection object: (requires database account password)
Connection = Drivermanager.getconnection (URL, username, password);
3. Get the statement object that manipulates the database:
Statement Statement = Connection.createstatement ();
4. Using the statement object for database manipulation
5. Close the Resultset,statement,connection object in sequence and disconnect from the database
6. Interface-Oriented programming
7. Encapsulation into jdbcutils to handle the corresponding basic operation of JDBC;
8.PrepareStatement can pre-compile SQL statements and prevent SQL injection vulnerabilities.
9. Always remember to close the database connection after use, the resource is extremely valuable.
10. Database paging: The main completion of the query total number of access, and then according to the page number to query in the database to query the corresponding part of the content back to the browser, limit?,? paged query, specific reference to common related components and CustomerSystem project. It mainly depends on the current number of pages in the browser maintenance work.
11. Large text operation: (large text to use flow operation, to prevent excessive memory consumption)
Set the value:
Preparedstatement.setcharacterstream (index, reader, length);
Note that length must be set and set to type int
Get Value:
Reader = ResultSet. Getcharacterstream (i);
Reader = Resultset.getclob (i). Getcharacterstream ();
string s = resultset.getstring (i);
12. Big Data operation: (Big data to use flow operation, to prevent excessive memory consumption)
Set Value: PreparedStatement. Setbinarystream (i, inputstream, length);
Get Value:
InputStream in = Resultset.getbinarystream (i);
InputStream in = Resultset.getblob (i). Getbinarystream ();
13. Batch processing:
Statement: Advantages: Different SQL statements can be called batch processing;
Cons: Same SQL statement to write multiple times, no precompilation
PreparedStatement:
Pros: Execute the same SQL statement but with different arguments you only need to write the SQL statement once
Cons: can only be applied in batches with the same SQL statement but with different parameters
14. Get the auto-generated primary key:
Set the statement execution to get the primary key:
Connection.preparestatement (Sql,statement.return_generated_keys);
Get primary Key result set: rs = Statement.getgeneratedkeys ();
15. Call the stored procedure:
Get stored Procedure statement:
Connection.preparecall ("{Call DEMOSP (?,?)}");
Input parameters:
Statement.setstring (1, "xxxx");
Output parameters:
Statement.registeroutparameter (2, Types.varchar);//Data type Types
Perform:
Statement.execute ();
Get the result of the execution:
Statement.getstring (2);
16. Transaction operations:
Open transaction: Connection.setautocommit (FALSE);
Commit TRANSACTION: Connection.commit ();
ROLLBACK TRANSACTION: Connection.rollback (); Automatically rolls back to the beginning of the transaction when not committed, the code user rolls back manually
Rollback point: SavePoint
Set rollback point: SP = Connection.setsavepoint ();
Rollback to Rollback point: connection.rollback (SP);
The SQL to be remembered before the rollback point is submitted: Connection.commit ();
17. Basic knowledge of the transaction:
Basic characteristics: atomicity (A)
Consistency (C): The overall state before and after a transaction is consistent
Isolation (I): Concurrent Thread safety issues
Persistence (D): After commit, guarantee must be saved.
Problem: Isolation!!! --Dirty reading, non-repeatable reading, virtual reading
Dirty read: A transaction modified but not committed, B transaction read, a transaction rollback, then B read data is dirty data;
Non-REPEATABLE READ: A transaction query, b transaction modification, a transaction second query results and previous inconsistencies;
Virtual read: A transaction query, b transaction Add a data, then a in the query results set more than one;
Isolation solution:

The higher the isolation level, the lower the performance, and the choice depending on the situation.
Note: The isolation level set here is to set the isolation level of the current transaction, which is not the same as the isolation level of the other transaction, and there will be a corresponding isolation problem for that transaction that should be at the isolation level.
Note: The query program can be set to read committed;
Statistical program to set serializable
18.JDBC Set Isolation Level:
Connection.settransactionisolation (connection.transaction_serializable);
Set the isolation level before opening a transaction;
19. Database Connection Pool:
The creation of database connections is expensive, so the implementation of creating multiple database connections to form a collection, when required to apply, and then return to the collection, and the connection pool to control the connection state, the maximum number of connections, the number of idle connections and other parameters, concurrency control.
A, DBCP connection pool: Need pool, DBCP, colletions Three Commons package, version to correspond;
B, dbcp use steps:
Load configuration file
InputStream is = JDBCUtil_DBCP.class.getClassLoader ()
. getResourceAsStream ("dbcpconfig.properties");
Properties.load (IS);
DataSource Factory
Factory = new Basicdatasourcefactory ();
Factory generates datasource objects from configuration files
ds = Factory.createdatasource (properties);
C. Request a database connection from a connection pool: (Create a connection pool in jdbcutils)
Ds.getconnection ();
D, C3P0 Connection pool:
Importing the C3P0 jar package
Join the C3p0-config.xml configuration file
To create a connection pool:
CPDs = new Combopooleddatasource ("Myconfig");
Get Connection object: Cpds.getconnection ();
E, the Tomcat server comes with a connection pool:
Tomcat creates a database connection pool into the Jndi container, and then gets the database connection pool through the Jndi container. (actually DBCP connection pool)
Attached: Jndi Detailed Tutorial:
Http://www.cnblogs.com/xing901022/p/4568803.html
A. Configure the Tomcat context (after which the Tomcat server creates the connection pool and puts it into the Jndi container)
Attached: can configure the location of Context.xml, see: http://localhost:8080/docs/config/context.html
Configuration details:
<Context>
<resource name= "Jdbc/employeedb"
Auth= "Container"
Type= "Javax.sql.DataSource"
Username= "Root"
Password= ""
Driverclassname= "Com.mysql.jdbc.Driver"
Url= "Jdbc:mysql://localhost:3306/customersystem"
Maxactive= "8"
maxidle= "4"/>
</Context>
B. Get the connection pool object from the Jndi container:
Initialize Jndi:
Context initctx = new InitialContext ();
Get the Jndi container:
Context Envctx = (context) initctx.lookup ("java:comp/env");
To retrieve the connection pool from the container:
DataSource ds = (DataSource) envctx.lookup ("Jdbc/employeedb");
Get the connection:
conn = Ds.getconnection ();
Note: This approach is to put the database driver into the Tomcat Lib, to prevent the database driver cannot be found.
20.JDBC Frame Related:
Get database metadata: (database basic information)
DatabaseMetaData metaData = Connection.getmetadata ();
Get parameter metadata for statement: (PreparedStatement SQL statement parameter details)
Parametermetadata parametermetadata = St.getparametermetadata ();
ResultSet result set metadata: (Get more information about the result set)
ResultSetMetaData ResultSetMetaData = Rs.getmetadata ();
Frame design ideas: Extract the same part of the code, and then use the template design mode, interface callback control master Logic, sub-class specific implementation of the specific process of each module. Ensure the unity of the main process and the diversity of the modules.
21.Oracle Big Data storage processing: (Dbutils not recommended)


Examples:
Write:

Read:

This article for Bo Master original, allow reprint, but please declare the original address: http://www.coselding.cn/blog/5/5-125.html

JDBC Learning Notes