jdbc的学习

操作数据库的基本步骤

•  1.引入依赖  注册驱动
   2.获取连接对象    如何获取数据库的连接？
   3.获取发送sql语句的对象  发送sql语句
   4.处理结果集
   5.释放资源
  

1.0 原始版本

•     public static void main(String[] args) throws SQLException {
    		 // 创建驱动对象
            Driver driver = new Driver();
  // 注册驱动
            DriverManager.registerDriver(driver);
            
            /**
             *  获取连接  url   user  password    http://www.baidu.com/login.html
             *  url:  数据库的通信地址  jdbc:mysql://localhost:3306/java2216
             *  user：  数据的用户名  root
             *  password：  用户密码  root
             */
            String url = "jdbc:mysql://localhost:3306/java2216?useSSL=false";
            String username = "root";
            String password = "root";
  //获取数据库的连接
            Connection connection = DriverManager.getConnection(url, username, password);
            System.out.println("connection = " + connection);
    
  // 通过连接获取发送sql语句的对象  Statement
            Statement statement = connection.createStatement();
            //编写sql
            //String sql = "SELECT  *  FROM  user   WHERE id = 1";
            String sql = "SELECT  *  FROM  user ";
            // 发送sql语句到mysql数据库
            ResultSet resultSet = statement.executeQuery(sql);
  // 处理结果集  resultSet  resultSet.next()  查询数据结果集中是否还有下一条数据
            // 创建集合 存储查询结果中封装的数据
            ArrayList<User> users = new ArrayList<>();
            while (resultSet.next()){
                //获取当前记录中字段值  根据字段值的数据类型获取
                int id = resultSet.getInt("id");  //  通过字段名获取
                String uname = resultSet.getString(2);  //  通过字段的列索引
                String pwd = resultSet.getString("password");
                User user = new User().setId(id).setPassword(pwd).setUsername(uname);
                users.add(user);
                //System.out.println("user = " + user);
            }
    
            // 遍历集合
            //users.forEach(user-> System.out.println("user = " + user));
    
            for (User user : users) {
                System.out.println(user);
            }
  // 释放资源
            resultSet.close();
            statement.close();
            connection.close();
        }
    }
  

2.0 封装JDBCUtil工具类（快速的获取连接 和释放资源）

•    public class JDBCUtil {
  
    	    private JDBCUtil(){}   //防止被外部类new一个JDBCUtil
    	
    	    private static String username;
        	private static String driverClassName;
    	    private static String password;
    	    private static String url;
    	
    	    static {  //  执行一次 类加载时
    	        ResourceBundle file = ResourceBundle.getBundle("jdbc");
    	        driverClassName = file.getString("driverClassName");
    	        url = file.getString("url");
    	        username = file.getString("username");
    	        password = file.getString("password");
    	    }
    	
    	
    	    //获取数据库连接的方法
    	    public  static Connection  getConnection(){
    	        //通过反射注册驱动
    	        Connection connection = null;
    	        try {
    	            Class.forName(driverClassName);
    	            connection = DriverManager.getConnection(url, username, password);
    	        } catch (Exception e) {
    	            e.printStackTrace();
    	        }
    	        return connection;
    	    }
    	
    	    //释放资源
    	    public static  void  closeAll(Connection connection, Statement statement, ResultSet resultSet){
    	
    	        if(resultSet!=null){
    	            try {
    	                resultSet.close();
    	            } catch (SQLException throwables) {
    	                throwables.printStackTrace();
    	            }
    	        }
    	
    	        if(statement!=null){
    	
    	            try {
    	                statement.close();
    	            } catch (SQLException throwables) {
    	                throwables.printStackTrace();
    	            }
    	        }
    	
    	        if(connection!=null){
    	
    	            try {
    	                connection.close();
    	            } catch (SQLException throwables) {
    	                throwables.printStackTrace();
    	            }
    	        }
    	    }
    	}
  

• 模拟用户登录和注册
    		public class UserService {
    		
    		    // 用户登录
    		    public  Boolean login(User user){
    		        //根据用户信息 查询数据库  1.数据库中存在用户信息 登录成功    jdbc查询数据库
    		        // 获取连接
    		        Connection connection = JDBCUtil.getConnection();
    		        Statement statement = null;
    		        ResultSet resultSet = null;
    		        try {
    		             statement = connection.createStatement();
    		            String sql = "select *  from user where username= '"+user.getUsername()+"' and password = '"+user.getPassword()+"'";
    		            System.out.println("sql = " + sql);
    		            resultSet = statement.executeQuery(sql);
    		            return  resultSet.next();
    		        } catch (SQLException throwables) {
    		            throwables.printStackTrace();
    		        }finally {
    		            JDBCUtil.closeAll(connection, statement, resultSet);
    		        }
    		        return  false;
    		    }
    		
    		    // 用户注册
    		    public  Boolean register(User user){
    		
    		        Connection connection = JDBCUtil.getConnection();
    		        try {
    		            Statement statement = connection.createStatement();
    		            String sql = "insert into user(username,password)values('"+user.getUsername()+"','"+user.getPassword()+"')";
    		            System.out.println("sql = " + sql);
    		            int i = statement.executeUpdate(sql);
    		            return  i>0;
    		        } catch (SQLException throwables) {
    		            throwables.printStackTrace();
    		        }
    		        return  false;
    		    }
  

3.0 解决sql注入

• 创建PreparedStatement 对象发送sql语句解决sql注入问题
  
  	@Test
  	public void test(){
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        // 使用 ？ 进行参数的占位
        String sql = "select *  from user  where username = ? and password = ? " ;
        //创建PreparedStatement 对象时 需要传递sql语句 因为该对象会将sql语句进行预编译
        try {
             preparedStatement = connection.prepareStatement(sql);
            // 在preparedStatement 执行sql语句之前 需要将sql语句中的参数设置好
            preparedStatement.setString(1, "zhangsan");
            preparedStatement.setString(2, "8888");
  
            //通过 preparedStatement对象 执行sql语句
            resultSet = preparedStatement.executeQuery();
            User user = null;
            while(resultSet.next()){
  
                String username = resultSet.getString(2);
                String password = resultSet.getString(3);
                user = new User().setPassword(password).setUsername(username);
            }
            System.out.println("user = " + user);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.closeAll(connection,preparedStatement,resultSet);
        }
    }