数据库 Mysql数据库的JDBC手写模板

目录

1. 所需jar包：

2. jdbcUtil接口

3. jdbcUtil接口实现类

4. 示例

---

1. 所需jar包：

    <!-- mysql 数据库依赖 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.16</version>
    </dependency>
    <!-- 添加日志相关的jar包 -->
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>1.7.25</version>
    </dependency>
    <!-- log end -->

2. jdbcUtil接口

package com.qtao.spring.common.jdbc;

import java.sql.ResultSet;

public interface JdbcUtil {

    /**
     * 查询
     * @param sql
     * @return
     */
    ResultSet query(String sql);

    /**
     * 修改
     * @param sql
     * @return
     */
    boolean update(String sql);

    /**
     * 关闭数据库连接(查询时务必关闭)
     */
    void closeConnection();
}

 

3. jdbcUtil接口实现类

package com.qtao.spring.common.jdbc;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;

/**
 * @ClassNames JdbcUtilImpl
 * @Description 此实现类仅支持mysql数据库
 * @Author qtao
 * @Date 2019/8/27 11:19
 * Version 1.0
 **/
public class JdbcUtilImpl implements JdbcUtil {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    private String mysqlDriverClass = "com.mysql.jdbc.Driver";
    private String url_1 = "jdbc:mysql://";
    private String url_2 = "?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull";

    // 数据库IP
    private String database_ip = "192.168.159.51";
    // 数据库端口
    private String database_port = "3306";
    // 数据库名称
    private String database_name = "ssm_demo";
    // 数据库名称
    private String username = "root";
    // 数据库密码
    private String password = "root";

    private Connection con;
    private PreparedStatement psm;
    private ResultSet rs;

    /**
     * 获取数据库连接
     *
     * @return
     */
    private Connection getConnection() {
        try {
            Class.forName(this.mysqlDriverClass);
            String url = url_1 + database_ip + ":" + database_port + "/" + database_name + url_2;
            this.con = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            logger.error("获取数据库连接驱动失败,ClassNotFoundException={}", e);
        } catch (SQLException e) {
            logger.error("连接数据库失败,SQLException={}", e);
        }
        return con;
    }

    /**
     * 对SQL进行预编译
     *
     * @param sql
     */
    private void getPreparedStatement(String sql) {
        try {
            psm = con.prepareStatement(sql);
        } catch (SQLException e) {
            logger.error("对SQL进行预编译失败,SQLException={}", e);
        }
    }

    /**
     * 关闭数据库连接
     */
    public void closeConnection() {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            logger.error("关闭ResultSet连接失败,Exception={}", e);
        }

        try {
            if (psm != null) {
                psm.close();
            }
        } catch (SQLException e) {
            logger.error("关闭PreparedStatement连接失败,Exception={}", e);
        }

        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            logger.error("关闭Connection连接失败,Exception={}", e);
        }
    }

    /**
     * 查询方法
     *
     * @param sql
     * @param obj
     * @return
     */
    public ResultSet query(String sql) {
        getConnection();
        getPreparedStatement(sql);
        try {
            rs = psm.executeQuery();
        } catch (SQLException e) {
            logger.error("查询时出现失败,SQLException={}", e);
        }
        return rs;
    }

    public boolean update(String sql) {
        getConnection();
        getPreparedStatement(sql);
        int num = 0;
        try {
            num = psm.executeUpdate();
        } catch (SQLException e) {
            logger.error("新增/更新/删除时出现失败,SQLException={}", e);
        } finally {
            closeConnection();
        }
        if (num > 0) {
            return true;
        }
        return false;
    }

}

4. 示例

    public static void main (String[] args){
        JdbcUtil jdbcUtil = new JdbcUtilImpl();
        String sql = "select * from USER";
        ResultSet rs = jdbcUtil.query(sql);
        List<User> list = new ArrayList<>();
        try {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getLong("ID"));
                user.setName(rs.getString("NAME"));
                user.setEmail(rs.getString("EMAIL"));
                user.setPassword(rs.getString("PASSWORD"));
                user.setRemark(rs.getString("REMARK"));
                list.add(user);
            }
        } catch (Exception e){
            logger.error("异常,Exception={}",e);
        } finally {
            jdbcUtil.closeConnection();
        }
        for (User user : list){
            System.out.println("编号："+user.getId());
            System.out.println("昵称："+user.getName());
            System.out.println("邮箱："+user.getEmail());
            System.out.println("备注：\n\t"+user.getRemark());
        }
    }

结果：

本文如果对您有所帮助记得点个赞哦 ^_^