Summary of MySQL database JDBC programming knowledge points

This article brings you relevant knowledge about mysql, which mainly organizes the related issues of JDBC programming knowledge points. JDBC (Java DataBase Connectivity, java database connection) is a kind of The Java API that executes SQL statements can provide unified access to a variety of relational databases. It consists of a set of classes and interfaces written in the Java language. Let's take a look at it together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

This article will introduce JDBC programming, JDBC (Java DataBase Connectivity, java database connection ) is a Java API for executing SQL statements that can provide unified access to a variety of relational databases. It consists of a set of classes and interfaces written in the Java language.

Summary of MySQL database JDBC programming knowledge points.JDBC pre-workof MySQL database JDBC programming knowledge points>Summary of MySQL database JDBC programming knowledge points.Summary of MySQL database JDBC programming knowledge points Prepare the MySQL driver packageof MySQL database JDBC programming knowledge points>

We can download the MySQL jdbc driver package from the official website, or we can download it from the maven central warehouse. This What is the maven central warehouse? You can think of it as the "app store" software in our mobile phones. Its function is similar to that of the app store, except that the mobile app store contains mobile software, while the maven central warehouse contains many APIs and dependency packages.

Now that MySQL has been acquired by Oracle, Oracle's "food appearance" is a bit ugly. You can find it from the official website, but I feel that the maven central warehouse is easier to find, so we go to the maven central warehouse to download the jdbc driver package.

Website: maven central warehouse

The first step is to click on the website to enter the maven central warehouse.

The second step is to search for MySQL and select the one shown below.

The third step is to click to enter and find the jdbc driver package corresponding to the major version. If your MySQL is version 5, then choose version 5 for the driver package. Of course, if your MySQL is version Summary of MySQL database JDBC programming knowledge points, Then select version Summary of MySQL database JDBC programming knowledge points for your driver package.

My MySQL is version 5, so I choose the driver package with the largest version 5.

The fourth step is to click in and follow the steps below to download the driver package.

Once the download is complete our driver package is ready.

Summary of MySQL database JDBC programming knowledge points.Summary of MySQL database JDBC programming knowledge points Create a projectof MySQL database JDBC programming knowledge points>

Use the compiler to create a project. After the project is created, follow the steps below:

The first step is to create a directory with a random name. , might as well be called the lib directory.

The second step is to copy the downloaded driver package to this directory.

The third step is to set options, right-click the directory you just created and copied the driver package to, and find As a Lib...

# # In this way, our driver package will be imported into our project, and then we can write jdbc code.

Summary of MySQL database JDBC programming knowledge points.JDBC programming stepsof MySQL database JDBC programming knowledge points>Summary of MySQL database JDBC programming knowledge points.Summary of MySQL database JDBC programming knowledge points Create a data source DataSourceof MySQL database JDBC programming knowledge points>The first step is to create a

DataSource object to describe where the database is.

DataSource dataSource = new MysqlDataSource();

DataSource is a jdbc interface in java.sql, MysqlDataSource is the source and an implementation in the driver package we downloaded DataSourceInterface class.

The second step is to set the "location" (URL) of the database, the account name and password to log in to the database.

        //设置数据库所在地址
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://Summary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge points.0.0.Summary of MySQL database JDBC programming knowledge points:Summary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge points06/jdbctest/characterEncoding=utfSummary of MySQL database JDBC programming knowledge points&useSSL=false");
        //设置登录数据库的账户名
        ((MysqlDataSource)dataSource).setUser("root");
        //设置登录数据库的密码
        ((MysqlDataSource)dataSource).setPassword("Summary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge points56");

jdbc:mysql://Summary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge points.0.0.Summary of MySQL database JDBC programming knowledge points:Summary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge points06/jdbctest?characterEncoding=utfSummary of MySQL database JDBC programming knowledge points&useSSL=false is a URL, also called a unique resource locator.
jdbc:mysql means that the url is the URL for jdbc mysql. Because there are many databases, not just jdbc, it needs to be subdivided.

Specific meaning independent comparison:

URL type statement://database address/database name?Character set encoding&whether to encrypt.

Summary of MySQL database JDBC programming knowledge points.Summary of MySQL database JDBC programming knowledge points Connect to the databaseof MySQL database JDBC programming knowledge points>The third step is to establish a connection with the server. After creating the data source

DataSource object, call the getConnection()## of the object. #Method, obtain the java.sql.Connection object, thus establishing a connection with the database. <pre class="brush:php;toolbar:false">Connection connection = dataSource.getConnection();</pre> <hsummary of mysql database jdbc programming knowledge points>Summary of MySQL database JDBC programming knowledge points.Summary of MySQL database JDBC programming knowledge points构造并执行sql语句（插入操作为例）</hsummary>of MySQL database JDBC programming knowledge points><p>第四步，构造<code>sql语句字符串，并将该语句包装成PreparedStatement对象，即调用Connection对象的prepareStatement方法，参数是sql字符串，会返回一个PreparedStatement对象，然后我们再调用PreparedStatement对象中的executeUpdate方法或executeQuery方法执行sql语句。

