JDBC connection to MySQL database and example _ MySQL

JDBC connection to the MySQL database and example DBC is a technology developed by Sun that can connect to the database using Java language.

I. basic JDBC knowledge

JDBC (Java Data Base Connectivity, java database connection) is a Java API used to execute SQL statements. it can provide unified access to multiple relational databases, it consists of a group of classes and interfaces written in Java. JDBC provides a standard API for database developers to build more advanced tools and interfaces so that database developers can use pure Java APIs to write database applications, it can run across platforms and is not restricted by database vendors.

1. cross-platform running:This inherits the "one-time compilation, running everywhere" feature of the Java language;

2. not restricted by database vendors:Cleverly, JDBC has two interfaces. one is for the application layer, which enables developers to call the database and process results through SQL without considering the database provider; the other is the driver layer, which processes the interaction with a specific driver. The JDBC driver can use the jdbc api to create a bridge between the Java program and the data source. The application can be moved to various drivers only once. Sun provides a driver manager. the drivers provided by the database vendors, such as MySQL and Oracle, can be identified and work properly if they meet the requirements of the driver manager. Therefore, JDBC is not restricted by database vendors.

JDBC APIs can be used as links to connect Java applications to various relational databases, which brings convenience and negative effects. The following are the advantages and disadvantages of JDBC.Advantages:

• Easy to operate:JDBC eliminates the need for developers to use complex drives to call commands and functions;

• High portability:JDBC supports different relational databases, so the same application can support access from multiple databases, as long as the corresponding driver is loaded;

• Good versatility:The JDBC-ODBC bridge driver replaces the JDBC function with ODBC;

• Object-oriented:Common JDBC database connections can be encapsulated into a class and called directly when used.

Disadvantages:

• The speed of accessing data records is affected to a certain extent;

• It is difficult to change the data source: JDBC supports multiple databases, and operations between different databases must be different, which makes it very difficult to change the data source.

2. process and principle of connecting to the database through JDBC

1,Load the driver of the specified database in the development environment. For example, in the following experiment, the database used is MySQL, so you need to download the JDBC-supported MySQL driver (the latest is: mysql-connector-java-5.1.18-bin.jar); and the development environment is MyEclipse, load the downloaded driver into the development environment (the detailed example shows how to load the driver ).

2,Load the driver in a Java program. In Java programs, you can use "Class. forName ("specify the database driver") to load the driver added to the development environment. for example, the code for loading the MySQL data driver is: 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 acts between the Java program and the JDBC driver to check whether the loaded driver can establish a connection. then, based on the URL, user name, and password of the database, it uses the getConnection method, create a JDBC Connection object. For example, Connection connection = DriverManager. geiConnection ("URL for database Connection", "username", "password "). URL = protocol name + IP address (domain name) + Port + database name; user name and password are the user name and password used to log on to the database. The connection code for creating a MySQL database is as follows:

Connection connectMySQL = DriverManager. geiConnection ("jdbc: mysql: // localhost: 3306/myuser", "root", "root ");

4,Create a Statement object: the object used to execute a static SQL Statement and return the result it generates. You can use the createStatement () method of the Connection object to create a Statement object. For example, Statement statament = connection. createStatement (); the code for creating a Statement object is as follows:

Statement statamentMySQL = connectMySQL. createStatement ();

5,Call the relevant methods of the Statement object to execute the corresponding SQL Statement: Use the execuUpdate () method to update data, including insert and delete operations, such as inserting a piece of data code into the staff table:

Statement. excuteUpdate ("insert into staff (name, age, sex, address, depart, worklen, wage)" + "VALUES ('tom1', 321, 'M', 'China ', 'personnel ', '3', '123 ')");

You can call the executeQuery () method of the Statement object to query data. the result of the query gets the ResulSet object. ResulSet indicates the set of data returned after the database is queried, the ResulSet object has a pointer that can point to the current data row. Use the next () method of the object to direct the pointer to the next row, and then retrieve the data by column number or field name. If the next () method returns null, it indicates that no data exists in the next row. The sample code is as follows:

ResultSet resultSel = statement.exe cuteQuery ("select * from staff ");

6,Close database Connection: when the database is used up or you do not need to access the database, close the data Connection in time using the close () method of Connection.

III. JDBC application example experiment

Lab content:Use phpMyAdmin to create a database (myuser) in MySQL and add the data required for the experiment (create a staff table and add some records); write a Java program, use JDBC to connect to a database (myuser) created 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

Lab environment setup:Refer to my blog

• Java environment: http://blog.csdn.net/cxwen78/article/details/6400798;

• Windows XAMPP installation configuration use: http://blog.csdn.net/cxwen78/article/details/6847927

