天天看點
傳回

111 JDBC_4 _三層架構、DAOUtils

111 JDBC_4 _三層架構、DAOUtils

文章目錄

  • ​​111 JDBC_4 _三層架構、DAOUtils​​
  • ​​一、三層架構​​
  • ​​1.1什麼是三層​​
  • ​​1.2三層架構項目搭建（按開發步驟）​​
  • ​​二、DaoUtils​​

一、三層架構

1.1什麼是三層

  • 表示層：

    命名：XXXView

    職責：收集使用者的資料和需求、展示資料。

  • 業務邏輯層：

    命名：XXXServicelmpl

    職責：資料加工處理、調用DAO完成業務實作、控制事務。

  • 資料通路層：

    命名：XXXDaoImpl

    職責：向業務層提供資料，将業務層加工後的資料同步到資料庳。

  • 三層架構核流程：
    • 111 JDBC_4 _三層架構、DAOUtils

1.2三層架構項目搭建（按開發步驟）

  • utils存放工具類（DBUtils)
  • entity存放實體類（Person)
  • dao 存放 DAO 接口（PersonDao)
  • impl 存放 DAO 接口 實作類（PersonDaoImpl)
  • service 存放 service 接口 (PersonService)
  • impl 存放 service接口實作類（PersonServicelmpI〉
  • view存放程式啟動類（main)
  • 程式設計時，考慮易修改、易擴充，為Service層和DAO層設計接口，便于未來更換實作類

二、DaoUtils

  • 在DAO層中，對資料庫表的增、删、改、查操作存在代碼備援，
  • 可對其逬行抽取封裝DaoUtils工具類實作複用。 
package com.wlw.chapter1_JDBC.demo04_person.utils;

import com.wlw.chapter1_JDBC.demo04_person.advanced.RowMapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//複用 增 删 改 查方法，提取公共部分
public class DaoUtils<T> {

