java jdbc insert sql_使用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

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();

}

}