无论用什么用什么orm工具,总有难免需要自己写sql语句的时候。用hibernate用多了,多多少少会怀念用jdbc的年代,因为可以自己写sql,再也不用去想什么n+1/延迟加载,缓存不一致问题了!
但是当有一天你用jdbc/mybatis 开发,自己写sql的时候,也会怀念起hibernate,因为当你面对着一张表有N个字段,写个简简单简的增删改查的SQL语句,也要对着数据库表表结构,对着每个字段,小心翼翼地写着地来写SQL语句
但是这对我来说,这不是问题,因为自己曾做过尝试,写过几个工具类帮我们生成
对于简单的SQL语句来讲,只要知道相关字段,表名,主键等,那剩下的工具就是拼字符串的问题了
借助DatabaseMetaData 接口借供的API,可以获取到数据库表名,字段等相关信息。 主要由四个类完成相关功能。程序很简单,太勿忙就不写注悉了。下面直接上代码:
table 主要用来存放相关table信息
public class TablesBean {
private String primaryKey;
private Map<String, String> columnMaps;
private List<String> columns;
private Class mapper;
private String tableName;
public Class getMapper() {
return mapper;
}
public void setMapper(Class mapper) {
this.mapper = mapper;
}
public String getPrimaryKey() {
return primaryKey;
}
public void setPrimaryKey(String primaryKey) {
this.primaryKey = primaryKey;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public Map<String, String> getColumnMaps() {
return columnMaps;
}
public void setColumnMaps(Map<String, String> columnMaps) {
this.columnMaps = columnMaps;
}
public List<String> getColumns() {
if (columns.contains(primaryKey))
columns.remove(primaryKey);
return columns;
}
public void setColumns(List<String> columns) {
this.columns = columns;
}
public boolean isPrimaryKey(String name) {
return columns.contains(name);
}
}
用来存放相关所有tables信息
public class DatabaseBean {
public DatabaseBean() {
}
private String databaseName;
private Map<String, TablesBean> tables;
public String getDatabaseName() {
return databaseName;
}
public void setDatabaseName(String databaseName) {
this.databaseName = databaseName;
}
public Map<String, TablesBean> getTables() {
return tables;
}
public void setTables(Map<String, TablesBean> tables) {
this.tables = tables;
}
public List<String> getAllTables() {
List<String> list = new ArrayList<String>();
for (Map.Entry<String, TablesBean> entry : tables.entrySet()) {
list.add(entry.getKey());
}
return list;
}
}
这个类是生成sql语句核心,获到数据库中所有表名,及相关的列名
public class DatabaseInfoService {
private static Logger logger = LoggerFactory
.getLogger(DatabaseInfoService.class);
private DatabaseBean databaseBean;
private DatabaseMetaData dataMetaData;
private Connection con;
public DatabaseInfoService(Connection con) {
this.con = con;
try {
this.init();
} catch (Exception e) {
logger.error("初始化数据失效:"+e.getMessage());
}
}
private void init() throws Exception {
List<String> tables = this.getTables(con,null);
databaseBean = this.getDatabaseBean();
Map<String, TablesBean> tablesMap = new HashMap<String, TablesBean>();
for (String table : tables) {
Map<String, String> primaryKeys = this.getPrimaryKeys(con,table);
TablesBean tablesBean = new TablesBean();
List<String> columns = this.getColumns(con,table);
tablesBean.setColumns(columns);
if (primaryKeys.containsKey(table)) {
tablesBean.setPrimaryKey(primaryKeys.get(table));
}
tablesBean.setTableName(table);
tablesMap.put(table, tablesBean);
databaseBean.setTables(tablesMap);
}
}
public List<String> getColumns(Connection con,String tableName) throws Exception {
DatabaseMetaData metaData = this.getDataMetaData(con);
ResultSet rs = metaData.getColumns(null, null, tableName, null);
List<String> columns = new ArrayList<String>();
String columnName;
while (rs.next()) {
columnName = rs.getString("COLUMN_NAME");
// System.out.println("列名:" + columnName);
columns.add(columnName);
}
return columns;
}
public Map<String, String> getPrimaryKeys(Connection con,String table) throws Exception {
Map<String, String> map = new HashMap<String, String>();
DatabaseMetaData metaData = this.getDataMetaData(con);
ResultSet rs = metaData.getPrimaryKeys(null, null, table);
String tableName;
String column;
while (rs.next()) {
tableName = rs.getString("TABLE_NAME");
column = rs.getString("COLUMN_NAME");
// System.out.println("表名为:" + tableName + "主键:" + column);
map.put(tableName, column);
}
return map;
}
public List<String> getTables(Connection con,String tableName) throws Exception {
DatabaseMetaData metaData = this.getDataMetaData(con);
ResultSet rs = metaData.getTables(null, null, tableName, null);
String rtableName;
List<String> tables = new ArrayList<String>();
while (rs.next()) {
rtableName = rs.getString("TABLE_NAME");
// System.out.println("表名:" + rtableName);
tables.add(rtableName);
}
return tables;
}
public DatabaseMetaData getDataMetaData(Connection con) {
if (dataMetaData != null)
return dataMetaData;
if(con ==null)
throw new NullPointerException("数据库连接不能为空");
try {
dataMetaData = con.getMetaData();
} catch (SQLException e) {
logger.error("获取metadata出错:" + e.getMessage());
}
return dataMetaData;
}
public DatabaseBean getDatabaseBean() {
return databaseBean != null ? databaseBean : new DatabaseBean();
}
}
供外部程序调用生成sql的类:
public class SqlAutoCreator {
//用线程安全map可以不进行同步
private ConcurrentMap<String, DatabaseBean> container;
private static final String CONTAINER_NAME = "DATABASE_INFO";
private static Logger logger = LoggerFactory
.getLogger(SqlAutoCreator.class);
private Connection connection;
private boolean hasColumnLable = false;
private boolean hasNamedParam = false;
public SqlAutoCreator(Connection con) {
try {
this.connection = con;
this.initContainer(con);//仅初始化一次,减少相关资源消耗
} finally {
closeDB(con);
}
}
public SqlAutoCreator() {
try {
this.initContainer(connection);
} finally {
closeDB(connection);
}
}
public String createQuerySql(String table) {
TablesBean tablebean = this.getTablesBean(table);
if (tablebean == null)
return null;
StringBuffer sb = new StringBuffer();
List<String> columns = tablebean.getColumns();
sb.append("SELECT ");
for (String column : columns) {
sb.append(column).append(",");
}
sb.deleteCharAt(sb.length() - 1);
sb.append(" FROM ").append(tablebean.getTableName());
sb.append(" WHERE ").append(tablebean.getPrimaryKey()).append(" = ");
if (hasNamedParam) {//可以设置是否开启命名参数,默认是false
sb.append(":").append(tablebean.getPrimaryKey());
} else {
sb.append("?");
}
return sb.toString();
}
public String createInsertSQL(String table) {
TablesBean tablebean = this.getTablesBean(table);
if (tablebean == null)
return null;
List<String> columns = tablebean.getColumns();
StringBuilder sb = new StringBuilder();
sb.append("INSERT INTO ").append(tablebean.getTableName() + "(");
for (String column : columns) {
sb.append(column);
sb.append(",");
}
sb.deleteCharAt(sb.length() - 1);
sb.append(")");
sb.append(" VALUES(");
for (String column : columns) {
if (hasNamedParam) {
sb.append(":");
sb.append(column);
} else {
sb.append("?");
}
sb.append(",");
}
sb.deleteCharAt(sb.length() - 1);
sb.append(")");
return sb.toString();
}
public String createUpdateSQL(String table) {
TablesBean tablebean = this.getTablesBean(table);
if (tablebean == null)
return null;
List<String> columns = tablebean.getColumns();
StringBuilder sb = new StringBuilder();
sb.append("UPDATE ");
sb.append(tablebean.getTableName());
sb.append(" SET ");
for (String column : columns) {
sb.append(column);
if (hasNamedParam) {
sb.append("=:");
sb.append(column);
} else {
sb.append("=?");
}
sb.append(",");
}
sb.deleteCharAt(sb.length() - 1);
sb.append(" WHERE ");
sb.append(tablebean.getPrimaryKey());
sb.append("=");
if (hasNamedParam) {
sb.append(":");
sb.append(tablebean.getPrimaryKey());
} else {
sb.append("?");
}
return sb.toString();
}
public String createDeleteSQL(String table) {
TablesBean tablebean = this.getTablesBean(table);
if (tablebean == null)
return null;
// List<String> columns = tablebean.getColumns();
StringBuilder sb = new StringBuilder();
sb.append("DELETE FROM ").append(tablebean.getTableName()).append(
" WHERE ").append(tablebean.getPrimaryKey()).append("=");
if (hasNamedParam) {
sb.append(":");
sb.append(tablebean.getPrimaryKey());
} else {
sb.append("?");
}
return sb.toString();
}
public List<String> createAllQuerySQL() {
List<String> allQuerySQL = new ArrayList<String>();
List<String> tables = this.getAllTables();
for (String table : tables) {
allQuerySQL.add(this.createQuerySql(table));
}
return allQuerySQL;
}
public List<String> createAllInsertSQL() {
List<String> list = new ArrayList<String>();
List<String> tables = this.getAllTables();
for (String table : tables) {
list.add(this.createInsertSQL(table));
}
return list;
}
public List<String> createAllUpdateSQL() {
List<String> list = new ArrayList<String>();
List<String> tables = this.getAllTables();
for (String table : tables) {
list.add(this.createUpdateSQL(table));
}
return list;
}
public List<String> createAllDeleteSQL() {
List<String> list = new ArrayList<String>();
List<String> tables = this.getAllTables();
for (String table : tables) {
list.add(this.createDeleteSQL(table));
}
return list;
}
protected void initContainer(Connection con) {
container = new ConcurrentHashMap<String, DatabaseBean>();
DatabaseInfoService databaseService = new DatabaseInfoService(con);
DatabaseBean databaseBean = databaseService.getDatabaseBean();
container.put(CONTAINER_NAME, databaseBean);
}
public List<String> getAllTables() {
return this.getDatabaseInfo().getAllTables();
}
private TablesBean getTablesBean(String table) {
DatabaseBean databaseinfo = getDatabaseInfo();
Map<String, TablesBean> tables = databaseinfo.getTables();
TablesBean tablebean = tables.get(table);
return tablebean;
}
public DatabaseBean getDatabaseInfo() {
return container.get(CONTAINER_NAME);
}
public void setConnection(Connection connection) {
this.connection = connection;
}
public boolean isHasColumnLable() {
return hasColumnLable;
}
public void setHasColumnLable(boolean hasColumnLable) {
this.hasColumnLable = hasColumnLable;
}
public boolean isHasNamedParam() {
return hasNamedParam;
}
public void setHasNamedParam(boolean hasNamedParam) {
this.hasNamedParam = hasNamedParam;
}
protected void closeDB(Connection con) {
try {
con.close();
} catch (SQLException e) {
logger.warn("关闭数据库连接异常:" + e.getMessage());
}
}
}
调用:
@ContextConfiguration(locations = "/applicationContext.xml")
public class ConnectionSourceTest extends AbstractJUnit4SpringContextTests {
@Resource
DataSource dataSource;
@Test
public void testGetQuerySQL()throws Exception{
SqlAutoCreator creator = new SqlAutoCreator(dataSource.getConnection());
String sql= creator.createQuerySql("t_userinfo");
System.out.println("查找SQL语句:"+sql);
}
@Test
public void testGetInsertSQL()throws Exception{
SqlAutoCreator creator = new SqlAutoCreator(dataSource.getConnection());
creator.setHasNamedParam(false);
String sql= creator.createInsertSQL("t_userinfo");
System.out.println("插入SQL语句:"+sql);
}
@Test
public void getDeleteSQL() throws Exception{
SqlAutoCreator creator = new SqlAutoCreator(dataSource.getConnection());
String sql= creator.createDeleteSQL("t_userinfo");
System.out.println("删除SQL语句:"+sql);
}
@Test
public void getUpdateSQL() throws Exception{
SqlAutoCreator creator = new SqlAutoCreator(dataSource.getConnection());
String sql= creator.createUpdateSQL("t_userinfo");
System.out.println("删除SQL语句:"+sql);
}
@Test
public void testGetAllSql() throws Exception{
SqlAutoCreator creator = new SqlAutoCreator(dataSource.getConnection());
creator.setHasNamedParam(true);
System.out.println("删除:"+creator.createAllDeleteSQL());
System.out.println("插入:"+creator.createAllInsertSQL());
System.out.println("查找:"+creator.createAllQuerySQL());
System.out.println("更新:"+creator.createAllUpdateSQL());
}
}
由针MyBatis或者spring jdbc sql文件以放在外部文件,我们其实可以利用模板技术(freemarker velocity)生成所需要的文件,当然这是后话了。
PS: 以上程序在mysql数据库测试通过,其它数据库未做测试,如有bug欢迎指出