一、前言
定义通用数据库操作工具类db_utils,实现连接mysql/oracle/hsqldb数据库(配置连接更多数据库,请参考其他更多网页)、数据导出exportData及数据导入importData操作用于数据库备份迁移等场景,详情代码示例说明。
二、代码示例
package com.xwood.util;@b@@b@import java.io.File;@b@import java.io.FileOutputStream;@b@import java.io.PrintWriter;@b@import java.sql.Connection;@b@import java.sql.DriverManager;@b@import java.sql.PreparedStatement;@b@import java.sql.ResultSet;@b@import java.sql.SQLException;@b@import java.sql.Statement;@b@import java.util.Scanner;@b@@b@/**@b@ * 数据库数据备份转移工具 @b@ */@b@public class DB_Util {@b@ private static Connection conn;@b@@b@ /**@b@ * 加裁mysql驱动@b@ * @b@ * @throws ClassNotFoundException@b@ * @throws SQLException@b@ */@b@ @SuppressWarnings("unused")@b@ private static void mysql() throws ClassNotFoundException, SQLException {@b@ Class.forName("com.mysql.jdbc.Driver");@b@ conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/ginkgo",@b@ "root", "a");@b@ }@b@@b@ @SuppressWarnings("unused")@b@ private static void hsqldb() throws ClassNotFoundException, SQLException {@b@ Class.forName("org.hsqldb.jdbcDriver");@b@ conn = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost",@b@ "sa", "");@b@ }@b@@b@ /**@b@ * 加裁oracle的驱动@b@ * @b@ * @throws ClassNotFoundException@b@ * @throws SQLException@b@ */@b@ private static void oracle() throws ClassNotFoundException, SQLException {@b@ Class.forName("oracle.jdbc.driver.OracleDriver");@b@ conn = DriverManager.getConnection(@b@ "jdbc:oracle:thin:@222.18.159.20:1521:orcl", "xwood", "xwood123");@b@ }@b@@b@ public static void main(String[] args) throws Exception {@b@ // oracle();// 打开oracle数据库@b@ mysql();// 打开mysql数据库@b@ // hsqldb();@b@@b@ // exportData("F:\\backup\\xwood\\", "member");// 导出数据@b@@b@ // importData("F:\\backup\\xwood\\member.figo", "member");// 导入数据@b@@b@ importCSV("N:\\test.csv", "t_user");// 导入CSV数据@b@ }@b@@b@ /**@b@ * @param filePath@b@ * 导出数据存放路径@b@ * @param table@b@ * 执行导出的表名@b@ */@b@ @SuppressWarnings("unused")@b@ private static void exportData(String filePath, String table) {@b@ try {@b@ Statement statement = conn.createStatement();@b@ String sql = "select * from " + table;@b@ ResultSet resultSet = statement.executeQuery(sql);@b@@b@ File file = new File(filePath + table + ".figo");@b@ PrintWriter pw = new PrintWriter(new FileOutputStream(file));@b@ int columnCount = resultSet.getMetaData().getColumnCount();@b@@b@ StringBuilder sb = new StringBuilder();@b@ for (int i = 0; i < columnCount; i++) {@b@ String name = resultSet.getMetaData().getColumnName(i + 1);@b@ sb.append(name + "\b");@b@ }@b@ pw.println(sb.toString());// 输出表头@b@@b@ while (resultSet.next()) {@b@ sb = new StringBuilder();@b@ for (int i = 0; i < columnCount; i++) {@b@ sb.append(resultSet.getString(i + 1) + "\b");@b@ }@b@ pw.println(sb.toString());// 输出数据@b@ }@b@ pw.close();@b@ } catch (Exception e) {@b@ e.printStackTrace();@b@ }@b@ System.out.println("data export success!");@b@ }@b@@b@ /**@b@ * 导入数据@b@ * @b@ * @param filePath@b@ * 导入的数据文件@b@ * @param table@b@ * 导入到的表名@b@ */@b@ private static void importData(String filePath, String table) {@b@ Scanner scanner = null;@b@ try {@b@ scanner = new Scanner(new File(filePath));@b@ String[] columnName = scanner.nextLine().split("\b");@b@ int columnCount = columnName.length;@b@@b@ StringBuilder sql = new StringBuilder();// 生成sql语句@b@ sql.append("INSERT INTO " + table + "(");@b@ for (int i = 0; i < columnCount - 1; i++) {@b@ sql.append(columnName[i] + ",");@b@ }@b@ sql.append(columnName[columnCount - 1] + ")" + " VALUES (");@b@ for (int i = 0; i < columnCount - 1; i++) {@b@ sql.append("?,");@b@ }@b@ sql.append("?)");// 生成sql语句END@b@ PreparedStatement statement = conn.prepareStatement(sql.toString()); // 创建插入语句@b@@b@ while (scanner.hasNext()) {@b@ String[] tableValues = scanner.nextLine().split("\b");@b@ for (int i = 0; i < columnCount; i++) {@b@ statement.setString(i + 1, tableValues[i]);@b@ }@b@ statement.execute();@b@ }@b@ } catch (Exception e) {@b@ System.out.println(e);@b@ }@b@ System.out.println("data import success!");@b@ }@b@@b@ /**@b@ * 导入CSV类型数据@b@ * @b@ * @param filePath@b@ * 文件路径@b@ * @param table@b@ * 表名@b@ */@b@ @SuppressWarnings("unused")@b@ private static void importCSV(String filePath, String table)@b@ throws Exception {@b@ boolean isFistLine = false;// 第一行表头少一个\r会导致第二行多出一个\n@b@ Scanner scanner = new Scanner(new File(filePath));@b@ scanner.useDelimiter("\n");@b@ String[] columnName = scanner.next().split(",");// 得到字段名@b@ int columnCount = columnName.length;// 字段个数@b@@b@ StringBuilder sql = new StringBuilder();// 生成sql语句@b@ sql.append("INSERT INTO " + table + "(");@b@ for (int i = 0; i < columnCount - 1; i++) {@b@ sql@b@ .append(columnName[i].substring(0, columnName[i].length())@b@ + ",");@b@ }@b@ sql.append(columnName[columnCount - 1].substring(1,@b@ columnName[columnCount - 1].length() - 1)@b@ + ")" + " VALUES (");@b@ for (int i = 0; i < columnCount - 1; i++) {@b@ sql.append("?,");@b@ }@b@ sql.append("?)");// 生成sql语句END@b@@b@ PreparedStatement statement = conn.prepareStatement(sql.toString());// 插入数据@b@ scanner.useDelimiter("\r\n");@b@ while (scanner.hasNext()) {@b@ String[] tableValues = scanner.next().split(",");@b@ for (int i = 0; i < columnCount; i++) {@b@ String value = null;@b@ if (isFistLine == false) {// 第一行数据每一个值从第二个索引处开始取子串@b@ value = tableValues[i]@b@ .substring(0, tableValues[i].length());@b@ isFistLine = true;@b@ } else {@b@ value = tableValues[i]@b@ .substring(1, tableValues[i].length());@b@ isFistLine = false;@b@ }@b@ statement.setString(i + 1, value);@b@ }@b@ statement.execute();@b@ }@b@ System.out.println("data import success!");@b@ }@b@}