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

In this issue, the editor will bring you how to analyze the JDBC programming in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. The necessary conditions of database programming

Programming languages, such as Java,C, C++, Python, etc., and database driver packages, such as Oracle,MySQL,SQL Server, etc.: different databases provide different database driver packages corresponding to different programming languages. For example, MySQL provides Java driver package mysql-connector-java, which is needed to operate MySQL based on Java. Similarly, to operate the Oracle database based on Java, you need Oracle's database driver package ojdbc.

2. Database programming of Java: JDBC

JDBC, or Java Database Connectivity,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.*,javax.sql.* package, which provides a standard API for Java developers to operate databases and provides unified access to a variety of relational databases.

Third, the working principle of JDBC

DBC provides a unified access mode for a variety of relational databases. As a high-level abstraction for accessing API in a specific manufacturer's database, it mainly includes some general interface classes.

JDBC access database hierarchy:

Fourth, the construction of the development environment

First check your MySQL version in the computer service, and then enter the maven warehouse

Because my own version is after 5.0, so I choose 5.1.47, the big version should be consistent.

Just download jar, remember, the jar package cannot be unzipped

Next, create a folder under the root directory in idea itself, and then import the jar package

If there is no error, there will be no problem if you continue to OK, indicating that the import is successful.

5. JDBC programming in MySQL 1. Five-step process

Establish a database connection

/ / load the JDBC driver: reflection, so that the call initializes the com.mysql.jdbc.Driver class, which loads the class into the JVM method area and executes the static method blocks and static properties of the class. Class.forName ("com.mysql.jdbc.Driver"); / / create a database connection Connection connection = DriverManager.getConnection ("jdbc:mysql://localhost:3306/test?user=root&password=root&useUnicode=true&characterEncoding=UTF-8"); / / the URL parameter format of the MySQL data connection is as follows: jdbc:mysql:// server address: Port / database name? Parameter name = parameter valu

Create Action Command (Statement)

Statement statement = connection.createStatement ()

Execute SQL statement

ResultSet resultSet= statement.executeQuery ("select id, sn, name, qq_mail, classes_id from student")

Working with result set

While (resultSet.next ()) {int id= resultSet.getInt ("id"); String sn= resultSet.getString ("sn"); String name= resultSet.getString ("name"); int classesId= resultSet.getInt ("classes_id"); System.out.println ("Student: id=%d, sn=%s, name=%s, classesId=%s", id, sn, name, classesId)) }

Release resources (close result sets, commands, connections)

/ / close the result set if (resultSet! = null) {try {resultSet.close ();} catch (SQLException e) {e.printStackTrace ();}} / / close the command if (statement! = null) {try {statement.close ();} catch (SQLException e) {e.printStackTrace () }} / / close the connection command if (connection! = null) {try {connection.close ();} catch (SQLException e) {e.printStackTrace ();}} 2, add information

First create a database and create a table

Create database java122; create table text (id int,name varchar (5), class_id int); import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;import javax.sql.DataSource;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException; public class TextJDBC {/ / DataSource / / Connection / / PrepareStatement public static void main (String [] args) throws SQLException {/ / 1, create DataSource object DataSource dataSource = new MysqlDataSource () / / set relevant content / / URL User password / / downward transformation access database protocol name ip address to access that address ((MysqlDataSource) dataSource) .setURL ("jdbc:mysql://127.0.0.1:3306/java122?characterEncoding=utf-8&useSSL=false"); ((MysqlDataSource) dataSource) .setUser ("root") ((MysqlDataSource) dataSource) .setPassword ("180210"); / / 2, connect with the database. Connect / / connect with a shorter life cycle Connection connection = dataSource.getConnection (); / / 3. Assemble the SQL statement int id = 1; String name = "Cao Cao"; int class_id = 10; / /? Is a placeholder that replaces the value of a specific variable to? String sql = "insert into text values"; PreparedStatement statement = connection.prepareStatement (sql); / / 1 2 3 is equivalent to? Subscript statement.setInt (1); statement.setString (2); statement.setInt (3); System.out.println ("statement:" + statement); / / 4. Execute the SQL statement int ret = statement.executeUpdate (); System.out.println ("ret:" + ret) / / 5. Close related resources / / and release them first after creation. The order cannot be wrong: statement.close (); connection.close ();}}

