JDBCSQL语句生成助手封装助手

用惯框架，来让你写一下JDBC的插入与封装，会让你崩溃的，哈哈，正在做JDBC项目，就现写了个JDBC助手，派上了大用场，呵呵

/**
 * @Version: 1
 * @JDK: jdk 1.7
 * @Module: myhrm

 * 2012-7-15 - 上午12:13:57 Created by Sharp
 */ 

 /*- 				History
 **********************************************
 *  ID      DATE           PERSON       REASON
 *  1     2012-7-15         Sharp        Created
 **********************************************
 */

package com.sharp.hrm.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;



/**
 * 测试元数据
 * */
public class ResultSetMetaData_Test {
	private static Connection conn;
	private static final String TABLENAME = "hrm_user" ;//表名
	private static final String entityClassString = "user"; //对象句柄

	@BeforeClass
	public static void setUp(){
		conn = ConnectionFactory.getConnection();
		System.out.println("conn---->" + conn);
	}
	@Test
	public void showMetadata() throws SQLException {
		DatabaseMetaData md = conn.getMetaData();
		//获取底层数据库的主版本号。
		System.out.println("getDatabaseMajorVersion:  "
				+ md.getDatabaseMajorVersion());

		//底层数据库的次版本号。
		System.out.println("getDatabaseMinorVersion:  "
				+ md.getDatabaseMinorVersion());

		// 获取此数据库产品的名称。
		System.out.println("getDatabaseProductName:  "
				+ md.getDatabaseProductName());

		// 获取此数据库产品的版本号。
		System.out.println("getDatabaseProductVersion:  "
				+ md.getDatabaseProductVersion());

		//获取此数据库的默认事务隔离级别。
		System.out.println("getDefaultTransactionIsolation:  "
				+ md.getDefaultTransactionIsolation());

		//获取此 DBMS 的 URL。
		System.out.println("getURL:  " + md.getURL());

		// 获取此数据库的已知的用户名称。
		System.out.println("getUserName:  " + md.getUserName());
		


	}
	