我们先以插入操作为例。

        //操作数据库 关键在于构造sql语句
        //jdbc 构造的sql语句不需要带上;
        String sql = "insert into student values(Summary of MySQL database JDBC programming knowledge points, Summary of MySQL database JDBC programming knowledge points9;张三Summary of MySQL database JDBC programming knowledge points9;)";
        //将sql字符串包装成一个语句对象，表示待执行的sql的对象
        PreparedStatement statement = connection.prepareStatement(sql);

        //执行sql
        //如果待执行的sql语句操作是insert, update, delete,则使用executeUpdate方法执行,返回值为影响数据的行数
        //如果待执行的sql语句操作是select,则使用executeQuery方法执行
        int ret = statement.executeUpdate();

其中，如果待执行的sql语句操作是insert, update, delete,则使用executeUpdate方法执行，返回值为影响数据的行数，如果待执行的sql语句操作是select,则使用executeQuery方法执行，返回值是一个ResultSet结果表对象。

Summary of MySQL database JDBC programming knowledge points.Summary of MySQL database JDBC programming knowledge points及时释放资源of MySQL database JDBC programming knowledge points>

第五步，释放资源，我们执行完毕sql语句后需要及时地将资源释放，在JDBC编程中，最常见需要释放的类或接口有三个，分别是Connection，PreparedStatement，ResultSet，其中前面两个在jdbc插入操作中已经使用过了，而最后一个，即ResultSet，它是在执行查询语句需要用到的，调用executeQuery方法执行查询语句之后，会返回一个“临时表”，该“临时表”上储存了查询的结果，我们可以通过遍历该结果表来获取查询数据库的结果。

//此时sql语句已经执行完毕了，需要释放资源statement.close();connection.close();

使用jdbc编程进行插入操作全部代码：

import com.mysql.jdbc.jdbcSummary of MySQL database JDBC programming knowledge points.optional.MysqlDataSource;import javax.sql.DataSource;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.Scanner;public class TestJdbc {

    public static void main(String[] args) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        //设置数据库所在地址
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://Summary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge points.0.0.Summary of MySQL database JDBC programming knowledge points:Summary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge points06/jdbctest?characterEncoding=utfSummary of MySQL database JDBC programming knowledge points&useSSL=false");
        //设置登录数据库的账户名
        ((MysqlDataSource)dataSource).setUser("root");
        //设置登录数据库的密码
        ((MysqlDataSource)dataSource).setPassword("Summary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge pointsSummary of MySQL database JDBC programming knowledge points56");

        //建立连接
        Connection connection = dataSource.getConnection();

        //操作数据库 关键在于构造sql语句
        //jdbc 构造的sql语句不需要带上;
        String sql = "insert into student values(Summary of MySQL database JDBC programming knowledge points, Summary of MySQL database JDBC programming knowledge points9;张三Summary of MySQL database JDBC programming knowledge points9;)";
        //将sql字符串包装成一个语句对象，表示待执行的sql的对象
        PreparedStatement statement = connection.prepareStatement(sql);

        //执行sql
        //如果待执行的sql语句操作是insert, update, delete,则使用executeUpdate方法执行,返回值为影响数据的行数
        //如果待执行的sql语句操作是select,则使用executeQuery方法执行
        int ret = statement.executeUpdate();

        //此时sql语句已经执行完毕了，需要释放资源
        statement.close();
        connection.close();
    }}

运行结果，我们通过查询数据库的表的结果来进行观察：

mysql> create database jdbctest;Query OK, Summary of MySQL database JDBC programming knowledge points row affected (0.0Summary of MySQL database JDBC programming knowledge points sec)mysql> use jdbctest;Database changed
mysql> create table student(id int, name varchar(Summary of MySQL database JDBC programming knowledge points0));Query OK, 0 rows affected (0.0Summary of MySQL database JDBC programming knowledge points sec)mysql> show tables;+--------------------+| Tables_in_jdbctest |+--------------------+| student            |+--------------------+Summary of MySQL database JDBC programming knowledge points row in set (0.00 sec)-- 执行jdbc代码后查询mysql> select * from student;+------+--------+| id   | name   |+------+--------+|    Summary of MySQL database JDBC programming knowledge points | 张三   |+------+--------+Summary of MySQL database JDBC programming knowledge points row in set (0.00 sec)

程序运行结果，表示Summary of MySQL database JDBC programming knowledge points行受到了影响。

Summary of MySQL database JDBC programming knowledge points.5不要写死的代码（以插入为例）of MySQL database JDBC programming knowledge points>

我们发现我们的sql语句是完完全全写死的，这一点不好，我们可以使用输入的操作让用户输入信息进行插入，那就需要使用到Scanner类了。

