Shulou(Shulou.com)06/02 Report--

This article introduces you how to carry out JDBC analysis, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. The basic conditions of database programming

If you want to implement code to manipulate the database, the following conditions are your prerequisite for implementing it

Programming language:

For example, Java, C++, Python and so on, these languages can operate the database.

The operation of a database:

For example, I introduced the operation of MySQL in the previous chapter. To implement other databases such as Oracle, SQL Server and so on, we should also learn some operations corresponding to the database.

Install the database driver package:

Different databases provide different database driver packages corresponding to different programming languages, and these driver packages implement API for operating the corresponding databases.

2. Database programming of Java: JDBC

Because the API of different database manufacturers is actually different, so many languages encapsulate the API of these databases again, encapsulating a set of unified API. This allows you to manipulate multiple different databases with one set of code.

In Java, this encapsulation is accomplished by the Java standard library, which encapsulates a set of unified database API called JDBC.

Expand:

Java itself is a cross-platform language. Although different operating systems provide different API, Java itself encapsulates these API and provides a unified interface in the standard library, so Java can be compiled at once and run everywhere.

JDBC introduction:

JDBC, or Java Database Connectivity, refers to the Java database connection. Is a Java API used to execute SQL statements, which is the database connection specification in Java.

This API consists of classes and interfaces in the java.sql and javax.sql packages. It provides a standard API for Java developers to operate databases and provides unified access to a variety of relational databases.

Note:

JDBC API is included in the Java standard library and can be used directly, but the JDBC driver of MySQL is not included in the system, so it needs to be downloaded and installed.

The JDBC driver of MySQL is actually the concrete implementation of some classes and interfaces in JDBC API.

3. The hierarchical structure of JDBC accessing database

4. Introduction to MySQL database operation 5. Download and add MySQL driver package to the project

Since the JDBC driver of MySQL is not included in the system, it needs to be downloaded and installed.

The official websites of major databases have JDBC drivers for the corresponding databases, but here I recommend using some central repositories for downloading, such as mvnrepository

Steps to download the driver package:

Go to the mvnrepository website, search MySQL in the search bar, and you can find the following results

By selecting the first MySQL Connector/J, you can jump to the page selected by the download version

Choose the driver of your corresponding database version (the big version must correspond, but the small version can't be chosen at will). Since I am a 5.x series MySQL, I just choose the large version number 5. After selecting, you will skip to the final download page.

Click jar to start downloading (this jar package is to package some .class files in the driver package in the form of a compressed package)

After the download is complete, the driver package has been downloaded to your local area. Just introduce it into your project and you can use it.

Add the driver package to the project step (one method is described below):

First create a project with your own compiler (IDEA for my personal use, but other compiler methods are the same)

Create a new directory in your project with a random name (I personally use lib)

Add the downloaded driver package to this directory and CV it directly. It will appear after you have successfully added it.

Let's make the newly created directory with the driver package added into a library (right-click the directory in IDEA and select add as Library. That's fine.)

Once you have added the library, you can use the classes and methods in the driver package, and you are ready for database programming

6. Steps for using JDBC

6.1 create a database source and connect to Connection

Create a database source:

DataSource dataSource=new MysqlDataSource (); / / DataSource is an interface from the Java standard library, which is used to indicate "where is the database" / / MysqlDataSource is a driver package from MySQL, which is a class that implements the DataSource interface

Database is a server program, which can describe the address, port, user name, password, database name and so on of the server through DataSource.

Set the location information of the database to DataSource

/ 1) use a URL to indicate the connected database, ip, port, database name, encoding method, whether to encrypt ((MysqlDataSource) dataSource) .setURL ("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&setSSL=false"); / / 2) set the user name of logging in to the database ((MysqlDataSource) dataSource) .setUser ("root") / / 3) set the password to log in to the database ((MysqlDataSource) dataSource) .setPassword ("1234")

