【JDBC学习及工具类的封装】_java 封装创建statement工具类-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_53061888/article/details/119255375

JDBC学习

1.1JDBC介绍

数据库有多种，如SQL sever ，MySQL等，那Java用什么连接数据库，总不能让我们的Java开发者精通所欲数据库吧，所以Java提供了一套标椎，让各个数据库厂商按照标椎来做，就像我们学的Java的接口一样，只提供标准，就是jdbc技术（Java DB Connection）
每个数据库都有自己的jar包；

1.2jdbc的操作

1.加载驱动,内部是不同数据库的Driver；
2.创建链接；
3.创建通道，发送SQL命令；
4.处理结果（结果集）
5.关闭资源（特别注意）

class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(url,用户名，密码);
Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate("sql语句");
conn.close();

对数据库的改查操作；

public static void select(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql:///myemployees?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","123456");
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from departments");
            while(rs.next()){
                int deptid = rs.getInt("department_id");
                String deptname = rs.getString("department_name");
                int mid = rs.getInt(3);
                int localid = rs.getInt(4);
                System.out.println(deptid+"\t"+deptname+"\t"+mid+"\t"+localid);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }
    public static void update(){
        try {
            //创建驱动实例
            Class.forName("com.mysql.cj.jdbc.Driver");
            //创建链接
            Connection conn = DriverManager.getConnection("jdbc:mysql:///myemployees?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","123456");
            System.out.println(conn);
            //3.创建通道发送SQL
            Statement stmt = conn.createStatement();
            int rows = stmt.executeUpdate("INSERT INTO departments VALUES(280,'测试部',NULL,1700)");
            System.out.println(rows);
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

1.3SQL注入，PreparedStatement

Statement :由createStatement创建，用于发送简单的SQL语句（不带参数）
PrepareStatement：继承自Statement接口，由prepareStatement创建，用于发送含有一个或多个输入参数的SQL语句。PreparedStatement对象比Statement对象的效率更高，并且可以防止SQL注入。我们多用的是PreparedStatement。
callableStatement ：继承自PreparedStatement。由方法prePareCall创建，用于调用存储过程。

当我们给SQL语句的某个属性赋值如：属性= ‘’ or ‘1’ = ‘1’;会造成SQL注入，也就会导致不安全；所以使用PreparedStatement，它是Statement的子类；
使用：
SQL传入的参数用？代替；

public static void select(int department_id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql:///myemployees?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","123456");
            String sql = "select * from departments where department_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, String.valueOf(department_id));
            rs = pstmt.executeQuery();
            while(rs.next()){
                int deptid = rs.getInt("department_id");
                String deptname = rs.getString("department_name");
                int mid = rs.getInt("manager_id");
                System.out.println(deptid+"\t"+deptname+"\t"+mid);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try{
                if(rs!=null){
                    rs.close();
                }
                if(pstmt!=null){
                    pstmt.close();
                }
                if(conn!=null){
                    conn.close();
                }
            }catch (SQLException e){
                e.printStackTrace();
            }
        }

    }

1.4 工具类的封装

1.4.1properties文件中属性的引入

package com.openlab.utils;

import java.io.IOException;
import java.util.Properties;

public class PropertiesUtil {
    private static Properties properties;
    public static Properties getProperties(String name){
        properties = new Properties();
        try {
            properties.load(PropertiesUtil.class.getClassLoader().getResourceAsStream(name));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return properties;
    }
}

1.4.2数据库连接方法的实现：

package com.openlab.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DButils {
    public static Connection getConnection(){
        Properties properties = PropertiesUtil.getProperties("jdbc.properties");
        String driver = properties.getProperty("jdbc.driver");
        String url = properties.getProperty("jdbc.url");
        String user = properties.getProperty("jdbc.user");
        String password = properties.getProperty("jdbc.password");
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url,user,password);
            return conn;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

}

1.4.3增删改查的实现极其流关闭方法实现

package com.openlab.utils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class DBHelper {
    /*
    *  数据的增删改；DML
    * @params args 动态参数；
    * */

    public static int update(String sql,Object...args){
        int count = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DButils.getConnection();
            //获取到SQL
            ps = conn.prepareStatement(sql);
            //为？SQL赋值；
            for(int i = 0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            count = ps.executeUpdate();
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            close(conn,ps,null);
        }
        return count;
    }
    /*
    *
    * 单个数据的查询：
    * */
    public static <T> T selectOne(Class<T> clazz,String sql,Object...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        T t = null;
        try {
            conn = DButils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i = 0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            if(rs.next()){
                //利用反射获取对象；
                t = clazz.getConstructor().newInstance();
                int columnCount = metaData.getColumnCount();
                //获取数据列数；
                for(int i = 1;i<=columnCount;i++){
                    //获取列名
                    String columnlabel = metaData.getColumnLabel(i);
                    //通过列名获取列值；
                    Object columnValue = rs.getObject(columnlabel);
                    //利用反射获取对象属性；
                    Field field = t.getClass().getDeclaredField(columnlabel);
                    field.setAccessible(true);//开启操作权限；
                    //进行赋值；
                    field.set(t,columnValue);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }finally {
            close(conn,ps,rs);
        }
        return t;
    }
    //查询多组数据；
    public static <T> List<T> selectAll(Class<T> clazz, String sql, Object...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<T> list = new ArrayList<>();
        T t = null;
        try {
            conn = DButils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i = 0;i<args.length;i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            while(rs.next()){
                t = clazz.getConstructor().newInstance();
                int columnCount = metaData.getColumnCount();
                for(int i = 1; i<= columnCount;i++){
                    String columnLabel = metaData.getColumnLabel(i);
                    Object columnValue = rs.getObject(columnLabel);
                    Field field = t.getClass().getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            close(conn,ps,rs);
        }
        return list;
    }
    //关流；
    public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(ps != null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}