MySql之原生JDBC的使用

JDBC（Java DataBase Connectivity,java数据库连接）

• 是一种用于执行SQL语句的Java API
• 是Java访问数据库的标准规范
• 由一组用Java语言编写的接口和类组成
• 它需要连接驱动

一.案例演示

1.测试前准备

• ①MySql数据库一枚
• ②database名为batistest的一枚

• 

• ③项目工程:

2.代码展示

***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.howie</groupId>
    <artifactId>native_JDBC</artifactId>
    <version>1.0-SNAPSHOT</version>
    
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>
</project>

***User.java

package com.howie.pojo;

import java.util.Date;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 17:18
 * @Version 1.0
 */
public class User {

    private int id;
    private String username;
    private int sex;
    private Date birthday;
    private String address;

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public int getSex() {
        return sex;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

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

***GetData.java

package com.howie;

import com.howie.pojo.User;
import com.mysql.jdbc.Driver;

import java.sql.*;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 16:56
 * @Version 1.0
 */
public class GetData {

    public static void main(String[] args) throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        String urlMySql = "jdbc:mysql://localhost:3306/batistest?characterEncoding=utf8";
        String usernameMySql = "root";
        String passwordMySql = "root";
        Connection connection = DriverManager.getConnection(urlMySql, usernameMySql, passwordMySql);
        //3.获取sql语句执行对象
        Statement statement = connection.createStatement();
        //4.获取结果集
        ResultSet resultSet = statement.executeQuery("SELECT * FROM `user`");
        //4.1遍历结果集
        while (resultSet.next()){
            //4.2封装对象
            User user = new User();
            int id = resultSet.getInt("id");
            user.setId(id);
            String username = resultSet.getString("username");
            user.setUsername(username);
            int sex = resultSet.getInt("sex");
            user.setSex(sex);
            Date birthday = resultSet.getDate("birthday");
            user.setBirthday(birthday);
            String address = resultSet.getString("address");
            user.setAddress(address);
            System.out.println(user);
        }
        //5.关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

3.测试

执行结果如下:

User{id=1, username='张三', sex=1, birthday=2018-07-10, address='北京'}
User{id=2, username='李四', sex=1, birthday=2018-07-10, address='上海'}
User{id=3, username='王五', sex=1, birthday=2018-07-10, address='广州'}
User{id=4, username='王六', sex=1, birthday=2018-07-10, address='深圳'}

二.总结:

当然,上面只演示了利用JDBC如何对数据库进行查询,增删改的方法如下:

int executeUpdate(String sql)

使用原生JDBC的重要步骤如下:

1. 注册驱动
2. 获取连接
3. 获取sql执行者
4. 执行sql语句并获取结果集
5. 关闭资源