Since setURL, setUser and setPassword are all implemented by MysqlDataSource, you need to make a downward transition when using them.

The above URL is written in a fixed way, for example: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&setSSL=false

Jdbc means to use JDBC to access the database

Mysql indicates that the database accessed is MySQL

172.0.0.1 is the IP address, and the IP address is used to distinguish which host. The IP address of 172.0.0.1 represents the host that is currently in use, because my MySQL server is also installed on my own computer and can be modified by myself.

3306 is the port number, which is manually set when the database server is installed. The default is 3306, which is used to distinguish a program on the host.

Test is the name of the database to be accessed. This is in my database. Modify it by yourself.

CharacterEncoding=utf8 is used to specify the encoding method. Here you use utf8, which corresponds to the database you use and modify it by yourself.

UseSSL=false is used to indicate whether it is encrypted. If it is not encrypted, modify it on your own.

Connect to the database for real network communication:

Connection connection=dataSource.getConnection ()

Connection is part of the Java standard library, although it is also available in the MySQL driver. It is used to make a network connection with the database server

GetConnection is used to obtain a connection that attempts to establish a connection to the specified database URL, returning a Connection object if the connection is successful, and throwing an exception if it fails

Because getConnection may fail to connect (such as IP address, port, etc.), you need to throw an exception to the upper caller through throws or use try-catch to handle the exception when the method is declared

6.2 construct SQL statements in preparation for the actions performed

Construct a sql to be executed from a string

