数据篇之JDBC连接MySQL

墨蓝 2022-07-21 02:18 158阅读 0赞

ps: 前段时间做了一个不存在网络，获取设备存储到mysql中的数据，然后根据数据生成二维码，用户通过扫描二维码进行操作的android 程序。

**其中一个需求：在没有网络的情况，要读取mysql中的数据，然后进行增删查改**

解决方式：1.采用java中的jdbc链接mysql，然后进行增删查改

准备工作：mysql-connector-java-xxx-bin.jar是不可少的

![这里写图片描述][20160713113817878]

**（一）存储连接mysql的账号和密码，采用properties来保存，将properties文件放置到assets文件夹下**

![这里写图片描述][20160713114216352] ![这里写图片描述][20160713114231489]

//获取Properties
    public Properties getProperties() {
            InputStream inputStream = null;
            try {
                properties = new Properties();
                inputStream = BaseApplication.getContext().getAssets().open(path);
                properties.load(inputStream);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (inputStream != null) {
                       inputStream.close();
                    }
                } catch (Exception e2) {
                    e2.printStackTrace();
                }
            }
            return properties;
        }

**（二）加载jdbc驱动，连接mysql:**

Connection接口常用api：
            1.prepareStatement()：创建一个PrepareStatement对象，用于操作数据库
            2.setAutoCommit():设置是否自动提交，与事务有关
            3.rollback():回滚事务
            4.setSavePoint():设置一个恢复点，与事务有关
            5.close():关闭数据库
            6.commit():提交操作，与事务有关

//驱动名
        public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
        private java.sql.Connection connection;
        //MySQL地址
        public static final String DBURL = "jdbc:mysql://192.168.0.xx:3306/yjqwxmp?useUnicode=true&characterEncoding=utf8";
    
        public void connnetJDBC() {
            try {
                // 加载jdbc驱动
                Class.forName(DBDRIVER);
                 //通过url,和账号密码，链接mysql,获取connection
                connection = DriverManager.getConnection(DBURL, getProperties());
                //注意点：用到了事务，所以取消自动提交，反之，省略
                connection.setAutoCommit(false);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
    
            }
        }

**（三）通过PreparedStatement（预处理）接口操作数据库：**

PreparedStatement是Statement的子类，效率更高。
    
     PreparedStatement的使用原理：
          采用"?"的占位符，然后根据"?"扥顺序来设置内容
    
     除了Statement的属性外，还新增属性：
             1. int executeUpdate():执行设置的预处理sql语句
             2. resultSet executeQuery():执行数据库的查询操作，返回resultSet
             3. void   setint(int parameterIndex,int  x):根据指定索引，设置整形数据
             除此之外，还有setFloat(),setString(),setDate()，根据指定索引，设置各种类型数据
    
      注意点：setDate()这个方法参数是java.sql.Date,
             若是java.util.Date需要通过getTime()转换为java.sql.Date

查询案例：
         //查询的sql
        public static final String GASWORKER_SELECT_SQL = "select id, gas_worker_id from t_gasworker_msg where gas_worker_id = ?";
        //包含数据的结果集
        ResultSet resultSet = null;
        //执行增删查改sql的接口
        PreparedStatement statement = null;
    
             statement = connection.prepareStatement(GASWORKER_SELECT_SQL);
             //"?"的索引为1，根据索引来设置 gas_worker_id = a123
             statement.setString(1,"a123");
             resultSet = statement.executeQuery();
             while(resultSet.next()){
                 //ResultSet中的所有数据都可以通过getString()方法获得
                 String id = resultSet.getString("id");
             }
             statement.close(); //关闭PreparedStatement
             result.close();    //关闭结果集
             connection.close();//关闭连接的接口

**（四）使用事务批量处理sql:**

事务：所有操作要门一起成功，要门一起失败
    MySQL中事务处理顺序：
              1. 取消自动提交：connection.setAutoCommint(false)将执行操作先存放在 session  
                             (每个链接数据库的用户都是 session)
              2.开启事务
              3.编写增，删，查，改sql语句
              4.提交事务:connection.commit();
              5.事务回滚：若是执行sql发生错误，取消操作，或者回滚到指定的记录点上

