JDBC Database common operations (MySQL)

Jdbc
English Name: javadatabaseconnectivity
English Name: Java database connection
Abbreviation: JDBC
JDBC is a JAVAAPI for executing SQL statements.
You can provide unified access to a variety of relational databases, consisting of a set of classes and interfaces written in the Java language. JDBC provides a benchmark to build more advanced tools and interfaces that enable database developers to write database applications
Program.

First, establish a database connection

1. Reference jar file (http://dev.mysql.com/downloads/connector/j/download jar file)

2, registered driver Class.forName ("Com.mysql.jdbc.Driver") (just register once)

3. Define the database connection string url= "jdbc:mysql://127.0.0.1:3306/database name? user= username &password= password &useUnicode=true& Characterencoding=utf-8 ";

JDBC: Sub-Protocol: Sub-name//hostname: Port/database name? Property name = attribute value &.
User: Username
Password: password
Useunicode: If the Unicode character set is used, the value of this parameter must be set to True if the parameter characterencoding is set to gb2312 or GBK
Characterencoding: Specifies the character encoding when Useunicode is set to true. For example, can be set to gb2312 or GBK
AutoReConnect: Whether to automatically reconnect when the database connection is interrupted unexpectedly. (Default: false)
Autoreconnectforpools: Whether to use a reconnection policy against the database connection pool. (Default: false)
Failoverreadonly: The connection is set to read-only after the auto-reconnect is successful. (Default: TRUE)
When Maxreconnects:autoreconnect is set to true, the number of times the connection is retried (default 3:)
When Initialtimeout:autoreconnect is set to true, the time interval between two re-interconnects, in seconds: 2
ConnectTimeout: Time-out, in milliseconds, when establishing a socket connection with the database server. 0 means never timeout, for JDK 1.4 and later 0
Sockettimeout:socket operation (Read-write) timeout, in milliseconds. 0 means never time out

Second, common database operation

// database connection string    Publicstaticfinalstringdriver= "Com.mysql.jdbc.Driver";    Publicstaticfinalstringurl= "jdbc:mysql://127.0.0.1:3306/mylibrary?user=root&password=123456& Useunicode=true&characterencoding=utf-8 ";

1. Add Data

  Publicstaticvoidinsert () throwssqlexception{Connectioncon  =null  ;        STATEMENTST  =null  ;             try  {Class.forName (DRIVER);            Con  =drivermanager.getconnection (URL);            St  =con.createstatement (); St.execute ( insertintoarticle (title) VALUES (' headings ');        );  catch   (Classnotfoundexceptione) {E.PR        Intstacktrace ();             finally  {con.close ();        St.close (); }    }

2, delete data

  Publicstaticvoiddelete (intid) throwssqlexception{Connectioncon  =null  ;        STATEMENTST  =null  ;             try  {Class.forName (DRIVER);            Con  =drivermanager.getconnection (URL);            St  =con.createstatement (); St.executeupdate ( "deletefromarticlewhereid=" +id+ ";"        );  catch   (Classnotfoundexceptione) {E.PR        Intstacktrace ();             finally  {con.close ();        St.close (); }    }

3. Modify the data

    publicstaticvoidupdate () throwssqlexception{        connectioncon=drivermanager.getconnection ( URL);        Statementst=con.createstatement ();        St.executeupdate ("updatearticlesettitle= ' new title ' Whereid=1;" );        Con.close ();        St.close ();    }

4. Query data

publicstaticvoidselect () throwssqlexception{Connectioncon=NULL; STATEMENTST=NULL; Try{class.forname (DRIVER); Con=drivermanager.getconnection (URL); St=con.createstatement (); Resultsetrs=st.executequery ("select*fromarticle;"));  while(Rs.next ()) {System.out.print (Rs.getint ("id")); System.out.println (Rs.getstring ("Title")); }            //automatic output by table structure//Resultsetmetadatarsmd=rs.getmetadata ();//For (Inti=1;i<rsmd.getcolumncount (); i++) {//System.out.print (Rsmd.getcolumnname (i));//System.out.println (Rsmd.getcolumntypename (i));//            }}Catch(Classnotfoundexceptione) {e.printstacktrace (); }finally{st.close ();        Con.close (); }    }

5. Parameter pretreatment

    Publicstaticvoidinsert (stringtitle) throwssqlexception{        connectioncon=  Drivermanager.getconnection (URL);        Preparedstatementpst=con.preparestatement ("Insertintoarticle" values (?); " );        Pst.setstring (1, title);        Pst.executeupdate ();        Con.close ();        Pst.close ();    }

6. Batch Processing

Publicstaticvoidinsertbatch (string[]title) throwssqlexception{//connectioncon=drivermanager.getconnection (URL);//statementst=con.createstatement ();//For (inti=0;i<title.length;i++) {//St.addbatch ("insertintoarticle (title) VALUES ('" +title[i]+ "');");//        }//St.executebatch (); //con.close ();//St.close (); //parameter preprocessing + batch processingconnectioncon=drivermanager.getconnection (URL); Preparedstatementpst=con.preparestatement ("Insertintoarticle" values (?); ");  for(inti=0;i<title.length;i++) {pst.setstring (1, Title[i]);        Pst.addbatch ();        } pst.executebatch ();        Con.close ();    Pst.close (); }

7. Transaction operations

7.1, the characteristics of database transactions:
　　　　atomicity (atomicity): A transaction is an inseparable unit of work, and the operations included in the transaction are either done or not.
　　　　Consistency (consistency): A transaction must change the database from one consistency state to another. Consistency is closely related to atomicity.
　　　　Isolation (Isolation): The execution of one transaction cannot be disturbed by other transactions. That is, the operations inside a transaction and the data used are isolated from other transactions that are concurrently executing, and cannot interfere with each other concurrently.
　　　　persistence (Durability): Persistence, also known as permanence (permanence), refers to the fact that once a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on it.
7.2. JDBC Transaction operation
(1) Setautocommit (Booleanauto commit): Set whether to commit the transaction automatically;
(2) Commit (): Commit the transaction;
(3) Rollback (): Undo transaction;
The default is to commit the transaction automatically, that is, each updated SQL statement on the database represents a transaction, the system automatically calls commit () after the operation succeeds, or rollback () is called to undo the transaction. In JDBC, you can suppress autocommit transactions by calling Setautocommit (false). You can then make multiple SQL statements that update the database as a single transaction, and then call commit () for the overall commit after all the operations have been completed. If one of the SQL operations fails, the commit () method is not executed, but the corresponding SqlException is generated, and the rollback () method can be captured in the exception code block to undo the transaction.

publicstaticvoidtransaction () throwssqlexception{Connectioncon=drivermanager.getconnection (URL); STATEMENTST=con.createstatement (); Try{con.setautocommit (false);//Change the default submission method for a JDBC transactionSt.executeupdate ("deletefromarticlewhereid=1;")); St.executeupdate ("DELETEFROMARTICLEWHEREID=2;"); Con.commit ();//submit a JDBC transactionCon.setautocommit (true);//Restore the default submission method for a JDBC transaction}Catch(EXCEPTIONEXC) {con.rollback ();//Rollback of a JDBC transaction}finally{st.close ();        Con.close (); }    }

8. Call the MySQL stored procedure

    publicstaticvoidcallprocedure () throwssqlexception{        connectioncon=  Drivermanager.getconnection (URL);        CALLABLESTATEMENTCST=con.preparecall ("{Callproc_select (?)}") );
Incoming parameter value        cst.setint (1,3);         // Returns true if the first result is a ResultSet object, or False if the first result is an update, add, modify, or no result        booleanissuccess=Cst.execute ();         // Success Returns True, Failure returns false         System.out.println (issuccess);    }

Third, JDBC executes the SQL statement method

　　1, executeQuery (String sql): executes the SQL query and returns the ResultSet object.

　　2, executeupdate (String sql): executable increment, delete, change, return the number of rows affected by execution.

　　3. Execute (String sql);: executes any SQL statement and returns a Boolean value that indicates whether to return resultset.

Iv. types of JDBC statement

　　1, Statement: interface provides a basic way to execute statements and get results, for the execution of simple SQL statements without parameters, each execution of SQL statements, the database to execute the compilation of SQL statements, preferably to execute a query only once and return the results of the case, Efficiency is higher than preparedstatement.

　　2, Preparestatement: variable parameters of SQL, compile once, execute multiple times, high efficiency, security, effectively prevent SQL injection and other issues; Support batch update, batch delete, execute SQL statements can take parameters, and support bulk execution of SQL. With the cache mechanism, pre-compiled statements are placed in the cache and can be removed directly from the cache the next time the same SQL statement is executed.

　　3. CallableStatement: used to execute the call to the stored procedure of the database, inherit from PreparedStatement, support the SQL operation, support call stored procedure, provide support for output and input/output parameters;
The syntax for invoking a stored procedure in JDBC is as follows:
{Call Procedure name [(?,?)]}
The syntax for the procedure that returns a result parameter is:
{? =call process name [(?,?,...)]}
The syntax for stored procedures without parameters is similar:
{Call Procedure name}
All methods defined in CallableStatement are used to process the output part of an out parameter or inout parameter: To register the JDBC type of the Out parameter (the generic SQL type), to retrieve the result from these parameters, or to check whether the returned value is Jdbcnull.

Common JDBC Database operations (MySQL)