15.3 JDBC数据库编程-CSDN博客

本文链接：https://blog.csdn.net/haozihua/article/details/142323801

15.3 JDBC数据库编程

15.3.1 创建数据库和表

创建一个名为webstore的数据库，并向其中添加数据，代码如下:

1.创建数据库

CREATE TABLE products(
id int PRIMARY KEY,
pname VARCHAR(20)
brand VARCHAR(20),
price FLOAT(7,2),
stock SMALLINT,
)

2.创建products表

INSERT INTO products (id,pname,brand,price,stock)VALUES(101,'数码相机','奥林巴斯',1330.00,);
INSERT INTO products (id,pname,brand,price,stock)VALUES(102,'平板电脑','苹果',1990.00,5);
INSERT INTO products (id,pname,brand,price,stock)VALUES(103,'笔记本电脑','Lenovo',4900.00,);
INSERT INTO products (id,pname,brand,price,stock)VALUES(104,'苹果7splus','苹果',5300.00,5);
INSERT INTO products (id,pname,brand,price,stock)VALUES(105,'台式计算机','戴尔',4500.00,10);

15.3.2 创建数据库和表

使用Maven项目，从中获取到Mysql驱动，目前使用的驱动版本为8.0.39

Pom.xml文件相关配置如下：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.haozihua</groupId>
    <artifactId>Mysql</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>


    </dependencies>

</project>

下面程序是访问products数据库的所有信息：

import java.sql.*;

/**
 * @package: PACKAGE_NAME
 * @className: JDBCconnect
 * @Description:
 * @author: haozihua
 * @date: 2024/9/6 19:53
 */
public class JDBCselect {
    public static void main(String[] args) throws ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/webstore?useSSL=true";
        String name = "root";
        String password = "root";
        String sql = "select * from products";
        try (Connection conn = DriverManager.getConnection(url, name, password);
             Statement stmt = conn.createStatement();

        ) {
            ResultSet rst = stmt.executeQuery(sql);
            while (rst.next()) {
                System.out.println(rst.getInt(1)
                        + "\t"
                        + rst.getString(2)
                        + "\t"
                        + rst.getString(3)
                        + "\t"
                        + rst.getDouble(4)
                        + "\t"
                        + rst.getInt(5));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

除查询外，使用Java程序还可以执行各种SQL语句操作数据库，例如，执行CREATE等DDL语句，执行插入、删除、修改等DML语句。