让程序帮我们生成常用的增删改查语句

      无论用什么用什么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欢迎指出

   

 

评论

2 楼 recole 2012-05-13  

我也有写一个生成基本sql操作的工具，当然我的是.net版的。对于自由select的结果集，.net可以返回DataTable，不知java有没有类似的？

1 楼 shuang245 2011-09-24  

非常好，顶顶顶！