我们得到用户输入的信息后，我们需要对信息进行整合，最容易想到的方式就是字符串拼接，但是这么做有如下的缺点，一是容易写错，特别是单双引号比较多的情况下，二是不安全，黑客可以使用sql注入的方式来修改数据库。

所以更好的方法就是把sql字符串中待输入的信息使用?来代替或者叫做占位，然后通过PreparedStatement中的setXXX系列方法来逐个设置每个?的内容是什么。

这个系列的方法，第一个参数表示sql待执行对象中第多少个?，第二个参数表示将这个?设置的内容。比如：

        //操作数据库 关键在于构造sql语句
        //jdbc 构造的sql语句不需要带上;
        String sql = "insert into student values(?, ?)";
        //将sql字符串包装成一个语句对象，表示待执行的sql的对象
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(Summary of MySQL database JDBC programming knowledge points, id);
        statement.setString(Summary of MySQL database JDBC programming knowledge points, name);

完整代码：jdbc插入操作源代码

运行结果：

数据库查询结果：

mysql> select * from student;+------+--------+| id   | name   |+------+--------+|    Summary of MySQL database JDBC programming knowledge points | 张三   ||    Summary of MySQL database JDBC programming knowledge points | 李四   |+------+--------+Summary of MySQL database JDBC programming knowledge points rows in set (0.00 sec)

Summary of MySQL database JDBC programming knowledge points.6JDBC删除，更新操作of MySQL database JDBC programming knowledge points>

在jdbc编程中，删除操作和更新操作，它的步骤与插入操作是一模一样的，只不过就是构造的sql语句不一样，其他都一样。

删除操作关键代码：

        //用户输入
        System.out.println("请输入需要删除的学号：");
        int id = sc.nextInt();

        //操作数据库 关键在于构造sql语句
        //jdbc 构造的sql语句不需要带上;
        String sql = "delete from student where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(Summary of MySQL database JDBC programming knowledge points, id);
        System.out.println("statement" + statement);

jdbc删除操作完整代码：源代码地址

程序运行结果：

数据库查询结果：

mysql> select * from student;+------+--------+| id   | name   |+------+--------+|    Summary of MySQL database JDBC programming knowledge points | 张三   |+------+--------+Summary of MySQL database JDBC programming knowledge points row in set (0.00 sec)

更新操作关键代码：

        //用户输入 需要修改的id 与修改后的姓名
        System.out.println("请输入需要修改的学号：");
        int id = sc.nextInt();
        sc.nextLine();
        System.out.println("请输入修改后的姓名：");
        String afterName = sc.nextLine();

        //操作数据库 关键在于构造sql语句
        //jdbc 构造的sql语句不需要带上;
        String sql = "update student set name = ? where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(Summary of MySQL database JDBC programming knowledge points, afterName);
        statement.setInt(Summary of MySQL database JDBC programming knowledge points, id);
        System.out.println("statement" + statement);

jdbc更新操作完整代码：源代码地址

程序运行结果：

数据库查询结果：

mysql> select * from student;+------+--------+| id   | name   |+------+--------+|    Summary of MySQL database JDBC programming knowledge points | 王五   |+------+--------+Summary of MySQL database JDBC programming knowledge points row in set (0.00 sec)

Summary of MySQL database JDBC programming knowledge points.Summary of MySQL database JDBC programming knowledge pointsJDBC查询操作of MySQL database JDBC programming knowledge points>

jdbc的查询操作与插入删除更新操作有一点点不同，执行查询sql语句的时候是调用executeQuery方法来执行语句的，并且会带回查询的结果，我们可以通过类似与迭代器的操作来获取查询的结果，这个结果集使用完后需要将它释放。

关键代码：

        //操作数据库 关键在于构造sql语句
        //jdbc 构造的sql语句不需要带上;
        String sql = "select * from student";
        PreparedStatement statement = connection.prepareStatement(sql);

        //执行sql 查询操作返回的不是int 而是一个临时表 使用ResultSet对象表示这个临时表
        ResultSet ret = statement.executeQuery();

        //便利结果集合
        //预期结果格式//        +------+--------+//        | id   | name   |//        +------+--------+//        |    Summary of MySQL database JDBC programming knowledge points | 王五   |//        +------+--------+
        while (ret.next()) {
            int id = ret.getInt("id");
            String name = ret.getString("name");
            System.out.println("id:" + id + ", name:" + name);
        }
        //关闭资源
        ret.close();
        statement.close();
        connection.close();

jdbc查询操作完代码：源代码地址

程序运行结果：

好了，JDBC的全部内容差不多就是这一些，所有的完整代码链接：地址。

推荐学习：mysql视频教程

The above is the detailed content of Summary of MySQL database JDBC programming knowledge points. For more information, please follow other related articles on the PHP Chinese website!