    /**
     *複用 增 删 改，公共處理
     * @param sql 執行的sql語句
     * @param args 參數清單
     * @return 傳回受影響的行數
     */
    public int commonsUpdate(String sql,Object... args){
        Connection connection= null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            int result = preparedStatement.executeUpdate();

            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.CloseAll(null,preparedStatement,null);
        }
        return 0;
    }

    /**
     * 公共的查詢方法（可查詢任意一張表，可查詢單個對象、也可查詢多個對象）
     * @param sql 執行的sql語句
     * @param args 參數清單
     * @return 集合
     */
    public List<T> commonsSelect(String sql, RowMapper<T> rowMapper, Object... args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet =null;
        List<T> list = new ArrayList<>();
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            if(args != null){
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i+1,args[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                //如何根據查詢結果來完成ORM，如何進行對象的建立與封裝
                T t = rowMapper.getRow(resultSet);//回調--> 調用者提供一個封裝方法
                list.add(t);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.CloseAll(null,preparedStatement,resultSet);
        }
        return null;
    }
}      
package com.wlw.chapter1_JDBC.demo04_person.utils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DateUtils {
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    //1.将字元串轉換為 util.Date
    public static Date strToUtil(String str){
        try {
            Date date = sdf.parse(str);
            return date;
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }

    //2.将 util.Date 轉換為 sql.Date
    public static java.sql.Date utilToSql(Date date){
        return new java.sql.Date(date.getTime());
    }

    //3.将util.Date 轉換為 字元串
    public static String utilToStr(Date date){
        return sdf.format(date);
    }
}      
package com.wlw.chapter1_JDBC.demo04_person.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class DBUtils {
    private static final Properties properties = new Properties();
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

    static {
        InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
        try {
            properties.load(is);
            Class.forName(properties.getProperty("driver"));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        Connection connection = threadLocal.get();//将目前線程中綁定的Connection對象，指派給connection
        try {
            if(connection == null){
                connection = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
                threadLocal.set(connection);//把第一次獲得的連接配接 存在目前線程中
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //開啟事務
    public static void begin(){
        try {
            Connection connection = getConnection();
            connection.setAutoCommit(false);//設定手動送出
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //送出事務
    public static void commit(){
        Connection connection = null;
        try {
            connection = getConnection();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            CloseAll(connection,null,null);
        }
    }
    //復原事務
    public static void rollback(){
        Connection connection = null;
        try {
            connection = getConnection();
            connection.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            CloseAll(connection,null,null);
        }
    }

    public static void CloseAll(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if(resultSet != null){
                resultSet.close();
            }
            if(statement !=null){
                statement.close();
            }
            if(connection != null){
                connection.close();
                threadLocal.remove();//關閉連接配接後，移除已關閉的Connection對象
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}      
db.properties:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8
username=root
password=123456      
package com.wlw.chapter1_JDBC.demo04_person.entity;
import java.util.Date;
//實體類 entity
public class Person {
    private int id;
    private String name;
    private int age;
    private Date bornDate;
    private String email;
    private String address;
    public Person() {}
    public Person(String name, int age, Date bornDate, String email, String address)    {
        this.name = name;
        this.age = age;
        this.bornDate = bornDate;
        this.email = email;
        this.address = address;
    }
    public Person(int id, String name, int age, Date bornDate, String email, String address) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.bornDate = bornDate;
        this.email = email;
        this.address = address;
    }
    public int getId() { return id;}
    public void setId(int id) {this.id = id;}
    public String getName() {return name;}
    public void setName(String name) {this.name = name;}
    public int getAge() {return age;}
    public void setAge(int age) {this.age = age;}
    public Date getBornDate() {return bornDate;}
    public void setBornDate(Date bornDate) {this.bornDate = bornDate;}
    public String getEmail() {return email;}
    public void setEmail(String email) {this.email = email;}
    public String getAddress() {return address;}
    public void setAddress(String address) {this.address = address;}
    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", bornDate=" + bornDate +
                ", email='" + email + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}      
package com.wlw.chapter1_JDBC.demo04_person.dao;
import com.wlw.chapter1_JDBC.demo04_person.entity.Person;
import java.util.List;
public interface PersonDao {
    public int insert(Person person);
    public int update(Person person);
    public int delete(int id);
    public Person select(int id);
    public List<Person> selectAll();
}      
package com.wlw.chapter1_JDBC.demo04_person.dao.impl;

import com.wlw.chapter1_JDBC.demo04_person.advanced.impl.PersonRowMapper;
import com.wlw.chapter1_JDBC.demo04_person.utils.DaoUtils;
import com.wlw.chapter1_JDBC.demo04_person.utils.DateUtils;
import com.wlw.chapter1_JDBC.demo04_person.entity.Person;
import com.wlw.chapter1_JDBC.demo04_person.dao.PersonDao;
import java.util.List;

public class PersonDaoImpl implements PersonDao {
    private DaoUtils<Person> daoUtils = new DaoUtils();
    //新增
    public int insert(Person person){
        String sql = "insert into Person(name,age,bornDate,email,address) values (?,?,?,?,?)";
        return daoUtils.commonsUpdate(sql, person.getName()
                , person.getAge(), DateUtils.utilToSql(person.getBornDate())
                , person.getEmail(), person.getAddress());
    }
    //修改
    public int update(Person person){
        String sql = "update Person set name=? ,age=?,bornDate=?,email=?,address=? where id=?";
        return daoUtils.commonsUpdate(sql,person.getName()
                , person.getAge(), DateUtils.utilToSql(person.getBornDate())
                , person.getEmail(), person.getAddress(),person.getId());
    }
    //删除
    public int delete(int id){
        String sql = "delete from Person where id=?;";
        return daoUtils.commonsUpdate(sql,id);
    }
    //查詢單個
    public Person select(int id){
        String sql= "select * from Person where id=?;";
        List<Person> list = daoUtils.commonsSelect(sql,new PersonRowMapper(),id);
        if (!list.isEmpty()) {
            return list.get(0);
        }
        return null;
    }
    //查詢全部
    public List<Person> selectAll(){
        String sql = "select * from Person";
        List<Person> list = daoUtils.commonsSelect(sql, new PersonRowMapper(), null);
        return list;
    }
}      
package com.wlw.chapter1_JDBC.demo04_person.service;
public interface PersonService {}      
package com.wlw.chapter1_JDBC.demo04_person.service.impl;
import com.wlw.chapter1_JDBC.demo04_person.service.PersonService;
public class PersonServiceImpl implements PersonService {}      
package com.wlw.chapter1_JDBC.demo04_person.advanced;
import java.sql.ResultSet;
/**
 * 限制封裝對象的ORM
 */
public interface RowMapper<T> {
    public T getRow(ResultSet resultSet);
}      
package com.wlw.chapter1_JDBC.demo04_person.advanced.impl;

import com.wlw.chapter1_JDBC.demo04_person.entity.Person;
import com.wlw.chapter1_JDBC.demo04_person.advanced.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class PersonRowMapper implements RowMapper<Person> {
    @Override
    public Person getRow(ResultSet resultSet) {
        Person person = null;
        try {
            int pid = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            Date bornDate = resultSet.getDate("bornDate");
            String email = resultSet.getString("email");
            String address = resultSet.getString("address");
            person = new Person(pid,name,age,bornDate,email,address);
            return person;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}      
package com.wlw.chapter1_JDBC.demo04_person.view;

import com.wlw.chapter1_JDBC.demo04_person.dao.PersonDao;
import com.wlw.chapter1_JDBC.demo04_person.dao.impl.PersonDaoImpl;
import com.wlw.chapter1_JDBC.demo04_person.entity.Person;
import com.wlw.chapter1_JDBC.demo04_person.utils.DateUtils;

import java.util.List;

public class TestPerson {
    public static void main(String[] args) {
        PersonDao personDao = new PersonDaoImpl();
        //Person person = new Person("xiaoli",20, DateUtils.strToUtil("2000-03-03"),"[email protected]","中國杭州");
        //int result = personDao.insert(person);

        //Person person = new Person(9,"xiaoli",22, DateUtils.strToUtil("1998-03-03"),"[email protected]","中國杭州");
        //int result = personDao.update(person);

        //int result = personDao.delete(9);
        //System.out.println(result);

        //Person person = personDao.select(1);
        //System.out.println(person);

        List<Person> people = personDao.selectAll();
        for (Person person : people) {
            System.out.println(person);
        }

    }
}
java JDBC mysql sql 三層架構
上一篇: shorewall 企業防火牆的完美實作
下一篇: 如何使用Python進行異常檢測?我來教你呀~

繼續閱讀