Java数据库连接（JDBC）的实现详解

JDBC（Java DataBase Connectivity）是Java语言访问数据库的一种标准API，其核心在于通过数据库 URL连接到数据库系统。本文将详细介绍JDBC编程的实现步骤、常用类及其应用实例。

1. 获取数据库连接的基础步骤

数据库连接是应用程序与数据库之间的桥梁，建立连接的关键在于正确配置数据库 URL和加载相应的数据库驱动。

使用JDBC获取数据库连接的步骤如下：

public class JdbcUtil {
    private final String userName;
    private final String password;
    private final String database;
    private final String host;
    private final Integer port;
    public JdbcUtil(String userName, String password, String database, String host, Integer port) {
        this.userName = userName;
        this.password = password;
        this.database = database;
        this.host = host;
        this.port = port;
    }
    // 获得数据库连接
    public Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String url = String.format(
            "jdbc:mysql://%s:%d/%s?user=%s&password=%s",
            host,
            port,
            database,
            userName,
            password
        );
        try {
            return DriverManager.getConnection(url);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    // 创建预编译语句
    public PreparedStatement getPreparedStatement(Connection connection, String sql) {
        try {
            return connection.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    // 执行数据库操作
    public int update(PreparedStatement preparedStatement) {
        try {
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    // 关闭数据库资源
    public void doClose(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
2. 数据库操作的实现类

2. 数据库操作的实现类

基于上述JdbcUtil类，可以对数据库进行 CRUD（增删改查）操作。以下是MemoGroupDaoImpl的实现代码：

public class MemoGroupDaoImpl implements MemoGroupDao {
    private JdbcUtil jdbcUtil;
    public MemoGroupDaoImpl(JdbcUtil jdbcUtil) {
        this.jdbcUtil = jdbcUtil;
    }
    @Override
    public boolean insert(MemoGroup memoGroup) {
        String sql = "insert into memo_group(name, created_time) values(?,?)";
        Connection connection = jdbcUtil.getConnection();
        PreparedStatement preparedStatement = jdbcUtil.getPreparedStatement(connection, sql);
        try {
            preparedStatement.setString(1, memoGroup.getName());
            preparedStatement.setDate(2, new Date(memoGroup.getCreatedTime().getTime()));
            int effect = jdbcUtil.update(preparedStatement);
            return effect == 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.doClose(null, preparedStatement, connection);
        }
        return false;
    }
    @Override
    public boolean update(MemoGroup memoGroup) {
        String sql = "update memo_group set name = ? where id = ?";
        Connection connection = jdbcUtil.getConnection();
        PreparedStatement preparedStatement = jdbcUtil.getPreparedStatement(connection, sql);
        try {
            preparedStatement.setString(1, memoGroup.getName());
            preparedStatement.setInt(2, memoGroup.getId());
            int effect = jdbcUtil.update(preparedStatement);
            return effect == 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.doClose(null, preparedStatement, connection);
        }
        return false;
    }
    @Override
    public boolean delete(int id) {
        String sql = "delete from memo_group where id = ?";
        Connection connection = jdbcUtil.getConnection();
        PreparedStatement preparedStatement = jdbcUtil.getPreparedStatement(connection, sql);
        try {
            preparedStatement.setInt(1, id);
            int effect = jdbcUtil.update(preparedStatement);
            return effect == 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.doClose(null, preparedStatement, connection);
        }
        return false;
    }
    @Override
    public MemoGroup queryById(int id) {
        String sql = "select * from memo_group where id = ?";
        Connection connection = jdbcUtil.getConnection();
        PreparedStatement preparedStatement = jdbcUtil.getPreparedStatement(connection, sql);
        ResultSet resultSet = null;
        try {
            preparedStatement.setInt(1, id);
            resultSet = jdbcUtil.query(preparedStatement);
            if (resultSet.next()) {
                MemoGroup memoGroup = new MemoGroup();
                memoGroup.setId(resultSet.getInt("id"));
                memoGroup.setName(resultSet.getString("name"));
                memoGroup.setCreatedTime(resultSet.getDate("created_time"));
                memoGroup.setModifyTime(resultSet.getDate("modify_time"));
                return memoGroup;
            } else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.doClose(resultSet, preparedStatement, connection);
        }
        return null;
    }
    @Override
    public List
    
      queryAll() {
        String sql = "select * from memo_group";
        Connection connection = jdbcUtil.getConnection();
        PreparedStatement preparedStatement = jdbcUtil.getPreparedStatement(connection, sql);
        ResultSet resultSet = null;
        List
     
       memoGroups = new ArrayList<>();
        try {
            resultSet = jdbcUtil.query(preparedStatement);
            while (resultSet.next()) {
                MemoGroup memoGroup = new MemoGroup();
                memoGroup.setId(resultSet.getInt("id"));
                memoGroup.setName(resultSet.getString("name"));
                memoGroup.setCreatedTime(resultSet.getDate("created_time"));
                memoGroup.setModifyTime(resultSet.getDate("modify_time"));
                memoGroups.add(memoGroup);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.doClose(resultSet, preparedStatement, connection);
        }
        return memoGroups;
    }
}
     
    

3. 注意事项

在实际应用中，建议使用连接池优化数据库连接管理。每次获取连接时请记得闭合资源，以避免资源泄漏。此外，对于敏感信息如数据库密码，请妥善处理保护。

希望以上内容对您有所帮助，如果需要进一步的Clrify或修正，请随时与我联系。