The principles and procedures of JDBC Connection database

JDBC is a technology that sun companies can use to connect to databases in the Java language.

first, the basic knowledge of JDBC

JDBC (Java Data Base Connectivity,java database connection) is a Java API for executing SQL statements that provides unified access to a variety of relational databases, consisting of a set of classes and interfaces written in the Java language. JDBC provides database developers with a standard API for building more advanced tools and interfaces that enable database developers to write database applications with a pure Java API and to run across platforms without being limited by database vendors.

1, cross-platform operation: This is the inheritance of the Java language "once compiled, running everywhere" characteristics;

2, not limited by the database vendor: The ingenious thing is that JDBC has two kinds of interfaces, one is the application layer, the role is to enable developers to call the database and processing results through SQL, without the need to consider the database provider; the other is the driver layer, Handles interaction with specific drivers, which the JDBC driver can use to create a bridge between Java programs and data sources. The application needs to be written once and can be moved to a variety of drivers. Sun provides a driver manager, database vendors-such as MySQL, Oracle, the driver provided to meet the requirements of the driver can be recognized, you can work properly. Therefore, JDBC is not restricted by the database vendor.

The JDBC API can be used as a link between a Java application and a variety of relational databases, bringing convenience and a negative impact, following are the advantages and disadvantages of JDBC. The advantages are as follows:

easy to operate: JDBC makes it unnecessary for developers to invoke commands and functions with complex drives; Strong portability:JDBC supports different relational databases, so you can enable the same application to support access to multiple databases, as long as the appropriate drivers are loaded; common good: JDBC-ODBC Bridge drives convert JDBC functions to ODBC; Object-oriented: You can encapsulate a common JDBC database connection into a class, and call it directly when you use it.

disadvantages are as follows:

The speed of accessing data records is affected to a certain extent; difficult to change data source: JDBC can support a variety of databases, operations between the various databases must be different, which brings a lot of trouble changing the data source

Ii. the process and principle of JDBC Connection database

1 . Load the driver for the specified database in the development environment. For example, in the next experiment, the database used was MySQL, so it was necessary to download the MySQL-enabled driver (most recently: Mysql-connector-java-5.1.18-bin.jar), and the development environment was MyEclipse, Load the downloaded driver into the development environment (the example will explain how to load it).

2, load the driver in the Java program. In Java programs, you can load drivers added to the development environment by using the Class.forName ("Specify the driver for the database"), such as the code for the data driver that loads MySQL: Class.forName (" Com.mysql.jdbc.Driver ")

3 . Create a data Connection object: Create a database Connection object connection through the DriverManager class. The DriverManager class works between the Java program and the JDBC driver to check whether the loaded driver can establish a connection, and then, through its Getconnection method, creates a JDBC based on the database's URL, user name, and password Connection object. such as: Connection Connection = drivermanager.geiconnection ("URL of the connection database", "username", "password"). Where the Url= protocol name +IP address (domain name) + port + database name; user name and password refers to the username and password used when you log in to the database. A specific example creates MySQL's database connection code as follows:

Connection connectmysql = drivermanager.geiconnection ("Jdbc:mysql://localhost:3306/myuser", "root", "root");

4 . Create the Statement object: The Statement class is primarily an object that executes a static SQL statement and returns the results it generates. You can create a statement object by using the Createstatement () method of the Connection object. For example: Statement statament = Connection.createstatement (); The specific example creates the statement object code as follows:

Statement statamentmysql =connectmysql.createstatement ();

5, the related method of calling the statement object executes the corresponding SQL statement: the Execuupdate () method is used to update the data, including actions such as inserting and deleting, such as inserting a piece of data into the staff table:

