JDBC操作Mysql 和 Oracel 数据库

约定不等于承诺〃 2023-02-28 15:16 20阅读 0赞

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);
            //更新
    
            //删除
    
        }
    
    }