java通过JDBC操作mysql

这里只操作查询示例

数据表结构

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

JdbcConnection.java 文件,封闭基本的操作方法

package java_project;

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.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class JdbcConnection{
    
     // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    static final String DB_URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
 
    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "root";
    static final String PASS = "";
    Connection conn = null;
    PreparedStatement  stmt = null;
    ResultSet rs = null;
    
    private void connectionDb() {
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
        }catch(SQLException e1) {
            e1.printStackTrace();
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
 
    public List<Map<String,Object>>  getResult(String sql,String[] coulmns, int[] types) {

        List<Map<String,Object>> list = null;
        try{
            connectionDb();
            setParams(sql,coulmns,types);
             rs = stmt.executeQuery();
            
            list = handleResultSetToMapList(rs);
            // 完成后关闭
            closeDb(conn,stmt,rs);
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally {
            closeDb(conn,stmt,rs);
        }
        return list;
    }
    
    
    //格式化结果为集合
    private List<Map<String,Object>> handleResultSetToMapList(ResultSet resultSet) throws SQLException{
        List<Map<String,Object>> values = new ArrayList<>();
        List<String> columnLabels = getColumnLabels(resultSet);
        Map<String,Object> map = null;
        while(resultSet.next()) {
            map = new HashMap<>();
            for(String columnLabel : columnLabels) {
                Object value = resultSet.getObject(columnLabel);
                map.put(columnLabel, value);
            }
            values.add(map);
        }
        return values;
    }
    
    
    // 获取表头，取表列名称
    private List<String> getColumnLabels(ResultSet rs) throws SQLException{
        List<String> labels = new ArrayList<>();
        ResultSetMetaData rsmd = rs.getMetaData();
        for(int i =0; i< rsmd.getColumnCount(); i++) {
            labels.add(rsmd.getColumnLabel(i+1));
        }
        return labels;
    }
    
    
    private void closeDb(Connection connection,PreparedStatement prepareStatement, ResultSet resultSet) {
        // 关闭结果集合       
        if(resultSet != null) {
            try
            {
                resultSet.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
        
        // 关闭预见处理语句
        if(prepareStatement != null) {
            try {
                prepareStatement.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
        
        // 关闭连接       
        if(connection != null) {
            try {
                connection.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
        
    }
    
    //格式化参数
    private boolean setParams(String sql,String[] columns, int[] types) throws NumberFormatException,SQLException{
        if(sql == null) {
            return false;
        }
        stmt = conn.prepareStatement(sql);
        if(columns != null && types != null && columns.length != 0 && types.length != 0) {
            for(int i  = 0; i < types.length; i ++) {
                switch(types[i]) {
                case Types.INTEGER:
                    stmt.setInt(i+1, Integer.parseInt(columns[i]));
                    break;
                case Types.BOOLEAN:
                    stmt.setBoolean(i+1, Boolean.parseBoolean(columns[i]));
                    break;
                case Types.CHAR:
                    stmt.setString(i+1, columns[i]);
                    break;
                case Types.DOUBLE:
                    stmt.setDouble(i+1, Double.parseDouble(columns[i]));
                    break;
                case Types.FLOAT:
                    stmt.setFloat(i+1, Float.parseFloat(columns[i]));
                    break;
                    default:
                        break;
                }
            }
        }
        return true;
    }
    
}
    

示例测试   Hello.java

package java_project;
import java.sql.Types;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class Hello{

    public static void main(String[] args) {
        JdbcConnection jdbcConnection = new JdbcConnection();
        String[] columns = new String[] {"2"};
        int[] types = new int[] {Types.INTEGER};
        String sql = "select *  from test  where id > ? limit 2";

        List<Map<String,Object>> list = jdbcConnection.getResult(sql,columns,types);
        System.out.println(list);
        for(int i =0; i < list.size();i++) {
            Set<Map.Entry<String, Object>> map = list.get(i).entrySet();
            for(Map.Entry<String, Object> obj : map) {
                String key = obj.getKey();
                Object val = obj.getValue();
                System.out.print(key+":"+val+"\t");
            }
            System.out.println();
        }
    }
}