Java 数据库学习

链接JBDC

注意：如果IDEA右侧没有Database的，看看你的IDEA是不是CE的，CE的和后面h2不兼容。 点击+号选择h2之后弹出下面的界面，填写URL。运行下面代码会有url，名字自己起。

public class Main1{  
    public static void main(String[] args) throws SQLException, ClassNotFoundException {  
        File projectDir = new File(System.getProperty("baseDir"), System.getProperty("user.dir"));  
        String jdbcUrl = "jdbc:h2:file:" + new File(projectDir, "xdmla").getAbsolutePath();  
        System.out.println(jdbcUrl);  
    }  
}

URL填写好之后，测试链接（Test Connection），然后Apply, OK, 生成数据库。

新增一个表

生成一个基础的user表，来举例子： password 不能是明文，只是来举例子的。

create table users(  
    id bigint primary key auto_increment,  
    name varchar(100),  
    password varchar(100),  
    tel varchar(20),  
    avatar varchar(100),  
    created_at timestamp,  
    updated_at timestamp  
)

• create 创建
• bigint 大数字
• primary key 主键
• auto_increment 自增
• varchar 字符串（多长）
• timestamp 时间戳

数据库SQL语句不区分大小写，命名风格是下划线分割（snake case）。图片中user是关键字，换成users，不然报错。

插入语句

insert into USERS (name, password, tel, avatar, created_at, updated_at)  
values ( '李四', '12345665432', '1301111111', 'http://www.baidu.com', now(), now())

• insert into 往表里插入
• values 插入的值有哪些

删除数据

这个操作有危险，是删除users表中所有数据。

delete from USERS

指定删除哪条数据需要用where

delete from USERS where ID=1

修改数据

很多时候删除一条数据是危险的，用户找不回来了，我们在需求中一般用一个状态来表示。 接下来修改这个表，加入一个列：

alter table USERS add column status int default 1

这样就加入了status列，默认为1。 但是我想修改status数据为0：

update USERS set STATUS=0 where ID=2

这样status为0的数据就是用户不存在了，以后用户想要存在了就可以改这个字段

查询数据

基本的select用法，选择一条符合条件的数据

select id,NAME,TEL from USERS where AVATAR is null

上面代码的意思：选择 id name tel 从 users 表中，AVATAR 是 Null 的。

我想看某条数据有多少行

select count(*) from USERS where AVATAR is null

上面代码表示，AVATAR is null 的行数：

排序

想把数据排个序：

select * from USERS order by UPDATED_AT asc

order by 通过什么定制你想要的结果，asc是升序，desc是降序。 也可以先按照什么排序，再按照什么排序

select * from USERS order by UPDATED_AT, id, name asc

选出最大值

选出 id 最大的，当然字符串也能比较出最大的。

select max(id) from USERS

分页

limit(2) 每页两条数据

select * from USERS limit(2)

一对多的关系

用户和订单，每个用户多个订单。这时候需要外键（一个表的某个列是其他表的主键）。 注意：钱的存储数据格式是 decimal，十进制的表示。 创建一个订单的表：

create table orders  
(  
    id bigint primary key auto_increment,  
    name varchar(100),  
    user_id bigint,  
    created_at timestamp default now(),  
    updated_at timestamp default now(),  
    constraint order_pk primary key (id)  
);  
  
alter table ORDERS add column amount decimal default 0;
  
insert into ORDERS(name, user_id, AMOUNT)  
values ( '肥皂', 3, 10);  
insert into ORDERS(name, user_id, AMOUNT)  
values ( '水', 3, 15);  
insert into ORDERS(name, user_id, AMOUNT)  
values ( '水果', 2, 40 );

将users表中用户，下过单的都挑出来，按照注册时间倒序。

select * from USERS where id in (  
    select USER_ID from ORDERS  
) order by CREATED_AT desc;

上面代码表示从USERS表中 选出id 在 select USER_ID from ORDERS 这些id中。

用 java 如何查询

public class Main1{  
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        System.out.println(isCorrectPassword("1", "1"));  
        System.out.println(isCorrectPassword("李四", "1231231"));  
    }  
    // 判断用户名 密码 是否正确  
    public static boolean isCorrectPassword(String username, String password) throws SQLException {  
        File projectDir = new File(System.getProperty("baseDir"), System.getProperty("user.dir"));  
        String jdbcUrl = "jdbc:h2:file:" + new File(projectDir, "xdmla").getAbsolutePath();  
        Connection connection = DriverManager.getConnection(jdbcUrl);  

        Statement statement = connection.createStatement();  
        ResultSet resultSet = statement.executeQuery("select * from users where name = '" + username+"'" + "and password='" + password +"'");  
        return resultSet.next();  
    }  
}

