Java实现登录mysql执行sql语句

最新推荐文章于 2024-04-27 13:29:09 发布

zhxjfjfkkfkfkeo

最新推荐文章于 2024-04-27 13:29:09 发布

阅读量309

点赞数

分类专栏： java代码

本文链接：https://blog.csdn.net/weixin_44948424/article/details/126020954

版权

Java HikariCP MySQL SQL查询数据源

关键词由CSDN通过智能技术生成

java代码专栏收录该内容

11 篇文章 0 订阅

订阅专栏

Java实现登录mysql执行sql语句

代码

package LoginDb;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class DataSourceUtil {
    private final Logger logger= LoggerFactory.getLogger(DataSourceUtil.class);
    private HikariDataSource dataSource=null;
    public enum
    DbType{
        Oracle,Db2,Mysql
    }

    public DataSourceUtil() {
    }

    public static void main(String[] args) {
        int a=0;
        System.out.println(a);
        DataSourceUtil dataSourceUtil=new DataSourceUtil("127.0.0.1",3306,"mysql","root","zhr123456","Mysql");
        dataSourceUtil.query("SELECT TABLE_SCHEMA FROM information_schema.`STATISTICS`");
    }

    DataSourceUtil(String jdbcUrl,String userName,String password,String driverName) throws Exception {
        init(jdbcUrl,userName,password,driverName);
    }
    //构造方法
    public DataSourceUtil(String ip,int port,String dataBase,String userName,String password,String dbType) {
        try {
            DbType dbType1=DbType.valueOf(dbType);
            init(ip,port,dataBase,userName,password,dbType1);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //构造方法需要传入枚举类类型
    DataSourceUtil(String ip,int port,String dataBase,String userName,String password,DbType dbType) throws Exception {
        init(ip,port,dataBase,userName,password,dbType);
    }

    //初始化方法，需要传入枚举类型的数据库类型
    private void init(String ip,int port,String dataBase,String userName,String password,DbType dbType) throws Exception {
        String jdbcUrl="";
        String driverClassName="";
        switch (dbType){
            case Db2:
                jdbcUrl="jdbc:db2://"+ip+":"+port+"/"+dataBase;
                driverClassName="com.ibm.db2.jcc.DB2Driver";
                break;
            case Mysql:
                jdbcUrl="jdbc:mysql://"+ip+":"+port+"/"+dataBase+"?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&useSSL=false";
               // driverClassName="com.mysql.cj.jdbc.Driver";
                driverClassName="com.mysql.jdbc.Driver";
                break;
            case Oracle:
                jdbcUrl="";
                driverClassName="";
                break;
        }
        init(jdbcUrl,userName,password,driverClassName);
    }

    //初始化方法传入jdbcUrl
    private void init(String jdbcUrl,String userName,String password,String driverClassName) throws Exception {
        try{
            HikariConfig hikarConfig=new HikariConfig();
            hikarConfig.setJdbcUrl(jdbcUrl);
            hikarConfig.setUsername(userName);
            hikarConfig.setPassword(password);
            hikarConfig.setDriverClassName(driverClassName);
            dataSource=new HikariDataSource(hikarConfig);
        }catch (Exception e){
            logger.info("输入错误");
            e.printStackTrace();
            throw e;
        }
    }
    //获取连接
    public Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    //执行查询语句，返回多条结果
    public List<Map<String,Object>> query(String sql){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try{
            connection=getConnection();
            preparedStatement=connection.prepareStatement(sql);
            List<Map<String,Object>> result=new ArrayList<Map<String, Object>>();
            resultSet=preparedStatement.executeQuery();
            ResultSetMetaData metaData=resultSet.getMetaData();
            int columnCount=resultSet.getMetaData().getColumnCount();
            while (resultSet.next()){
                Map<String,Object> row=new HashMap<String, Object>();
                for (int i = 1; i <=columnCount ; i++) {
                    row.put(metaData.getColumnName(i),resultSet.getObject(i));
                }
                result.add(row);
            }
            return result;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(resultSet!=null){
                    resultSet.close();
                }
                if(preparedStatement!=null){
                    preparedStatement.close();
                }
                if(connection!=null){
                    connection.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }

}

需要引入的依赖

 <dependencies>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>2.7.9</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.21</version>
        </dependency>
    </dependencies>