<repo_name>DrewKjellWCTC/bookWebApp<file_sep>/src/main/java/edu/wctc/adk/distjava/bookwebapp/model/MySQLDBAccessor.java
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.wctc.adk.distjava.bookwebapp.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.StringJoiner;
import java.util.stream.Collectors;

/**
 *
 * @author drew
 */
public class MySQLDBAccessor implements DBAccessor {
    
    private Connection conn;
    
    // Consider creating custom exception
    @Override
    public void openConnection(String driverClass, String url, String userName, String pwd) 
            throws ClassNotFoundException, SQLException{
        // Needs null check validation
        
        Class.forName(driverClass);
        conn = DriverManager.getConnection(url, userName, pwd);
    }
    
    @Override
    public int insertRecord(String table, Map<String,Object> colInfo) throws SQLException{
        int recordCount = -1;
        
        StringJoiner sj = new StringJoiner(",", "(", ")");
        
        for(String key : colInfo.keySet())
        {
            sj.add(key);
        }
        String sql = "INSERT into " + table + " " + sj.toString();
        
        sj = new StringJoiner(",", "(", ")");
        for(Object val : colInfo.values())
        {
            sj.add("?");
        }
        
        sql += " VALUES " + sj.toString();
        
        PreparedStatement stmt = conn.prepareStatement(sql);
        
        List<String> keys = new ArrayList(colInfo.keySet());
        
        for(int i = 0; i< keys.size(); i++)
        {
            stmt.setObject(i+1, colInfo.get(keys.get(i)));
            
        }
        
        recordCount = stmt.executeUpdate();
        
        
        return recordCount;
    }
    
    @Override
    public int deleteById(Object dataId, String table, String colName) throws SQLException
    {
        int recordCount = -1;
        String sql = "DELETE FROM " + table + " WHERE " + colName + " = ?";
        
        PreparedStatement stmt = conn.prepareStatement(sql);
        
        stmt.setObject(1, dataId);
        
        recordCount = stmt.executeUpdate();
                
        return recordCount;
    }
    
    @Override
    public int updateById(Object dataId, String table, String colName, Map<String,Object> colInfo) throws SQLException
    {
        int recordCount = -1;
        String sql = "UPDATE " + table + " SET ";
        
        StringJoiner sj = new StringJoiner(",");
        
        for(String key : colInfo.keySet())
        {
            sj.add(" " + key + " = ?");
        }
        sql += sj.toString();
        
        sql += " WHERE " + colName + " = ?";
                
        PreparedStatement stmt = conn.prepareStatement(sql);
        
        int colNum = 0;
        for (Object val : colInfo.values())
        {
            stmt.setObject(colNum + 1, val);
            colNum++;
        }
        
        stmt.setObject(colNum + 1, dataId);
        
        recordCount = stmt.executeUpdate();
                
        return recordCount;
    }
    
    @Override
    public List<Map<String, Object>> findRecordsFor(String tableName, int maxRecords) 
            throws SQLException{
        
        String sql = "";
        
        if (maxRecords > 0)
            sql = "SELECT * FROM " + tableName + " LIMIT " + maxRecords;
        else 
            sql = "SELECT * FROM " + tableName;
        
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        
        List<Map<String,Object>> results = new ArrayList<>();
        ResultSetMetaData rsmd = rs.getMetaData();
        
        // Get column count from meta data
        // Knowing # of columns using getInt() and column number
        int colCount = rsmd.getColumnCount();
        Map<String,Object> record = null;
        
        while (rs.next())
        {
            // Linked hash map maintains the order of the keys
            // Also faster for looping over the contents
            
            // Normal hash map does not maintain the order
            record = new LinkedHashMap<>();
            
            for (int colNo = 1; colNo <= colCount; colNo++)
            {
                // Store each column for each row as a map
                String colName = rsmd.getColumnName(colNo);
                Object data = rs.getObject(colNo);
                
                record.put(colName, data);
                
            }
            results.add(record);
        }
                       
        return results;
        
    }
    
    @Override
    public void closeConnection() throws SQLException{
        if (conn != null)
            conn.close();
    }
    
    public static void main(String[] args) throws Exception{
        DBAccessor db = new MySQLDBAccessor();
        
        db.openConnection("com.mysql.jdbc.Driver", 
                "jdbc:mysql://localhost:3306/book", 
                "root", "root");
        Map<String, Object> colInfo = new LinkedHashMap<>();
        
        colInfo.put("author_name", "<NAME>");
        colInfo.put("date_added", new Date());
        
        db.insertRecord("author", colInfo);
        
        List<Map<String,Object>> records = db.findRecordsFor("author", 10);
        for(Map<String,Object> record : records)
        {
            System.out.println(record);
        }
        
        colInfo.clear();
        colInfo.put("author_name", "<NAME>");
        colInfo.put("date_added", new Date());
        
        db.updateById(3, "author", "author_id", colInfo);
        
        records = db.findRecordsFor("author", 10);
        for(Map<String,Object> record : records)
        {
            System.out.println(record);
        }
        
        db.closeConnection();
        
        
        
        
    }
}
<file_sep>/src/main/java/edu/wctc/adk/distjava/bookwebapp/model/AuthorDao.java
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.wctc.adk.distjava.bookwebapp.model;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

