1. Necessary conditions for database programming

Programming languages, such as Java, C, C, Python and other databases, such as Oracle, MySQL, SQL Server and other database driver packages: different databases correspond to different Programming languages ??provide different database driver packages. For example, MySQL provides the Java driver package mysql-connector-java. This driver package is required to operate MySQL based on Java. If you want to use Java to operate the Oracle database, you must use Oracle's database driver package ojdbc.

2. Java database programming: JDBC

Java Database Connectivity, referred to as JDBC, is a Java API used to connect to a database. Java API is used to execute SQL statements, which is a database connection specification in Java. This API consists of some classes and interfaces in the java.sql.*, javax.sql.* packages. It provides a standard API for Java developers to operate databases and can provide unified access to multiple relational databases.

3. Working Principle of JDBC

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

JDBC access database hierarchy:

4. Development environment setup

First check your MySQL version in the computer service, and then Enter the maven repository

Because my own version is after 5.0, I chose 5.1.47, and the major versions must be consistent

Just download the jar. Remember, the jar package cannot be decompressed.

The next step is to put the idea in the root directory. Create a folder under and then import the jar package

# # If there is no error and it continues to be OK, there will be no problem, indicating that the import is successful

5. JDBC programming in MySQL

1. Five-step process

Establishing a database connection

// 加載JDBC驅(qū)動(dòng)程序：反射，這樣調(diào)用初始化com.mysql.jdbc.Driver類，即將該類加載到JVM方法
區(qū)，并執(zhí)行該類的靜態(tài)方法塊、靜態(tài)屬性。
Class.forName("com.mysql.jdbc.Driver");
// 創(chuàng)建數(shù)據(jù)庫(kù)連接
Connection connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/test?
user=root&password=root&useUnicode=true&characterEncoding=UTF-8");

//MySQL數(shù)據(jù)連接的URL參數(shù)格式如下：
jdbc:mysql://服務(wù)器地址:端口/數(shù)據(jù)庫(kù)名?參數(shù)名=參數(shù)值

Create operation command (Statement)

Statement statement = connection.createStatement();

Execute SQL statement

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

Process the 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(String.format("Student: id=%d, sn=%s, name=%s, 
classesId=%s", id, sn, name, classesId));
       }

Release resources (close result set, command, connection)

//關(guān)閉結(jié)果集
if (resultSet != null) {
    try {
        resultSet.close();
   } catch (SQLException e) {
        e.printStackTrace();
   }
}
//關(guān)閉命令
if (statement != null) {
    try {
        statement.close();
   } catch (SQLException e) {
        e.printStackTrace();
   }
}
//關(guān)閉連接命令
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、創(chuàng)建DataSource對(duì)象
         DataSource dataSource = new MysqlDataSource();
        //設(shè)置相關(guān)內(nèi)容
        //URL User password
        //向下轉(zhuǎn)型                     訪問(wèn)數(shù)據(jù)庫(kù)   協(xié)議名        ip地址 要訪問(wèn)那個(gè)地址
        ((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、和數(shù)據(jù)庫(kù)連接.進(jìn)行后續(xù)連接
        //connect生命周期較短
        Connection connection = dataSource.getConnection();
        
        //3、拼裝SQL語(yǔ)句
        int id = 1;
        String name = "曹操";
        int class_id = 10;
        //?是一個(gè)占位符，可以把一個(gè)具體的變量的值替換到？
        String sql = "insert into text values(?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        //1 2 3相當(dāng)與？的下標(biāo)
        statement.setInt(1,id);
        statement.setString(2,name);
        statement.setInt(3,class_id);
        System.out.println("statement:" + statement);
 
        //4、執(zhí)行SQL語(yǔ)句
        int ret = statement.executeUpdate();
        System.out.println("ret:" + ret);
 
        //5、關(guān)閉相關(guān)資源
        //后創(chuàng)建的先釋放，順序不能錯(cuò)
        statement.close();
        connection.close();
 
    }
}

3. Query 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，創(chuàng)建實(shí)列
        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，數(shù)據(jù)庫(kù)連接
        Connection connection = dataSource.getConnection();
 
        //3,構(gòu)造SQL語(yǔ)句
 
        String sql ="select * from text";
        PreparedStatement statement = connection.prepareStatement(sql);
 
        //4,執(zhí)行SQL語(yǔ)句
        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,關(guān)閉相關(guān)資源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

4. Delete 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("請(qǐng)輸入要?jiǎng)h除學(xué)生的姓名：");
        String name = scanner.next();
        //1，創(chuàng)建實(shí)列
        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，數(shù)據(jù)庫(kù)連接
        Connection connection = dataSource.getConnection();
 
        //3,構(gòu)造SQL語(yǔ)句
        String sql = "delete from text where name = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,name);
 
        //4,執(zhí)行SQL
        int ret = statement.executeUpdate();
        if (ret == 1){
            System.out.println("刪除成功");
        }else {
            System.out.println("刪除失敗");
        }
 
        //5，關(guān)閉資源
        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 {
 
 
        //修改信息
        Scanner scanner = new Scanner(System.in);
        System.out.println("請(qǐng)輸入學(xué)生的id：");
        int id = scanner.nextInt();
        System.out.println("請(qǐng)輸入修改學(xué)生姓名：");
        String name = scanner.next();
 
        //1，創(chuàng)建實(shí)列
        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，數(shù)據(jù)庫(kù)連接
        Connection connection = dataSource.getConnection();
 
        //3,拼裝SQL
        String sql = "update text set name = ? where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,name);
        statement.setInt(2,id);
        //4,執(zhí)行SQL
        int set = statement.executeUpdate();
        if (set == 1){
            System.out.println("修改成功");
        }else {
            System.out.println("修改失敗");
        }
 
        //5，關(guān)閉資源
        statement.close();
        connection.close();
    }
}

The above is the detailed content of How to analyze JDBC programming in MySQL. For more information, please follow other related articles on the PHP Chinese website!