JDBC Connection MySQL Database and demo sample

JDBC is a technology developed by Sun to connect 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 a standard API for database developers to build more advanced tools and interfaces that enable database developers to write database applications in pure Java APIs, and can be executed across platforms and not subject to the limitations of the database vendor.

1, cross-platform execution: This is the Java language inherited the "one-time compilation, execution everywhere" feature;

2, is not limited by the database vendor: The ingenious is that JDBC has two interfaces, one for the application layer, the role is to enable developers to call the database through SQL and processing results, without the need to consider the database provider, and a driver layer, Working with detailed driver interactions, the JDBC driver is able to leverage the JDBC API to create bridges between Java programs and data sources. The application needs to be written only once and can be moved to various drivers for execution. Sun provides a driver manager, and database vendors-such as MySQL, Oracle-provide drivers that meet the requirements of the driver Manager to be recognized and work correctly. Therefore, JDBC is not limited by the database vendor.

The JDBC API can be used as a link between Java applications and a variety of relational databases, at the same time bring convenience, there are also negative effects, the following is the advantages of JDBC, shortcomings. strengths such as the following:

• Convenient operation: JDBC makes it unnecessary for developers to call commands and functions with complex drives;
• Portability is strong: JDBC supports different relational databases, so you can enable the same application to support multiple database access, just load the corresponding driver;
• Good general: JDBC-ODBC bridging drives Replace the JDBC function with ODBC;
• Object-oriented: the ability to encapsulate frequently used JDBC database connections into a class that can be invoked directly when used.

disadvantages such as the following:

• The speed of access to data records has been affected to a certain extent;
• Changing the data source is difficult: JDBC supports multiple databases, and the operations between the various databases are different, which can cause a lot of trouble for changing the data source.

Ii. the process and principle of JDBC Connection database

1 . Load the driver for the specified database into the development environment. For example, in the next experiment, the database used is MySQL, so we need to download the MySQL-supported JDBC driver (the latest is:Mysql-connector-java-5.1.18-bin.jar) While the development environment is MYECLIPSE, the downloaded drivers are loaded into the development environment (a detailed demonstration of the sample will explain how to load).

2 . Load the driver in the Java program. In Java programs, drivers that are added to the development environment can be loaded by "class.forname (" The driver for the specified database)", such as the code for a data driver loaded into MySQL: class.forname (" Com.mysql.jdbc.Driver ")

3 . Create a data Connection object: Create a database Connection object Connectionthrough the DriverManager class. The DriverManager class acts between the Java program and the JDBC driver to check whether the loaded driver can establish a connection, and then through its getconnection method, depending on the URL of the database, Username and password, create a JDBC Connection object. such as:Connection Connection = drivermanager.geiconnection ("URL of the connection database", "username", "password"). , url= protocol name +IP address (domain name) +port+ database name, username and password are the username and password used when logging into the database. A detailed demo sample creates a MySQL database connection code such as the following:

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

4 . Create a Statement object: The Statement class is primarily used to run a static SQL statement and return the object it produces. You can create a statement object by using the Createstatement () method of the Connection object. For example:Statement statament = Connection.createstatement (); A detailed Demo sample creates statement object code such as the following:

Statement statamentmysql =connectmysql. createstatement ();

5 . Call the relevant method of the statement object to run the corresponding SQL statement: Use the Execuupdate () method to update the data, including insertions and deletions, such as inserting a piece of data into the staff table code:

statement.excuteupdate ("INSERT into the staff (name, age, Sex,address, Depart, Worklen,wage)" + "VALUES (' Tom1 ', 321, ' M ') , ' China ', ' personnel ', ' 3 ', ' n ') ');

Queries for data are made by calling the ExecuteQuery () method of the Statement object, and the query results are Resulset objects, Resulset represents the collection of data returned after the query database is run, and the Resulset object has pointers that can point to the current data row. Through the object's next () method, the pointer points to the next line, and then the data is taken out with the column number or field name. Suppose that when the next () method returns NULL, no data exists in the next row. Use the Demo sample code such as the following:

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

6 . Close the database connection: When you are finished using the database or do not need to access the database, close the data connection in a timely manner through the Connection Close () method.

Third, the JDBC Application Demonstration Example Experiment

Experiment: 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 a Java program, Use the JDBC connection to create a database (myuser) in MySQL to insert, UPDATE, delete, and query the staff table.

Lab environment and development tools: Win7 operating system; JDK1.6.0_26;XAMPP1.7.7(MySQL 5.1, phpMyAdmin); MyEclipse 8.5

the construction of the experimental environment: can take a test of my blog

• Java environment collocation: http://blog.csdn.net/cxwen78/article/details/6400798;
• Windows system XAMPP installation configuration using: http://blog.csdn.net/cxwen78/article/details/6847927

Experimental process and steps:

1 . Download MySQL support JDBC driver: If you already have one, you can skip this step. Go to the MySQL website (http://www.mysql.com/products/connector/) to download the driver, MySQL for different platforms to provide different connectors, we need to be DBC Driver for MySQL ( connector/j), for example, as seen, click Download Follow the site's boot 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) into the MySQL folder ( This is only available for convenience), for use when loading drivers.

2 . Create a database: Use phpMyAdmin, log in to MySQL, create a database myuser, and insert a table called staff. and add some data, operation steps, log in to MySQL database after:

1) Create a database with the name MyUser, encoded as UTF8_GENERAL_CI (support Chinese);

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

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

4) After the successful accession, view the staff table situation:

