2、JDBC基本使用

（1）createStatement的使用

executeUpdate

• executeUpdate可以实现
  • DML（insert update delete）
  • DDL（数据表的相关操作） ``` import java.sql.*;

public class Main {

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

// 添加数据 // String sql = “insert card values(null,2000,’zhangfei’);”; // int count = statement.executeUpdate(sql); // System.out.println(count);

// 更新数据 // String sql = “update card set money = 3000 where id = 1;”; // int count = statement.executeUpdate(sql); // System.out.println(count);

// 删除数据 // String sql = “delete from card where id = 1;”; // int count = statement.executeUpdate(sql); // System.out.println(count);

        // 创建表，直接返回0
        String sql = "create table usb(id int,name varchar(20));";
        int count = statement.executeUpdate(sql);
        System.out.println(count);

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

}

<a name="piKUm"></a>
#### executeQuery

- 查询结果集ResultSet，这个也需要释放

import java.sql.*;

public class Main {

public static void main(String[] args)  {

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {

        Class.forName("com.mysql.cj.jdbc.Driver");
        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/test",
                "root", "root");
        statement = connection.createStatement();

        // 创建表，直接返回0
        String sql = "select * from card;";
        resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            int money = resultSet.getInt("money");
            String name = resultSet.getString("name");
            System.out.println(id+","+money+","+name);
        }

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

}

<a name="KhJjE"></a>
### （2）工具类

- 工具类

import java.sql.*;

public class JDBCUtils {

private static final String url = "jdbc:mysql://localhost:3306/test";
private static final String user = "root";
private static final String password = "root";
private static final String driver = "com.mysql.cj.jdbc.Driver";

static {
    try {
        Class.forName(driver);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

public static Connection getConnection() throws SQLException {

    return DriverManager.getConnection(url,user,password);
}

/**
 * 释放资源
 *
 * @param statement
 * @param connection
 */
public static void close(ResultSet resultSet, Statement statement, Connection connection) {

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

    close(statement, connection);
}

/**
 * 释放资源
 *
 * @param statement
 * @param connection
 */
public static void close(Statement statement, Connection connection) {
    if (connection != null) {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

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

}

- 工具类使用

import java.sql.*;

public class Main {

public static void main(String[] args)  {

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {

        connection = JDBCUtils.getConnection();
        statement = connection.createStatement();

        String sql = "select * from card;";
        resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            int money = resultSet.getInt("money");
            String name = resultSet.getString("name");
            System.out.println(id+","+money+","+name);
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.close(resultSet,statement,connection);
    }
}

}

<a name="zGtOe"></a>
### （3）prepareStatement

- 日常开发使用prepareStatement，效率高还可以防止sql注入，createStatement不会使用

import java.sql.*;

public class Main {

public static void main(String[] args) {

    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    try {

        connection = JDBCUtils.getConnection();

        String sql = "select * from card where id = ? and name = ?;";
        statement = connection.prepareStatement(sql);
        // 从1开始，防止sql注入，效率高
        statement.setInt(1, 4);
        statement.setString(2, "zhangfei");

        resultSet = statement.executeQuery();

        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            int money = resultSet.getInt("money");
            String name = resultSet.getString("name");
            System.out.println(id + "," + money + "," + name);
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.close(resultSet, statement, connection);
    }
}

}

```