java之jdbc使用

时间：2023-03-09 15:48:15
java之jdbc使用

简单使用

Statement

通过 Statement 执行 ，其实是拼接 sql 语句的。  先拼接 sql 语句，然后在一起执行。

package com.zze.test;

import java.sql.*;

public class Test1 {

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 建立连接：url：协议+访问的数据库 user：用户名 password：密码
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/1221?serverTimezone=GMT&characterEncoding=utf8", "root", "root");
            // 创建statement
            statement = connection.createStatement();
            // 执行查询
            String sql = "select * from user";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println(String.format("%s:%s", id, name));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                resultSet = null;
            }
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                statement = null;
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                connection = null;
            }
        }
    }
}

PreparedStatement

相比较以前的 Statement， 预先处理给定的 sql 语句，对其执行语法检查。 在 sql 语句里面使用 ? 占位符来替代后续要传递进来的变量。 后面进来的变量值，将会被看成是字符串，不会产生任何的关键字。

package com.zze.test;

import java.sql.*;

public class Test3 {

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT&characterEncoding=utf8", "root", "root");
            String sql = "insert into user (name) values (?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1, "王五");
            int flag = statement.executeUpdate();
            System.out.println(flag);
            if (flag > 0) {
                System.out.println("添加成功");
            } else {
                System.out.println("添加失败");
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                statement = null;
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                connection = null;
            }
        }
    }
}

工具类封装

package com.zze.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {
    private static String driverName = null;
    private static String url = null;
    private static String user = null;
    private static String password = null;

    static {
        try {
            // 创建配置文件对象
            Properties properties = new Properties();
            // 使用类加载器读取src下的资源文件
            InputStream resourceAsStream = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            // 读取配置文件
            properties.load(resourceAsStream);

            // 读取属性
            driverName = properties.getProperty("driverName");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 获取连接
     *
     * @return 连接对象
     */
    public static Connection getConnection() {
        try {
            // 注册驱动 com.mysql.cj.jdbc.Driver 中有注册的静态代码块
            Class.forName(driverName);
            // 建立连接：url：协议+访问的数据库 user：用户名 password：密码
            Connection connection = DriverManager.getConnection(url, user, password);
            return connection;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 释放连接资源
     */
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null) closeResultSet(resultSet);
        if (statement != null) closeStatement(statement);
        if (connection != null) closeConnection(connection);
    }

    private static void closeResultSet(ResultSet rs) {
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            rs = null;
        }
    }

    private static void closeStatement(Statement st) {
        try {
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            st = null;
        }
    }

    private static void closeConnection(Connection conn) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn = null;
        }
    }

}

JDBCUtil.java

driverName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/1221?serverTimezone=GMT
user=root
password=root

jdbc.properties

package com.zze.test;

import com.zze.util.JDBCUtil;

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

public class Test2 {

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            // 创建statement
            statement = connection.createStatement();
            // 执行查询
            String sql = "select * from user";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println(String.format("%s:%s", id, name));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            JDBCUtil.release(connection, statement, resultSet);
        }
    }
}

完整示例下载

相关文章