javaSE--jdbc

更新语句类：

实例化Driver中可省去手动newInstance的原因：其类中本身就是被static代码块包裹，随着该类的加载而自动加载进内存中。只需如下写：


//2.加载驱动 （①实例化Driver ②注册驱动）
        Class.forName(driverName);

然而java的spi机制，上一步也不用写！

 @Test
    public void testPrepareStatement() {

        Connection connection =null;
        Statement statement = null;
        ResultSet resultSet =null;


        try {
            String sql ="update vip set username=? where id=?";
            connection = jdbcUtil.getConnection();
            PreparedStatement  preparedStatement = connection.prepareStatement(sql);
            //1：代表第一个？
            preparedStatement.setString(1,"xzhao");//string双引号，char单引！！！
//            2：代表第二个？占位符
            preparedStatement.setInt(2,2);
            preparedStatement.execute();



        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtil.closeAll(connection,statement,resultSet);

        }
    }

查询语句类：

@Test
    public void testPrepareStatement1() {

        Connection connection =null;
        Statement statement = null;
        ResultSet resultSet =null;


        try {
            String sql ="select * from vip where id > ?";
            connection = jdbcUtil.getConnection();
            PreparedStatement  preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,0);
            resultSet=preparedStatement.executeQuery();

            while ((resultSet.next())){
                System.out.println(resultSet.getInt("id"));
                System.out.println(resultSet.getString("username"));
            }



        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtil.closeAll(connection,statement,resultSet);

        }
    }

事务演示类：

@Test
    public void transaction() {
         //★一个事务必须在一个connection中用哦！
        Connection connection =null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet =null;


        try {
            String sql ="update vip set username= ? where id =?";
            //获取链接并 关闭自动提交
            connection = jdbcUtil.getConnection();
            connection.setAutoCommit(false);
             preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"jhie");  //第一个问号占位符
            preparedStatement.setInt(2,1);    //修改第二个问号占位符
            int i=preparedStatement.executeUpdate();
            System.out.println(i);

            int res =i/0; //模拟异常，用来导致事务回滚，本次操作作废，数据退回上一步的亚子

            String sql2 ="update vip set username= ? where id =?";
            //获取链接并 关闭自动提交
            connection = jdbcUtil.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"特殊t");
            preparedStatement.setInt(2,2);
            int i2=preparedStatement.executeUpdate();
            System.out.println(i2);

            connection.commit();


        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } finally {
            jdbcUtil.closeAll(connection,preparedStatement,resultSet);

        }
    }

抽取重复代码Utils：连接和关闭：

public class jdbcUtil {

    public static Connection getConnection(){
        Connection conn = null;
        try {
            Properties properties = new Properties();
            properties.load(JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));

            String url = properties.getProperty("mysql.url");
            String user = properties.getProperty("mysql.username");
            String password = properties.getProperty("mysql.password");
            String driverName = properties.getProperty("mysql.driverName");

            //2.加载驱动 （①实例化Driver ②注册驱动）
            Class.forName(driverName);

            //3.获取连接
            conn = DriverManager.getConnection(url, user, password);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(statement!=null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(resultSet!=null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

jdbc.properties配置文件：

mysql.username=root
mysql.password=root
mysql.url=jdbc:mysql://127.0.0.1:3306/meta?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
mysql.driverName=com.mysql.jdbc.Driver