Statement.excuteupdate ("INSERT into Staff" (name, age, Sex,address, Depart, Worklen,wage) "+" VALUES (' Tom1 ', 321, ' M ', ' C Hina ', ' personnel ', ' 3 ', ' 3000 ');

A query that makes data by calling the ExecuteQuery () method of the Statement object, and the query results get the Resulset object, Resulset the collection of data returned after the query database is executed, and the Resulset object has a pointer that can point to the current data row. With the next () method of the object, make the pointer point to the next line, and then remove the data with the column number or field name. If the next () method returns NULL, there is no data in the next row. Use the sample code as follows:

ResultSet Resultsel = Statement.executequery ("SELECT * from staff");

6, Close the database connection: Use the database or do not need to access the database, through the connection close () method in time to turn off the data connection.

Three, JDBC application example Experiment

Experiment content: use phpMyAdmin to create a database (myuser) in MySQL, and add the data needed for the experiment (create a new staff table, add some records), write Java programs, Use JDBC to connect to create a good database in MySQL (myuser), insert, UPDATE, delete and query the staff table.

experimental environment and development tools: Win7 operating system; jdk1.6.0_26;xampp1.7.7 (MySQL 5.1, phpmyadmin); MyEclipse 8.5

Construction of the experimental environment: Reference to my blog

Java environment collocation: http://blog.csdn.net/cxwen78/article/details/6400798 Windows system xampp installation configuration use: http://blog.csdn.net/ cxwen78/article/details/6847927

experimental process and steps:

1, download MySQL support JDBC driver: If you already have, you can skip this step. Go to the MySQL official website (http://www.mysql.com/products/connector/) to download the driver, MySQL for different platforms to provide a different connector, we need is DBC Driver for MySQL ( connector/j), as shown in the following figure, click Download Follow the site's guidance to download. Open the downloaded compressed package (mysql-connector-java-5.1.18.zip), and copy the Java Package (Mysql-connector-java-5.1.18-bin.jar) to the MySQL directory ( Only to be put here for convenience, for use when loading the driver.

2 . Create DATABASE: Use phpMyAdmin, login to MySQL, create database myuser, and insert a table named staff. and add some data, operation steps, as shown in the MySQL database after logging in:

1 Create the database, the name is MyUser, the code is UTF8_GENERAL_CI (support Chinese);

2 new form, the name is staff, the table has 8 fields;

3 8 field settings, including name, type, value length, initial value, encoding, etc. (click to view larger image);

4 after the success of the add, see the Staff table situation:

5 Insert some experimental data to the table, need to insert two, one is the staff Lucy's, and Lili:

3 . Create the project in Myeclips and add the MySQL driver to the project: the project type created can be either a Java project or a Java Web project. The Web project is created here, the project name can be arbitrarily taken, I named "JavaWebChp07". Add the MySQL driver package (Mysql-connector-java-5.1.18-bin.jar) that you downloaded in step 1 to your project's build path after you create it, and add the process as shown in the following illustration:

4, the writing JDBC connection MySQL database instance concrete code, Jdbc_test.java:

Specific code:

<pre name= "code" class= "Java" >package chp07;  
Import java.sql.Connection;  
Import Java.sql.DriverManager;  
Import Java.sql.ResultSet;  
Import java.sql.SQLException;  
  
Import java.sql.Statement;  
  
    public class Jdbc_test {//create static global variable Connection conn;  
  
    Static Statement St;   public static void Main (string[] args) {insert ();   Insert Add record update ();   Update record Data Delete ();    Delete record query (); Query record and display}/* Insert data record and output the number of data records inserted */public static void Insert () {conn = get Connection (); To get the connection first, connect to the database try {String sql = INSERT into staff (name, age, Sex,address, Depart, Workle  N,wage) "+" VALUES (' Tom1 ', +, ' M ', ', ', ' personnel ', ' 3 ', ' 3000 ') ";    SQL statement for inserting data St = (Statement) conn.createstatement (); Create a statement object int count = St.executeup to execute a static SQL statementDate (SQL); Executes the SQL statement of the insert operation and returns the number of inserted data System.out.println (insert in staff table + count + Bar data);   The processing result of the output insert Operation Conn.close ();   
        Close database connection} catch (SQLException e) {System.out.println ("Insert data Failure" + e.getmessage ()); }/* Update the records that meet the requirements and return the updated number of records */public static void update () {conn = Getconnec tion (); Also get the connection first, that is, connect to the database try {String sql = "Update staff set wage= ' 2200 ' WHERE name = ' Lucy '";//Update Data S    QL Statement st = (Statement) conn.createstatement (); Create a statement object for executing static SQL statements, St genus local variable int count = st.executeupdate (SQL);//SQL statement that performs the update operation, returning the update data      Number System.out.println ("Update in staff table" + Count + "data");   The processing result of the output Update operation Conn.close (); Close database connection} catch (SQLException e) {System.out.println ("Update dataFailure ");  }/* Query the database, output the records that meet the requirements * * public static void query () {conn = getconnection ();     Also get the connection first, that is, connect to the database try {String sql = "SELECT * from staff";    SQL statement for querying Data St = (Statement) conn.createstatement ();    Create a statement object for executing static SQL statements, St genus local variable ResultSet rs = st.executequery (SQL);  
            Executes the SQL query statement, returning the result set of the query data System.out.println ("Final query result is:"); while (Rs.next ()) {//Determine if there is a next data//the corresponding value String name = rs based on the field name.  
                GetString ("name");  
                int age = Rs.getint (' age ');  
                String sex = rs.getstring ("Sex");  
                String address = rs.getstring ("Address");  
                String depart = rs.getstring ("Depart");  
                String Worklen = rs.getstring ("Worklen");  
                  
                String wage = rs.getstring ("wage"); LoseThe values of each field of the record System.out.println (name + "+ Age +" "+ Sex +" "+ Address  
              
            + "" + Depart + "" + Worklen + "" + wage);   } conn.close ();  
        Close database connection} catch (SQLException e) {System.out.println ("query data Failed"); }/* Delete records that meet the requirements, output/public static void Delete () {conn = getconnection (); , that is, connect to the database try {String sql = "Delete from staff where name = ' Lili '";//SQL statement to delete data s    t = (Statement) conn.createstatement ();  
              
            Create a statement object for executing static SQL statements, St genus local variable int count = st.executeupdate (SQL);//Execute SQL DELETE statement, return number of deleted data    System.out.println ("delete" + count + "data \ n" in the staff table);   The processing result of the output delete operation Conn.close (); Close database connection} catch (SQLException e) {System.out.println ("delete data failed"); }//* Get database connection function/public static Connection getconnection () {Connection Co  n = null;  
              
            Create a try {class.forname ("Com.mysql.jdbc.Driver") for the connection object used to connect to the database;//load MySQL data driver con = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/myuser", "root", "Ro" OT ");//Create Data connection} catch (Exception e) {System.out.println (" database connection Failed "+ E.getmessage (  
        )); return con;   Returns the established database connection}}