使用JDBC拼接SQL更新语句——Insert语句的代码思路

一、在使用JDBC拼接Insert into SQL语句的思路：

1、获取列名和字段列总数。

2、拼接Insert into 的字段部分

3、遍历ResultSet 变量拼接SQL语句的values部分，数据类型的转换

二、简单的生成SQL语句。

package conn;

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

public class DBConnection {
	public static void main(String[] args) throws ClassNotFoundException, SQLException{
		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=pub";
		String user =  "sa";
		String password = "sa";
		Class.forName(driver);
		Connection conn = DriverManager.getConnection(url,user,password);

		String sql = "select * from SQ_JXZB_JBYL";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		ResultSetMetaData mt = rs.getMetaData();
		int colCounts = mt.getColumnCount();
		while(rs.next()){
		String insert = "insert into SQ_JXZB_JBYLs(";
		String values = ") values(";
		for(int i = 0;i<colCounts;i++){
			String colName = mt.getColumnName(i+1);
			String colType = mt.getColumnTypeName(i+1);
			
			//获取字段值
			String value = rs.getString(colName);
			//对表字段进行处理如日期字段
			if(colType.equalsIgnoreCase("date")||colType.equalsIgnoreCase("datetime")){
				value = "to_date('"+value+"','yyyy-mm-dd')";
			}else{
				value="'"+value+"'";
			}
			if(value==null||value.equalsIgnoreCase("")){
				value = "''";
			}
			
			//拼接SQL语句
			if(i+1>=colCounts){
				values+=value+");";
				insert +=colName+values;
			}
			else{
				insert +=colName+",";
				values+=value+",";
			}
		}
		System.out.println(insert);
		}
		rs.close();
		stmt.close();
		conn.close();
	}
	
}

转载于:https://my.oschina.net/u/174862/blog/285993