Java Dao模式通過JDBC串連資料庫的操作

標籤：

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;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;    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();            }        }    }    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.Driverurl=jdbc:mysql://localhost:3306/testusername=rootpassword=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 + "]";    }    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);        }        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);        }    }}

到此基本實現了Dao模式通過JDBC操做資料庫了。

 

Java Dao模式通過JDBC串連資料庫的操作