JDBC

一、JDBC基本概念

概念：Java DataBase Connectivity

1. JDBC是Java DataBase Connectivity的缩写，Java数据库连接，也就是Java语言操作数据库。
2. JDBC的本质：是sun公司定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套借口提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码是jar包当中的实现类。

二、快速入门

步骤

1、导入驱动jar包

• 在项目当中创建libs目录，并将mysql-connector-java-5.1.48-bin.jar文件复制到libs目录下
• 右键libs文件夹，选择add as library

2、注册驱动

	Class.forName("com.mysql.jdbc.Driver"); //调用静态代码块

3、 获取数据库连接对象 Connection

/**
 *url:指明数据库的名称
 *user:用户名
 *password:密码
 */
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/city", "root", "root");

4、定义sql

//同sql语句
String sql = "UPDATE employee SET age = 22 WHERE NAME = '张三'";

5、 获取执行sql语句的对象 statement

Statement statement = connection.createStatement();

6、 执行sql，接收返回结果

//结果是整型
int count = statement.executeUpdate(sql);

7、 处理结果

//仅仅是代表一个步骤,实际上不是光打印count这么简单
System.out.println(count);

8、 释放资源

//close()方法释放资源
statement.close();
connection.close();

整体代码段

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

public class JDBCDemo {
    public static void main(String[] args) throws Exception {
        // 1.导入驱动jar包 mysql-connector-java-5.1.48-bin.jar
        // 2.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 3.获取数据库连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/city", "root", "cjd510510");
        // 4.定义sql语句
        String sql = "UPDATE employee SET age = 22 WHERE NAME = '张三'";
        // 5.获取执行sql的对象
        Statement statement = connection.createStatement();
        // 6.执行sql
        int count = statement.executeUpdate(sql);
        // 7.处理结果
        System.out.println(count);
        // 8.释放资源
        statement.close();
        connection.close();
    }
}

三、对JDBC中各个接口和类的详细解析

DriverManager：驱动管理对象

1、注册驱动

	// DriverManager当中的注册驱动的方法
	public static synchronized void registerDriver(java.sql.Driver driver)
	       throws SQLException {
	
	       registerDriver(driver, null);
	}
	// 在com.mysql.jdbc.Driver类当中存在静态代码块
	// 静态块当中的代码将会调用DriverManager的registerDriver方法
	public class Driver extends NonRegisteringDriver implements java.sql.Driver {
	    public Driver() throws SQLException {
	    }
	
	    static {
	        try {
	            DriverManager.registerDriver(new Driver());
	        } catch (SQLException var1) {
	            throw new RuntimeException("Can't register driver!");
	        }
	    }
	}

2、获取数据库连接

/**
 *url:指定连接的路径
 *jdbc:mysql://ip地址(域名):端口号/数据库名称
 *user:用户名
 *password:密码
 */
public static Connection getConnection(String url, String user, String password) 
	throws SQLException {}

Connection：数据库连接对象

/**
 *功能:获取执行sql语句的对象
 *createStatement()
 *preparedStatement()
 *=============================
 *功能:管理实务
 *开启事务:setAutoCommit(boolean autoCommit)
 *autoCommit=false->开启事务
 *提交事务:commit()
 *回滚事务:rollback()
 */
Statement createStatement() throws SQLException;
PreparedStatement prepareStatement(String sql) throws SQLException;

void setAutoCommit(boolean autoCommit) throws SQLException;
void commit() throws SQLException;
void rollback() throws SQLException;

Statement：执行sql对象

/**
 *功能:执行静态sql语句并返回其生成的结果对象
 *执行任意sql语句:execute(String sql)
 *执行DDL,DML语句:executeUpdate(String sql)
 *return->影响的行数
 *执行DQL语句:executeQuery(String sql)
 *return->ResultSet
 */
boolean execute(String sql) throws SQLException;
int executeUpdate(String sql) throws SQLException;
ResultSet executeQuery(String sql) throws SQLException;

• 实例练习

import java.sql.*;

public class JDBCDemo {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/city","root", "root");
            statement = connection.createStatement();

            String insertSQL = "INSERT INTO STUDENT (ID, NAME, AGE, SEX, ADDRESS, MATH, ENGLISH) VALUES (9, '陈', 20, '男', '山东', 100, 99)";
            String updateSQL = "UPDATE STUDENT SET ENGLISH = 100 WHERE NAME = '陈'";
            String deleteSQL = "DELETE FROM STUDENT WHERE NAME = '马德'";

            System.out.println(statement.executeUpdate(insertSQL));
            System.out.println(statement.executeUpdate(updateSQL));
            System.out.println(statement.executeUpdate(deleteSQL));

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {

            if(statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

    }
}

ResultSet：结果集对象

/**
 *游标向下移动一行:next()
 *获取数据:getXxx(),只能一行按列获取
 *获取某一列的数据:getXxx(int columnIndex),getXxx(String columnLabel)
 *columnIndex从1开始
 */
boolean next() throws SQLException;
double getDouble(String columnLabel) throws SQLException;
int getInt(int columnIndex) throws SQLException;

• 简单实例练习

import java.sql.*;

public class JDBCDemo {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/city","root", "cjd510510");
            statement = connection.createStatement();

            String  querySQL = "SELECT * FROM STUDENT WHERE NAME LIKE '___'";
            resultSet = statement.executeQuery(querySQL);

            System.out.println("id    name    age    sex    address    math    english");

            while(resultSet.next()) {
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                int age = resultSet.getInt(3);
                String sex = resultSet.getString(4);
                String address = resultSet.getString(5);
                int math = resultSet.getInt(6);
                int english = resultSet.getInt(7);
                System.out.println(" " + id + "    " + name + "    " + age + "      " + sex + "      " + address + "       " + math + "      " + english);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {

            if(statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

    }
}

PreparedStatement：执行sql对象（后期全部使用该类来完成增删改查的所有操作）

sql注入问题

在拼接sql时有一些sql特殊关键字参与字符串的拼接，造成安全性的问题

使用PreparedStatement类解决sql注入问题

/*
 * 预编译SQL:参数作为占位符
 * 步骤:
 * 1.在定义sql时参数使用占位符
 * "select * from ？ where name = ?"
 * 2.给占位符赋值
 * setXxx(int parameterIndex, typedef x)
 * parameterIndex:问号的位置
 * typedef:数据类型
 * 3.执行sql语句
 * executeUpdate(),executeQuery()
 */
import java.sql.*;
import java.util.Scanner;

public class JDBCDemo {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            String sql = "SELECT password FROM users WHERE name = ?";
            preparedStatement = connection.prepareStatement(sql);

            String user, password;
            while(scanner.hasNext()) {
                user = scanner.next();
                password = scanner.next();

                preparedStatement.setString(1, user);
                resultSet = preparedStatement.executeQuery();
                if(!resultSet.next()) {
                    System.out.println("账号不存在!");
                    continue;
                }

                if(password.trim().equals(resultSet.getString("password").trim())) {
                    System.out.println("登陆成功!");
                } else {
                    System.out.println("登陆失败!");
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        JDBCUtil.close(connection, preparedStatement, resultSet);
        scanner.close();
    }
}