JDBC

JDBC

1.JDBC基本概念

//JDBC:就是Java提供的一套接口（规范），用于和数据库建立连接的一套API ,各家数据库厂商，想要建立连接
// 就需要实现JDBC这套规范，我们数据库厂商，写的这套实现类称之为数据库驱动

2.JDBC入门步骤

/JDBC快速入门
//1.导入数据库厂商写的驱动
//2.加载驱动


//3.建立连接
//4.获取操作对象
//5.编写SQL语句
//6.执行SQL语句
//7.释放资源

3.插入与查询

public class JDBCdemo { //插入
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");//加载驱动
        String url = "jdbc:mysql://localhost:3306/lianxi";
        String username = "root";
        String password = "1";
        Connection con = DriverManager.getConnection(url, username, password);//建立连接
        Statement statement = con.createStatement();//获取操作对象
        String sql = "insert into user(id,username) values(8,'袁伟')"; //编写sql语句
        statement.executeUpdate(sql);//执行sql语句
        con.close();
        statement.close();


    }
}

public class JDBCdemo2 { //查询
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");//加载驱动
        String url = "jdbc:mysql://localhost:3306/lianxi";
        String username = "root";
        String password = "1";
        Connection con = DriverManager.getConnection(url, username, password);//建立连接
        Statement statement = con.createStatement();
        String sql = "select * from user";
        ResultSet resultSet = statement.executeQuery(sql);
        ArrayList<user> list = new ArrayList<>();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String username1 = resultSet.getString("username");

            user user = new user(id, username1);
            list.add(user);

        }
        System.out.println(list);
        con.close();
        statement.close();
        resultSet.close();
    }
}

4.为了防止sql注入，提供另外一种方式

public class JDBCDemo3 { //防止sql注入
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/lianxi";
        String username = "root";
        String password = "1";
        Connection connection = DriverManager.getConnection(url, username, password); //建立连接
        String sql = "insert into user values(?,?)";
        CallableStatement callableStatement = connection.prepareCall(sql);
        callableStatement.setInt(1,9);
        callableStatement.setString(2,"张六");
        callableStatement.execute();
        connection.close();
        callableStatement.close();
    }

}


public class JDBCDemo4 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/lianxi";
        String username = "root";
        String password = "1";
        Connection connection = DriverManager.getConnection(url, username, password);
        String sql = "select * from user";
        CallableStatement callableStatement = connection.prepareCall(sql);
        ResultSet resultSet = callableStatement.executeQuery();
        ArrayList<user> users = new ArrayList<>();
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String username1 = resultSet.getString("username");

            user user = new user(id, username1);
            users.add(user);
        }
        System.out.println(users);
        connection.close();
        resultSet.close();
        callableStatement.close();
    }
}

5.登录代码

public class JDBCDemo5 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {  //登录
        while(true) {
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入用户名");
            String s = scanner.nextLine();
            System.out.println("请输入密码");
            String s1 = scanner.nextLine();
            Class.forName("com.mysql.jdbc.Driver"); //加载驱动
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/lianxi", "root", "1");//建立连接
            String sql = "select * from list where username =? and password = ?";
            CallableStatement callableStatement = connection.prepareCall(sql);
            callableStatement.setString(1, s);
            callableStatement.setString(2, s1);
            ResultSet resultSet = callableStatement.executeQuery();
            boolean next = resultSet.next();
            if (next) {
                System.out.println("登录成功");
                break;
            } else {
                System.out.println("登录失败");
            }

            connection.close();
            callableStatement.close();
            resultSet.close();
        }
    }
}

6.数据库登录—配置文件修改

public class JDBCUtils {
    private static String url;
    private static String username;
    private static String password;

    private JDBCUtils() {
    }

