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")); } } }