动态生成sql语句实例

package arithmetic;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 * 通过数据库表名来生成基本的数据库操作语句
 * 核心代码是getMetaData(String tableName)方法  想生成哪个具体的sql语句自己写 我这只列举简单的增删改查
 * JDBCUtil地址：http://blog.youkuaiyun.com/liujiding/article/details/73739648
 * @author liujd
 *
 */
public class DaoHelper {

	/**
	 * 获取某个表的表结构(这个是本程序的关键)
	 * @param tableName
	 * @return
	 */
	public static ResultSetMetaData getMetaData(String tableName) {
		//查询该表的第一行内容 
		String sel = "select * from " + tableName +" limit 1";
		try {
		//利用之前的JDBCUtil工具类来获取连接
		Connection connection = JDBCUtil.getConnection("mysql", "localhost:3306/test", "liujd", "1234");
		//获取statement
		PreparedStatement prep = connection.prepareStatement(sel);
		//查询得到结果
		ResultSet res = prep.executeQuery();
		//根据结果获取表结构
		ResultSetMetaData metaData = res.getMetaData();
		return metaData;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 生成简单的查询语句select id,name,age from student
	 * @param tableName
	 * @return
	 * @throws SQLException 
	 */
	public static String getSelect(String tableName) throws SQLException {
		//获取表结构
		ResultSetMetaData metaData = getMetaData(tableName);
		//定义查询语句的头
		StringBuilder builder = new StringBuilder("select ");
		for(int i = 1 ; i<=metaData.getColumnCount();i++) {
			//获取某一列名 并且拼接到sql语句中
			String columnName = metaData.getColumnName(i);
			builder.append((i==metaData.getColumnCount()) ? columnName : columnName+",");
		}
		builder.append(" from "+tableName);
		return builder.toString();
	}
	/**
	 * 生成简单的插入语句insert into student(id,name,age) values (?,?,?)
	 * @param tableName
	 * @return
	 * @throws SQLException 
	 */
	public static String getInsert(String tableName) throws SQLException {
		//获取表结构
		ResultSetMetaData metaData = getMetaData(tableName);
		//定义插入语句的头
		StringBuilder builder = new StringBuilder("insert into "+tableName+"(");
		for(int i = 1 ; i<=metaData.getColumnCount();i++) {
			//获取某一列名 并且拼接到sql语句中
			String columnName = metaData.getColumnName(i);
			builder.append((i==metaData.getColumnCount()) ? columnName : columnName+",");
		}
		builder.append(") values (");
		for(int i = 1 ; i<=metaData.getColumnCount();i++) {
			builder.append((i==metaData.getColumnCount()) ? "?" : "?,");
		}
		builder.append(")");
		return builder.toString();
	}
	/**
	 * 生成简单的更新语句update student set id=?,name=?,age=?
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public static String getUpdate(String tableName) throws SQLException {
		//获取表结构
		ResultSetMetaData metaData = getMetaData(tableName);
		//定义更新语句的头
		StringBuilder builder = new StringBuilder("update "+tableName+" set " );
		for(int i = 1 ; i<=metaData.getColumnCount();i++) {
			//获取某一列名 并且拼接到sql语句中
			String columnName = metaData.getColumnName(i);
			builder.append((i==metaData.getColumnCount()) ? columnName+"=?" : columnName+"=?,");
		}
		return builder.toString();
	}
	public static void main(String[] args) {
		try {
			System.out.println(getSelect("student"));
			System.out.println(getInsert("student"));
			System.out.println(getUpdate("student"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}