    static {
        try {
            //读取配置文件方式1
            Properties properties = new Properties();
            properties.load(new FileReader("src/jdbcProperties.properties"));
            Class<?> aClass = Class.forName(properties.getProperty("ClassName"));
            url = properties.getProperty("jdbc:mysql://localhost:3306/lianxi");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            
            //读取配置文件方式2
InputStream stream = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbcProperties");
BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(stream));
                char[] chars = new char[1024];
                String len =null;
                while(((len=bufferedReader.readLine())!=null)){
                    String[] split = len.split("=");
                    String s1 = split[0];
                    String s2 = split[1];
                    System.out.println(s1);
                    System.out.println(s2);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接对象的方法
    public static Connection getConnection() throws Exception {

        Connection connection = DriverManager.getConnection(url, username, password);


        return connection;
    }

    //释放资源的方法
    public static void colse(Connection conn, Statement statement, ResultSet resultSet) throws SQLException {
        if (conn != null) {
            conn.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (resultSet != null) {
            resultSet.close();

        }
    }

    public static void colse(Connection conn, Statement statement) throws SQLException {
        if (conn != null) {
            conn.close();
        }
        if (statement != null) {
            statement.close();
        }

    }
}

7.批处理

public class JDBCDemo6 {
    public static void main(String[] args) throws Exception {
        //插入大量数据,批处理
        ArrayList<user> list = new ArrayList<>();
        for (int i = 1; i < 1000; i++) {
            user user = new user(i+11, "123456");
            list.add(user);
        }
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into user values(?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        for (user user : list) {
            statement.setInt(1,user.getId());
            statement.setString(2,user.getUsername());
            statement.addBatch(); //添加批处理

        }
       statement.executeBatch();
       statement.clearBatch();
       JDBCUtils.colse(connection,statement);

    }
}

8.存储过程

调用存储过程{call <procedure-name>[(<arg1>,<arg2>,...)]}
  connection  conn =JDBCUtils.getconnection()
  String sql ="{call testPro(?,?)}";//输入或输出参数用？ 占位
  CallableStatement prepareCall = conn.prepareCall(sql);
  给输入参数设置值
  preparecall.setint(1,7369);
  如果有输出参数我们需要注册输出参数
  preparecall.registeroutparameter(2,Types.INERGER);
  boolean b=preparecall.execute();
  获取输出结果
  int r = preparecall.getint(2);
  sout(r)
  JDBCUtils.close(conn,preparecall);

9.自定义函数

调用自定义函数 {？=call<procedure -name>[(<arg1>,<arg2>,...)]
String sql="{?=call testFun(?)}"
 CallableStatement callableStatement = conn.prepareCall(sql);
        //设置输入参数
        callableStatement.setInt(2,7902);
        //注册返回值
        callableStatement.registerOutParameter(1, Types.INTEGER);
        callableStatement.execute();
        //获取返回的结果
        int r = callableStatement.getInt(1);
        System.out.println("结果是："+r);
        //释放资源
        JDBCUtils.close(conn,callableStatement);

10.事物

事物的基板概念：
    事物是指一组最小逻辑操作单元，里面有多个操作组成。组成事务的每一部分必须要同时提交成功，如果有一个操作失败，整个操作就回滚。
    事务ACID特性：
    原子性
    是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
    一致性
    事务必须使数据库从一个一致性状态变换到另外一个一致性的状态
    隔离性
    事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
    持久性
    持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其他有任何影响
    
    事务的隔离级别
    不考虑隔离会出现问题
    脏读：在一个事务中读取到另一个事务没有提交的数据
    
    不可重复读：在一个事务中，两次查询的结果不一致（值update）不可重复读，是指在数据库访问中，一个事物范围内两个相同的查询却返回了不用数据
    
    虚读：在一个事务中，两次查询的结果不一致（针对insert）无法演示出来，Mysql已经默认避免了
    
    隔离级别
    read uncommitted  读未提交  级别最低 会出现上面3个问题
    read committed    读已提交  可避免脏读 orcle默认级别
    repetable read    可重复读  可以避免脏读和不可重复读  mysql级别
    seralizable       串行化    可避免所有问题