连接数据库:
以下使用来自Hutool工具包的对象处理，请参考apidoc.gitee.com/loolly/hutool/
import cn.hutool.db.handler.BeanHandler;
import cn.hutool.db.handler.BeanListHandler;
public class JDBCutils {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
try (InputStream in = new FileInputStream("src/jdbc.properties");) {
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("jdbc.driver");
url = properties.getProperty("jdbc.url");
username = properties.getProperty("jdbc.username");
password = properties.getProperty("jdbc.password");
//1.加载驱动程序
Class.forName(driver);
// System.setProperty("jdbc.drivers",driver);
//2. 获得数据库连接
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static <E> E beanHandler(ResultSet resultSet, Class clazz) throws SQLException {
BeanHandler<E> handler = new BeanHandler(clazz);
return handler.handle(resultSet);
}
public static <E> List<E> beanListHandler(ResultSet resultSet, Class clazz) throws SQLException {
BeanListHandler<E> 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 <T> List<T> findByNative(String sql, Map<String, Object> 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);
resultSet = preparedStatement.executeQuery();
return JDBCutils.beanListHandler(resultSet, clazz);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCutils.close(resultSet, preparedStatement, connection);
}
return null;
}
public <T> List<T> findByNative(String sql, Object... 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);
resultSet = preparedStatement.executeQuery();
return JDBCutils.beanListHandler(resultSet, clazz);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCutils.close(resultSet, preparedStatement, connection);
}
return null;
}
public <T> T getOneByNative(String sql, Map<String, Object> 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);
resultSet = preparedStatement.executeQuery();
return JDBCutils.beanHandler(resultSet, clazz);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCutils.close(resultSet, preparedStatement, connection);
}
return null;
}
public <T> T getOneByNative(String sql, Object... 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);
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<String, Object> 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);
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCutils.close(resultSet, preparedStatement, connection);
}
return -1;
}
}
解析sql：
class ParamSql {
private final String sql;
private String parseSql;
public static final Map<Integer, String> PARAM_KEY_MAP = new HashMap<>();
private static final String REGEX = ":(\\w*)";
private static final String REPLACEMENT = "\\?";
private static final Pattern PATTERN_SQL = Pattern.compile(REGEX);
public ParamSql(String sql) {
this.sql = sql;
this.parseSql();
}
public String getParseSql() {
return parseSql;
}
public String getSql() {
return sql;
}
private void parseSql() {
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);
this.parseSql = parseSql.toUpperCase();
}
public void setParameter(PreparedStatement statement, Map<String, Object> param) throws SQLException {
if (!PARAM_KEY_MAP.isEmpty()) {
if (param == null || param.isEmpty()) {
throw new IllegalArgumentException("param is null");
}
}
for (Map.Entry<Integer, String> 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 (param != null) {
for (Map.Entry<Integer, String> entry : PARAM_KEY_MAP.entrySet()) {
Integer idx = entry.getKey();
preparedStatement.setObject(idx, param[(idx - 1)]);
}
}
}
}
示例
public static void main(String[] args) {
// T 为泛型
BaseDao baseDao = new BaseDao(T.class);
String sql = "select * from table where field1=:field1 and field2=:field2 limit 1";
Map<String, Object> param = new HashMap<>();
param.put("field1", "field1-value");
param.put("field2", "field2-value");
T t = baseDao.getOneByNative(sql, param);
sql = "select * from table where field1=:field1 and field2=:field2";
List<T> t1 = baseDao.findByNative(sql, "field1-value", "field2-value");
}
注：请根据自己的实际情况进行调整
网友评论