JDBC访问数据库以及分层开发

poilt

已于 2023-11-14 19:59:41 修改

阅读量78

点赞数

文章标签：数据库

于 2023-10-18 14:44:32 首次发布

本文链接：https://blog.csdn.net/poilt/article/details/133854367

版权

一、概念

JDBC：Java database connectivity （Java数据库连接技术）

作用：（1）建立与数据库之间的额访问链接

（2）将编写好的sql语句发送到数据库执行

（3）对数据库返回的结果进行处理

二、JDBC连接数据库五步

（1）加载驱动

（2）通过DiverManager连接的对象

（3）创建preparedstateMent对象用来执行sql语句

（4）处理sql语句执行结果

（5）释放资源

三、JDBC连接代码

ps.URL中的hospital是你所使用的数据库

package dao;

import java.sql.*;

public class PatientDao {
    public static void main(String[] args) {
        ResultSet resultSet = null;
        Connection conn = null;
        Statement statement = null;
        try {
            //1.加载驱动（固定写法）
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.用户信息、URL（固定写法）//**这个hospital是你所要使用的数据库
            String url = "jdbc:mysql://localhost:3306/hospital?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8";
            String username = "root";
            String password = "123456";
            //3.进行连接，数据库对象（固定写法）
            conn = DriverManager.getConnection(url, username, password);
            //4.执行sql的对象(固定写法)
            statement = conn.createStatement();
            //5.执行sql语句，返回结果集
            String sql = "select p.* from patient p";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("pname");
                int age = resultSet.getInt("page");
                String pcase = resultSet.getString("pcase");
                System.out.println(id + "," + name + "," + age + "," + pcase);
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                resultSet.close();
                statement.close();
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

        }
    }

}

四、使用dao模式开发，四块功能封装BaseDao

package cn.kgc.dao;



import java.sql.*;

public class BaseDao {
    protected Connection conn=null;
    protected Statement stmt=null;
    protected ResultSet rs=null;
    protected PreparedStatement pstmt=null;
    //1、连接数据库
    public boolean getConnection(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.用户信息、URL（固定写法）
            String url = "jdbc:mysql://localhost:3306/hospital";
            String username = "root";
            String password = "123456";
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return  true;
    }
    //2、关闭流
    public void closeAll() {
        try {
            if (rs != null) {
                rs.close();
            }
            if(pstmt!=null){
                pstmt.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }


    //3.执行增删改语句
    public int executeUpdate(String sql, Object[] params){
        int count = 0;
        try {
            if(getConnection()){
                pstmt = conn.prepareStatement(sql);
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i+1,params[i]);
                }
                count = pstmt.executeUpdate();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return count;

    }

    //4.执行查询语句
    public ResultSet executeQuery(String sql,Object[] params){
        try {
            if(getConnection()){
                pstmt = conn.prepareStatement(sql);
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i+1,params[i]);
                }
                rs = pstmt.executeQuery();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return rs;
    }


}