java解析sql语句简书,JDBC基本操作，支持命名参数的SQL语句

import cn.hutool.db.handler.BeanHandler;

import cn.hutool.db.handler.BeanListHandler;

public class JDBCutils {

private static Connection conn = null;

static {

try (InputStream in = new FileInputStream("src/jdbc.properties");) {

Properties properties = new Properties();

properties.load(in);

String driver = properties.getProperty("jdbc.driver");

String url = properties.getProperty("jdbc.url");

String username = properties.getProperty("jdbc.username");

String password = properties.getProperty("jdbc.password");

//1.加载驱动程序

Class.forName(driver);

//2. 获得数据库连接

conn = DriverManager.getConnection(url, username, password);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

}

public static Connection getConnection() {

return conn;

}

public static E beanHandler(ResultSet resultSet, Class clazz) throws SQLException {

BeanHandler handler = new BeanHandler(clazz);

return handler.handle(resultSet);

}

public static List beanListHandler(ResultSet resultSet, Class clazz) throws SQLException {

BeanListHandler handler = new BeanListHandler(clazz);

return handler.handle(resultSet);

}

public static void setParameter(PreparedStatement preparedStatement, Object... param) throws SQLException {

if (preparedStatement != null && param != null) {

for (int x = 0; x < param.length; x++) {

preparedStatement.setObject(x + 1, param[x]);

}

}

}

public static void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {

try {

if (resultSet != null) {

resultSet.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

try {

if (preparedStatement != null) {

preparedStatement.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

try {

if (connection != null) {

connection.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

简单的数据库查询：

public class BaseDao {

private final Class> clazz;

public BaseDao(Class> clazz) {

this.clazz = clazz;

}

public List findByNative(String sql, Map param) {

Connection connection = JDBCutils.getConnection();

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

try {

ParamSql paramSql = new ParamSql(sql, clazz);

preparedStatement = connection.prepareStatement(paramSql.getParseSql());

paramSql.setParameter(preparedStatement, param);

resultSet = preparedStatement.executeQuery();

return JDBCutils.beanListHandler(resultSet, clazz);

} catch (SQLException e) {

e.printStackTrace();

} finally {

JDBCutils.close(resultSet, preparedStatement, connection);

}

return null;

}

public List findByNative(String sql, Object... param) {

Connection connection = JDBCutils.getConnection();

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

try {

ParamSql paramSql = new ParamSql();

preparedStatement = connection.prepareStatement(sql);

paramSql.setParameter(preparedStatement, param);

resultSet = preparedStatement.executeQuery();

return JDBCutils.beanListHandler(resultSet, clazz);

} catch (SQLException e) {

e.printStackTrace();

} finally {

JDBCutils.close(resultSet, preparedStatement, connection);

}

return null;

}

public T getOneByNative(String sql, Map param) {

Connection connection = JDBCutils.getConnection();

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

try {

ParamSql paramSql = new ParamSql(sql, clazz);

preparedStatement = connection.prepareStatement(paramSql.getParseSql());

paramSql.setParameter(preparedStatement, param);

resultSet = preparedStatement.executeQuery();

return JDBCutils.beanHandler(resultSet, clazz);

} catch (SQLException e) {

e.printStackTrace();

} finally {

JDBCutils.close(resultSet, preparedStatement, connection);

}

return null;

}

public T getOneByNative(String sql, Object... param) {

Connection connection = JDBCutils.getConnection();

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

try {

ParamSql paramSql = new ParamSql();

preparedStatement = connection.prepareStatement(sql);

paramSql.setParameter(preparedStatement, param);

resultSet = preparedStatement.executeQuery();

return JDBCutils.beanHandler(resultSet, clazz);

} catch (SQLException e) {

e.printStackTrace();

} finally {

JDBCutils.close(resultSet, preparedStatement, connection);

}

return null;

}

public int update(String sql, Map param) {

Connection connection = JDBCutils.getConnection();

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

try {

ParamSql paramSql = new ParamSql(sql);

preparedStatement = connection.prepareStatement(paramSql.getParseSql());

paramSql.setParameter(preparedStatement, param);

return preparedStatement.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

} finally {

JDBCutils.close(resultSet, preparedStatement, connection);

}

return -1;

}

}

解析sql：

public class ParamSql {

private final String sql;

private final Class> clazz;

private static final Map PARAM_KEY_MAP = new HashMap<>();

private static final String REGEX = ":(\\w*)";

private static final String REPLACEMENT = "\\?";

private static final String SELECT = "SELECT";

private static final Pattern PATTERN_SQL = Pattern.compile(REGEX);

public ParamSql() {

this.sql = null;

this.clazz = null;

}

public ParamSql(String sql) {

this.sql = sql;

this.clazz = null;

}

public ParamSql(String sql, Class> clazz) {

this.sql = sql;

this.clazz = clazz;

}

public String getParseSql() {

if (sql == null || sql.length() == 0) {

throw new IllegalArgumentException("sql is null");

}

PARAM_KEY_MAP.clear();

Matcher m = PATTERN_SQL.matcher(sql);

int idx = 1;

while (m.find()) {

String field = m.group(1);

if (field == null || field.length() == 0) {

throw new IllegalArgumentException("query param field is null");

}

PARAM_KEY_MAP.put(idx++, field);

}

String parseSql = sql.replaceAll(REGEX, REPLACEMENT);

if (parseSql.toUpperCase().indexOf(SELECT) < 0) {

if (clazz != null) {

parseSql = sqlField(parseSql);

}

}

return parseSql.toUpperCase();

}

public void setParameter(PreparedStatement statement, Map param) throws SQLException {

if (!PARAM_KEY_MAP.isEmpty()) {

if (param == null || param.isEmpty()) {

throw new IllegalArgumentException("param is null");

}

}

for (Map.Entry entry : PARAM_KEY_MAP.entrySet()) {

Integer idx = entry.getKey();

String field = entry.getValue();

if (!param.containsKey(field)) {

throw new IllegalArgumentException("not param field [" + field + "]");

}

statement.setObject(idx, param.get(field));

}

}

public void setParameter(PreparedStatement preparedStatement, Object[] param) throws SQLException {

if (preparedStatement != null && param != null) {

for (int x = 0; x < param.length; x++) {

preparedStatement.setObject(x + 1, param[x]);

}

}

}

private String sqlField(String sql) {

Field[] fields = clazz.getDeclaredFields();

StringBuilder sbd = new StringBuilder(SELECT).append(" ");

int x = 1;

for (Field field : fields) {

sbd.append(toUnderline(field.getName()));

if (x < fields.length) {

sbd.append(" ,");

} else {

sbd.append(" ");

}

x++;

}

return sbd.toString() + sql;

}

private String toUnderline(String str) {

if (str == null || str.length() == 0) {

return null;

}

StringBuilder sbd = new StringBuilder();

for (int i = 0; i < str.length(); i++) {

char c = str.charAt(i);

if (Character.isUpperCase(c)) {

sbd.append("_");

}

sbd.append(c);

}

return sbd.toString();

}

}

注：请根据自己的实际情况进行调整