栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

JDBC连接MySQL数据库及自定义JDBCUtils

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

JDBC连接MySQL数据库及自定义JDBCUtils

JDBC连接MySQL数据库及自定义JDBCUtils
  • 前言
  • 一、JDBC连接数据库
    • 1. 引入依赖
    • 2. 连接数据库
  • 二、自定义JDBCUtils
    • 1. JDBCUtils
    • 2. BeanJDBCUtils
    • 3. 使用自定义JDBC类操作数据库

前言

JDBC 指 Java 数据库连接,是一种标准Java应用编程接口( JAVA API),用来连接 Java 编程语言和广泛的数据库。
本文使用的是JDBC连接MySQL数据库
MySQL版本为8.0.28

一、JDBC连接数据库 1. 引入依赖

使用maven引入依赖包
如果MySQL版本为8.x.xx


	mysql
	mysql-connector-java
	8.0.29

如果MySQL版本为 5.x.xx
则引入可用



    mysql
    mysql-connector-java
    5.1.45

2. 连接数据库

使用JDBC连接数据库条件

  • 需要指定连接的路径url = "jdbc:mysql://localhost:3306/fruitdb"
  • 需要指定用户名userName = "root"
  • 需要指定用户的密码password = "xxxxxxx"
  • 需要MySQL驱动diver = com.mysql.cj.jdbc.Driver

url 格式为:jdbc:mysql://地址或主机名:端口号/数据库名

driver

  • 如果MySQL版本为8.x.xx为 com.mysql.cj.jdbc.Driver
  • 如果MySQL版本为5.x.xx为 com.mysql.jdbc.Driver

代码如下:

public class JDBCUtils {