Experiment process and steps:

1,Download the JDBC-supported MySQL driver: skip this step if you already have one. Go to the MySQL official website (http://www.mysql.com/products/connector/) to download the Driver, MySQL provides different connectors for different platforms, we need DBC Driver for MySQL (Connector/J), as shown in, click Download to follow the website guide for Download. Open the downloaded package (mysql-connector-java-5.1.18.zip), copy the Java package (mysql-connector-java-5.1.18-bin.jar) to the MySQL Directory (put here only for convenience) for loading the driver.

2,Create a database: use phpMyAdmin to log on to MySQL, create a database myuser, and insert a table named staff into it. And add some data, Operation steps, after logging on to the MySQL database:

1) create a database named myuser, encoded as utf8_general_ci (Chinese supported );

2) create a new table named staff. The table has 8 fields;

3) set 8 fields, including name, type, value length, initial value, encoding, and so on (click to view the big picture );

4) after the table is added, view the status of the staff table:

5) insert some data required for the experiment into the table. you need to insert two data entries, one for employee lucy and the other for lili:

3,Create a project in MyEclips and add the MySQL driver to the project: the created project type can be Java project or Java Web project. The Web project is created here. the project name can be obtained at will. I name it JavaWebChp07 ". After the creation is successful, add the MySQL driver package (mysql-connector-java-5.1.18-bin.jar) downloaded in step 1 to the Build path of the project, adding process:

4,Compile the code for connecting JDBC to the MySQL database, JDBC_Test.java:

Code:

[Java]View plaincopy

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 static 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 inserted data records */public static void insert () {conn = getConnection (); // first obtain the connection, connect to the database try {String SQL = "INSERT INTO staff (name, age, sex, address, depart, worklen, wage)" + "VALUES ('tom1 ', 32, 'M', 'China', 'personnel ', '3', '000000') "; // SQL Statement st = (Statement) conn for data insertion. createStatement (); // create the Statement object int count = st.exe cuteUpdate (SQL) for executing static SQL statements; // SQL Statement for executing the insert operation, and return the number of inserted data. out. println ("insert to the staff table" + count + "items"); // output the insert operation result conn. close (); // close the database connection} catch (SQLException e) {System. out. println ("failed to insert data" + e. getMessage () ;}}/* update the records that meet the requirements, and return the number of updated records */public static void update () {conn = getConnection (); // Obtain the connection first, that is, connect to the database try {String SQL = "update staff set wage = '000000' where name = 'Lucy '"; // SQL Statement st = (Statement) conn for updating data. createStatement (); // create a Statement object used to execute static SQL statements. The st local variable int count = st.exe cuteUpdate (SQL); // the SQL Statement used to execute the update operation, returns the number of updated data. out. println ("updates in the staff table" + count + "items"); // outputs the conn processing result of the update operation. close (); // close the database connection} catch (SQLException e) {System. out. println ("failed to update data") ;}/ * queries the database and outputs records meeting the requirements */public static void query () {conn = getConnection (); // Obtain the connection first, that is, connect to the database try {String SQL = "select * from staff"; // SQL Statement st = (Statement) conn for data query. createStatement (); // create a Statement object used to execute static SQL statements. The st is the local variable ResultSet rs = st.exe cuteQuery (SQL); // execute the SQL query Statement, returns the result set System of the queried data. out. println ("The final query result is:"); while (rs. next () {// determine whether another data exists. // obtain 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"); // output the value of each field of the record found: System. out. println (name + "" + age + "" + sex + "" + address + "" + depart + "" + worklen + "" + wage);} conn. close (); // close the database connection} catch (SQLException e) {System. out. println ("failed to query data") ;}/ * delete records that meet the requirements, output information */public static void delete () {conn = getConnection (); // get the connection first, that is, connect to the database try {String SQL = "delete from staff where name = 'Lili'"; // SQL Statement st = (Statement) for data deletion) conn. createStatement (); // create a Statement object for executing static SQL statements. The st local variable int count = st.exe cuteUpdate (SQL); // execute the SQL deletion Statement, returns the number of data deleted. out. println ("delete from the staff table" + count + "data entries/n"); // output the results of the delete operation conn. close (); // close the database connection} catch (SQLException e) {System. out. println ("failed to delete data") ;}/ * function for retrieving database connections */public static Connection getConnection () {Connection con = null; // create the Connection object try {Class for database Connection. forName ("com. mysql. jdbc. driver "); // load Mysql data Driver con = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/myuser", "root", "root"); // create a data connection} catch (Exception e) {System. out. println ("database connection failed" + e. getMessage () ;}return con; // return the database connection established }}