JDBC Data Batch Processing

JDBC (Java Data Base Connectivity): A set of Java operational Specifications (interfaces) that sun has defined for the operation of the database, becomes JDBC;

Two packages in the API that comprise JDBC: java.sql | Javax.sql

JDBC Process (for example, MySQL, Oracle database)

MySQL database default port: 3306
Oracle database default port: 1521

Services.msc: View all services in the system

The most important configuration files in the MySQL database
C:\Program Files (x86) \mysql\mysql Server 5.0\my.ini


port=3306 Port number
Default-character-set=utf8 MySQL Database encoding
C:/Program Files (x86)/mysql/mysql Server 5.0/mysql installation path
C:/Program Files (x86)/mysql/mysql Server 5.0/data/database File storage path
DEFAULT-STORAGE-ENGINE=INNODB MySQL Database engine

1, join the driver package, register driver (except jar package, also need to import the corresponding JDBC database implementation (that is, database-driven))

Load Database Driver
A, Class.forName ("Com.mysql.jdbc.Driver"),//mysql two ways, in this way, the program only needs a string, does not need the import-driven API, so that the program does not rely on the specific driver, Make the program more flexible.

B, DriverManager. Registerdriver (Driver Driver)

Oracle

Driver Driver = new Oracle.jdbc.driver.OracleDriver ();

Properties Info = new properties ();
Oracle Specifies the data user name and password
Info.setproperty ("User", "Scott");
Info.setproperty ("Password", "Tiger")

2. Get the connection

MySQL get connection//protocol: Sub-protocol host Port database
Connection con = drivermanager.getconnection ("Jdbc:mysql://127.0.0.1:3306/test", "root", "root");

Oracle Get Connections
Connection con = driver.connect ("JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL", info);

3. Get statement

Preparing SQL statements
String sql = "INSERT into Tb_user (Loginname,username,password) VALUES (?,?,?)";
Get container
PreparedStatement pstm = null;

4. Send SQL

5. Processing the returned result set resultset

6. Close Resources

try {
pstm = con.preparestatement (sql);
for (int i=0;i<list.size (); i++) {
Pstm.setstring (1, List.get (i). Getloginname ());
Pstm.setstring (2, List.get (i). GetUserName ());
Pstm.setstring (3, List.get (i). GetPassword ());
To add an SQL statement to a batch command
Pstm.addbatch ();
}

Perform batch processing
int[] flag = Pstm.executebatch ();
System.out.println ("Number of rows affected:" +flag.length);
Empty Batch Command
Pstm.clearbatch ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}finally {
To close a database connection
Connectionfactory.close (con, pstm, null);
}

Statement and Preparestatement

Preparestatement is the statement sub-class support placeholder?

Better performance and efficiency when BULK insert

Prevent SQL injection and make it more secure

Code is different:

Statement:

To add an SQL statement to a batch command
A, Statement.addbatch (SQL);
Perform batch processing
B, Statement.executebatch ();

Empty Batch Command
C, Statement.clearbatch ();


PreparedStatement:

To add an SQL statement to a batch
A, pstm.addbacth (); Note that you do not need to pass in the SQL statement here because the SQL is already in the Get container (PreparedStatement)
Statement incoming
Perform batch processing
B, Pstm.executebatch ();

Empty Batch Command
C, Pstm.clearbatch ();

Database connection pooling: Optimizing the performance of your project
Many Web servers (Weblogic, WebSphere, Tomcat) now provide the implementation of Datasoruce, which is the implementation of the connection pool.
Usually we put the implementation of DataSource, according to its English meaning is called the data source, the data source contains the database connection pool implementation.
There are also open source organizations that provide a separate implementation of the data source:
DBCP Database Connection pool (Tomcat)
C3P0 Database connection pool (Hibernate)

Get Data source
Basicdatasource Basicdatasource = new Basicdatasource ();
Setting the connection Address
Basicdatasource.seturl ("Jdbc:mysql://127.0.0.1:3306/bookapp");
Maximum number of activities
Basicdatasource.setmaxactive (3);
Set maximum number of saves
Basicdatasource.setmaxidle (3);
Time-out wait unit milliseconds
Basicdatasource.setmaxwait (5000);
Set User name
Basicdatasource.setusername ("root");
Set Password
Basicdatasource.setpassword ("root");
Get connections
Connection con = basicdatasource.getconnection ();


In actual development we can configure the data source with a Web server
Locate the context.xml file in the server

<resource name= "Jdbc/shop_app" auth= "Container" type= "Javax.sql.DataSource"
Maxtotal= "maxidle=" maxwaitmillis= "10000"
Username= "root" password= "root" driverclassname= "Com.mysql.jdbc.Driver"
Url= "Jdbc:mysql://localhost:3306/bank"/>

Code initialization Data Source
static{
try {
InitialContext context = new InitialContext ();
Retrieve the DataSource resource name from the Jndi container using the lookup method of the context to retrieve note: The prefix of the Tomcat container should be added to the corresponding resource prefix of the Web container at the time of retrieval: java:/comp/env
DataSource = (DataSource) context.lookup ("Java:/comp/env/jdbc/bookapp");
} catch (Namingexception e) {
TODO auto-generated Catch block
E.printstacktrace ();
}

}

Meta Data
try {
con = connectionfactory.getconnection ();
String sql = "SELECT * from Tb_user";
Get container
pstm = con.preparestatement (sql);
rs = Pstm.executequery ();
Get metadata (included in the data, number of columns in the table, column type, column name)
ResultSetMetaData metaData = Rs.getmetadata ();
Get the number of columns
int column = Metadata.getcolumncount ();
System.out.println ("Get the number of columns:" +column);
Traversing result Sets
Rs.next ();
for (int i=1;i<=column;i++) {
Get Column Name
String name = Metadata.getcolumnname (i);
Get data type
String type = Metadata.getcolumntypename (i);
Get data based on column name
Object object = Rs.getobject (name);
SYSTEM.OUT.PRINTLN ("Column name:" +name+ "class type:" +type+ "gets the data:" +object);
}
}

JDBC Data Batch Processing