Java Dao模式通过JDBC连接数据库的操作

Love The Way You Lie 2021-07-24 15:27 381阅读 0赞

**Java程序访问数据库：**

*  1、获取数据库厂商提供的驱动（jdbc接口的实现类）

如ojdbc14.jar——Oracle数据库驱动jar包

mysql-connector-java-5.1.8-bin.jar——MySQL数据库驱动jar包

自己去网上下载就行。

*  2、使用JDBC的API访问数据库

连接、SQL语句执行、结果

java.sql.Driver：各个数据库厂商需要实现该接口，驱动的标记

java.sql.Connection：封装和数据库的连接

java.sql.Statement：封装需要执行的SQL语句

java.sql.ResultSet：封装查询的结果集

*  3、JDBC编程步骤

step1——加载驱动

step2——获取连接对象

step3——执行SQL语句

step4——处理结果集

step5——关闭资源

*  4、下面给出连接数据库的工具类（自己写的连接MySql数据库，如要连接Oeacle可修改对应参数）

package com.day03;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    
    
    //加入Java开发交流君样：756584822一起吹水聊天
    public class ConnectionUtils {
    
        // 线程单例
        private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
        private static String url;
        private static String driver;
        private static String username;
        private static String password;
    
    
    //加入Java开发交流君样：756584822一起吹水聊天
        static {
            Properties props = new Properties();
            try {
                // 从属性文件中读取数据库配置信息，以加载类的方式加载配置文件
                props.load(
                        ConnectionUtils.class.getClassLoader()
                        .getResourceAsStream("com/day03/db_mysql.properties"));
    
            } catch (IOException e) {
            }
            if (props != null) {
                url = props.getProperty("url");
                driver = props.getProperty("driver");
                username = props.getProperty("username");
                password = props.getProperty("password");
    
                // 装载并注册数据库驱动
                try {
                    Class.forName(driver);
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
            }
        }//加入Java开发交流君样：756584822一起吹水聊天
    
        public static Connection getConnection() throws SQLException {
            Connection con = tl.get();
            if (con == null) {
                con = DriverManager.getConnection(url, username, password);
                tl.set(con);
            }
            return con;
        }
    
        public static void closeConnection() {
            Connection con = tl.get();
            try {
                if (con != null) {
                    con.close();
                    tl.set(null);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
        public static void closeStatement(Statement stmt) {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
        public static void closeResultSet(ResultSet rs) {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        public static void closeAll(Statement stmt, ResultSet rs){
            closeConnection();
            closeStatement(stmt);
            closeResultSet(rs);
        }
        
        public static void main(String[] args) throws Exception{
            System.out.println(ConnectionUtils.getConnection());
        }
    
    }

## 5、配置参数文件db\_mysql.properties ##

driver=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost:3306/test
    username=root
    password=root

**6、Dao模式操作数据库下面是代码示例**

**1）Emp.java**

//实体类
    public class Emp {
        private int id;
        private String name;
        private double salary;
        public int getId() {
            return id;
        }
        @Override
        public String toString() {
            return "Emp [id=" + id + ", name=" + name + ", salary=" + salary + "]";
        }//加入Java开发交流君样：756584822一起吹水聊天
        public void setId(int id) {
            this.id = id;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
        public double getSalary() {
            return salary;
        }
        public void setSalary(double salary) {
            this.salary = salary;
        }
        public Emp(int id, String name, double salary) {
            super();
            this.id = id;
            this.name = name;
            this.salary = salary;
        }
        public Emp() {
            super();
        }
        public Emp(String name, double salary) {
            super();
            this.name = name;
            this.salary = salary;
        }
    }

**2)Dao接口类**

import java.util.List;
    
    public interface EmpDao {
        List<Emp> findAllEmp() throws Exception;
    }

**3）工厂类**

public class EmpDaoFactory {
        // 读取文件中实现类的类名，通过反射实例化
        public static EmpDao getEmpDao(){
            return new EmpDaoMySQL();
        }
    }

**4）Dao接口实现类**

public class EmpDaoMySQL implements EmpDao{
        public static final String FIND_ALL_EMP = "select * from t_emp";//查询语句
        
        public List<Emp> findAllEmp() throws Exception{
            List<Emp> empList = new ArrayList<Emp>();
            Connection conn = ConnectionUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(FIND_ALL_EMP);
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString(2);
                double salary = rs.getDouble(3);
                Emp emp = new Emp(id, name, salary);
                empList.add(emp);
            }//加入Java开发交流君样：756584822一起吹水聊天
            ConnectionUtils.closeAll(stmt, rs);
            return empList;
        }
    }

**5）测试类**

public class EmpBiz {
        public static void main(String[] args) throws Exception{
            EmpDao dao = EmpDaoFactory.getEmpDao();
            List<Emp> empList = dao.findAllEmp();
            for(Emp e : empList){
                System.out.println(e);
            }
        }
    }

![在这里插入图片描述][20201222203301985.gif_pic_center]

[20201222203301985.gif_pic_center]: /images/20210724/15b56ad71ebe4cad96e90a5e470548f1.png