数据库学习之JDBC

简述

JDBC全称为：Java Data Base Connectivity (java数据库连接）

JDBC主要功能

（1）建立与数据库或者其他数据源的链接
（2）向数据库发送SQL命令
（3）处理数据库的返回结果

主要步骤

连接到数据库（Connection）
建立操作指令（Statement）
执行查询指令（executeQuery）
获得查询结果（ResultSet)

操作数据库

获取操作驱动

Class.forName("com.mysql.jdbc.Driver");

在此之前需要存在驱动的jar包
mysql-connector-java-5.1.39-bin.jar
在idea中需要引入这个包并添加为依赖

图片.png

选中自己的jar包所在的目录点击ok即可

打开一个连接

Connection conn = DriverManager.getConnection(DB_URL,USER,PASSWORD);

其中

static final String USER = "root";
static final String PASSWORD = "123456";
static final String DB_URL = "jdbc:mysql://localhost:3306/test";

User为mysql的用户名
password为mysql的密码
DB_url 为 jdbc:mysql: //localhost:3306/test
test为操作的数据库，3306为mysql的端口号
至此，在运行的时候报了一个SSL错误，错误信息为：

Sun Jan 27 18:28:46 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

通过错误信息可以看到需要设置SSL为false就行，
所以现在的DB_URL变更为

jdbc:mysql://localhost:3306/test?useSSL=false

此时不再出现错误

执行SQL

Statement stmt = conn.createStatement();

创建表

    String sql;
    sql = "CREATE TABLE `websites` (\n" +
                "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
                "  `name` char(20) NOT NULL DEFAULT '' COMMENT '名字',\n" +
                "  `age` int(3) NOT NULL DEFAULT 0,\n" +
                "  `sex` char(10) NOT NULL DEFAULT '0' COMMENT '性别',\n" +
                "  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',\n" +
                "  PRIMARY KEY (`id`)\n" +
                ") ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;";

    stmt.executeUpdate(sql);

在创建表的过程中需要注意sql语句的正确性
如果你的SQL 语句是诸如update,insert的更新语句，应该用statement的execute()方法
如果是查询语句，用的是statement的executeQuery()

插入数据

        sql = "INSERT into websites(name,age,sex,country) values (?,?,?,?)";
        Object[] my_info = {"monster",22,"男","中国"};
        PreparedStatement statement = conn.prepareStatement(sql);
        for(int i = 0;i<my_info.length;i++){
            if(my_info[i] instanceof String){
                statement.setString(i+1,(String) my_info[i]);
            }else {
                statement.setInt(i+1,(Integer) my_info[i]);
            }
        }
        statement.execute();

PreparedStatement 是继承自 Statement 的子类，拥有父类的所有功能，并且基本防止了SQL注入的危险

sql插入后

查询数据库

        sql = "SELECT id,name,age,sex,country FROM websites";
        ResultSet rs = statement.executeQuery(sql);
        while (true){
            if(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String country = rs.getString("country");

                System.out.println("id" + " : " + id);
                System.out.println("name" + " : " + name);
                System.out.println("age" + " : " + age);
                System.out.println("country" + " : " + country);
                System.out.println("*************************");
            }
        }

打印出来的数据

图片.png

sql 改删

这些都是执行sql语句，只要熟悉一下sql的语句编写就能够写了~

释放资源

最后要记得关闭 statement，conn的连接
调用
statement.close()
conn.close()

所有代码

import java.sql.*;
import java.util.Objects;

public class MyJdbc {
    static final String USER = "root";
    static final String PASSWORD = "123456";
    static final String DB_URL = "jdbc:mysql://localhost:3306/test?useSSL=false";
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(DB_URL,USER,PASSWORD);
        Statement stmt = conn.createStatement();
        String sql;
        sql = "CREATE TABLE `websites` (\n" +
                "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
                "  `name` char(20) NOT NULL DEFAULT '' COMMENT '名字',\n" +
                "  `age` int(3) NOT NULL DEFAULT 0,\n" +
                "  `sex` char(10) NOT NULL DEFAULT '0' COMMENT '性别',\n" +
                "  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',\n" +
                "  PRIMARY KEY (`id`)\n" +
                ") ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;";
        try{
            stmt.executeUpdate(sql);
        }catch (Exception e){
            e.printStackTrace();
        }
        sql = "INSERT into websites(name,age,sex,country) values (?,?,?,?)";
        Object[] my_info = {"angle",20,"女","美国"};
        PreparedStatement statement = conn.prepareStatement(sql);
        for(int i = 0;i<my_info.length;i++){
            if(my_info[i] instanceof String){
                statement.setString(i+1,(String) my_info[i]);
            }else {
                statement.setInt(i+1,(Integer) my_info[i]);
            }
        }
        statement.execute();

        sql = "SELECT id,name,age,sex,country FROM websites";
        ResultSet rs = statement.executeQuery(sql);
        while (true){
            if(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String country = rs.getString("country");

                System.out.println("id" + " : " + id);
                System.out.println("name" + " : " + name);
                System.out.println("age" + " : " + age);
                System.out.println("country" + " : " + country);
                System.out.println("*************************");
            }else{
                break;
            }
        }
        if(statement != null){
            stmt.close();
            statement.close();
        }
        if(conn != null){
            conn.close();
        }

    }
    
}