Java - JDBC封装和简单的读取代码

DataSource: 数据源配置，主要是配置数据库的连接，驱动，用户名，密码。

这里可以用定死的默认值，也可以用get set方法。

public class DataSource {


    private String url = "";
    private String driver = "com.mysql.jdbc.Driver";
    private String username = "root";
    private String password = "focuss";

    public String getUrl() {
        return url;
    }
    public String getDriver() {
        return driver;
    }
    public String getUsername() {
        return username;
    }
    public String getPassword() {
        return password;
    }

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

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "DataSource{" +
                "url='" + url + '\'' +
                ", driver='" + driver + '\'' +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
    
}

BaseDao:  JDBC操作基础工具类，用来进行基础的数据库操作。

import java.sql.*;
import java.util.List;
import java.util.Map;

public class BaseDao {

    private DataSource dataSource;

    private static Connection connection = null;

    private static PreparedStatement preparedStatement = null;

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        if (null == this.dataSource) {
            try {
                Class.forName(this.dataSource.getDriver());
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
        this.dataSource = dataSource;
    }

    public BaseDao() {

    }

    public BaseDao(DataSource dataSource) {
        this.dataSource = dataSource;
        try {
            Class.forName(this.dataSource.getDriver());
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() {
        try {
            if (null == connection) {
                connection = DriverManager.getConnection(this.dataSource.getUrl(), this.dataSource.getUsername(), this.dataSource.getPassword());
            }
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败");
            return null;
        }
    }


    public void closeAll(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
        try {
            if (null != connection) {
                connection.close();
            }
            if (null != preparedStatement) {
                preparedStatement.close();
            }
            if (null != resultSet) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public List<Map<String, Object>> select(String sql) {
        ResultSet resultSet = null;
        try {
            preparedStatement = getConnection().prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            return ResultData.getDataByResult(resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(connection, preparedStatement, resultSet);
        }
        return null;
    }

    public void execute(String sql) {
        try {
            preparedStatement = getConnection().prepareStatement(sql);
            preparedStatement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(connection, preparedStatement, null);
        }
    }

}

ResultData：自己用来读取ResultSet封装成list的工具类

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ResultData {

    public static List<Map<String, Object>> getDataByResult(ResultSet resultSet) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            Integer columnCount = resultSet.getMetaData().getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<>();
                for (Integer i = 0; i < columnCount; i++) {
                    map.put(resultSet.getMetaData().getColumnName(i + 1), resultSet.getObject(i + 1));
                }
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}

Test: 简单一个读取案例

        DataSource dataSource = new DataSource();
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/cms");
        dataSource.setDriver("com.mysql.jdbc.Driver");
        dataSource.setPassword("root");
        dataSource.setUsername("root");
        BaseDao baseDao = new BaseDao(dataSource);
        List<Map<String, Object>> list = baseDao.select("select * from file");
        list.forEach(x->{
            x.forEach((k,v)->{
                System.out.print( k + ":" + v + " \t");
            });
            System.out.println();
        });

测试结果: