javaweb22/4/11

JDBC

JDBC基本用法

1.普通编译，connection.createStatement

public class JDBCTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //配置信息
        String url = "jdbc:mysql://localhost:3306/hotel?useUnicode=true&charachterEncoding=utf-8";
        String username = "root";
        String password = "297999";
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //连接数据库,connection代表数据可库
        Connection connection = DriverManager.getConnection(url, username, password);
        //向数据库发送SQL对象statement：做增删改查
        Statement statement = connection.createStatement();
        String sql= "select * from account";
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()){
            System.out.println("id:"+rs.getInt("id"));
            System.out.println("name:"+rs.getString("name"));
            System.out.println("pwd:"+rs.getString("pwd"));
            System.out.println("mobile:"+rs.getString("mobile"));
            System.out.println("email:"+rs.getString("email"));
            System.out.println("type:"+rs.getString("type"));
        }
        //一定要关闭连接！！
        rs.close();
        statement.close();
        connection.close();
    }

}

2.预编译，connection.prepareStatement()

public class JDBCTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //配置信息
        String url = "jdbc:mysql://localhost:3306/hotel?useUnicode=true&charachterEncoding=utf-8";
        String username = "root";
        String password = "297999";
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //连接数据库,connection代表数据可库
        Connection connection = DriverManager.getConnection(url, username, password);
        //向数据库发送SQL对象statement：做增删改查

        String sql= "insert into account(id, name, pwd, mobile, email, type) values (?,?,?,?,?,?)";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setInt(1,3);
        preparedStatement.setString(2,"张三");
        preparedStatement.setString(3,"123456");
        preparedStatement.setString(4,"987654321");
        preparedStatement.setString(5,"226177@qq.com");
        preparedStatement.setInt(6,1);

        //i是影响的行数
        int i = preparedStatement.executeUpdate();
        //一定要关闭连接！！
        if (i>0){
            System.out.println("添加成功");
        }
        preparedStatement.close();
        connection.close();
    }

}

事务

public class JDBCTest3 {
    @Test
   public void test() throws SQLException, ClassNotFoundException {
        //配置信息
        String url = "jdbc:mysql://localhost:3306/hotel?useUnicode=true&charachterEncoding=utf-8";
        String username = "root";
        String password = "297999";
        Connection connection = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //连接数据库,connection代表数据可库
            connection = DriverManager.getConnection(url, username, password);

            //开启事务,false是开启
            connection.setAutoCommit(false);
            //事务的第一条语句
            String sql = "update account set name='李四' where id=1";
            connection.prepareStatement(sql).executeUpdate();
            //错误代码
//            int i=1/0;
            //事务的第二条语句
            String sql2 = "update account set name='王五' where id=2";
            connection.prepareStatement(sql2).executeUpdate();
            //若以上两条语句都执行成功则提交事务
            connection.commit();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            //事务回滚
            connection.rollback();
            e.printStackTrace();
        }finally {
            connection.close();
        }


    }
}