回顧以前學(xué)習(xí)HeroDAO，在每個(gè)DAO里自行創(chuàng)建Connnection驯妄，在這個(gè)項(xiàng)目里有多個(gè)DAO里都需要獲取數(shù)據(jù)庫(kù)的連接荷并，并且在本項(xiàng)目中都是一樣的數(shù)據(jù)庫(kù)連接。 所以就可以把獲取數(shù)據(jù)庫(kù)連接的代碼重構(gòu)到一個(gè)類(lèi)里青扔。

這樣做的好處是有兩個(gè)

1. 不需要DAO里分別進(jìn)行編寫(xiě)源织，直接調(diào)用就可以了

2. 如果賬號(hào)密碼發(fā)生了變化，值需要修改這一個(gè)地方微猖，而不用每個(gè)DAO里就分別修改谈息，降低了維護(hù)成本，也降低了因?yàn)橥浶薷亩鲥e(cuò)的概率

package util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBUtil {

    static String ip = "127.0.0.1";

    static int port = 3306;

    static String database = "hutubill";

    static String encoding = "UTF-8";

    static String loginName = "root";

    static String password = "admin";

    static{

        try {

            Class.forName("com.mysql.jdbc.Driver");

        } catch (ClassNotFoundException e) {

            e.printStackTrace();

        }

    }

    public static Connection getConnection() throws SQLException {

        String url = String.format("jdbc:mysql://%s:%d/%s?characterEncoding=%s", ip, port, database, encoding);

        return DriverManager.getConnection(url, loginName, password);

    }

}

使用:

 public int getTotal() {
        int total = 0;
        try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
 
            String sql = "select count(*) from config";
 
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                total = rs.getInt(1);
            }
 
            System.out.println("total:" + total);
 
        } catch (SQLException e) {
 
            e.printStackTrace();
        }
        return total;
    }
 
    public void add(Config config) {
 
        String sql = "insert into config values(null,?,?)";
        try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
            ps.setString(1, config.key);
            ps.setString(2, config.value);
            ps.execute();
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                int id = rs.getInt(1);
                config.id = id;
            }
        } catch (SQLException e) {
 
            e.printStackTrace();
        }
    }

• ResultSet rs = ps.getGeneratedKeys();//獲取主鍵

• 想要返回對(duì)象集合的用executeQuery執(zhí)行,不返回則用execute.

public Config get(int id) {
        Config config = null;
 
        try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
 
            String sql = "select * from config where id = " + id;
 
            ResultSet rs = s.executeQuery(sql);
 
            if (rs.next()) {
                config = new Config();
                String key = rs.getString("key_");
                String value = rs.getString("value");
                config.key = key;
                config.value = value;
                config.id = id;
            }
 
        } catch (SQLException e) {
 
            e.printStackTrace();
        }
        return config;
    }