创建JdbcTemplate，利用DataSource与数据库直接交互，实现通用方法
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; /** * 根据资源获取连接，执行SQL，结果集 */ public abstract class JdbcTemplate extends MyDataSource { protected static Connection connection; protected static PreparedStatement preparedStatement; protected ResultSet resultSet; //查询 protected <T> List<T> executeQuery(String sql , RowMapper<T> rowMapper) throws Exception { preparedStatement = preparedStatement(sql); resultSet = preparedStatement.executeQuery(); List<T> list = resultSet(resultSet, rowMapper); close(); return list; } //更新 protected int executeUpdate(String sql) throws SQLException { preparedStatement = preparedStatement(sql); int i = preparedStatement.executeUpdate(); close(); return i; } protected void execute(String sql){ try { preparedStatement = preparedStatement(sql); preparedStatement.execute(); }catch (Exception e){ e.printStackTrace(); }finally { //close(); } } //获取链接 private Connection getConnection() throws SQLException { if (connection == null){ connection = this.dataSource.getConnection(); } return connection; } //预执行sql private PreparedStatement preparedStatement(String sql) throws SQLException { connection = getConnection(); if (preparedStatement == null){ preparedStatement = connection.prepareStatement(sql); } return preparedStatement; } //结果集 private <T> List<T> resultSet(ResultSet resultSet , RowMapper<T> rowMapper) throws Exception { List<T> list = new ArrayList<>(); while (resultSet.next()){ list.add(rowMapper.mapRow(resultSet)); } return list; } //关闭 private void close(){ try { if (connection != null) { connection.close(); } if (preparedStatement != null) { preparedStatement.close(); } if (this.resultSet != null) { this.resultSet.close(); } }catch (Exception e){ e.printStackTrace(); } } }
其中结果集的映射：RowMapper，这里写的接口，当使用的时候，使用匿名内部类操作
public interface RowMapper<T> { T mapRow(ResultSet resultSet) throws Exception; }
用户面向对象编程，不写SQL，所以我们底层需要反射获取对象的参数，然后解析拼接为SQL
这里我们将反射对象的通用操作封装
public abstract class BaseSQLBuilder { protected String tableName; //表名 protected String primaryKeyName; //主键名 protected final String SELECT = "select "; protected final String FROM = " from "; protected final String WHERE = " where "; protected final String AND = " and "; protected final String IN = " IN "; protected final String UPDATE = " UPDATE "; protected final String SET = " SET "; protected final String VALUES = " VALUES "; protected final String OR = " OR "; protected final String DELETE = " DELETE "; protected final String INSERT = " INSERT "; protected final String INTO = " INTO "; protected StringBuilder sqlBuilder = new StringBuilder(); protected void getTableName(Object obj){ Table table = obj.getClass().getAnnotation(Table.class); tableName = table.value(); if (Objects.equals(tableName, "")) { // 全类名拆分 tableName = StringUtil.getLastStr(obj.getClass().getName()); } } protected void getPrimaryKey(Field field){ PrimaryKey primaryKey = field.getAnnotation(PrimaryKey.class); primaryKeyName = primaryKey.value(); if (Objects.equals(primaryKeyName, "")) { primaryKeyName = StringUtil.getLastStr(field.getClass().getName()); } } protected boolean hasPrimaryKey(Field field){ PrimaryKey primaryKey = field.getAnnotation(PrimaryKey.class); return primaryKey != null; } protected String getField(String fieldStr){ return "get" + fieldStr.substring(0, 1).toUpperCase() + fieldStr.substring(1); } protected Field[] getFields(Object obj){ return obj.getClass().getDeclaredFields(); } }
这里我们只以查询为例
public class QuerySQLBuilder extends BaseSQLBuilder { public String querySql(Object t) throws Exception { // 获取操作表名 getTableName(t); // SQL拼接 sqlBuilder.append(SELECT + "*" + FROM).append(tableName).append(WHERE + " 1=1 "); for (Field field : getFields(t)) { String fieldStr = StringUtil.getLastStr(field.toString()); Object value = t.getClass().getMethod(getField(fieldStr)).invoke(t); if (!"".equals(value) && null != value) { sqlBuilder.append(AND).append(fieldStr).append("=").append("'").append(value).append("'"); } } return sqlBuilder.toString(); } }
我们将QuerySQLBuilder。。。其他Builder封装到一个工厂类中，进行获取
public class SQLBuilderInstanceFactory { static QuerySQLBuilder queryBuilder = null; static SaveSQLBuilder saveBuilder = null; static UpdateSQLBuilder updateBuilder = null; static DeleteSQLBuilder deleteSQLBuilder = null; public static QuerySQLBuilder getQueryBuilder(){ if (queryBuilder == null) { queryBuilder = new QuerySQLBuilder(); } return queryBuilder; } public static SaveSQLBuilder getSaveBuilder(){ if (saveBuilder == null) { saveBuilder = new SaveSQLBuilder(); } return saveBuilder; } public static UpdateSQLBuilder getUpdateBuilder(){ if (updateBuilder == null) { updateBuilder = new UpdateSQLBuilder(); } return updateBuilder; } public static DeleteSQLBuilder getDeleteSQLBuilder(){ if (deleteSQLBuilder == null) { deleteSQLBuilder = new DeleteSQLBuilder(); } return deleteSQLBuilder; } }
定义查询接口
public interface IQuery<T> { List<T> query(T t) throws Exception; }
查询具体实现类
public class Query<T> extends JdbcTemplate implements IQuery<T> { @Override public List<T> query(T t) throws Exception { // 解析出的SQL String sql = SQLBuilderInstanceFactory.getQueryBuilder().querySql(t); System.out.println("sql "+sql); Field[] fields = t.getClass().getDeclaredFields(); // 执行SQL return executeQuery(sql, new RowMapper<T>() { @Override public T mapRow(ResultSet resultSet) throws Exception { for (Field field : fields) { String getField = StringUtil.getSetMethod(StringUtil.getLastStr(field.toString())); Object object = resultSet.getObject(StringUtil.getLastStr(field.toString()), field.getType()); t.getClass().getMethod(getField,field.getType()).invoke(t,object); } return t; } }); } }
现在大体上的代码都已经实现了，我们测试一下
public class Client { public static void main(String[] args) throws Exception { User user = new User(); user.setAddress("china"); // 查询操作 Query<User> userQuery = new Query<>(); List<User> userList = userQuery.query(user); } }
可以看到这期间用户在使用的过程中，没有写一句SQL，但可以正常操作数据库了。剩下的细节就等着我们去完善。
总结
本文中，我大概总结了一下，Java操作Mysql的演化过程，从最基本的JDBC到自动化的ORM操作，可以看到操作是越来越简单，也越来越灵活了，但底层大致的原理我们还是应该了解的。
本文我参考了韩顺平老师的Mysql基础文章和小四的技术之旅文章。十分感谢！！！原作者的文章中的内容更加详细，具体可在网上查询。
