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

一、JDBC连接数据库的步骤

1、加载jdbc驱动程序；
2、提供JDBC连接的URL
2、创建数据库的连接；
3、创建preparedStatement；
4、执行SQL语句；
5、遍历结果集；
6、处理异常，关闭JDBC对象资源。

package com.example.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JdbcProcess {
    public static void main(String[] args){
        try{
            //首先加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //提供JDBC连接的URL
            String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai&autoReconnect=true";
            String username="root";
            String password="123456";
            //创建数据库的连接
            Connection con = DriverManager.getConnection(url,username,password);
            //创建一个statement执行者
            String sql="SELECT * FROM sys_role WHERE id = ?";
            PreparedStatement statement = con.prepareStatement(sql);
            statement.setLong(1,1);
            //执行SQL语句
            ResultSet result = statement.executeQuery();
            //处理返回结果
            while (result.next()){
                System.out.println(result.getString("name") + "---" + result.getString("remark"));
            }
            //关闭JDBC对象
            con.close();
            result.close();
            statement.close();
        }catch(ClassNotFoundException e){
        	e.printStackTrace();
        } catch(Exception e){
        	e.printStackTrace();
        }
    }
}

二、实操过程

1、创建数据库表sys_role

2、编写配置 dbConfig.properties

jdbcDriver=com.mysql.cj.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai&autoReconnect=true
jdbcUser=root
jdbcPassword=123456

3、编写工具类JdbcUtil.java

package com.example.jdbc;

import java.sql.*;
import java.util.ResourceBundle;
/**
 * JDBC工具类
 * @author 
 *
 */
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("dbConfig");
        jdbcDriver = bundle.getString("jdbcDriver");
        jdbcUrl = bundle.getString("jdbcUrl");
        jdbcUser = bundle.getString("jdbcUser");
        jdbcPassword = bundle.getString("jdbcPassword");
    }
 
    //装载驱动
    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();
        }
    }
 
}

4、实现增删改查JdbcTest.java

package com.example.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JdbcTest {
	 public static void main(String[] args) throws Exception{
		 jdbcSelect();
	     jdbcInsert();
	     jdbcUpdate();
	     jdbcDelete();
	 }
	
	/**
	 * 查询
	 * @throws Exception
	 */
    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 sys_role";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtil.release(rs, stmt, conn);
        }
    }
 
    /**
     * 插入
     * @throws Exception
     */
    public static void jdbcInsert() throws Exception{
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "INSERT INTO sys_role(module, name, remark, available)"
                        +"values(?,?,?,?)";
            stmt = conn.prepareStatement(sql);
            String module = "张三";
            String name = "M";
            String remark = "备注";
            int available = 1;
            stmt.setString(1, module);
            stmt.setString(2, name);
            stmt.setString(3, remark);
            stmt.setInt(4, available);
            stmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtil.release(stmt, conn);
        }
    }
 
    /**
     * 更新
     * @throws Exception
     */
    public static void jdbcUpdate() throws Exception{
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "update sys_role set name=? where id=?";
            stmt = conn.prepareStatement(sql);
            String name = "超级管理员";
            int id = 34;
            stmt.setString(1, name);
            stmt.setInt(2, id);
            stmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtil.release(stmt, conn);
        }
    }
 
    /**
     * 删除
     * @throws Exception
     */
    public static void jdbcDelete() throws Exception{
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "delete from sys_role where id=?";
            stmt = conn.prepareStatement(sql);
            int id = 34;
            stmt.setLong(1, id);
            stmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtil.release(stmt, conn);
        }
    }
}