Javaweb Learning Summary (32)--JDBC Learning Primer

Javaweb Learning Summary (32)--jdbc Learning introductory One, JDBC related concepts Introduction 1.1, Database driver

Here the concept of driving and usually hear the kind of driving concept is the same, such as the usual purchase of sound card, network card directly plugged into the computer is not available, you must install the appropriate driver before you can use the sound card and network card, the same reason, after we installed the database, Our application is also unable to use the database directly, must through the corresponding database driver, through the driver to deal with the database, as follows:

　　

1.2. Introduction to JDBC

In order to simplify and unify the operation of database, Sun Company defines a set of Java Operation Database Specification (interface), called JDBC. This interface is implemented by the database vendor, so that developers can manipulate the database by simply learning the JDBC interface and loading the specific driver through JDBC.

As shown in the following:

　　

JDBC is all called: Java data Base Connectivity (Java database connection), which consists primarily of interfaces.
The 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).

Ii. writing JDBC Program 2.1, setting up the experiment environment

1. Create a library in MySQL and create data for the user table and the inserted table.

The SQL script is as follows:

1 CREATE database Jdbcstudy character set UTF8 collate utf8_general_ci; 2  3 use Jdbcstudy; 4  5 CREATE TABLE users (6     ID int primary KEY, 7     name varchar (), 8     password Varcha R (+), 9     email varchar (     birthday date11); insert into users (Id,name,password,email,birthday) values (1, ' Zhansan ', ' 123456 ', ' [email protected] ', ' 1980-12-04 '); INSERT into users (Id,name,password,email,birthday) VALUES (2, ' Lisi ', ' 123456 ', ' [email protected] ', ' 1981-12-04 '); INSERT into users (Id,name,password,email,birthday) VALUES (3, ' Wangwu ', ' 123456 ', ' [email protected] ', ' 1979-12-04 ');

2, create a new Java project, and import data-driven.
　　

3. Write the program to read the data from the user table and print it in the Command Line window.
The specific code is as follows:

 1 package Me.gacl.demo; 2 Import java.sql.Connection; 3 Import Java.sql.DriverManager; 4 Import Java.sql.ResultSet; 5 Import java.sql.Statement; 6 7 public class Jdbcfirstdemo {8 9 public static void Main (string[] args) throws Exception {10//database to connect ur L11 string url = "Jdbc:mysql://localhost:3306/jdbcstudy"; 12//The user name that is used when connecting to the database is string username = "R Oot "; 14//The password that is used when connecting the database is password =" XDP "; 16 17//1. Load driver//drivermanager. Registerdriver (New Com.mysql.jdbc.Driver ()); This method is not recommended to load the driver Class.forName ("Com.mysql.jdbc.Driver");//         It is recommended to use this method to load the drive 20//2. Get a link to the database Connection conn = drivermanager.getconnection (URL, username, password); 22 23//3. Gets the statement24 Statement st = conn.createstatement () used to send SQL statements to the database; ing sql = "Select Id,name,password,email,birthday from Users"; 27//4. Send SQL to the database and get resultset28 representing the result set ResultS ET rs = st.eXecutequery (SQL); 29 30//5. Extract data from the result set (Rs.next ()) {System.out.println ("id=" + R S.getobject ("id")), System.out.println ("name=" + rs.getobject ("name")), System.out.println ("Pa Ssword= "+ rs.getobject (" password ")), System.out.println (" email= "+ rs.getobject (" email "); Stem.out.println ("birthday=" + rs.getobject ("Birthday")); 37}38 39//6. Close the link and release the resource. E (); St.close (); Conn.close (); 43}44}

The results of the operation are as follows:

　　

2.2, DriverManager class explanation

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:

1. Drivermanager.registerdriver (New Driver ())
2. 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:
1, see driver Source code can see, if in this way, will cause the driver to register two times, that is, in memory there will be two driver objects.
2, 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.

2.3, the database URL explanation

The URL is used to identify the location of the database and tells the JDBC program which database to connect to by the URL address:

　　

Common database URL address notation:

• Oracle notation: Jdbc:oracle:thin: @localhost: 1521:sid
• SQL Server notation: jdbc:microsoft:sqlserver://localhost:1433; Databasename=sid
• MySQL notation: jdbc:mysql://localhost:3306/sid

If you are connecting to a local MySQL database, and the connection uses a port of 3306, the URL address can be abbreviated as: jdbc:mysql:///database

2.4, Connection class explanation

Connection in the JDBC program, which is used to represent the link of the database, collection is the most important object in the database programming, the client and the database all interaction is done through the connection object, the common method of this object:

• Createstatement (): Creates a statement object that sends SQL to the database.
• Preparestatement (SQL): Creates a Preparesatement object that sends precompiled SQL to the database.
• Preparecall (SQL): Creates the CallableStatement object that executes the stored procedure.
• Setautocommit (Boolean autocommit): Sets whether the transaction is automatically committed.
• Commit (): commits the transaction on the link.
• Rollback (): Rolls back the transaction on this link.

2.5, Statement class explanation

The statement object in the JDBC program is used to send SQL statements to the database, statement object common methods:

• ExecuteQuery (String sql): Used to send query statements to data.
• Executeupdate (String sql): Used to send INSERT, UPDATE, or DELETE statements to the database
• Execute (String SQL): Used to send arbitrary SQL statements to the database
• Addbatch (String sql): puts multiple SQL statements into one batch.
• ExecuteBatch (): Sends a batch of SQL statement execution to the database.

2.6, ResultSet class explanation

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.
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)

ResultSet also provides a way to scroll the result set:

• Next (): Move to the next line
• Previous (): Move to previous line
• Absolute (int row): Move to the specified line
• Beforefirst (): Moves the front of the resultset.
• Afterlast (): Moves to the last face of the resultset.

2.7. Releasing resources

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, especially connection objects, which are very rare resources, Must be released immediately after use, 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.

Javaweb Learning Summary (32)--JDBC Learning Primer