当前是基础的代码进行查询，我们的密码当前有危险

SQL 注入

接下来进行攻破，攻破自己写的代码：

public static void main(String[] args) throws SQLException, ClassNotFoundException {
        System.out.println(isCorrectPassword("1", "1"));  
        System.out.println(isCorrectPassword("李四", "1231231"));
        System.out.println(isCorrectPassword("李四", "' or 1=1 --"));
}

第三条的打印数据本应该是false，才是我们期望的值，但是为true。

我们的sql最后的拼接是 ...password='' or 1=1 --，SQL注入：通过精心拼装的字符串攻破了你的数据库。

恐怖的是能够删除你数据库的记录....

预防 SQL 注入

public static boolean isCorrectPassword(String username, String password) throws SQLException {  
    File projectDir = new File(System.getProperty("baseDir"), System.getProperty("user.dir"));  
    String jdbcUrl = "jdbc:h2:file:" + new File(projectDir, "xdmla").getAbsolutePath();  
    try (  
        Connection connection = DriverManager.getConnection(jdbcUrl);  
        Statement statement = connection.prepareStatement("select * from users where name = ? and password = ?");  
    ) {  
        ResultSet resultSet = statement.executeQuery("select * from users where name = '" + username + "'" + "and password='" + password + "'");  
        return resultSet.next();  
    }  
}

connection.prepareStatement 能够预防SQL注入。

原理

原因是?相当于一个站位，当参数传进来时就会替换掉问号，而不会再次重新生成AST抽象语法树。

Docker 安装数据库

好处：

• 兼容
• 无残留
• 统一、方便

去安装docker之后，运行：docker run --name java-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=****** -d mysql:8 密码自己设置

• -p 3306:3306 : 将本地3306端口映射到docker的3306端口。本地访问3306之后自动访问到docker的3306端口上。

链接这个数据库

上面创建好了docker容器之后，本地点击 “+” 符号，采用MySQL。输入账号密码，看看你的端口号是不是3306。

创建使用数据库：mydb

show databases;  
create database mydb;  
show databases;  
use mydb;

安装 sequel pro 对本地数据库连接，展示本地数据库数据。可视化的对表进行增删改查操作。

数据持久化

现在我们的数据库是在docker上存储，我们本地是映射到docker上去的。 现在需要持久化，避免docker不存在了，数据还在。这就需要将docker映射到本地目录上。 由于MySQL版本最新的连接不上 sequel pro，所以我换成 postgressql。

docker run --name postgres -v `pwd`/pgdata:/var/lib/postgressql/data -p 3306:3306 -e POSTGRES_PASSWORD=qwe3518456 -d postgres:12

-v `pwd`/pgdata:/var/lib/postgressql/data 这个代码表示映射到本地绝对路径的pgdata 文件夹里 首先得创建这个文件夹，不然找不到。

数据库表设计原则

• 每个实体一张表：用户/商品等
• 每个实体一个主键
• 按照业务创建索引
• 每个关系用一张表联系

训练一下 select

limit 分页，接受两个参数，从第几个元素开始找、到底几个元素结束

group by xxx 选择xxx组，里面有多少个

select ORDERS.ID, ORDERS.USER_ID, ORDERS.ORDER_NUMBER, USERS.ID from ORDERS  
join USERS on USERS.ID = ORDERS.USER_ID

join xxx on 是一个加入操作，能够查找不同表的数据进行展示。能够展示交集，并集，或单独的左表，查找：

• www.w3schools.com/sql/sql_joi…
• www.w3schools.com/sql/sql_joi…
• www.w3schools.com/sql/sql_joi…
• www.w3schools.com/sql/sql_joi…
• www.w3schools.com/sql/sql_joi…
• www.w3schools.com/sql/sql_joi…

select count(distinct USER_ID) from ORDERS where ORDER_NUMBER = 1

上面代码表示：统计 ORDERS 表中 ORDER_NUMBER = 1 的 USER_ID 有多少个

总结

• 链接JBDC
• 学习SQL语句
• 本地数据库持久化