编写一个可以使用2个数据库的应用程序？ [关闭]

Question

I'm trying to write a application in Go that is capable of connecting to oracle and MySQL based on a configuration. The issue I encountered now is when I used prepared statements. For example, consider the query below

Select * from data_table where id = 1

The corresponding prepared statement in MySQL and oracle is given below

MySQL -> Select * from data_table where id = ?
ORACLE -> Select * from data_table where id = :val1

If it is like this I have maintain 2 sets of queries and select the query based on the configuration. Is there a better way to do this?

I would like to avoid the hassle of keeping 2 sets of queries

Answer 1

It very much is by using interfaces.

Let‘s assume you create a web applications and you want to display users.

First, you‘d define an interface like

type Creator interface{
  Create(u User)(User,error)
}

type Reader interface{
  Read(k PrimaryKey)(User, error)
  ListAll()([]User,error)
  ListPaginated(page, offset int)([]User,error)
}

type Updater interface{
  Update(u User)(User, error)
  UpdateByKey(k PrimaryKey, u User)(User, error)
  UpdateMany(...User)error
}

type Deleter interface{
  Delete(u User)error
  DeleteMany(u ...User)error
  DeleteByKey(keys ...PrimaryKey)error
}

type CRUD interface {
  Creator
  Reader
  Updater
  Deleter
}

Next, implement the CRUD interface for each database type you want to support.

Now, you can create a handler:

// ListHandler uses an interface instead of a concrete type to
// retrieve the data from the databases.
// Not only does this approach make it possible to provide different
// implementations, but it makes unit testing way easier.
//
// "Thou Shalt Write Tests"
func ListHandler(rdr Reader) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {

        // Pagination ommited for brevity

        // Note that the handler is agnostic of the underlying implementation.
        u, err := rdr.ListAll()
        if err != nil {
            log.Printf("ListHandler: error retrieving user list: %s", err)
            // Do not do this in production! It might give an attacker information
            // Use a static error message instead!
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }

        if err := json.NewEncoder(w).Encode(u); err != nil {
            log.Printf("ListHandler: error encoding user list to JSON: %s", err)
            // See above
            http.Error(w, err.Error(), http.StatusInternalServerError)
        }
    })
}

and set it up something like this:

func main() {
    // Get your config
    // Then simply use an implementation of CRUD

    var dbConn CRUD
    switch cfg.DbType {
    case "myql":
        // returns your implementation of CRUD using MySQL
        dbConn = createMySQLConnector(cfg)
    case "oracle":
        // returns your implementation of CRUD using Oracle
        dbConn = createOracleConnector(cfg)
    }

    http.Handle("/users/", ListHandler(dbConn))

    log.Fatal(http.ListenAndServe("0.0.0.0:8080", nil))
}

hth