【JDBC连接数据库】

本文为JDBC连接数据库的一个小工具，仅限初学者练习和复习，不可用于线上环境

注意：本示例需要加载mysql驱动包，mysql-connector-java-5.1.39.jar

 

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.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DataBaseUtils {

    private static final String URL = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
    private static final String USERNAME = "test1";
    private static final String PASSWORD = "123456";


    private static Connection CONN = null;


    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection openConnection() {
        try {
            return CONN = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return CONN;
    }

    public static List<String> getColumnNameList(ResultSet resultSet) throws SQLException {
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int columnCount = resultSetMetaData.getColumnCount();
        List<String> columnNameList = new ArrayList<>(columnCount << 1);
        for (int i = 1; i <= columnCount; i++) {
            columnNameList.add(resultSetMetaData.getColumnName(i));
        }
        return columnNameList;
    }

    public static List<Map<String, Object>> query(String sql) throws SQLException {
        openConnection();
        PreparedStatement preparedStatement = CONN.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Map<String, Object>> resultList = new ArrayList<>();
        List<String> columnNameList = getColumnNameList(resultSet);
        while (resultSet.next()) {
            Map<String, Object> rowData = new HashMap<>();
            for (String column : columnNameList) {
                rowData.put(column, resultSet.getString(column));
            }
            resultList.add(rowData);
        }
        resultSet.close();
        preparedStatement.close();
        CONN.close();
        return resultList;
    }

    public static int update(String sql) throws SQLException {
        openConnection();
        PreparedStatement preparedStatement = CONN.prepareStatement(sql);
        return preparedStatement.executeUpdate();
    }

    public static int save(String sql) throws SQLException {
        return update(sql);
    }

    public static int delete(String sql) throws SQLException {
        return update(sql);
    }


    public static void main(String[] atgs) throws SQLException {
        List<Map<String, Object>> resultList = query("select * from test.tableOne limit 0,100");
        System.out.println(resultList);
    }


    /**
     * 格式化字符串
     * @param old
     * @param length
     * @return
     */
    public static String format(String old, int length) {
        StringBuilder stringBuilder = new StringBuilder(null==old?"":old);
        int hasLen = length - length(stringBuilder.toString());
        while (hasLen-->0) {
            stringBuilder.append(" ");
        }
        return stringBuilder.toString();
    }


    /**
     * 计算字符串长度
     * @param value
     * @return
     */
    public static int length(String value) {
        String chinese = "[\u0391-\uFFE5]";
        int len = 0;
        for (char c : value.toCharArray()) {
            len += String.valueOf(c).matches(chinese)? 2 : 1;
        }
        return len;
    }


}