5 ) insert some experimental data into the table and insert two, one for the employee, Lucy , 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". After the successful creation, add the MySQL driver package (Mysql-connector-java-5.1.18-bin.jar) that you downloaded in step 1 to project build path, as seen in the join process:

4, write the JDBC connection MySQL database instance detailed code, Jdbc_test.java:

Detailed code:

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 a static global variable static Connection conn;static Statement st;public static void Main (string[] args) {insert ();//Insert Join record update ();//update record data Delete ();//delete record query ();// The query records and displays the}/* insert data record, and outputs the number of data records inserted */public static void Insert () {conn = getconnection ();//First to get the connection, that is, to connect to the database try {String sql = "in SERT into the staff (name, age, Sex,address, Depart, Worklen,wage) "+" VALUES (' Tom1 ', +-M ', ' China ', ' personnel ', ' 3 ', ' 3000 ') ";///INSERT Data SQL Statement St = (Statement) conn.createstatement ();//Create Statement object to run the static SQL statement int count = st.executeupdate (SQL); /Run the Insert operation SQL statement and return the number of inserted data System.out.println ("Insert" + Count + "bar data into the staff table");//output insert operation results conn.close ();//Close database connection} catch (SQLException e) {System.out.println ("Insert data Failed" + e.getmessage ());}} /* Updates the records that meet the requirements and returns the number of updated records */public static void Update () {conn = getconnection ();//The same first to get the connection, that is, to connect to the database try {String sql = "Update s TaFF set wage= ' 2200 ' WHERE name = ' Lucy ';//SQL statement to update data St = (Statement) conn.createstatement ();//Create a Statement object to run a static SQL statement , ST local variable int count = st.executeupdate (SQL),//SQL statement that runs the update operation, returns the number of update data System.out.println ("Update in staff table" + Count + "bar data");// Processing result of output update operation Conn.close ();//Close database connection} catch (SQLException e) {System.out.println ("Failed to update Data");}} /* Query the database, output a condition that meets the required records */public static void query () {conn = getconnection ();//The same first to get the connection, that is, to connect to the database try {String sql = "Select * FR OM staff ";//SQL statement querying Data St = (Statement) conn.createstatement ();//Create Statement object for running static SQL statements, ST local variable resultset rs = St.executequery (SQL),//Run the SQL query statement, return the result set of the query data System.out.println ("Last query result is:"), while (Rs.next ()) {//infer if there is a next data// Gets the corresponding value according to the field name String name = Rs.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");//output The values of the individual fields of the recorded 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 compliant records, output */public static void Delete () {conn = getconnection ();//The same first to get the connection, that is, to connect to the database try {String sql = "Delete from St AFF WHERE name = ' Lili ';//SQL statement to delete data St = (Statement) conn.createstatement ();//Create Statement object to run static SQL statement, ST local variable int Count = st.executeupdate (SQL),//Run the SQL DELETE statement, return the number of deleted data System.out.println ("Delete in staff table" + Count + "bar data \ n");// Output delete operation result Conn.close ();//Close database connection} catch (SQLException e) {System.out.println ("delete data Failed");}} /* Get database connection function */public static Connection getconnection () {Connection con = null;//Create a Connection object to connect to the database try { Class.forName ("Com.mysql.jdbc.Driver");//load MySQL data-driven con = drivermanager.getconnection ("Jdbc:mysql://localhost : 3306/myuser "," root "," root ");//Create Data connection} catch (Exception e) {System.out.println (" database connection Failed "+ e.getmessage ());} Return con;//Returns the database connection established}}

The project is deployed to server, and then the results are executed: