JDBC connection to MySQL instance details, jdbcmysql instance details

JDBC connection to MySQL instance details, jdbcmysql instance details

JDBC connection to MySQL

JDBC connection to MySQL

Load and register the JDBC driver

Class. forName ("com. mysql. jdbc. Driver ");
Class. forName ("com. mysql. jdbc. Driver"). newInstance ();

Jdbc url defines the connection between the driver and the data source

Standard Syntax:

<Protocol (main communication protocol)>: <subprotocol (secondary communication protocol, that is, the driver name) >:< data source identifier (data source)>

Jdbc url format of MySQL:

Jdbc: mysql // [hostname] [: port]/[dbname] [? Param1 = value1] [& param2 = value2]...

Example: jdbc: mysql: // localhost: 3306/sample_db? User = root & password = your_password

Common parameters:
User Username
Password
AutoReconnect online failed, whether to bring online again (true/false)
Number of times that maxReconnect tries to reconnect
InitialTimeout
Maximum number of rows returned by maxRows
Whether useUnicode uses Unicode font encoding (true/false)
CharacterEncoding what encoding (GB2312/UTF-8 /...)
Whether relaxAutocommit is automatically submitted (true/false)
CapitalizeTypeNames data definition name expressed in uppercase

Create a connection object

String url = "jdbc: mysql: // localhost: 3306/sample_db? User = root & password = your_password ";
Connection con = DriverManager. getConnection (url );

Create an SQL Statement Object)

Statement stmt = con. createStatement ();

Execute SQL statements

ExecuteQuery () String query = "select * from test"; ResultSet rs1_stmt.exe cuteQuery (query); result set ResultSetwhile (rs. next () {rs. getString (1); rs. getInt (2);} executeUpdate () String upd = "insert into test (id, name) values (1001, xuzhaori)"; int con=stmt.exe cuteUpdate (upd); execute ()

Example:

try{ }catch(SQLException sqle){}finally{}

Java and SQL Technical Manual P421

PreparedStatement (prepared statement)

PreparedStatement stmt = conn. prepareStatement ("insert into test (id, name) values (?,?) ");
Stmt. setInt (1, id );
Stmt. setString (2, name );

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

CallableStatement (pre-storage program) Technical Manual P430

Use JDBC2.0

Move the cursor up or down freely in the ResultSet object
Statement stmt = con. createStatement (ResultSet. TYPE_SCROLL_SENSITIVE, ResultSet. CONCUR_READ_ONLY );
ResultSet rs1_stmt.exe cuteQuery ("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 or down to obtain the changed value.
TYPE_SCROLL_INSENSITIVE can be moved up or down.

ResultSetConcuttency

CONCUR_READ_ONLY read-only
The CONCUR_UPDATABLE ResultSet object can be used to add, modify, and remove databases.

Update data directly using the ResultSet object

Add data

Statement stmt = con. createStatement (ResultSet. TYPE_SCROLL_SENSITIVE, ResultSet. CONCUR_PUDATABLE); ResultSet uprs1_stmt.exe cuteQuery ("select * from test"); uprs. moveToInsertRow (); uprs. updateInt (1,1001); uprs. updateString (2, "Xu Zhao Day"); 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); disable the automatic approval mode Statement stmt = con. createStatement (); int [] rows; stmt. addBatch ("insert into test values (1001, xuzhaori)"); stmt. addBatch ("insert into test values (1002, xuyalin)" inserts into rows1_stmt.exe cuteBatch (); con. commit (); if there is no error, perform the batch processing stmt.exe cuteBatch ();

JNDI-Data Source and Connection Pool)

Tomcat JDBC data source setup Technical Manual P439

Connection Pool 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> Add the following <servlet> <servlet-name> Admin </servlet-name> <servlet-class> org. logicalcobw Ebs. 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 = JSPBookjdbc-0.proxool.driver-class = com. mysql. jdbc. Driverjdbc-0.proxool.driver-url = jdbc: mysql: // localhost: 3306/sample_db? User = root & password = browser & useUnicode = true & characterEncoding = UTF-8jdbc-0.proxool.maximum-connection-count = 10jdbc-0.proxool.prototype-count = select CURRENT_DATEjdbc-0.proxool.verbose = truejdbc-0.proxool.statistics = 10 s, 1 m, 1d back-end statistics interface add this row jdbc-0.proxool.statistics-log-level = DEBUG

Use 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 and hope to help you. Thank you for your support for this site!