数据库-JDBC

简介

• Java DataBase Connectivity Java数据库连接
• JDBC是一套标准,是Java与各大数据库厂商共同定制的一套接口. 这套接口由各大数据库厂商进行了实现.

使用

• 使用步骤

1. 引入jar文件.
2. 加载数据库驱动 (JavaSE项目中可以省略 , JavaWeb项目必须编写此步骤)
Class.forName("com.mysql.jdbc.Driver");
3. 通过驱动管理器, 获取JDBC连接对象.
Connection conn = DriverManager.getConnection("数据库连接地址","帐号","密码");
// 数据库连接地址格式: 主协议:子协议://ip地址:端口号/数据库名称
// mysql的连接地址: jdbc:mysql://localhost:3306/java35
// oracle的连接地址: jdbc:oracle:thin:@localhost:1521:ORCL
4. 通过连接对象, 创建SQL执行对象 (SQL执行环境)
Statement state = conn.createStatement();
5. 通过SQL执行对象 ,执行SQL语句.
state.execute(String sql语句);
6. 释放资源
state.close();
conn.close();

• 案例

package com.java.demo;

import java.sql.*;

public class Demo1 {
    public static void createTable() throws ClassNotFoundException, SQLException {
        //1.    加载数据库驱动 (可以省略的.  在JavaEE的web项目中,jar包不可以省略.)
        //      mysql6以下驱动:com.mysql.jdbc.Driver
        //      mysql6和6以上驱动:com.mysql.cj.jdbc.Driver
        Class.forName("com.mysql.jdbc.Driver");
        //2.    通过驱动管理器, 获取JDBC的连接对象
        //      连接地址格式:
        //           主协议:子协议://ip地址:端口号/数据库名称
        //           mysql:     jdbc:mysql://localhost:3306/test
        //           oracle:    jdbc:oracle:thin:@ip地址:1521/ORCL
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123");
        //3.    通过连接对象,创建SQL执行对象 (SQL执行环境)
        Statement state = conn.createStatement();
        //4.    通过SQL执行对象 执行SQL
        state.execute("create table person(id int,nickname varchar(32))");
        //5.    释放连接
        state.close();
        conn.close();
    }
	//加上?useUnicode=true&characterEncoding=utf8防止乱码
    public static void insertData() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        //
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8", "root", "123");
        Statement state = conn.createStatement();
        state.execute("insert into person values(1,'张三')");
        state.close();
        conn.close();
    }
     public static void insertPerson() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "123");
        Statement state = conn.createStatement();
        int flag = state.executeUpdate("insert into person values(1,'张三'),(2,'李四'),(3,'王二麻子')");
        state.close();
        conn.close();
        System.out.println("返回:"+flag);
    }

    public static void deletePerson() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "123");
        Statement state = conn.createStatement();
        int flag = state.executeUpdate("delete from person where id=1");
        state.close();
        conn.close();
        System.out.println("返回:"+flag);
    }

    public static void updatePerson() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "123");
        Statement state = conn.createStatement();
        int flag = state.executeUpdate("update person set nickname='haha' where id=2");
        state.close();
        conn.close();
        System.out.println("返回:"+flag);
    }

    public static void selectPerson() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "123");
        Statement state = conn.createStatement();
        ResultSet rs = state.executeQuery("select * from person");
        while(rs.next()){
            int id = rs.getInt("id");
            String nickname = rs.getString("nickname");
            System.out.println("id="+id+"\tnickname="+nickname);
        }
        rs.close();
        state.close();
        conn.close();
    }
}

PreparedStatement 预编译的SQL执行环境

内部实现原理:
1. 将未拼接参数的SQL语句, 作为SQL指令, 先传递给数据库 进行编译.
2. 再将参数传递给数据库, 此时传递的参数不会再作为指令执行, 只会被当作文本存在.
操作流程与Statement基本一致:
1. 如何得到一个PreparedStatement 对象
PreparedStatement state = conn.prepareStatement("预编译的SQL语句");
2. 预编译的SQL语句如何编写
需要填充参数的位置, 使用?代替即可! 例如:
select id from xzk_user where username=? and password=?
3. 参数如何填充
state.setXXX(int index,XXX value);
setXXX中XXX指的是数据类型,
参数1: index : SQL语句中?的索引值 , 从1开始
参数2: value : 填充的参数值.
4. 如何执行填充完毕参数的SQL
- boolean execute();
- int executeUpdate();
- ResultSet executeQuery();

• 案例

	@Override
    public boolean findByPassword(String username, String password) {
        Connection conn = null;
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "123");
            //参数: 预编译的SQL语句, 参数部分使用?替代.
            state = conn.prepareStatement("select * from xzk_user where username=? and password=?");
            //向预编译的执行环境中, 加入参数的内容
            state.setString(1,username);
            //根据类型set+你传入的类型
            state.setString(2,password);
            //执行
            rs = state.executeQuery();
            return rs.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                rs.close();
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }
            try {
                state.close();
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }
        }
        return false;
    }