    private static final String url;
    private static final String userName;
    private static final String password;
    private static final String driver;

	
    static {

        Properties properties = new Properties();
        
        InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        try {
            properties.load(is);
            url = properties.getProperty("url");
            userName = properties.getProperty("userName");
            password = properties.getProperty("password");
            driver = properties.getProperty("driver");
            //注册驱动
            Class.forName(driver);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

	
    public Connection getConnection() {
        try {
            return DriverManager.getConnection(url, userName, password);
        } catch (SQLException e) {
            throw new RuntimeException("无法连接到数据库n");
        }
    }
}

jdbc.properties文件的路径我放在资源文件下的

jdbc.properties文件的内容:

url=jdbc:mysql://localhost:3306/fruitdb
userName=root
password=2043602397l
driver=com.mysql.cj.jdbc.Driver

测试连接是否成功:

二、自定义JDBCUtils

我采用的是:

  • JDBCUtils来连接和关闭数据库相关资源。
  • 再写一个类(BeanJDBCUtils)来进行数据库的基本操作。
  • 最后写一个类来实现具体的操作。
1. JDBCUtils

代码如下:


@SuppressWarnings("all")
public class JDBCUtils {

    private static final String url;
    private static final String userName;
    private static final String password;
    private static final String driver;

    static {

        Properties properties = new Properties();
        InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        try {
            properties.load(is);
            url = properties.getProperty("url");
            userName = properties.getProperty("userName");
            password = properties.getProperty("password");
            driver = properties.getProperty("driver");

            //注册驱动
            Class.forName(driver);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    
    public Connection getConnection() {
        try {
            return DriverManager.getConnection(url, userName, password);
        } catch (SQLException e) {
            throw new RuntimeException("无法连接到数据库n");
        }
    }

    
    public void close(Connection connection) {
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    
    public void close(Connection connection, ResultSet resultSet) {
        try {
            connection.close();
            resultSet.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
2. BeanJDBCUtils

代码如下:



@SuppressWarnings("all")
public class BeanJDBCUtils extends JDBCUtils {

    private Connection connection;
    private PreparedStatement preparedStatement;

    private Class entityClass;

    
    public BeanJDBCUtils() {
        //getClass() 获取Class对象,当前我们执行的是new FruitDAOImpl() , 创建的是FruitDAOImpl的实例
        //那么子类构造方法内部首先会调用父类(BaseDAO)的无参构造方法
        //因此此处的getClass()会被执行,但是getClass获取的是FruitDAOImpl的Class
        //所以getGenericSuperclass()获取到的是BaseJDBCUtils的Class

        //System.out.println("获取父类对象:" + clazz.getSuperclass());
        Type genericType = getClass().getGenericSuperclass();
        //ParameterizedType 参数化类型
        Type[] actualTypeArguments = ((ParameterizedType) genericType).getActualTypeArguments();
        //获取到的中的T的真实的类型
        Type actualType = actualTypeArguments[0];
        try {
            entityClass = Class.forName(actualType.getTypeName());
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    
    public List query(String sql, Object... args) {
        List list = new ArrayList<>();
        connection = getConnection();
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            
            for (int i = 0; i < args.length; ++i)
                preparedStatement.setObject(i + 1, args[i]);
            preparedStatement.execute();
            
            resultSet = preparedStatement.getResultSet();
            
            ResultSetMetaData metaData = resultSet.getMetaData();
            
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                
                T entity = (T) entityClass.newInstance();
                Object object = new Object();
                for (int i = 0; i < columnCount; ++i) {
                    
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    object = resultSet.getObject(i + 1);
                    
                    Field field = entity.getClass().getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(entity, object);
                }
                list.add(entity);
            }

            return list;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            close(connection, resultSet);
        }
    }


    
    public T query(String sql, Class clazz, Object... args) {
        connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; ++i)
                preparedStatement.setObject(i + 1, args[i]);
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            T t = (T) entityClass.newInstance();
            if (resultSet.next()) {
                Object object = new Object();
                for (int i = 0; i < columnCount; i++) {
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Field field = t.getClass().getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    object = resultSet.getObject(columnLabel);
                    field.set(t, object);
                }
            }
            return t;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            close(connection, resultSet);
        }
    }

    
    public Integer updata(String sql, Object... args) {

        connection = getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; ++i)
                preparedStatement.setObject(i + 1, args[i]);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(connection);
        }
    }

    
    public  E getElement(String sql, Object... args) {
        connection = getConnection();
        ResultSet resultSet = null;
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            Object object = null;
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                object = resultSet.getObject(1);
            }
            return (E) object;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(connection, resultSet);
        }
    }

}

3. 使用自定义JDBC类操作数据库

表名如下

表中内容如下

创建一个与数据库表t_fruit一一对应的Fruit类

import lombok.*;


@ToString
@Data
@NoArgsConstructor
@AllArgsConstructor
@SuppressWarnings("all")
public class Fruit {
    private Integer fid;
    private String fname;
    private Integer price;
    private Integer fcount;
    private String remark;

    public Fruit(String fname, Integer price, Integer fcount, String remark) {
        this.fname = fname;
        this.price = price;
        this.fcount = fcount;
        this.remark = remark;
    }
}

创建一个接口(FruitDAO)来声明对改数据库的表中的操作的方法。

import java.util.List;

public interface FruitDAO {
    
    List getAllFruit();

    
    Integer addFruit(Fruit fruit);

    
    Long getAllCount();

    
    Integer getMaxPrice();

    
    Integer getMinPrice();

    Fruit getFruitById(Integer id);

}

对上述接口(FruitDAO)的实现类(FruitDAOImpl)

public class FruitDAOImpl extends BeanJDBCUtils implements FruitDAO {

    @Override
    public List getAllFruit() {
        String sql = "select * from t_fruit";
        return query(sql);
    }

    @Override
    public Integer addFruit(Fruit fruit) {
        String sql = "insert into t_fruit(fname,price,fcount,remark) values(?,?,?,?)";
        return updata(sql, fruit.getFname(), fruit.getPrice(), fruit.getFcount(), fruit.getRemark());
    }

    @Override
    public Long getAllCount() {
        String sql = "select count(*) from t_fruit";
        return getElement(sql);
    }

    @Override
    public Integer getMaxPrice() {
        String sql = "select max(price) from t_fruit";
        return getElement(sql);
    }

    @Override
    public Integer getMinPrice() {
        String sql = "select min(price) from t_fruit";
        return getElement(sql);
    }

    @Override
    public Fruit getFruitById(Integer id) {
        String sql = "select * from t_fruit where fid = ?";
        return query(sql, Fruit.class, id);
    }

}

测试结果:


数据库和代码文件可以通过下方链接进行下载

https://github.com/inTheSummer-long/useJDBCtoMySQL

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/980961.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号