mySql学习11

jDbc的事务的处理

Connection对象

（1） void setAutoCommit(boolean autoCommit)  true为自动提交 false为手动提交 可以在事务开启之前先置为false然后 事务结束后还原为true 不影响其他的sql

批处理

使用preparedStatement.addBatch()添加批处理 然后通过

preparedStatement.executeBatch() 执行批处理

进行批处理需要设置连接的 rewriteBatchedStatements=true  这个mysql默认是关闭的

1. conn = DriverManager.getConnection(\"jdbc:mysql://127.0.0.1:3306/test_deletable?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8\",  \"mysql\", \"mysql\"); // 链接本地MYSQL  

@Test

     public void test2() {

          Properties properties = new Properties();

          try {

               properties.load(ClassLoader.getSystemResourceAsStream(\"jdbc.properties\"));

               Class.forName(properties.getProperty(\"drivername\"));

              String sql = \"insert into student values(null,?,?,?,?,null)\";

              long startTime = System.currentTimeMillis();

              // 获取连接

              Connection connection = DriverManager.getConnection(properties.getProperty(\"url\"), properties);

              // 这个对象不需要重复创建多次

              PreparedStatement prepareStatement = connection.prepareStatement(sql);

              for (int i = 0; i < 2000; i++) {

                   prepareStatement.setString(1, \"名字\");

                   prepareStatement.setInt(2, 2);

                   prepareStatement.setString(3, \"ceec\");

                   prepareStatement.setInt(4, 2);

//                 prepareStatement.setBlob(5, new FileInputStream(\"./pic/temp.jpg\"));  //这个是存储图片为二进制的大文件

                   prepareStatement.addBatch();

                   if (i % 500 == 0) {

                        prepareStatement.executeBatch();

                        prepareStatement.clearBatch();

                   }

              }

              prepareStatement.executeBatch();

              prepareStatement.close();

              connection.close();

              long endTime = System.currentTimeMillis();

              System.out.println(\"消耗的时间----\" + (endTime - startTime));

          } catch (Exception e) {

              e.printStackTrace();

          }

     }

(2)如何获取自动增长的键值

preparedStatement=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

............

preparedStatement.execteUpdate();

ResultSet rs=preparedStatement.getGeneratedKeys();

oid;

    if(rs.next()){

        oid=rs.get (1);

    }

Systom.out.println(\"获取的新订单的编号：\"+oid)

查询的代码

@Test

     public void testOne() {

          try {

              Properties properties = new Properties();

               properties.load(ClassLoader.getSystemResourceAsStream(\"jdbc.properties\"));

               Class.forName(properties.getProperty(\"drivername\"));

              // // ②通过gid查询商品的的价格和名称

              String sql_2 = \"select gname,price from goodstable where gid = \" + 3;

              // 获取连接

              Connection connection_2 = DriverManager.getConnection(properties.getProperty(\"shopping_url\"), properties);

              // // 这个对象不需要重复创建多次

              PreparedStatement prepareStatement_2 = connection_2.prepareStatement(sql_2,

                        Statement.RETURN_GENERATED_KEYS);

              ResultSet resultSet = prepareStatement_2.executeQuery();

              String goodsNname = \"\";

              double goodsPrice = 0;

              ResultSet Data rsmd = resultSet.get Data();

              HashMap<String, > map = new HashMap();

//这是将所有的列都遍历到map中，然后根据需要获取相应的字段的值

              while (resultSet.next()) {

                   // 把列名的别名和列值分别取出来放到map中作为键值出现(resultSet和rsmd结合得到的就是一个表，和数据库表一样)，

                   // 由ResultSet Data得到每一列的别名，

                   for (int i = 0; i < rsmd.getColumnCount(); i++) {

                        String columnLabel = rsmd.getColumnLabel(i + 1);

                         columnValue = resultSet.get (columnLabel);

                        System.out.println(\"columnLabel---\" + columnLabel + \"   columnValue---\" + columnValue);

                        map.put(columnLabel, columnValue);

                   }

                   System.out.println(resultSet.get (1));

                   System.out.println(resultSet.get (2));

              }

              // 遍历map对象，用反射填充对象属性值

              for (Map.Entry<String, > entry : map.entrySet()) {

                   String fieldName = entry.getKey();

                    fieldValue = entry.getValue();

              }

              System.out.println(\"goodsNname----\" + goodsNname);

              System.out.println(\"goodsPrice----\" + goodsPrice);

              prepareStatement_2.close();

              connection_2.close();

          } catch (Exception e) {

              // TODO: handle exception

              e.printStackTrace();

          }

     }