数据篇之JDBC连接MySQL

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

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

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

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

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

//获取Propertiespublic 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) {            }        }    }}