数据库批量增删改查工具
目录结构：
从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); } }
