JDBC

1.简介

• JDBC：Java DataBase Connectivity，java数据库连接
• JDBC是一座桥梁，连接着java应用程序和数据库

2.对象操作

• JDBC的核心对象

|--DriverManager：驱动管理对象
    |--注册驱动(被Class.forName()替代)
    |--连接数据库获取 连接对象DriverManager.getConnection(url,name,pwd)

|--Connection：连接对象
    |--获取指令对象conn.createStatement()
    |--获取预编译对象conn.prepareStatement(sql)

|--Statement：指令对象
    |--执行sql
    |--查询操作，返回查询的结果集ResultSet executeQuery(String sql)
    |--int executeUpdate(String sql)//增加、修改、删除方法，返回成功的记录数

|--PreparedStatement：预编译对象
    |--解决sql注入安全问题
    |--预编译sql语句PreparedStatement prepareStatement(String sql)

|--ResultSet：结果集对象
    |--查询到的结果

• 操作步骤

|--加载驱动
|--获取连接对象
|--准备sql
|--获取指令对象
|--执行sql
|--[处理查询的结果集]
|--释放资源

public static void main(String[] args) throws Exception {
    //加载驱动，除了加载的时候加载mysql对象,其它sql对象全是java对象
    //DriverManager.registerDriver(new Driver());//加载两次
    Class.forName("com.mysql.jdbc.Driver");//加载一次

    //创建连接
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/table_name", "username", "password");

    //编写sql
    String sql = "select * from table_name";

    //得到statement对象
    Statement stmt = conn.createStatement();

    //执行sql
    ResultSet rs = stmt.executeQuery(sql);

    //具体操作
    while(rs.next()) {
        int id = rs.getInt("id");
        String username = rs.getString("username");
        String age=rs.getInt("age");
        System.out.println(id+" :: "+username+" :: "+"age");
    }

    //释放资源(启动越早，关闭越晚)
    rs.close();
    stmt.close();
    conn.close();
}

3.读取配置文件

• io流读取properties配置文件

//创建properties对象
Properties p = new Properties();
//文件的输入流
InputStream in = new FileInputStream("src/db.properties");
//把文件的输入流放到对象里面
p.load(in);
String drivername = p.getProperty("drivername");
String url = p.getProperty("url");
String username = p.getProperty("username");
String password = p.getProperty("password");

• ResourceBundle类读取properties配置文件
• 使用条件：文件格式为properties，位置在src下面

String drivername = ResourceBundle.getBundle("db").getString("drivername");
String url = ResourceBundle.getBundle("db").getString("url");
String username = ResourceBundle.getBundle("db").getString("username");
String password = ResourceBundle.getBundle("db").getString("password");

4.SQL注入和防止

注入原理：输入的内容被作为sql语句的一个条件被sql执行

账户: bbb     
密码: rrr
select * from tableName where username='bbb' and password='rrr'//and 同时满足账户密码正确

账户: bbb' or '1=1    
密码: ggg
select * from tableName where username='bbb' or '1=1' and password='ggg'//or 满足一个条件即可

• 使用PreparedStatement预编译对象防止sql注入
• 使用 ? 作为占位符

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

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/table_name", "username", "password");

String sql = "select * from tableName where username=? and password=?";

psmt = conn.prepareStatement(sql);

psmt.setString(1, username);
psmt.setString(2, password);

rs = psmt.executeQuery();
if(rs.next()) {
    System.out.println("login success");
    } else {
    System.out.println("fail");
}

4.示例代码

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

public class JDBC_Test {

    /**
     * @author pengshulin
     * 
     */
    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = JDBC_Utils.getConnection();
            stmt = conn.createStatement();
            String sql = "insert into exam values(1100,'pengshulin','boss','1006','2001-10-1')";
            stmt.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBC_Utils.releaseResource(conn, stmt, rs);
        }
    }
}

import java.io.*;
import java.sql.*;
import java.util.*;

/**
 * JDBC工具
 * 
 * @author pengshulin
 * 
 */
public class JDBC_utils {
    private static String URL;
    private static String USERNAME;
    private static String PASSWORD;

    // 读取配置文件
    static {
        try {
            InputStream is = new FileInputStream(".\\JDBC_util\\db.properties");
            Properties prt = new Properties();
            prt.load(is);
            URL = prt.getProperty("URL");
            USERNAME = prt.getProperty("username");
            PASSWORD = prt.getProperty("password");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 创建数据库连接
    public static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection(URL, USERNAME,
                PASSWORD);
        return connection;
    }

    // 释放资源
    public static void releaseResource(Connection conn, Statement stmt,
            ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {

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

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

                e.printStackTrace();
            }
            conn = null;
        }
    }
}