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驅動程序：反射，這樣調用初始化com.mysql.jdbc.Driver類，即將該類加載到JVM方法
區(qū)，并執(zhí)行該類的靜態(tài)方法塊、靜態(tài)屬性。
Class.forName("com.mysql.jdbc.Driver");
// 創(chuàng)建數(shù)據(jù)庫連接
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://服務器地址:端口/數(shù)據(jù)庫名?參數(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)

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