Use JDBC to connect to MySQL database and perform data query operations

2017-10-30

Next article (use Java IO package for file read and write operations here, I'll summarize how to use JDBC to connect to the MySQL database and perform data query operations .

Scene

Use the JDBC connection database to query the data, return to the front end, and then use the JS plugin to further manipulate the data.

Examining Knowledge points

• JDBC Connection
• Give the plugin API to investigate the speed of learning new knowledge on the spot

Data

I use the database is Cjl_demo, the query table is Stu, the table has the data:

first give the code example

I will also host code on the cloud platform, can go to reference: Https://gitee.com/jinglun404/jdbc-demo

1 ImportJava.sql.*;2 3 /**4 * Use JDBC to query MySQL database example5  */6  Public classMain {7 8      Public Static voidMain (string[] args) {9 Ten         Try { One             //1. Reflection get MySQL Driver instance AClass.forName ("Com.mysql.jdbc.Driver"); -}Catch(ClassNotFoundException e) { -System.out.println ("Driver class not found, load driver failed!") "); the e.printstacktrace (); -         } -  -String url = "Jdbc:mysql://localhost:3306/cjl_demo"; +String username = "username"; -String password = "Password"; +         Try { A             //2. Driver instance->connection atConnection conn =drivermanager.getconnection (URL, username, password); -  -             //3.connection->statement -Statement stmt =conn.createstatement (); -  -             //4.statement->resultset inString sql = "SELECT * from Stu"; -ResultSet rs =stmt.executequery (SQL); to  +             //5. Get data through ResultSet -              while(Rs.next ()) { theString id = rs.getstring ("id"); *String name = rs.getstring ("name"); $String score = rs.getstring ("Score");Panax NotoginsengString sex = rs.getstring ("Sex"); -String Theclass = rs.getstring ("Class"); theSystem.out.println (ID + "--" + name + "--" + score + "--" + Sex + "--" +theclass); +                 /** A * 1--a--66--male--1 the * 2--b--82--female--2 + * 3--c--77--male--1 - * 4--d--90--female--2 $ * 5--e--90--female--1 $                  */ -             } -  the             //6. Turn off Resultset,statement,connection -             if(rs! =NULL) {Wuyi                 Try { the rs.close (); -}Catch(SQLException e) { WuSystem.out.println ("ResultSet" error when closing!) "); - e.printstacktrace (); About                 } $             } -             if(stmt! =NULL) { -                 Try { - stmt.close (); A}Catch(SQLException e) { +SYSTEM.OUT.PRINTLN ("statement" error when closing!) "); the e.printstacktrace (); -                 } $             } the             if(Conn! =NULL) { the                 Try { the conn.close (); the}Catch(SQLException e) { -SYSTEM.OUT.PRINTLN ("Connection" error when closing!) "); in e.printstacktrace (); the                 } the             } About  the}Catch(SQLException e) { theSYSTEM.OUT.PRINTLN ("Database connection failed! "); the e.printstacktrace (); +         } -     } the}

Description

Let's take a look at a diagram that summarizes the process of using JDBC to connect to database operations

It is not difficult to understand that JDBC connects to the database and is further operational, and the steps can be broadly summarized as:

1. Import the driver package for the appropriate database
2. Load the corresponding database-driven instance by reflection
3. Have DriverManager get Connection instance
4. Obtaining an Statement instance from an Connection instance
5. Manipulate the database through Statement instances and return ResultSet
6. Get data for a query by ResultSet
7. Turn off ResultSet, Statement, Connection

Note

In the summary above, I summarize the operation of using JDBC queries, and of course, in addition, JDBC can be used to insert data, update data operations.

The difference between the different operations is the use of different Statement methods:

1. ResultSet executeQuery(String sqlString): Executes the SQL statement that queries the database and returns a result set (ResultSet) object.
2. int executeupdate(String sqlString): Used to execute INSERT, UPDATE, or DELETE statements, as well as SQL DDL statements, such as CREATE TABLE and DROP TA BLE, and so on, returns the number of rows affected by the operation.
3. Boolean execute(sqlString): Returns a Boolean value for executing statements that return multiple result sets, multiple update counts, or a combination of both.

Reference website

Http://www.cnblogs.com/hongten/archive/2011/03/29/1998311.html

Http://www.cnblogs.com/lee/archive/2007/08/25/869656.html

Use JDBC to connect to MySQL database and perform data query operations