JDBC MYSQL Learning Note (i) JDBC Basic use

1. Introduction to JDBC

In order to simplify and unify the operation of database, Sun Company defines a set of Java Operation Database specification, called JDBC. JDBC is all called: Java data Base Connectivity (Java database connection), which consists primarily of interfaces. 2 packages that make up JDBC: java.sql javax.sql

Developing a JDBC application requires support for the above 2 packages, as well as importing the appropriate JDBC database implementations (that is, database-driven).

2. Steps for using JDBC--the first JDBC program

Requirement: Programming reads data from the user table and prints it in a command-line window.
(1) Setting up the experimental environment:
A, create a database in MySQL , and create a user table , inserting data into the table.
B. Create a new Java project and import the database driver.
(2) write the program, load the database driver in the program

         A, way one: DriverManager. Registerdriver (Driver Driver)          B, mode two: Class.forName ("Com.mysql.jdbc.Driver");

(3) Establishing a connection (Connection)

Connection conn = drivermanager.getconnection (Url,user,pass);

(4) Create a statement object to send SQL to the database and send the SQL

         Statement st = Conn.createstatement ();          ResultSet rs = st.excutequery (SQL);

(5) Extract data from resultset representing result set, Print to command line window

(6) Disconnect from the database and release related resources

Dome