批量插入数据到数据库的案例：
        //首先设置，自动提交取消
        connection.setAutoCommit(false);
    
        public static final String GASFILTRATIONRECODER_INSERT = "insert into t_gas_filtration_recoder (gas_exchange_index,staff_ic_id) values (?,?)";
        PreparedStatement insert_Statement = null;
    
        try{ 
            insert_Statement  =connection.prepareStatement(GASFILTRATIONRECODER_INSERT);
            for(int i=0；i<2;++i){
                    insert_Statement.setString(1,String.valuesOf(i));
                    insert_Statement.setString(2,"a"+i);
                    insert_Statement.addBatch();//添加事务
              }
    
             //批量执行
             int[]  i= statement.executeBatch(); 
             //事务提交
             connection.commit();
             if(i==2){  //当执行sql的数量为添加事务的总数时，执行成功
    
             }
           }catch(Exception e){
              try {
                //若是事务执行sql错误，回滚事务
                 connection.rollback();
               } catch (Exception e2) {
            }
             e.printStackTrace();
    
           }finally{
               if (insert_Statement != null) {
                   insert_Statement.close();
               }
               if (connection != null) {
                   connection.close();
                   connection = null;
               }
    
           }

**(五)使用元数据分析数据库：**

ResultSetMetaData:获取到Resultet对象中列的数据类型和属性信息得对象
         1.int getColumnnCount():获取一个查询结果中的列数
         2.boolean isAutolncrement(int column):判断指定列是否为自动编号
         3.string getColumnName(int column):返回指定列的名字

**个人指定需求案例：**

项目需求：
           1.在mysql中查询到新数据，修改新数据标示，将新数据保存到本地  
             （android 自带数据库 SQLiteDatabase）
           2.向myslq中插入数据，更新数据，删除数据，mysql数据库和SQLiteDatabase同步
           3.合并多条数据为一条数据，插入MySql中

**将jdbc操作封装在一个utls类中：**

public class JDBCUtils { 
        public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
        private static final String path = "gasstation.properties";
        private java.sql.Connection connection;
        private Properties properties;
        //数据库的url
        public static final String DBURL = "jdbc:mysql://192.168.0.xx:3306/yjqwxmp?useUnicode=true&characterEncoding=utf8";
    
        private String selectSql1 = "select geid, gas_station_gun_id,gas_type,gas_volume,gas_desity,staff_ic_id,consumer_ic_id,group_no,gas_exchange_index,gas_exchange_time_start,gas_exchange_time_end from t_gas_exchange_info where upload_flg = 0 and ";
    
        public String workNumber = "staff_ic_id =";
    
    
        public static final String GASMSG_UPDATE_SQL = "update t_gas_msg set gas_machine_id=? where gas_station = ? ";
    
        public static final String GASMSG_SELECT_SQL = "select id ,gas_station,gas_machine_id from t_gas_msg where gas_station = ?";
    
    
        public static final String GASWORKER_INSERT_SQL = "insert into t_gasworker_msg(gas_worker_id) values(?)";
    
        public static final String GASWORKER_UPDATE_SQL = "update t_gasworker_msg set gas_worker_id=? where gas_worker_id =?";
        public static final String GASFILTRATIONRECODER_INSERT = "insert into t_gas_filtration_recoder (gas_exchange_index,staff_ic_id,gas_type,gas_volume,gas_exchange_time_end,merge_flg,data_source) values (?,?,?,?,?,?,?)";
    
        public static final String GASFILITRATIONRECODER_UPDATE = "update t_gas_filtration_recoder set merge_flg =1 where gas_exchange_index=";
    
        public static final String AND = " and geid=";
    
        public JDBCUtils() {
            connnetJDBC();
        }
    
