数据库批量增删改查工具

https://www.cnblogs.com/gqhwk/p/9041222.html

从sql.txt读取sql语句，将运行结果写到result.txt

batch.java

复制代码
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class batch2 {

public static void main(String[] args) {

    testOracle();
}

   public static boolean supportBatch(Connection con) {
        try {
            // 得到数据库的元数据
            DatabaseMetaData md = con.getMetaData();
            return md.supportsBatchUpdates();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

public static void testOracle()
{
    Connection con = null;// 创建一个数据库连接
    PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
    ResultSet result = null;// 创建一个结果集对象
    String sb="";
    try
    {
        //读取配置
        InputStream inStream = new FileInputStream(new File(System.getProperty("user.dir")+"\\data\\Paras.properties"));
        Properties prop = new Properties();    
        prop.load(inStream);    
        // 加载Oracle驱动程序
        Class.forName("oracle.jdbc.driver.OracleDriver");
        System.out.println("开始尝试连接数据库！");
        //读取配置文件中数据库主机名，端口号，服务名
        String url = prop.getProperty("url");
        String user = prop.getProperty("username");// 用户名,系统默认的账户名
        String password = prop.getProperty("password");// 你安装时选设置的密码
        con = DriverManager.getConnection(url, user, password);// 获取连接
        System.out.println("连接成功！");

        try {

// BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(System.getProperty(“user.dir”)+”\data\sql.txt”),”UTF-8”));
BufferedReader br = new BufferedReader(new UnicodeReader(new FileInputStream(System.getProperty(“user.dir”)+”\data\sql.txt”), Charset.defaultCharset().name()));
System.out.println(“”);
for (String line = br.readLine(); line != null; line = br.readLine()) {
System.out.println(line);
if(line.startsWith(“select”)){
String sql = line;// 预编译语句，“？”代表参数
pre = con.prepareStatement(sql);// 实例化预编译语句
result = pre.executeQuery();

                   FileOutputStream fos = new FileOutputStream(new File(System.getProperty("user.dir"))+"\\data\\result.txt",true);
                PrintStream p = new PrintStream(fos);
                p.print(line);p.println();
                ResultSetMetaData m=result.getMetaData();  
                int columns=m.getColumnCount();  
                while(result.next())  
                {  
                 for(int i=1;i<=columns;i++) 
                 {  
                  System.out.print(result.getString(i));  
                  System.out.print(",");  
                  sb=result.getString(i)+",";
                  p.print(sb);
                 }  
                 System.out.println();
                 p.println();
                } 
                System.out.println();
                p.println();
                p.close();
                fos.flush();
                 }else if(!line.contains("#"))
                 {
                   String sql = line;// 预编译语句，“？”代表参数
                   pre = con.prepareStatement(sql);// 实例化预编译语句
                   pre.executeUpdate();// 执行非查询语句
                   System.out.println("执行成功!");
                 }
             }                       
           br.close();
           } catch (IOException e) {
               e.printStackTrace();
           }

    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
    finally
    {
        try
        {
            // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
            // 注意关闭的顺序，最后使用的最先关闭
            if (result != null)
                result.close();
            if (pre != null)
                pre.close();
            if (con != null)
                con.close();
            System.out.println("数据库连接已关闭！");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
}

}

UnicodeReader.java
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.PushbackInputStream;
import java.io.Reader;

public class UnicodeReader extends Reader {
PushbackInputStream internalIn;
InputStreamReader internalIn2 = null;
String defaultEnc;

  private static final int BOM_SIZE = 4;


  UnicodeReader(InputStream in, String defaultEnc) {
     internalIn = new PushbackInputStream(in, BOM_SIZE);
     this.defaultEnc = defaultEnc;
  }

  public String getDefaultEncoding() {
     return defaultEnc;
  }


  public String getEncoding() {
     if (internalIn2 == null) return null;
     return internalIn2.getEncoding();
  }


  protected void init() throws IOException {
     if (internalIn2 != null) return;

     String encoding;
     byte bom[] = new byte[BOM_SIZE];
     int n, unread;
     n = internalIn.read(bom, 0, bom.length);

     if ( (bom[0] == (byte)0x00) && (bom[1] == (byte)0x00) &&
                 (bom[2] == (byte)0xFE) && (bom[3] == (byte)0xFF) ) {
        encoding = "UTF-32BE";
        unread = n - 4;
     } else if ( (bom[0] == (byte)0xFF) && (bom[1] == (byte)0xFE) &&
                 (bom[2] == (byte)0x00) && (bom[3] == (byte)0x00) ) {
        encoding = "UTF-32LE";
        unread = n - 4;
     } else if (  (bom[0] == (byte)0xEF) && (bom[1] == (byte)0xBB) &&
           (bom[2] == (byte)0xBF) ) {
        encoding = "UTF-8";
        unread = n - 3;
     } else if ( (bom[0] == (byte)0xFE) && (bom[1] == (byte)0xFF) ) {
        encoding = "UTF-16BE";
        unread = n - 2;
     } else if ( (bom[0] == (byte)0xFF) && (bom[1] == (byte)0xFE) ) {
        encoding = "UTF-16LE";
        unread = n - 2;
     } else {
        // Unicode BOM mark not found, unread all bytes
        encoding = defaultEnc;
        unread = n;
     }    
     //System.out.println("read=" + n + ", unread=" + unread);

     if (unread > 0) internalIn.unread(bom, (n - unread), unread);

     // Use given encoding
     if (encoding == null) {
        internalIn2 = new InputStreamReader(internalIn);
     } else {
        internalIn2 = new InputStreamReader(internalIn, encoding);
     }
  }

  public void close() throws IOException {
     init();
     internalIn2.close();
  }

  public int read(char[] cbuf, int off, int len) throws IOException {
     init();
     return internalIn2.read(cbuf, off, len);
  }

}