Java学习记录四 JDBC

JDBC入门

简单的JDBC执行过程

    public static void main(String[] args) throws Exception {
        // 导入驱动jar包
        	// 复制 mysql-connector-java-8.0.12.jar 到项目的libs目录下
        	// 右键Add As Library
        // 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 获取数据库连接对象
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/shangpinhoutai?serverTimezone=UTC&useSSL=false","root","123456");
        // 定义sql
        String sql = "update sp_user set username = '张三' where user_id = 11";
        // 获取执行sql对象Statement
        Statement statement = conn.createStatement();
        // 执行sql语句
        int cut =  statement.executeUpdate(sql);
        System.out.println(cut);
        // 释放资源
        statement.close();
        conn.close();
    }

简易的JDBC封装

jdbc.properties 配置文件

url=jdbc:mysql://localhost:3306/shangpinhoutai?serverTimezone=UTC&useSSL=false
user=root
password=123456
driver=com.mysql.cj.jdbc.Driver

package wy.soft;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class MySqlHelper {
    //数据库访问字符串
    private static String url = null;
    //用户名
    private static String user = null;
    //密码
    private static String password = null;
    // 驱动
    private static String driver = null;
    static {
        try {
            // 读取配置文件
            Properties pro = new Properties();
            // 获取src路径下的文件的方式   ClassLoader 类加载器
            ClassLoader classLoader = MySqlHelper.class.getClassLoader();
            URL res = classLoader.getResource("jdbc.properties");
            String path = res.getPath();
            pro.load(new FileReader(path));
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 数据库的链接对象
     * @return
     */
    public static Connection getConnection() {
        //链接数据库
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

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

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


    /**
     * 执行数据的增加、修改和删除
     * @param commandText
     * @return 受影响的行数
     */
    public static int ExecuteNonQuery(String commandText) {
        //链接数据库
        Connection conn = getConnection();
        Statement statement = null;
        try {
            statement = conn.createStatement();
            // 执行数据库的增加，修改和删除操作命令，并返回影响数据的行数
            int cut = statement.executeUpdate(commandText);
            close(statement, conn);
            return cut;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return  0;
    }


    /**
     * 查询数据集合
     * @param commandText
     * @return
     */
    public static ResultSet GetDataReader(Statement statement, String commandText) {
        // 链接数据库
        // Connection conn = getConnection();
        // Statement statement = null;
        try {
            //statement = conn.createStatement();
            // 执行数据库的增加，修改和删除操作命令，并返回影响数据的行数
            ResultSet resultSet = statement.executeQuery(commandText);
            return resultSet;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}