mysql的驱动包 mysql-connect-java

1. 内部封装了jdbc:

• jdbc(java database connectivity):本身是由一组接口组成 , 可以使得Java编译来访问各种数据库
• 无需自己实现接口,这些接口的实现类由第三方数据库厂商实现

jdbc的核心

接口或类	作用
DriverManager类	创建数据库的连接
Connection 接口	创建一个连接对象
Statement 接口	代表一条发送给服务器的sql语句
ResultSet接口	代表从服务器返回的查询结果集

2. jdbc四个参数
   • 用户名
   • 密码
   • 驱动类的全名 : com.mysql.jdbc.Driver
   • 连接字符串的url : mysql8.0以前 jdbc:mysql://真实ip:3306/database_name mysql8.0以后:jdbc:mysql://真实ip:3306/database_name?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=true

DriverManager 类 ,Connection接口 ,Statement接口

DriverManager类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
运行这一段要求:
1.导入对应数据库版本的mysql-connector-java 包
2.url要对应数据库版本的书写
**/
public class Demo01Jdbc {
    public static void main(String[] args) throws SQLException {
    //mysql8.0以后使用的url
        String url = "jdbc:mysql://localhost:3306/database_name?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=true";
        Connection connection = DriverManager.getConnection(url, "root", "root");
        System.out.println(connection);

//DriverManager内部静态方法
//  public static Connection getConnection(String url, java.util.Properties info)
//  public static Connection getConnection(String url,String user, String password)
//  public static Connection getConnection(String url)
//properties方式连接
//之所以可以使用properties是因为在DriverManager.getConnection()中也是创建properties对象
//只这里的properties对象通过setProperties()重新设置的键值对元素
//而getConnection()中设置好了键元素,只要求用户传入值即可

        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","root");

        Connection connection1 = DriverManager.getConnection(url, properties);
        System.out.println(connection1);
    }
}

• 为了切合java思想可以将封装为:

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCLink {
//连接本地jdbc:mysql://localhost:3306/database_name?...    可以省略为jdbc:mysql:///database_name?...
    private static final String url = "jdbc:mysql:///database_name?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=true";
    private static final String user = "root";
    private static final String password = "root";
    public static Connection connection;

    public static Connection getConnection() {
        return connection;
    }
    /**
    开始连接
    **/
    public static void startLink(){
        try {
            connection = DriverManager.getConnection(url, user, password);
            System.out.println(connection);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    
    /**
    结束释放资源
    **/
    public static void end(){
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

---

Statement接口

接口中的方法	描述
boolean execute(String sql)	执行任意sql语句,如果返回一个resultSet对象,则表示true,如果返回更新数或不存在任何结果,则为false
int executeUpdate(String sql)	用于执行增删改,返回受影响的行数
ResultSet executeQuery(String sql)	用于执行查询操作,查询到的结果集

创建表格:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo2DDL {
    public static void main(String[] args) throws SQLException {
        String sql = "create table stu(id int primary key auto_increment,name varchar(20) not null,sex boolean,birthday date)";

        JDBCLink.startLink();
        Connection connection = JDBCLink.getConnection();
        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
        JDBCLink.end();
        System.out.println("创建表成功");
    }
}

插入数据:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo02Insert {
    public static void main(String[] args) throws SQLException {

        JDBCLink.startLink();
        Connection connection = JDBCLink.getConnection();
        Statement statement = connection.createStatement();
        //更新和删除同样在这里操作
        int row = statement.executeUpdate("insert into stu (name,sex,birthday) values ('张三',1,'1999-2-10')," +
                "('李四',1,'1999-2-10'),('王五',1,'1999-2-10'),('赵六',1,'1999-2-10')");
        System.out.println(row);
        statement.close();
        JDBCLink.end();
    }
}

对应stu表的实体类:

import java.util.Date;
public class Stu {
    private int id;
    private String name;
    private boolean sex;
    private Date birthday;

    public Stu() {
    }

    public Stu(int id, String name, boolean sex, Date birthday) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.birthday = birthday;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public boolean isSex() {
        return sex;
    }

    public void setSex(boolean sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Stu{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex=" + sex +
                ", birthday=" + birthday +
                '}';
    }
}

查询:

import com.pojo.Stu;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

public class Demo03Select {
    public static void main(String[] args) throws SQLException {

        JDBCLink.startLink();
        Connection connection = JDBCLink.getConnection();

        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from stu");

        LinkedList<Stu> stus = new LinkedList<>();
        Stu stu = new Stu();
        while (resultSet.next()){
            stu.setId(resultSet.getInt("id"));
            stu.setName(resultSet.getString("name"));
            stu.setSex(resultSet.getBoolean("sex"));
            stu.setBirthday(resultSet.getDate("birthday"));
            stus.add(stu);
        }

        for (Stu stus1 : stus) {
            System.out.println(stus1);
        }

        statement.close();
        JDBCLink.end();
    }
}