        public void connnetJDBC() {
            try {
                // 加载jdbc驱动
                Class.forName(DBDRIVER);
                connection = DriverManager.getConnection(DBURL, getProperties());
                connection.setAutoCommit(false);// 取消自动提交
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                LogController.i("result_return", connection + " ");
            }
        }
        //关闭连接
        public void closeConnection() {
            try {
                if (connection != null) {
                    connection.close();
                    connection = null;
                }
            } catch (Exception e) {
            }
    
        }
        //获取Properties
        public Properties getProperties() {
            InputStream inputStream = null;
            try {
                properties = new Properties();
                inputStream = BaseApplication.getContext().getAssets().open(path);
                properties.load(inputStream);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (inputStream != null) {
                        inputStream.close();
                    }
                } catch (Exception e2) {
                    e2.printStackTrace();
                }
            }
            return properties;
        }
    
        //获取到android本地数据库上的员工信息
        public List<String> getGasWorkerRecoder() {
            Cursor cursor = null;
            try {
                ContentResolver resolver = BaseApplication.getContext()
                        .getContentResolver();
                cursor = resolver.query(DateProviderContract.GASWORKER_URI, null,
                        null, null, null);
                if (cursor.moveToFirst()) {
                    List<String> list = new ArrayList<String>();
                    do {
                        list.add(cursor.getString(cursor
                                .getColumnIndex(DateProviderContract.COLUMN_NUMBER)));
                    } while (cursor.moveToNext());
                    return list;
                }
            } catch (Exception e) {
            } finally {
                try {
                    if (cursor != null) {
                        cursor.close();
                    }
                } catch (Exception e2) {
                }
            }
            return null;
        } 
    
        //获取mySql中最新设备信息，且修改新信息得标志为旧信息，将获取到的信息存到本地
        public void operateGasRecoder() {
            ResultSet resultSet = null;
            PreparedStatement statement = null, insert_Statement = null;
            List<ContentValues> list = null;
            List<String> workerList = getGasWorkerRecoder();
            if (workerList == null) {
                closeConnection();
                return;
            }
            LogController.i("tiemss", "connection " + connection);
            try {
                if (connection == null) {
                    return;
                }
                LogController.i("tiemss", "list size" + workerList.size());
                StringBuffer buffer = new StringBuffer();
                buffer.append(selectSql1);
                for (int i = 0; i < workerList.size(); i++) {
                    if (i != 0) {
                        buffer.append(" or ");
                    } else {
                        if (workerList.size() > 1) {
                            buffer.append("(");
                        }
                    }
                    buffer.append(workNumber);
                    buffer.append("'");
                    buffer.append(workerList.get(i).trim());
                    buffer.append("'");
                    if (i == workerList.size() - 1) {
                        if (workerList.size() > 1) {
                            buffer.append(")");
                        }
                    }
                }
                statement = connection.prepareStatement(buffer.toString());
    
                resultSet = statement.executeQuery();
                insert_Statement = connection
                        .prepareStatement(GASFILTRATIONRECODER_INSERT);
                StringBuffer buffer2 = null;
                while (resultSet.next()) {
                    if (list == null) {
                        list = new ArrayList<ContentValues>();
                        buffer2 = new StringBuffer();
                        buffer2.append("update t_gas_exchange_info set upload_flg = 1 where geid = ");
                    }
                    buffer2.append(resultSet.getInt(1));
                    buffer2.append(AND);
                    insertGasFiltrayionTable(resultSet, insert_Statement);
                    list.add(getContentValues(resultSet));
                }
                if (buffer2 != null) {
                    String str = buffer2.toString().trim();
                    LogController.i("tiemss", str
                            .substring(0, str.lastIndexOf(AND)).trim());
                    insert_Statement.addBatch(str
                            .substring(0, str.lastIndexOf(AND)).trim());
                }
                int[] insertSize = insert_Statement.executeBatch();
                connection.commit();
                // 保存到本地 android 数据库，
                if (insertSize.length > 0) {
                    ContentValues[] values = new ContentValues[list.size()];
                    for (int i = 0; i < list.size(); i++) {
                        values[i] = list.get(i);
                    }
                    ContentResolver resolver = BaseApplication.getContext()
                            .getContentResolver();
                    //批量插入
                    int length = resolver.bulkInsert(
                            DateProviderContract.RECODER_URI, values);
                    LogController.i("result_return", length + " ");
                }
            } catch (Exception e) {
                e.printStackTrace();
                if (resultSet != null) {
                    try {
                        connection.rollback();// 事务回滚
                    } catch (Exception e2) {
                    }
                }
            } finally {
                try {
    
                    if (resultSet != null) {
                        resultSet.close();
                    }
                    if (statement != null) {
                        statement.close();
                    }
                    if (insert_Statement != null) {
                        insert_Statement.close();
    
                    }
                    if (list != null) {
                        list.clear();
                        list = null;
                    }
                    if (workerList != null) {
                        workerList.clear();
                        workerList = null;
                    }
                    closeConnection();
    
                } catch (Exception e2) {
                    e2.printStackTrace();
                }
    
            }
        }
    
        //插入过滤后的数据
        public void insertGasFiltrayionTable(ResultSet resultSet,
                PreparedStatement statement) {
            try {
                statement.setInt(1, resultSet.getInt(9));
                statement.setString(2, resultSet.getString(6));
                statement.setInt(3, resultSet.getInt(3));
                statement.setString(4, resultSet.getString(4));
                statement.setTimestamp(5, resultSet.getTimestamp(11));
                statement.setInt(6, 0);
                statement.setString(7, null);
                statement.addBatch();
            } catch (Exception e) {
                e.printStackTrace();
            }
    
        }
    
        public ContentValues getContentValues(ResultSet resultSet) {
            ContentValues values = new ContentValues();
            try {
                values.put(DateProviderContract.COLUMN_CHANGNUMBER,
                        resultSet.getString(9));
                values.put(DateProviderContract.COLUMN_GASNUMBER,
                        resultSet.getString(2));
                values.put(DateProviderContract.COLUMN_GASSTYLE,
                        resultSet.getString(3));
                values.put(DateProviderContract.COLUMN_GASVOLUME,
                        resultSet.getString(4));
                values.put(DateProviderContract.COLUMN_TIME,
                        resultSet.getString(11));
            } catch (Exception e) {
                e.printStackTrace();
            }
            return values;
        }
    
        //更新特殊条件过滤后的信息记录
        public void upDateFiltrationRecoder(String  gaschangindex,String staff_id,String gas_type,Double gas_volume,String gas_time,String  source,String updateSql) {
            if (connection == null) {
                return;
    
            }
            PreparedStatement statement = null;
            try {
                statement = connection
                        .prepareStatement(GASFILTRATIONRECODER_INSERT);
                statement.setInt(1, Integer.valueOf(gaschangindex));
                statement.setString(2, staff_id);
                statement.setInt(3, Integer.valueOf(gas_type));
                statement.setString(4, String.valueOf(gas_volume));
                statement.setTimestamp(5, Timestamp.valueOf(gas_time));
                statement.setInt(6, 1);
                statement.setString(7, source);
                statement.addBatch();
                statement.addBatch(updateSql);
                statement.executeBatch();
                connection.commit();
            } catch (Exception e) {
                try {
                    connection.rollback();
                } catch (Exception e2) {
                }
                e.printStackTrace();
            } finally {
                try {
                    if (statement != null) {
                        statement.close();
                    }
                    if (connection != null) {
                        connection.close();
                    }
                } catch (Exception e2) {
                }
    
            }
    
        }
    
        public void updateDb(int geid, Statement statement) {
            StringBuilder builder = new StringBuilder();
            builder.append("update t_gas_exchange_info set upload_flg = 1 where geid =");
            builder.append(geid);
            try {
                statement.addBatch(builder.toString());
            } catch (Exception e) {
            }
        } 
    
        //插入设备信息到mysql中
        public synchronized void operateGasSystem(SparseArray<String> array) {
            if (connection == null) {
                return;
            }
            PreparedStatement statement = null;
            ResultSet resultSet = null;
            try {
                statement = connection.prepareStatement(GASMSG_SELECT_SQL);
                statement.setString(1, array.get(0));
                resultSet = statement.executeQuery();
    
                if (!resultSet.next()) {
                    statement = connection.prepareStatement(GASMSG_INSET_SQL);
                    statement.setString(1, array.get(0));
                    statement.setString(2, array.get(1).trim());
                    statement.execute();
                    connection.commit();
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (statement != null) {
                        statement.close();
                    }
                    if (resultSet != null) {
                        resultSet.close();
                    }
                    closeConnection();
                } catch (Exception e2) {
                }
    
            }
        } 
    
        //插入员工信息到mysql中
        public synchronized void operateGasWorker(SparseArray<String> array) {
            if (connection == null) {
                return;
            }
            PreparedStatement statement = null;
            ResultSet resultSet = null;
            try {
                statement = connection.prepareStatement(GASWORKER_SELECT_SQL);
                statement.setString(1, array.get(0));
                resultSet = statement.executeQuery();
    
                if (!resultSet.next()) {
                    statement = connection.prepareStatement(GASWORKER_INSERT_SQL);
                    statement.setString(1, array.get(0));
                    statement.execute();
                    connection.commit();
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (statement != null) {
                        statement.close();
                    }
                    if (resultSet != null) {
                        resultSet.close();
                    }
                    closeConnection();
                } catch (Exception e2) {
                }
    
            }
    
        }
    
    }

[20160713113817878]: /images/20220720/93c6c0278ba5486893dee400871d8b7f.png
[20160713114216352]: https://img-blog.csdn.net/20160713114216352
[20160713114231489]: /images/20220720/5e61b92c87664a92a94d5e041bc71633.png