Java.Mysql数据库自定义工具类

GarsonW

已于 2022-12-03 10:49:57 修改

阅读量504

点赞数 1

分类专栏：软件开发文章标签： java 数据库 mysql

于 2022-09-17 12:43:48 首次发布

本文链接：https://blog.csdn.net/m0_56190554/article/details/126903954

版权

软件开发专栏收录该内容

44 篇文章 0 订阅

订阅专栏

话不多说，直接上代码

所有方法为静态方法直接在别的类可以直接调用

首先先定义信息字段，分别载入数据库的驱动、地址、用户名和账号信息。（需要注意的是：在JDBC_DRIVER中，旧版MySQL不需要加cj，新版MySQL需要加cj）

    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String URL = "jdbc:mysql://localhost:3306/Flight";
    static final String USER = "root";
    static final String PASS = "123";

1.实现查询

/*
     * @该方法用于数据库的查询
     * @需要正确的输入sql查询语句
     */
    public static void select(String sql) throws ClassNotFoundException, SQLException {
        ArrayList arrayList = new ArrayList<>();
        Connection conn;
        Statement stmt;
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(URL,USER,PASS);
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsd = rs.getMetaData();
            int Totalcount = rsd.getColumnCount();
            int count = 1;
            // 展开结果集数据库
            while(rs.next()){// 通过字段检索
                // 输出数据
                while(count<=Totalcount)
                {
                    System.out.print("["+rsd.getColumnName(count)+"]"+"-"+rs.getString(count));
                    if(count<Totalcount)
                        System.out.print(", ");
                    count++;
                }
                System.out.println();
                count = 1;
            }
            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
            if(stmt!=null) stmt.close();
            if(conn!=null) conn.close();
    }

2.实现增删查改

 //可以执行增删查改操作
    public static void Update(String sql) throws ClassNotFoundException, SQLException{
        Class.forName(JDBC_DRIVER);
        Connection conn = DriverManager.getConnection(URL,USER,PASS);
        Statement stmt = conn.createStatement();
        stmt.executeUpdate(sql);
        stmt.close();
        conn.close();
    }

3.实现顺序输入


    /*
    * @该方法用于数据库的插入数据
    * @第一个参数用于定位表，第二个参数可以顺序输入任意数量的数据作为数据库内容
    * @第二个参数一定要按照数据库顺序插入
    */
    public static <T> String insert(String table,T... parms) throws ClassNotFoundException, SQLException{
        int length = parms.length;
        Class.forName(JDBC_DRIVER);
        Connection conn = DriverManager.getConnection(URL,USER,PASS);
        StringBuilder sql = new StringBuilder("insert into "+table+" VALUES (");
        for(int i=1;i<=parms.length;i++)
        if(i!= parms.length)sql.append("?,");else sql.append("?)");
        PreparedStatement preparedStatement = conn.prepareStatement(sql.toString());
        for(int i=0;i<parms.length;i++) {

            if (parms[i].getClass().getSimpleName().equals("String"))
                preparedStatement.setString(i + 1, parms[i].toString());
            else preparedStatement.setInt(i + 1, (Integer) parms[i]);
        }
        int result = preparedStatement.executeUpdate();
        conn.close();
        preparedStatement.close();
        return "操作成功！影响数据："+result+"条";
    }

完整代码

package com.example.Garson3;

/**
 * @ About: 自定义数据库数据工厂
 * @ Author: Garson
 * @ Time: 2022/9/16
 **/
import java.sql.*;
import java.util.ArrayList;

@SuppressWarnings("all")
public class GarsonDB {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String URL = "jdbc:mysql://localhost:3306/Flight";
    static final String USER = "root";
    static final String PASS = "wuguixin";

    /*
     * @该方法用于数据库的查询
     * @需要正确的输入sql查询语句
     */
    public static void select(String sql) throws ClassNotFoundException, SQLException {
        ArrayList arrayList = new ArrayList<>();
        Connection conn;
        Statement stmt;
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(URL,USER,PASS);
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsd = rs.getMetaData();
            int Totalcount = rsd.getColumnCount();
            int count = 1;
            // 展开结果集数据库
            while(rs.next()){// 通过字段检索
                // 输出数据
                while(count<=Totalcount)
                {
                    System.out.print("["+rsd.getColumnName(count)+"]"+"-"+rs.getString(count));
                    if(count<Totalcount)
                        System.out.print(", ");
                    count++;
                }
                System.out.println();
                count = 1;
            }
            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
            if(stmt!=null) stmt.close();
            if(conn!=null) conn.close();
    }

    //可以执行增删查改操作
    public static void Update(String sql) throws ClassNotFoundException, SQLException{
        Class.forName(JDBC_DRIVER);
        Connection conn = DriverManager.getConnection(URL,USER,PASS);
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        stmt.executeUpdate(sql);
        stmt.close();
        conn.close();
    }

    /*
     * @该方法用于得到所输入表的列数
     */
    public static int getRowCount(String table) throws SQLException, ClassNotFoundException {
        ArrayList arrayList = new ArrayList<>();
        Connection conn;
        Statement stmt;
        // 注册 JDBC 驱动
        Class.forName(JDBC_DRIVER);
        conn = DriverManager.getConnection(URL,USER,PASS);
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM "+table);
        ResultSetMetaData rsd = rs.getMetaData();
        int count=0;
        while(rs.next()){
            count++;
        }
        // 完成后关闭
        rs.close();
        stmt.close();
        conn.close();
        if(stmt!=null) stmt.close();
        if(conn!=null) conn.close();
        return count;
    }


    /*
    * @该方法用于数据库的插入数据
    * @第一个参数用于定位表，第二个参数可以顺序输入任意数量的数据作为数据库内容
    * @第二个参数一定要按照数据库顺序插入
    */
    public static <T> String insert(String table,T... parms) throws ClassNotFoundException, SQLException{
        int length = parms.length;
        Class.forName(JDBC_DRIVER);
        Connection conn = DriverManager.getConnection(URL,USER,PASS);
        StringBuilder sql = new StringBuilder("insert into "+table+" VALUES (");
        for(int i=1;i<=parms.length;i++)
        if(i!= parms.length)sql.append("?,");else sql.append("?)");
        PreparedStatement preparedStatement = conn.prepareStatement(sql.toString());
        for(int i=0;i<parms.length;i++) {

            if (parms[i].getClass().getSimpleName().equals("String"))
                preparedStatement.setString(i + 1, parms[i].toString());
            else preparedStatement.setInt(i + 1, (Integer) parms[i]);
        }
        int result = preparedStatement.executeUpdate();
        conn.close();
        preparedStatement.close();
        return "操作成功！影响数据："+result+"条";
    }


    public static String selectPasscode(String sql) throws ClassNotFoundException, SQLException {
        ArrayList arrayList = new ArrayList<>();
        Connection conn;
        Statement stmt;
        // 注册 JDBC 驱动
        Class.forName(JDBC_DRIVER);
        conn = DriverManager.getConnection(URL,USER,PASS);
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        ResultSetMetaData rsd = rs.getMetaData();
        int Totalcount = rsd.getColumnCount();
        int count = 1;
        // 展开结果集数据库
        while(rs.next()){// 通过字段检索
            // 输出数据
            while(count<=Totalcount)
            {
//                System.out.print("["+rsd.getColumnName(count)+"]"+"-"+rs.getString(count));
                return rs.getString(count);
            }
            System.out.println();
            count = 1;
        }
        // 完成后关闭
        rs.close();
        stmt.close();
        conn.close();
        if(stmt!=null) stmt.close();
        if(conn!=null) conn.close();
        return "";
    }
}