JDBC Connection Mysql Instance detailed _mysql

JDBC Connection MySQL

JDBC Connection MySQL

Loading and registering JDBC drivers

Class.forName ("Com.mysql.jdbc.Driver");
Class.forName ("Com.mysql.jdbc.Driver"). newinstance ();

The JDBC URL defines the connection between the driver and the data source

Standard syntax:

<protocol (Primary communication Protocol) >:<subprotocol (Secondary communication protocol, driver name) >:<data Source identifier (data source) >

MySQL's JDBC URL format:

jdbc:mysql//[hostname][:p ort]/[dbname][?param1=value1][&param2=value2] ....

Example: Jdbc:mysql://localhost:3306/sample_db?user=root&password=your_password

Common parameters:
User username
Password Password
AutoReConnect online failed, back online (true/false)
Maxreconnect attempts to get back online
Initialtimeout try to go back online interval
MaxRows returns the maximum number of rows
Useunicode whether to use Unicode font encoding (TRUE/FALSE)
characterencoding what encoding (gb2312/utf-8/...) ）
Relaxautocommit is automatically submitted (True/false)
Capitalizetypenames the name of the data definition in uppercase

To establish a Connection object

String url= "Jdbc:mysql://localhost:3306/sample_db?user=root&password=your_password";
Connection con = drivermanager.getconnection (URL);

Create SQL Statement object (Statement object)

Statement stmt = Con.createstatement ();

Execute SQL statement

ExecuteQuery ()
String query = "SELECT * from Test";
ResultSet rs=stmt.executequery (query);
Result set resultset while
(Rs.next ())
{rs.getstring (1); Rs.getint (2);}
Executeupdate ()
String upd= "INSERT INTO Test (id,name) VALUES (1001,xuzhaori)";
int con=stmt.executeupdate (UPD);
Execute ()

Example:

Try
{
 }
catch (SQLException Sqle)
{
}
finally
{
}

Java type and SQL type technical manual P421

PreparedStatement (pre-prepared statement)

PreparedStatement stmt = conn.preparestatement ("INSERT into Test (id,name) VALUES (?,?)");
Stmt.setint (1,id);
Stmt.setstring (2,name);

Note: Once you set the parameter value of the statement, you can execute the change statement multiple times until the Clearparameters () method is called to clear it.

CallableStatement (pre-stored procedure) Technical manual P430

JDBC2.0 use

The cursor moves freely up and down in the ResultSet object
Statement stmt = con.createstatement (resultset.type_scroll_sensitive, resultset.concur_read_only);
ResultSet rs=stmt.executequery ("SELECT * from Test");

Public Statement createstatement (int resultsettype,int resultsetconcuttency) throws SQLException

ResultsetType

Type_forward_only can only use the next () method.
Type_scroll_sensitive can be moved up and down to achieve the changed value.
Type_scroll_insensitive can move up and down.

Resultsetconcuttency

Concur_read_only Read Only
Concur_updatable ResultSet objects can perform additions, modifications, and removal of the database

Performing update data directly using the ResultSet object

New data

Statement stmt=con.createstatement (resultset.type_scroll_sensitive,resultset.concur_pudatable);
ResultSet uprs=stmt.executequery ("SELECT * from Test");
Uprs.movetoinsertrow ();
Uprs.updateint (1,1001);
Uprs.updatestring (2, "Xu Chunji");
Uprs.insertrow;

Update data

Statement stmt=con.createstatement (resultset.type_scroll_sensitive,resultset.concur_pudatable);
ResultSet uprs=stmt.executequery ("SELECT * from Test");
Uprs.last ();
Uprs.updatestring ("name", "Xuzhaori");
Uprs.updaterow;

Delete data

Statement stmt=con.createstatement (resultset.type_scroll_sensitive,resultset.concur_pudatable);
ResultSet uprs=stmt.executequery ("SELECT * from Test");
Uprs.absolute (4);
Uprs.deleterow ();

Batch Processing

Con.setautocommit (FALSE); Turn off automatic recognition mode
Statement stmt=con.createstatement ();
int[] rows;
Stmt.addbatch ("INSERT into test values (1001,xuzhaori)");
Stmt.addbatch ("INSERT into test values (1002,xuyalin)");
Rows=stmt.executebatch ();
Con.commit (); Without any errors, execute batch processing stmt.executebatch ();

jndi-data source and connection pool (Connection pool)

Tomcat's JDBC Data source Setup Technology manual P439

Connection Pooling Tool-proxool Var 0.8.3 Technical Manual P446

Set Web.xml

<?xml version= "1.0" encoding= "iso-8859-1"?> <!--<?xml version= "1.0" encoding= "GB2312"?>--> < Web-app xmlns= "Http://java.sun.com/xml/ns/j2ee" xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance" xsi: schemalocation= "http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd" version= "2.4" > ... <servlet> < Servlet-name>servletconfigurator</servlet-name> <servlet-class> Org.logicalcobwebs.proxool.configuration.servletconfigurator</servlet-class> <init-param> < Param-name>propertyfile</param-name> <param-value>web-inf/classes/proxool.properties</
Param-value> </init-param> <load-on-startup>1</load-on-startup> </servlet> back-end statistics port add the following <servlet> <servlet-name>Admin</servlet-name> <servlet-class> Org.logicalcobwebs.proxool.admin.servlet.adminservlet</servlet-class> </servlet> <servlet-mapping > <servlet-name>Admin</servlet-name> <url-pattern>/admin</url-pattern> </servlet-mapping> .... </web-app>
 

Configure Proxool.properties

Jdbc-0.proxool.alias=jspbook
Jdbc-0.proxool.driver-class=com.mysql.jdbc.driver
Jdbc-0.proxool.driver-url=jdbc:mysql://localhost:3306/sample_db?user=root&password=browser&useunicode= True&characterencoding=utf-8
jdbc-0.proxool.maximum-connection-count=10
jdbc-0.proxool.prototype-count=4
jdbc-0.proxool.house-keeping-test-sql=select CURRENT_DATE
Jdbc-0.proxool.verbose=true
jdbc-0.proxool.statistics=10s,1m,1d  Back-end statistical interface add this row
Jdbc-0.proxool.statistics-log-level=debug

Using the Proxool connection pool

Connection con = drivermanager.getconnection ("Proxool.") Jspbook ");
Statement stmt = con.createstatement (resultset.type_scroll_sensitive, resultset.concur_updatable);
String query = "SELECT * from employee";
ResultSet rs = stmt.executequery (query);

Thank you for reading this article, I hope to help you, thank you for your support for this site!