jdbc编程基础

我们先写一个用来置换sql的工具类：

 

package com.local.jdbc;

import com.local.util.MapUtil;
import com.local.util.ObjectUtil;

import java.util.Map;
import java.util.Set;

/**
 * @date 2023/4/15 21:11
 * @description 预备sql
 */
public class PreliminarySql {
    private String sourceSql;
    private Map<String, Object> param;
    private String targetSql;

    public PreliminarySql(String sourceSql, Map<String, Object> param) {
        this.sourceSql = sourceSql;
        this.param = param;
        if(!MapUtil.isEmpty(param)) {
            targetSql = assembleSql();
        }
    }

    public String assembleSql() {
        String assembleSql = sourceSql;

        Set<Map.Entry<String, Object>> entries = param.entrySet();
        for(Map.Entry<String, Object> entry : entries) {
            String key = entry.getKey();
            Object value = entry.getValue();

            if(assembleSql.contains("${" + key + "}")) {
                String prepareValue = "";
                if(ObjectUtil.isNumber(value)) {
                    prepareValue = String.valueOf(value);
                } else {
                    prepareValue = "'" + value + "'";
                }
                assembleSql = assembleSql.replaceAll("\\$\\{" + key + "\\}", prepareValue);
            }
        }
        return assembleSql;
    }

    public String getTargetSql() {
        return targetSql;
    }
}

 

再改造一下原本的查询方式：

package com.local.util;

import com.local.jdbc.PreliminarySql;

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

/**
 * @date 2023/4/15 18:13
 * @description
 */
public class JdbcUtil {

    public static final String DRIVER_NAME = "com.mysql.cj.jdbc.Driver";

    public static final String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";

    public static final String USER = "root";

    public static final String PASSWORD = "";

    public static ResultSet select(String sql, Map<String, Object> param) {
        Connection connection = newConnection();
        try {
            Statement statement = connection.createStatement();

            PreliminarySql preliminarySql = new PreliminarySql(sql, param);

            ResultSet resultSet = statement.executeQuery(preliminarySql.getTargetSql());
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static boolean insert(String sql, Map<String, Object> param) {
        Connection connection = newConnection();
        try {
            Statement statement = connection.createStatement();

            PreliminarySql preliminarySql = new PreliminarySql(sql, param);

            boolean result = statement.execute(preliminarySql.getTargetSql());
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static int update(String sql, Map<String, Object> param) {
        Connection connection = newConnection();
        try {
            Statement statement = connection.createStatement();

            PreliminarySql preliminarySql = new PreliminarySql(sql, param);

            int result = statement.executeUpdate(preliminarySql.getTargetSql());
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static Map<String, Object> convertToMap(ResultSet resultSet) {
        Map<String, Object> map = new HashMap<>();

        return map;
    }

    public static Connection newConnection() {
        return newConnection(URL, USER, PASSWORD);
    }

    public static Connection newConnection(String url, String user, String password) {
        try {
            Class.forName(DRIVER_NAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            Connection connection = DriverManager.getConnection(url, user, password);
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            resultSet.close();
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 

 

 测试一下：

package com.local.jdbc;

import com.local.util.JdbcUtil;

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

/**
 * @date 2023/4/15 15:13
 * @description
 */
public class JdbcTest {

    public static final String DRIVER_NAME = "com.mysql.cj.jdbc.Driver";
    // public static final String DRIVER_NAME = "com.mysql.jdbc.Driver";

    public static final String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";

    public static final String USER = "root";

    public static final String PASSWORD = "";

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        // test01();

        test02();
    }

    public static void test01() throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER_NAME);
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT username, age FROM user");
        while(resultSet.next()) {
            System.out.println("姓名：" + resultSet.getString("username") + " 年龄：" + resultSet.getInt("age"));
        }
        JdbcUtil.close(resultSet, statement, connection);
    }

    public static void test02() throws SQLException {
        String sql = "select * from user where id = ${id}";
        Map<String, Object> map = new HashMap<>();
        map.put("id", 1);

        ResultSet select = JdbcUtil.select(sql, map);
        while(select.next()) {
            System.out.println("姓名：" + select.getString("username") + " 年龄：" + select.getInt("age"));
        }
    }
}