/**
 *
 * @author drew
 */
public class AuthorDao implements IAuthorDao {
    private DBAccessor db;
    private String driverClass;
    private String url;
    private String userName;
    private String pwd;

    public AuthorDao(DBAccessor db, String driverClass, String url, String userName, String pwd) {
        this.db = db;
        this.driverClass = driverClass;
        this.url = url;
        this.userName = userName;
        this.pwd = pwd;
    }
    
    
    @Override
    public DBAccessor getDb() {
        return db;
    }

    @Override
    public void setDb(DBAccessor db) {
        this.db = db;
    }
    
    @Override
    public List<Author> getAuthorList(String tableName, int maxRecords) 
            throws ClassNotFoundException, SQLException{
        
        List<Author> authorList = new ArrayList<>();
        
        db.openConnection(driverClass, url, userName, pwd);
        
        List<Map<String,Object>> rawData = db.findRecordsFor(tableName, maxRecords);
        
        db.closeConnection();
        
        // Translate from rawData to authorList
        for(Map<String,Object> record : rawData)
        {
            Author author = new Author();
            // Don't need null check as author id is a PK
            author.setAuthorId((Integer)record.get("author_id"));
            
            Object objAuthorName = record.get("author_name");
            if (objAuthorName != null)
                author.setAuthorName(objAuthorName.toString());
            else
                author.setAuthorName("");
            // can use one line.
            String name = objAuthorName != null ? objAuthorName.toString() : "";
            author.setAuthorName(name);
            
            Object objDate = record.get("date_added");
            
            Date dateAdded = objDate != null ? (Date)objDate : null;
            
            author.setDateAdded(dateAdded);          
            
            authorList.add(author);
            
        }
        
        return authorList;
    }
    
    @Override
    public int addAuthor(String table, Author newAuth) throws SQLException, ClassNotFoundException{
        db.openConnection(driverClass, url, userName, pwd);
        
        // Map author object to Map for insert
        Map<String,Object> colInfo = new HashMap<>();
        
        colInfo.put("author_name", newAuth.getAuthorName());
        colInfo.put("date_added", newAuth.getDateAdded());
                
        int affectedRecords = db.insertRecord(table, colInfo);
        
        db.closeConnection();
        
        return affectedRecords;
    }
    
    @Override
    public int removeAuthor(String table, Author remAuth) throws ClassNotFoundException, SQLException{
        db.openConnection(driverClass, url, userName, pwd);
        int affected = db.deleteById(remAuth.getAuthorId(), table, "author_id");
        
        db.closeConnection();
        
        return affected;
    }
    
    @Override
    public int updateAuthor(String table, Author updAuth) throws SQLException, ClassNotFoundException{
        db.openConnection(driverClass, url, userName, pwd);
        
        Map<String,Object> colInfo = new HashMap<>();
        
        colInfo.put("author_name", updAuth.getAuthorName());
        if (updAuth.getDateAdded() != null)
            colInfo.put("date_added", updAuth.getDateAdded());
        
        int affected = db.updateById(updAuth.getAuthorId(), table, "author_id",  colInfo);
        
        db.closeConnection();
        
        return affected;
    }

    @Override
    public String getDriverClass() {
        return driverClass;
    }

    @Override
    public void setDriverClass(String driverClass) {
        this.driverClass = driverClass;
    }

    @Override
    public String getUrl() {
        return url;
    }

    @Override
    public void setUrl(String url) {
        this.url = url;
    }

    @Override
    public String getUserName() {
        return userName;
    }

    @Override
    public void setUserName(String userName) {
        this.userName = userName;
    }

    @Override
    public String getPwd() {
        return pwd;
    }

    @Override
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
    
    public static void main(String[] args) throws Exception {
        
        
        IAuthorDao test;
        test = new AuthorDao(new MySQLDBAccessor(), 
                "com.mysql.jdbc.Driver", 
                "jdbc:mysql://localhost:3306/book", "root", "root");
        
        Author tmp = new Author();
        tmp.setAuthorName("<NAME>");
        tmp.setDateAdded(new Date());
        
        test.addAuthor("author", tmp);
        
        tmp.setAuthorId(11);
        tmp.setAuthorName("DAO Updated");
        
        System.out.println(test.updateAuthor("author", tmp));
        
        List<Author> results = test.getAuthorList("author", 100);
        
        for (Author row : results)
        {
            System.out.println(row);
        }
        
        System.out.println(test.removeAuthor("author", tmp));
        
        
    }
    
    
}
