JDBC操作Mysql 和 Oracel 数据库

public class JdbcUtil {

    //开启连接
    private static Connection getConn() {
            //Oracel连接
	        String driver = "oracle.jdbc.driver.OracleDriver";
	        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
            String username = "ZHANG";
	        String password = "Zsq963092066";

	        //mysql连接
//        String driver = "com.mysql.jdbc.Driver";
//        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai";
//        String username = "root";
//        String password = "root";
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public void createIncrement(String table){
        Connection conn = JdbcUtil.getConn();

    }

    /**
     * 判断数据库表是否存在
     * @param data          数据库名
     * @param tableName     表名
     * @return    true 存在  false 不存在
     */
    public static boolean tables(String data ,String tableName){
        boolean flag=false;
        Connection conn = JdbcUtil.getConn();
        try {
            DatabaseMetaData meta = conn.getMetaData();
            // 第一个参数catalog在MySQL中对应数据库名：michaeldemo
//            ResultSet rsTables = meta.getTables(data, null, tableName,
//                    new String[] { "TABLE" });

            // 第二个参数schemaPattern在ORACLE中对应用户名：ZHANG
             ResultSet rsTables = meta.getTables(null, "ZHANG", tableName,
             new String[] { "TABLE" });
            System.out.println("getTables查询信息如下：");
            while (rsTables.next()) {
                System.out.println("获取的表有    "+rsTables.getString("TABLE_NAME"));
                if(tableName.equals(rsTables.getString("TABLE_NAME"))){
                    flag=true;
                    break;
                }
            }
            rsTables.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (null != conn) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }

    /**
     * 插入数据
     * @param columnList  字段集合
     * @param dataList    数据集合
     * @param table       表名
     */
    public static int insert(List<String> columnList, List<List<String>> dataList, String table) throws SQLException {
        Connection conn = getConn();
        conn.setAutoCommit(false);
        int count = 0;
        //拼接处理insert 语句
        String s1 = "insert into " + table + " (id ,";
        StringBuffer sb = new StringBuffer(s1);
        StringBuffer ssb = new StringBuffer("( "+table+"_ID.nextval ,");
        if (columnList != null && columnList.size() > 0) {
            for (String s : columnList) {
                sb.append(s).append(",");
                ssb.append("?").append(",");
            }
        }
        String valu = ssb.substring(0, ssb.lastIndexOf(",")) + ")";   //(?,?,?,?)
        String inse = sb.substring(0, sb.lastIndexOf(",")) + ") values";  //insert into test (id,name,password,phone) values
        StringBuffer sbb = new StringBuffer();
        String sql = sbb.append(inse).append(valu).toString(); //insert into test (id,name,password,phone) values(?,?,?,?)
        //插入语句
        System.out.println(sql);
        PreparedStatement pstmt = null;
        //insertMySql(conn,sql,columnList,dataList);  //mysql插入
        insertOracel(conn,sql,columnList,dataList);  //oracel插入
        return count;
    }

    //Oracel插入
    private static void insertOracel(Connection conn, String sql, List<String> columnList, List<List<String>> dataList) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            //循环赋值 插入数据
            for (int j = 0; j < dataList.size(); j++) {
                for (int i = 0; i < columnList.size(); i++) {
                    pstmt.setString(i + 1, dataList.get(j).get(i));
                }
                pstmt.addBatch();
                if(j%300==0){
                    pstmt.executeBatch();
                    conn.commit();
                    pstmt.clearBatch();
                }
            }
            //执行插入语句
            pstmt.executeBatch();
            conn.commit();
            pstmt.clearBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //mysql插入
    public static void insertMySql(Connection conn,String sql ,List<String> columnList, List<List<String>> dataList){
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            //循环赋值 插入数据
            for (int j = 0; j < dataList.size(); j++) {
                for (int i = 0; i < columnList.size(); i++) {
                    pstmt.setString(i + 1, dataList.get(j).get(i));
                }
                pstmt.addBatch();
                if(j%300==0){
                    pstmt.executeBatch();
                    conn.commit();
                    pstmt.clearBatch();
                }
            }
            //执行插入语句
            pstmt.executeBatch();
            conn.commit();
            pstmt.clearBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    private static void query() {
        Connection conn = getConn();
        String sql = "select * from users";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println("name: " + rs.getString("username")
                        + " \tpassword: " + rs.getString("password"));
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static int update(String oldName, String newPass) {
        Connection conn = getConn();
        int i = 0;
        String sql = "update users set password='" + newPass
                + "' where username='" + oldName + "'";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    private static int delete(String username) {
        Connection conn = getConn();
        int i = 0;
        String sql = "delete users where username='" + username + "'";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    //创建表
    public static void createTable(String sql) {
        try {
            Connection conn = getConn();
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            int count = preparedStatement.executeUpdate(sql);
            if (count == 0) {
                System.out.println("创建成功");
            } else {
                System.out.println("创建失败");
            }
            preparedStatement.close();
            conn.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }


    public static void main(String[] args) throws SQLException {

//        String table="zhang123";
//        boolean bool=JdbcUtil.tables("test",table);
//        System.out.println(bool);
        //创建表
			String sql =  "CREATE TABLE Test_Table (" +
                    "ID number(11) primary key," +
                    "Name varchar(50) not null, " +
                    "Value1 varchar(50) not null " +
                    ")";
			createTable(sql);
        //插入
//        List<String> columnList = new ArrayList<>();
//        columnList.add("id");
//        columnList.add("name");
//        columnList.add("password");
//        columnList.add("phone");
//        List<List<String>> dataList = new ArrayList<>();
//        for (int i = 0; i < 10; i++) {
//            List<String> s = new ArrayList<>();
//            s.add(i + 1 + "");
//            s.add("张三" + i);
//            s.add("123456" + i);
//            s.add("17607183394" + i);
//            dataList.add(s);
//        }
//        String table = "test";
//        insert(columnList, dataList, table);
        //更新

        //删除

    }

}