</pre><p><pre name= "code" class= "java" >import Java.sql.*;import Com.sun.org.apache.regexp.internal.recompile;public class Dome {/** * @param args */public static void main (string[] args ) throws sqlexception,classnotfoundexception{//TODO auto-generated method stubstring url = "Jdbc:mysql://localhost : 3306/skyfin "; String username = "root"; String password = "Skyfin";//1. Load Driver//drivermanager.registerdriver (new Com.mysql.jdbc.Driver ()); Class.forName ("Com.mysql.jdbc.Driver");//2 Gets the connection connection connection = Drivermanager.getconnection (Url,username, Password)//3. Gets the Statament object that sends the SQL statement to the database statement stat = Connection.createstatement ();//4. Send SQL to the database, Gets the result set returned by the database resultset rsresultset = stat.executequery ("SELECT * from User"),//5. Getting data from the result set while (Rsresultset.next ()) { SYSTEM.OUT.PRINTLN ("id =" + rsresultset.getobject ("id")); System.out.println ("name =" + rsresultset.getobject ("name")); SYSTEM.OUT.PRINTLN ("password =" + rsresultset.getobject ("password"));} 6. Release resources (release link) rsresultset.close ();Stat.close (); Connection.close ();}} 

3. drivermanager--Load Database Driver

The DriverManager in the JDBC program is used to load the driver and create a link to the database, a common method of this API:

    Drivermanager.registerdriver (New Driver ());    Drivermanager.getconnection (URL, user, password);

Note: It is not recommended to use the Registerdriver method to register drivers in real-world development. There are two reasons:
First, look at the source code of driver can be seen, if in this way, will cause the driver to register two times, that is, in memory there will be two driver objects.
Second, the program relies on the MySQL API, out of the MySQL jar package, the program will not compile, the future program to switch the underlying database will be very troublesome.
Recommended way:

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

This approach does not cause the drive object to recur in memory, and in this way, the program requires only a single string and does not rely on specific drivers to make the program more flexible.
Also, in development, it is not recommended to use a specific driver type to point to the connection object returned by the Getconnection method.

4, Database url--identify the location of the database

URLs are used to identify the location of the database, and the programmer tells the JDBC program which database to connect to by URL address.

The URL of the MYSQL database is written as: Jdbc:mysql:[]//localhost:3306/test? parameter name: Argument value

Common database URL address notation:
Oracle:jdbc:oracle:thin: @localhost: 1521:Skyfin
sqlserver:jdbc:microsoft:sqlserver://localhost:1433; Databasename=Skyfin
Mysql:jdbc:mysql://localhost:3306/skyfin
Short form of MySQL URL address: jdbc:mysql://Skyfin
Common Properties: Useunicode=true&characterencoding=utf-8

jdbc:mysql://localhost:3306/test?user=root&password=&useunicode=true&characterencoding=gbk& Autoreconnect=true&failoverreadonly

5, connection--represents the database link in the JDBC program Connection, which is used to represent the database link. Connection is the most important object in database programming, and the client-to-database interaction is done through the Connection object, a common method of this object:
(1) createstatement (): Creates a statement object that sends SQL to the database.
(2) preparestatement (SQL): Creates a Preparesatement object that sends precompiled SQL to the database.
(3) Preparecall (SQL): Creates the CallableStatement object that executes the stored procedure.
(4) Setautocommit (Boolean autocommit): Sets whether the transaction is automatically committed.
(5) Commit (): commits the transaction on the link.
(6) Rollback (): Rollback a transaction on this link.
6. statement--send SQL statements to the database the Statement object in the JDBC program is used to send SQL statements to the database, Statement object common methods:
(1) executeQuery (String sql): Used to send query statements to the database.
(2) executeupdate (String sql): Used to send INSERT, UPDATE, or DELETE statements to the database
(3) Execute (String sql): Used to send arbitrary SQL statements to the database
(4) Addbatch (String sql): Put multiple SQL statements into one batch.
(5) ExecuteBatch (): Sends a batch of SQL statement execution to the database.
(6) Clearbatch (): Clears the current list of SQL commands for this Statement object.

7. resultset--represents the execution result of the SQL statement the ResultSet in the JDBC program is used to represent the execution result of the SQL statement. A table-like approach is used when the resultset encapsulates the execution result. The ResultSet object maintains a cursor to the table data row, and initially, the cursor calls the Resultset.next () method before the first row, allowing the cursor to point to a specific row of data and invoke the method to fetch the row's data.
(1) ResultSet provides a way to scroll the result set: A, Next (): Move to the next line
B, Previous (): Move to previous line
c, Absolute (int row): Move to the specified line
D, Beforefirst (): Move the front of the resultset.
E, Afterlast (): Moves to the last face of the resultset.
(2) ResultSet since it is used to encapsulate execution results, the object provides a GET method for getting the data: Get any type of data
GetObject (int index)
GetObject (String columnName)
Gets the data of the specified type, for example:
getString (int index)
GetString (String columnName)
Other methods for obtaining data of the specified type are shown in the following table:
Common data type conversion tables:

8. Releasing resources-releasing objects interacting with the database after the JDBC program runs, remember to release the objects that were created by the program to interact with the database during the run, typically resultset, statement, and connection objects.
In particular, connection object, it is very rare resources, after use must be released immediately, if the connection can not be timely, correct shutdown, it is very easy to cause system downtime. Connection's use principle is to create as late as possible, releasing as early as possible.
To ensure that the resource release code can run, the resource release code must also be placed in the finally statement.
9, using JDBC to the database CRUD JDBC Statement object used to send SQL statements to the database, want to complete the database additions and deletions to change, only through this object to the database to send additions and deletions to change the statement.
The Executeupdate method of the statement object is used to send an increment, delete, and change SQL statement to the database, and after the execution of the executeupdate, an int integer is returned (that is, the add and DELETE statements cause the database to change several rows of data).
The Statement.executequery method is used to send a query statement to the database, and the ExecuteQuery method returns the ResultSet object that represents the query result.

public static void Main (string[] args) throws sqlexception,classnotfoundexception{//TODO auto-generated method Stubstring url = "Jdbc:mysql://localhost:3306/skyfin"; String username = "root"; String password = "Skyfin";//drivermanager.registerdriver (New Com.mysql.jdbc.Driver ()); Class.forName ("Com.mysql.jdbc.Driver"); Connection Connection = drivermanager.getconnection (Url,username,password); Statement stat = connection.createstatement ();/* Perform a find operation */resultset rsresultset = Stat.executequery ("SELECT * from User" ); while (Rsresultset.next ()) {System.out.println ("id =" + rsresultset.getobject ("id")); System.out.println ("name =" + rsresultset.getobject ("name")); SYSTEM.OUT.PRINTLN ("password =" + rsresultset.getobject ("password"));} /* * Perform insert operation */string sql = "INSERT into user (Id,name,password) value (6," + "' staff '" + "," + "' staff '" + ")"; SYSTEM.OUT.PRINTLN (SQL), int statentnum = stat.executeupdate (sql), if (statentnum>0) {System.out.println ("Insert OK ");} /* * Perform update operation */sql = "Update user set name = ' sKstaff ' WHERE name = ' Staff ' "; SYSTEM.OUT.PRINTLN (sql), statentnum = stat.executeupdate (sql), if (statentnum>0) {System.out.println ("update OK");} /* * Perform delete operation */sql = "Delete from user where name = ' Skstaff '"; SYSTEM.OUT.PRINTLN (sql), statentnum = stat.executeupdate (sql), if (statentnum>0) {System.out.println ("delete ok");} /* * Release of resources */rsresultset.close (); Stat.close (); Connection.close ();}

10. Start a transaction with JDBC PreparedStatement using Batch executebatch ()when JDBC uses MySQL to process big data, it's natural to think of using batch processing,
the normal execution process is: every data processing, access to a database;
batch processing is: accumulate to a certain amount, and then commit to the database once, reduce the number of interactions with the database, so efficiency will greatly improve
As for transactions: A transaction is a logical set of operations, each unit that makes up this set of operations, either all succeeds or is not all successful, by default the transaction is closed.

1. PreparedStatement uses batch executebatch () 1.1. Not using ExecuteBatch (), but using executeupdate (), very slow

public static void Main (string[] args) throws  sqlexception,classnotfoundexception{//TODO auto-generated method Stubstring url = "Jdbc:mysql://localhost:3306/skyfin"; String username = "root"; String password = "Skyfin";//drivermanager.registerdriver (New Com.mysql.jdbc.Driver ()); Class.forName ("Com.mysql.jdbc.Driver"); Connection  Connection = drivermanager.getconnection (Url,username,password); String sql = "INSERT into User1 (id,name) value (?,?)"; PreparedStatement preparedstatement = connection.preparestatement (sql); for (int i = 0;i<10000;i++) { Preparedstatement.setint (1, i);p reparedstatement.setstring (2, "Skyfin" +i);p reparedstatement.executeupdate ();}}

1.2. Use of ExecuteBatch ()

public static void Main (string[] args) throws  sqlexception,classnotfoundexception{//TODO auto-generated method Stubstring url = "Jdbc:mysql://localhost:3306/skyfin"; String username = "root"; String password = "Skyfin";//drivermanager.registerdriver (New Com.mysql.jdbc.Driver ()); Class.forName ("Com.mysql.jdbc.Driver"); Connection  Connection = drivermanager.getconnection (Url,username,password); String sql = "INSERT into User1 (id,name) value (?,?)"; PreparedStatement preparedstatement = connection.preparestatement (sql); for (int i = 0;i<10000;i++) { Preparedstatement.setint (1, i);p reparedstatement.setstring (2, "Skyfin" +i);//preparedstatement.executeupdate ();/* * Use ExecuteBatch () */preparedstatement.addbatch ();} Execute batch preparedstatement.executebatch ();}

Note: 1. If you use Addbatch (), ExecuteBatch () or slow, you have to use this parameter rewritebatchedstatements=true (start batch operation)
Add this parameter after the database connection URL: String dburl = "Jdbc:mysql://localhost:3306/user?" Rewritebatchedstatements=true ";
2. In the code, the position of the pstmt can not be placed in a random place, must be placed outside the loop body2. Enable transaction processing

public static void Main (string[] args) throws  sqlexception,classnotfoundexception{//TODO auto-generated method Stubstring url = "Jdbc:mysql://localhost:3306/skyfin"; String username = "root"; String password = "Skyfin";//drivermanager.registerdriver (New Com.mysql.jdbc.Driver ()); Class.forName ("Com.mysql.jdbc.Driver"); Connection  Connection = drivermanager.getconnection (Url,username,password);//Turn off autocommit connection.setautocommit ( FALSE); String sql = "Update user1 set name =? where id =?"; PreparedStatement preparedstatement = connection.preparestatement (sql); for (int i = 0;i<10000;i++) { Preparedstatement.setstring (1, "Loco" +i);p reparedstatement.setint (2, I);//preparedstatement.executeupdate ();/* * Use ExecuteBatch () */preparedstatement.addbatch ();} Execute Batch preparedstatement.executebatch ();p reparedstatement.close ();//Commit the transaction manually after execution Connection.commit ();// Turn on Autocommit connection.setautocommit (true); Connection.close ();}

3. Mixed use of transactions and batches

public static void Main (string[] args) throws sqlexception,classnotfoundexception{//TODO auto-generated method Stubstri ng url = "Jdbc:mysql://localhost:3306/skyfin"; String username = "root"; String password = "Skyfin";//drivermanager.registerdriver (New Com.mysql.jdbc.Driver ()); Class.forName ("Com.mysql.jdbc.Driver"); Connection Connection = drivermanager.getconnection (Url,username,password);//Turn off autocommit connection.setautocommit (false ); String sql = "Update user1 set name =? where id =?"; PreparedStatement preparedstatement = connection.preparestatement (sql); for (int i = 0;i<10000;i++) { Preparedstatement.setstring (1, "Skyfin" +i);p reparedstatement.setint (2, I);//preparedstatement.executeupdate ();/* * Use ExecuteBatch () */if (i>0&&i%500 = = 0) {preparedstatement.executebatch ();//If you do not want to make an error, do not retain the data, You can not commit once, but ensure that the data does not repeat Connection.commit ();} Preparedstatement.addbatch ();} Execute Batch preparedstatement.executebatch ();p reparedstatement.close ();//Commit the transaction manually after execution Connection.commit ();// Turn on auto-commit Connection.setAutocommit (True); Connection.close ();} 

11. Prevention of SQL injection SQL injection is the behavior of a user exploiting some systems that do not adequately check the input data for malicious damage.
1, statement there is a SQL injection attack problem, such as login user name with ' or 1=1 or username= '
2, for the prevention of SQL injection, you can use PreparedStatement instead of statement.
Note: This example is just the most basic way to prevent SQL injection, in other cases please check the information.

12. PreparedStatement Preperedstatement is a child of statement, and its instance object can be obtained by invoking the Connection.preparedstatement () method, Benefits relative to statement objects:
(1) Prevent SQL injection: Preperedstatement can avoid problems with SQL injection.
(2) Precompiled SQL statement: statement causes the database to compile SQL frequently, potentially causing a database buffer overflow. PreparedStatement can pre-compile SQL to improve the efficiency of database execution.
(3) Use placeholders to simplify statements: and preperedstatement for parameters in SQL, allowing for substitution in the form of placeholders, simplifying the writing of SQL statements. (for example, multiple loops insert data)

Public list<customer> GetAll () {Connection conn = null;        PreparedStatement st = null;        ResultSet rs = null;              try{conn = Jdbcutils.getconnection ();              String sql = "SELECT * from Customer";              st = conn.preparestatement (SQL);              rs = St.executequery ();              List List = new ArrayList ();                     while (Rs.next ()) {Customer c = new Customer ();                     C.setbirthday (rs.getdate ("Birthday"));                     C.setcellphone (rs.getstring ("cellphone"));                     C.setdescription (rs.getstring ("description"));                     C.setemail (rs.getstring ("email"));                     C.setgender (rs.getstring ("gender"));                     C.setid (rs.getstring ("id"));                     C.setname (rs.getstring ("name"));                     C.setpreference (rs.getstring ("preference"));                     C.settype (rs.getstring ("type")); List.add (c);        } return list;        }catch (Exception e) {throw new Daoexception (e);        }finally{Jdbcutils.release (Conn, St, RS);  }  }

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

JDBC MYSQL Learning Note (i) JDBC Basic use