Java MySQL 数据库 JDBC操作（增删改查）

1. JDBC (Java DataBase Connectivity) java 数据库连接

解释说明?：用来执行 SQL 语句的 Java API， 可以为多种关系数据库提供统一访问。

2. MySQL (关系型数据库管理系统)

解释说明?：

MySQL由瑞典 MySQL AB 公司开发，目前属于 Oracle 旗下产品

是最流行的关系型数据库管理系统之一，体积小、速度快、开源！

3. Java JDBC 操作

工程准备?：MySQL 连接驱动

正菜开始?：

1. 创建工程，引入驱动 jar 包?：

 

创建数据库表，用来测试?：

 

2.  正式编写代码?：

2.1 编写配置文件?: 配置了 JDBC 需要的操作数据库信息

JDBC_Driver=com.mysql.jdbc.Driver
JDBC_Url=jdbc:mysql://localhost:3306/test
JDBC_User=root
JDBC_Password=123

2.2 编写获取连接释放连接工具类?：

package utils;

import java.sql.*;
import java.util.ResourceBundle;

public class JDBCUtil {
    private static final String jdbcDriver;
    private static final  String jdbcUrl;
    private static final String jdbcUser;
    private static final String jdbcPassword;

    static {
        ResourceBundle bundle = ResourceBundle.getBundle("config/dbConfig");
        jdbcDriver = bundle.getString("JDBC_Driver");
        jdbcUrl = bundle.getString("JDBC_Url");
        jdbcUser = bundle.getString("JDBC_User");
        jdbcPassword = bundle.getString("JDBC_Password");
    }

    //装载驱动
    private static void loadDriver() throws ClassNotFoundException{
        Class.forName(jdbcDriver);
    }

    //获取连接
    public static Connection getConnection() throws Exception{
        loadDriver();
        return DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
    }

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

    public static void release(Statement stmt, Connection conn){
        try {
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
    }

}

2.3 编写 CURD Demo (增删改使用的是 PreparedStatement ，可以防止 SQL 注入) ?：

package test;

import utils.JDBCUtil;

import java.sql.*;

public class JDBCTest {
    public static void jdbcSelect() throws Exception{
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try{
            conn = JDBCUtil.getConnection();
            stmt = conn.createStatement();
            String sql = "select * from student";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString("stu_name"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtil.release(rs, stmt, conn);
        }
    }

    public static void jdbcInsert() throws Exception{
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "insert into student values(null, ?, ?, ?, ?)";
            stmt = conn.prepareStatement(sql);
            String stuName = "张三";
            String stuSex = "M";
            int stuAge = 18;
            String stuInfo = "三好青年";
            stmt.setString(1, stuName);
            stmt.setString(2, stuSex);
            stmt.setInt(3, stuAge);
            stmt.setString(4, stuInfo);
            stmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtil.release(stmt, conn);
        }
    }

    public static void jdbcUpdate() throws Exception{
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "update student set stu_name=? where stu_id=?";
            stmt = conn.prepareStatement(sql);
            String stuName = "王五";
            long stuId = 1L;
            stmt.setString(1, stuName);
            stmt.setLong(2, stuId);
            stmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtil.release(stmt, conn);
        }
    }

    public static void jdbcDelete() throws Exception{
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "delete from student where stu_id=?";
            stmt = conn.prepareStatement(sql);
            long stuId = 1L;
            stmt.setLong(1, stuId);
            stmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtil.release(stmt, conn);
        }
    }

    public static void main(String[] args) throws Exception{
        jdbcInsert();
        jdbcSelect();
        jdbcUpdate();
        jdbcDelete();
    }
}

4. SQL 的具体学习网站推荐?：http://www.runoob.com/sql/sql-tutorial.html