	@Test
	public void showMeatdataOfTable()  
            throws SQLException {  
        Statement stm = conn.createStatement();  
        ResultSet rs = stm.executeQuery("select * from " + TABLENAME);  
        //可用于获取关于 ResultSet 对象中列的类型和属性信息的对象  
        ResultSetMetaData rmsds = rs.getMetaData();  
        //返回此 ResultSet 对象中的列数。  
        int count = rmsds.getColumnCount();  
        if(rs.next()){  
            for (int i = 1; i <= count; i++) {  
                // 获得列名和数据类型  
                System.out.print("列名: " + rmsds.getColumnName(i) + "\t");  
                System.out.print("数据类型: " + rmsds.getColumnTypeName(i) + "\t");  
                System.out.print("字段值: " + rs.getObject(rmsds.getColumnName(i)));  
                System.out.println();  
            }  
            System.out.println();  
        }  
    }  
	/**
	 * 获得表的数据信息:
 	 * 		INSERT INTO hrm_employee(id, NAME, depEmpName, remark, ) VALUES(?,?,?,?,)
	 * 获取INSERT 语句
	 * @param con
	 * @param tablename
	 * @throws SQLException
	 */
	@Test
	public void showMeatdataOfTable_INSERT()
			throws SQLException {
		Statement stm = conn.createStatement();
		ResultSet rs = stm.executeQuery("select * from " + TABLENAME);
		//可用于获取关于 ResultSet 对象中列的类型和属性信息的对象
		ResultSetMetaData rmsds = rs.getMetaData();
		//返回此 ResultSet 对象中的列数。
		int count = rmsds.getColumnCount();
		if(rs.next()){
			StringBuffer sb = new StringBuffer();
			sb.append("INSERT INTO hrm_employee(");
			for (int i = 1; i <= count; i++) {
				// 获得列名和数据类型
				if(!"id".equals(rmsds.getColumnName(i))){
					if(i != count){
						sb.append(rmsds.getColumnName(i) + ", ");
					}else{
						sb.append(rmsds.getColumnName(i));
					}
				}
				
			}
			sb.append(") VALUES(");
			for (int i = 1; i <= count; i++) {
				// 获得列名和数据类型
				if(!"id".equals(rmsds.getColumnName(i))){
					if(i != count){
						sb.append("?,");
					}else{
						sb.append("?");
					}
					
				}
			}
//			sb.setCharAt(sb.length(), '');
			sb.append(")");
			System.out.println(sb.toString());
		}
	}
	/**
	 * 获得表的数据信息:
	 * 	dept.getId(), dept.getNAME(), dept.getDepEmpName(), dept.getRemark(), 
	 *
	 * 获取设值  语句
	 * @param con
	 * @param tablename
	 * @throws SQLException
	 */
	@Test
	public void showMeatdataOfTable_SETVALUES()
			throws SQLException {
		Statement stm = conn.createStatement();
		ResultSet rs = stm.executeQuery("select * from " + TABLENAME);
		//可用于获取关于 ResultSet 对象中列的类型和属性信息的对象
		ResultSetMetaData rmsds = rs.getMetaData();
		//返回此 ResultSet 对象中的列数。
		int count = rmsds.getColumnCount();
		if(rs.next()){
			StringBuffer sb = new StringBuffer();
			for (int i = 1; i <= count; i++) {
				// 获得列名和数据类型
				if(i != count){
					sb.append(entityClassString + ".get" + rmsds.getColumnName(i).substring(0,1).toUpperCase() + rmsds.getColumnName(i).substring(1)  + "(), ");
				}else{
					sb.append(entityClassString + ".get" + rmsds.getColumnName(i).substring(0,1).toUpperCase() + rmsds.getColumnName(i).substring(1)  + "()");
				}
				
			}
			System.out.println(sb.toString());
		}
	}
	/**
	 * 
	 * 获取更新  语句
	 * @param con
	 * @param tablename
	 * @throws SQLException
	 */
	@Test
	public void showMeatdataOfTable_UPDATE()
			throws SQLException {
		Statement stm = conn.createStatement();
		ResultSet rs = stm.executeQuery("select * from " + TABLENAME);
		//可用于获取关于 ResultSet 对象中列的类型和属性信息的对象
		ResultSetMetaData rmsds = rs.getMetaData();
		//返回此 ResultSet 对象中的列数。
		int count = rmsds.getColumnCount();
		if(rs.next()){
			StringBuffer sb = new StringBuffer();
			sb.append("UPDATE SET ");
			for (int i = 1; i <= count; i++) {
				// 获得列名和数据类型
				if(!"id".equals(rmsds.getColumnName(i))){
					if(i != count){
						sb.append(rmsds.getColumnName(i)  + " = ? ,");
					}else{
						sb.append(rmsds.getColumnName(i)  + " = ?");
					}
				}
				
			}
			sb.append(" WHERE id = ? ");
			System.out.println(sb.toString());
		}
	}
	/**
	 * 
	 * 获取取回对象  语句
	 * @param con
	 * @param tablename
	 * @throws SQLException
	 */
	@Test
	public void showMeatdataOfTable_TOObject()
			throws SQLException {
		Statement stm = conn.createStatement();
		ResultSet rs = stm.executeQuery("select * from " + TABLENAME);
		//可用于获取关于 ResultSet 对象中列的类型和属性信息的对象
		ResultSetMetaData rmsds = rs.getMetaData();
		//返回此 ResultSet 对象中的列数。
		int count = rmsds.getColumnCount();
		if(rs.next()){
			StringBuffer sb = new StringBuffer();
			for (int i = 1; i <= count; i++) {
				// 获得列名和数据类型
				sb.append(entityClassString + ".set" + FirstCharToUpperCase(rmsds.getColumnName(i))  + "(rs.get" + processType(rmsds.getColumnTypeName(i)+"")+ "(\"" + TABLENAME + "." + rmsds.getColumnName(i) + "\"));\n");
			}
			System.out.println(sb.toString());
		}
	}
	
	/**
	 * 首字母转大写
	 * @param args
	 */
	public static String FirstCharToUpperCase(String name){
		return name.substring(0,1).toUpperCase() + name.substring(1);
	}
	
	/**
	 * 处理类型
	 * 
	 * @param args
	 */
	public static String processType (String name){
		String result = null;
		switch(name){
			case "VARCHAR":
				result =  "String";
				break;
			case "INT":
				result =  "Int";
				break;
			case "TIMESTAMP":
				result =  "Timestamp";
				break;
			case "DATETIME":
				result =  "Timestamp";
				break;
		}
		return result;
	}
	
	@AfterClass
	public static void tearDown(){
		ConnectionFactory.close();
	}

}