15.3 JDBC数据库编程

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语句。