3. Query the information import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import javax.sql.DataSource;import javax.xml.transform.Source;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Scanner; public class Text1 {public static void main (String [] args) throws SQLException {/ / 1, and create a solid column DataSource dataSource = new MysqlDataSource () ((MysqlDataSource) dataSource) .setURL ("jdbc:mysql://127.0.0.1:3306/java122?characterEncoding=utf-8&useSSL=false"); ((MysqlDataSource) dataSource) .setUser ("root"); ((MysqlDataSource) dataSource) .setPassword ("180210"); / / 2, database connection Connection connection = dataSource.getConnection (); / / 3, construct the SQL statement String sql = "select * URL" PreparedStatement statement = connection.prepareStatement (sql); / / 4, execute the SQL statement ResultSet resultSet = statement.executeQuery (); while (resultSet.next ()) {int id = resultSet.getInt ("id"); String name = resultSet.getString ("name"); int class_id = resultSet.getInt ("class_id") System.out.println ("id:" + id + "name:" + name + "class_id:" + class_id);} / / 5, close related resources resultSet.close (); statement.close (); connection.close ();}}

4. Delete the information import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import javax.sql.DataSource;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.Scanner; public class Textur2 {public static void main (String [] args) throws SQLException {Scanner scanner = new Scanner (System.in); System.out.println ("Please enter the name of the student to be deleted:"); String name = scanner.next () / / 1, create the actual column DataSource dataSource = new MysqlDataSource (); ((MysqlDataSource) dataSource) .setURL ("jdbc:mysql://127.0.0.1:3306/java122?characterEncoding=utf-8&useSSL=false"); ((MysqlDataSource) dataSource) .setUser ("root"); ((MysqlDataSource) dataSource) .setPassword ("180210"); / / 2, database connection Connection connection = dataSource.getConnection () / / 3, construct the SQL statement String sql = "delete from text where name =?"; PreparedStatement statement = connection.prepareStatement (sql); statement.setString (1 statement.executeUpdate name); / / 4, execute SQL int ret = statement.executeUpdate (); if (ret = = 1) {System.out.println ("deleted successfully") } else {System.out.println ("deletion failed");} / / 5, close the resource statement.close (); connection.close ();}}

5. Modify information import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import javax.sql.DataSource;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.Scanner; public class Text4 {public static void main (String [] args) throws SQLException {/ / modify information Scanner scanner = new Scanner (System.in); System.out.println ("Please enter the student's id:") Int id = scanner.nextInt (); System.out.println ("Please enter and modify the student name:"); String name = scanner.next (); / / 1, create the actual column DataSource dataSource = new MysqlDataSource (); ((MysqlDataSource) dataSource) .setURL ("jdbc:mysql://127.0.0.1:3306/java122?characterEncoding=utf-8&useSSL=false") ((MysqlDataSource) dataSource) .setUser ("root"); ((MysqlDataSource) dataSource) .setPassword ("180210"); / / 2, database connection Connection connection = dataSource.getConnection (); / / 3, assemble SQL String sql = "update text set name =? Where id =? "; PreparedStatement statement = connection.prepareStatement (sql); statement.setString (1MagneName); statement.setInt (2Magneid); / / 4, execute SQL int set = statement.executeUpdate (); if (set = = 1) {System.out.println (" modified successfully ");} else {System.out.println (" modified failed ") } / / 5, close resource statement.close (); connection.close ();}}

This is how to analyze the JDBC programming in MySQL shared by Xiaobian. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.