/ / for example, to perform a new element operation (table name student with two columns id and studentScanner scanner=new Scanner (System.in); System.out.print ("Please enter id:"); int id=scanner.nextInt (); System.out.print ("Please enter name:"); String name=scanner.next (); String sql= "insert into student values (?)"

Sql is the constructed SQL statement, which is the specific operation to be performed.

The semicolon may not be added in the sql statement

Represents a wildcard, which can be used to dynamically replace the content in the sql statement. Instead, use some methods of the PreparedStatement object later to replace it with the specific value you want to change

For example:

Void setInt (int paramenterIndex, int x): paramenterIndex indicates the specific position in the sql statement where the wildcard character is to be replaced (starting at 1), and x indicates that the specific value is to be replaced

Void setString (int parameterIndex, String x): paramenterIndex indicates the specific position in the sql statement where the wildcard character is to be replaced (starting at 1), and x indicates that the specific value is to be replaced

Through the prepareStatement (sql) method, the constructed string sql is transformed into a real database underlying SQL statement.

PreparedStatement statement=connection.prepareStatement (sql); / / through the setInt method, the first wildcard in the SQL statement is replaced with a specific value statement.setInt (1Magi id); / / through the setString method, the second wildcard in the SQL statement is replaced with a specific value statement.setString (2Cinnamename)

In JDBC, the connection object connection is established by using Connection and the database, so connection can call the prepareStatement (String sql) method to preprocess the SQL statement specified by the parameter sql, generate the underlying internal command of the database, and encapsulate the command in the PreparedStatement object.

6.3 execute SQL and process the result set

Execute SQL through the executeQuery or executeUpdate method of the PreparedStatement object

If you are performing a content change operation (add, modify, delete), use the int executeUpdate () method

The return result of int ret=statement.executeUpdate (); / / executeUpdate is the number of rows affected after the operation / / the number of rows affected can be displayed by printing the return value System.out.println ("ret:" + ret)

If you want to perform a query operation, use the ResultSet executeQuery () method

The returned result of ResultSet resultSet=statement.executeQuery (); / / executeQuery is the temporary table-like structure queried after performing the operation, which is stored in the ResultSet object / / and then we can traverse it, similar to iterator traversal, as follows: while (resultSet.next ()) {/ / suppose there are two columns id and name int id=resultSet.getInt (id); String name=resultSet.getString (name) System.out.println ("id=" + id+ ", name=" + name);}

The query operation of the SQL query statement to the database will return a ResultSet object, which consists of rows of data organized by column (field)

The ResultSet object can only see one row of data at a time, and using the next () method, you can move to the next row of data (similar to iTunes +)

You can use the getXxx () method of the ResultSet object to get the field. The common methods will be introduced later.

6.4 release resources

When we have finished executing our SQL statement, if we no longer use some objects, we need to close the connection and release the corresponding resources

/ / if there are ResultSet objects that do not need to be used, you need to close the connection resultSet.close (); / / after Connection does not need to be used, you need to close the connection connection.close (); / / after PreparedStatement does not need to use, you need to close this connection statement.close ()

ResultSet, Connection and PreparedStatement all correspond to some hardware resources of the machine, which should be returned in time if they are not used. It is similar to borrowing books. If people only borrow books in the library and do not return them, then the resources of books in the library will always be reduced.

These objects can use the close () method to close the connection between the client and the server, thus freeing up the occupied resources

6.5 JDBC programming template

Through the above five steps, you can do basic Java JDBC programming, although there is more than one way, but if you do not already know it, you can master this method. Finally, we summarize the whole template. According to it, we can use Java language to do various operations on MySQL database.

Public static void test () {/ / 1. Create a database source and connect to Connection DataSource dataSource=new MysqlDataSource (); ((MysqlDataSource) dataSource) .setURL ("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false"); ((MysqlDataSource) dataSource) .setUser ("root"); ((MysqlDataSource) dataSource) .setPassword ("1234"); Connection connection=dataSource.getConnection (); / / 2. Construct a SQL statement to prepare String sql= "; PreparedStatement statement=connection.prepareStatement (sql); / / 3 for the operation to take place. Execute SQL and process the result set int ret=statement.executeUpdate (); / / 4. Execution completed, release resources statement.close (); connection.close ();} 7. JDBC common interfaces and classes

The following interfaces and classes have been introduced a lot in the above JDBC programming steps, so they can complement each other.

7.1 DataSource and MysqlDataSource

Add:

The above code can also use MysqlDataSource directly to create a data source so that you don't have to use a downward transformation. But if you use DataSource, then other code in the code actually uses the DataSource type, which is a class that has nothing to do with the specific database type. When you need to switch databases, you don't need to modify the code significantly.

7.2 Connection

The Connection interface implementation class is provided by the database, and there are usually two ways to get Connection objects:

Method 1: get it through the DataSource (data source) object

/ / create a database source DataSource dataSource=new MysqlDataSource (); / set database details ((MysqlDataSource) dataSource) .setURL ("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false"); ((MysqlDataSource) dataSource) .setUser ("root"); ((MysqlDataSource) dataSource) .setPassword ("1234"); / / create database connection Connection connection=dataSource.getConnection ()

Method 2: get it through the static method of DriverManager (driver management class).

/ / load the JDBC driver Class.forName ("com.mysql.cj.jdbc.Driver"); / / create a database connection Connection connection=DriverManager.getConnection (url)

The MySQL database driver is encapsulated in the Driver class, whose package name is com.mysql.cj.jdbc, which is not a class in the Java runtime class library

The difference between the two ways mentioned above:

The Connection connection obtained by the DriverManager class cannot be reused. When resources are released after each use, the physical connection is closed through connection.close ().

DataSource provides support for connection pooling. Connection pooling creates a certain number of database connections during initialization, which can be reused. Each time the database connection is used and resources are released through connection.close (), the Connection connection object is recycled.

7.3 PreparedStatement

PreparedStatement is one of three objects provided by JDBC API that can send SQL statements to a database. Here is a brief introduction to these three.

Statement: used to execute a simple SQL without parameters

PreparedStatement:

Used to execute SQL statements with or without parameters

SQL statements will be precompiled in the database system

Executes faster than Statement objects

CallableStatement: calls to execute database stored procedures

7.4 ResultSet

The ResultSet object, called the result set, represents all rows that meet the criteria of the SQL statement, and it provides access to the data in those rows through a set of getXxx () methods

The data in ResultSet is arranged one by one. Whenever there are multiple fields and there is a record pointer, the data row that the pointer refers to is called the current data row, and we can only manipulate the current data row. If we want to get a record, we need to use the next () method of ResultSet, which can be combined with the while loop to traverse all the records in the ResultSet

Common ResultSet object methods:

Note:

The ResultSet object and the database connection object Connection implement a tight connection. Once the connection object is closed, the data in the ResultSet object will disappear immediately.

8. Java operates a database instance

8.1 add data to the table

Add students id and name to the student table:

Public static void testInsert () throws SQLException {DataSource dataSource=new MysqlDataSource (); ((MysqlDataSource) dataSource) .setURL ("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false"); ((MysqlDataSource) dataSource) .setUser ("root"); ((MysqlDataSource) dataSource) .setPassword ("1234"); Connection connection=dataSource.getConnection (); Scanner scanner=new Scanner (System.in); System.out.print ("Please enter id:"); int id=scanner.nextInt () System.out.print ("Please enter name:"); String name=scanner.next (); String sql= "insert into student values"; PreparedStatement statement=connection.prepareStatement (sql); statement.setInt (1jue id); statement.setString (2dint); int ret=statement.executeUpdate (); System.out.println ("ret:" + ret); statement.close (); connection.close ();} 8.2Delete the data in the table

In the table named student, delete the records in the student table that meet the id criteria:

Public static void testDelete () throws SQLException {DataSource dataSource=new MysqlDataSource (); ((MysqlDataSource) dataSource) .setURL ("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false"); ((MysqlDataSource) dataSource) .setUser ("root"); ((MysqlDataSource) dataSource) .setPassword ("1234"); Connection connection=dataSource.getConnection (); Scanner scanner=new Scanner (System.in); System.out.print ("Please enter the id to delete:") Int id=scanner.nextInt (); String sql= "delete from student where id=?"; PreparedStatement statement=connection.prepareStatement (sql); statement.setInt (1 ret id); int ret=statement.executeUpdate (); System.out.println ("ret:" + ret); statement.close (); connection.close ();} 8.3 modify the data in the table

In the table named student, modify the names of students who meet the id criteria:

Public static void testUpdate () throws SQLException {DataSource dataSource=new MysqlDataSource (); ((MysqlDataSource) dataSource) .setURL ("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false"); ((MysqlDataSource) dataSource) .setUser ("root"); ((MysqlDataSource) dataSource) .setPassword ("1234"); Connection connection=dataSource.getConnection (); Scanner scanner=new Scanner (System.in); System.out.print ("enter the student id you want to change:") Int id=scanner.nextInt (); System.out.print ("Please enter the student name you want to change:"); String name=scanner.next (); String sql= "update student set name=? Where id=? "; PreparedStatement statement=connection.prepareStatement (sql); statement.setString (1MagneName); statement.setInt (2Magneid); int ret=statement.executeUpdate (); System.out.println (" ret: "+ ret); statement.close (); connection.close ();} 8.4Lookup table data

Find all the data in the table named student:

Public static void testSelect () throws SQLException {DataSource dataSource=new MysqlDataSource (); ((MysqlDataSource) dataSource) .setURL ("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false"); ((MysqlDataSource) dataSource) .setUser ("root"); ((MysqlDataSource) dataSource) .setPassword ("1234"); Connection connection=dataSource.getConnection (); String sql= "select * from student"; PreparedStatement statement=connection.prepareStatement (sql); ResultSet resultSet=statement.executeQuery () While (resultSet.next ()) {int id=resultSet.getInt ("id"); String name=resultSet.getString ("name"); System.out.println ("id=" + id+ ", name=" + name);} resultSet.close (); statement.close (); connection.close () } this is the end of the analysis on how to carry out JDBC. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.