java基础--JDBC

绝地灬酷狼 2023-09-26 19:07 63阅读 0赞

### JDBC程序编写步骤: ###

1.注册驱动,加载driver类
    2.获取连接得到connection
    3.执行增删改查,发送sql给mysql执行(Preparestatemennt)
    4.释放资源关闭连接
    public class Jdbc01 {
        
        public static void main(String[] args) throws SQLException {
        
            //在项目下创建一个文件夹比如libs
            //将mysql.jar拷贝到该目录下,点击add to project
            //1.注册驱动
            Driver driver = new Driver();
            //2.得到连接
            String url = "jdbc:mysql://localhost:3306/db03?characterEncoding=utf8";
            //将用户名和密码放入到Properties 对象
            Properties properties = new Properties();
            properties.setProperty("user","root");//用户
            properties.setProperty("password","123456");//密码
            Connection connect = driver.connect(url, properties);
            //3.执行sql
            String sql = "insert into actor values(null,'刘德华','男','1970-11-11','110')";
            //String sql = "update actor set name='周星驰'where id = 1";
            //用于执行静态SQL语句并返回其生成的结果的对象
            Statement statement = connect.createStatement();
            int row = statement.executeUpdate(sql);//如果是dml语句,返回的是影响的行数
            System.out.println(row>0? "成功":"失败");
            //4.关闭连接
            statement.close();
            connect.close();
    
        }
    }

### 连接数据库五种方式 ###

1.上述方式  
2.使用反射连接

//使用反射加载Driver类
            Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
           Driver driver = (Driver)aClass.newInstance();
           //得到连接
            String url = "jdbc:mysql://localhost:3306/db03?characterEncoding=utf8";
            //将用户名和密码放入到Properties 对象
            Properties properties = new Properties();
            properties.setProperty("user","root");//用户
            properties.setProperty("password","123456");//密码
            Connection connect = driver.connect(url, properties);
            System.out.println("方式二"+connect);

3.使用driverManager替代driver进行统一管理

//使用反射类加载Driver
            Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
            Driver driver  = (Driver)aClass.newInstance();
            //创建url,user,passward
            String url = "jdbc:mysql://localhost:3306/db03?characterEncoding=utf8";
            String user = "root";
            String password = "123456";
            DriverManager.registerDriver(driver);//注册driver驱动
            Connection connection = DriverManager.getConnection(url, user, password);
            System.out.println("第三种方式"+connection);

4.使用class.forName自动完成注册驱动,简化代码

public void connect04() throws ClassNotFoundException, SQLException {
        
            Class.forName("com.mysql.jdbc.Driver");
           //创建url,user,passward
           String url = "jdbc:mysql://localhost:3306/db03?characterEncoding=utf8";
           String user = "root";
           String password = "123456";
           Connection connection = DriverManager.getConnection(url, user, password);
           System.out.println("第四种范式"+connection);
    
    
       }

5.使用配置文件

public void connect05() throws IOException, SQLException, ClassNotFoundException {
        
            //通过properties对象获取配置文件的信息
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\mysql.properties"));
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String driver = properties.getProperty("driver");
            String url = properties.getProperty("url");
            Class.forName(driver);//反射
            Connection connection = DriverManager.getConnection(url, user, password);
            System.out.println("第五种方式"+connection);
    
        }

二.\#\# ResultSet\[结果集\],表示数据库结果集的数据表,用于查询数据库的语句生成,resultSet对象保持一个光标指向当前的数据行,next方法将光标移向下一行

//用于执行静态SQL语句并返回其生成的结果的对象
            Statement statement = connection.createStatement();
            //组织sql
            String sql = "select id,name,sex,borndate from actor";
            //执行给定的sql语句,该语句返回单个的ResultSet对象
            ResultSet resultSet = statement.executeQuery(sql);
            //5.使用while取出数据
            while (resultSet.next()) {
        
                int id = resultSet.getInt(1);//获取该行的第一列
                String name = resultSet.getString(2);
                String sex = resultSet.getString(3);
                Date date = resultSet.getDate(4);
                System.out.println(id + "\t" + name + "\t" + sex + "\t" + date);
            }
    
            //6.关闭连接
            resultSet.close();
            statement.close();
            connection.close();

三.\#\# SQL注入:  
statement存在sql注入问题,sql注入是指在用户输入数据中注入非法的sql语句段或者命令,用PreparStatement解决即可

Scanner scanner = new Scanner(System.in);
            System.out.println("请输入名字和性别");
    
            //让用户输入管理员和密码
            String admin_name =scanner.nextLine();//看到注入效果用nextLine
            String admin_sex =scanner.nextLine();
            //通过properties对象获取配置文件的信息
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\mysql.properties"));
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String driver = properties.getProperty("driver");
            String url = properties.getProperty("url");
            Class.forName(driver);//反射,注册驱动
            Connection connection = DriverManager.getConnection(url, user, password);
            //得到prepareStatement
            //组织sql,Sql语句的?就相当于占位符
            String sql = "select name,sex from actor where name = ? and sex= ?";
           //prepareStatement对象实现了prepareStatement接口的实现类的对象
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //给?赋值
            preparedStatement.setString(1,admin_name);
            preparedStatement.setString(2,admin_sex);
    
    //执行用executeQuery,不要在里写sql
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
        
                System.out.println("登陆成功");
    
            }else{
        
                System.out.println("登录失败");
            }
    //关闭连接
            resultSet.close();
            preparedStatement.close();
            connection.close();

四:\#\# 事务  
![在这里插入图片描述][471576dd6b4643a0a23ebe94330b7f34.png]

//操作转帐的业务
    //1.得到连接
            Connection connection = null;
            //2.组织一个sql
            String sql = "update account set balance= balance-100 where id = 1";
            String sql2 = "update account set balance= balance+100 where id = 2";
            PreparedStatement preparedStatement = null;
            //创建PrepareStatement对象
            try {
        
                //建立连接
                connection = JDBCUtils.getConnection();
                //将connection设置为不自动提交
                connection.setAutoCommit(false);
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.executeUpdate();//执行
                preparedStatement = connection.prepareStatement(sql2);
                preparedStatement.executeUpdate();//执行第二条
    
                //这里提交事务
                connection.commit();
    
            } catch (SQLException e) {
        
                System.out.println("执行发生了异常,撤销执行的sql");
                connection.rollback();//默认回滚到事务开始
                e.printStackTrace();
            }finally{
        
                JDBCUtils.close(null,preparedStatement,connection);
            }

事务实例就是银行转账。  
JDBC的批处理语句:prepareStatement.addBatch(),放在写出的语句后面,相当于加入语句  
addBatch:添加需要批量处理的SQl语句和参数,executeBatch:用于执行批量处理语句,clearBatch:清空批处理包的语句  
批量处理语句有效果需要在连接数据库的语句后面加上rewriteBatchedStatement=true

### 连接池 ###

传统的连接不能连接太多,否则会崩溃  
![在这里插入图片描述][f2541afc16f94e39b93b869607771d27.png]  
C3P0:

//1.创建一个数据源对象
           ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
           //2.通过配置文件获取相关的连接信息
           Properties properties = new Properties();
           properties.load(new FileInputStream("src\\mysql.properties"));
           String user =properties.getProperty("user");
           String password =properties.getProperty("password");
           String url =properties.getProperty("url");
           String driver =properties.getProperty("driver");
           //给数据源comboPooledDataSource设置相关的参数
           //注意连接管理由comboPooledDataSource来管理
           comboPooledDataSource.setDriverClass(driver);
           comboPooledDataSource.setJdbcUrl(url);
           comboPooledDataSource.setUser(user);
           comboPooledDataSource.setPassword(password);
           //设置初始化连接数
           comboPooledDataSource.setInitialPoolSize(10);
           //设置最大连接数
           comboPooledDataSource.setMaxPoolSize(50);
            for (int i = 0; i <5000 ; i++) {
        
                Connection connection = comboPooledDataSource.getConnection();//拿到连接
                connection.close();
            }
           Connection connection = comboPooledDataSource.getConnection();//拿到连接
           //System.out.println("连接成功");
    
       }
       //第二种方式,使用配置文件模板来完成
        //将c3p0提供的c3p0.config.xml拷贝到src
        public void testC3P0_02() throws SQLException {
        
            ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hsp_edu");
            Connection connection = comboPooledDataSource.getConnection();
            connection.close();
        }

德鲁伊连接:

public void testDruid() throws Exception {
        
            //1.加入Druid jar包
            //2.加入配置文件,将该文件拷贝到src目录
            //创建properties对象读取配置文件
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\druid.properties"));
    
            //4.创建一个指定参数的数据库连接池,德鲁伊连接池
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            for (int i = 0; i <5000 ; i++) {
        
                Connection connection = dataSource.getConnection();
                //System.out.println("连接成功");
                connection.close();
            }
    
    对其进行创造成工具:
    
    ```java
    private static DataSource ds;
        static {
        
            Properties properties = new Properties();
            try {
        
                properties.load(new FileInputStream("src\\druid.properties"));
                //创建连接池
                ds = DruidDataSourceFactory.createDataSource(properties);
            } catch (Exception e) {
        
                e.printStackTrace();
            }
        }
        //编写getConnection方法
        public static Connection getConnection() throws SQLException {
        
            return ds.getConnection();
        }
        public static void close(ResultSet resultset, Statement statement,Connection connection ) throws Exception{
        
            if(resultset!=null){
        
                resultset.close();
            }
            if(statement!=null){
        
                statement.close();
            }
            if(connection!=null){
        
                connection.close();
            }
        }

## ApDBUtils:将返回的结果集进行遍历,封装到一个集合中,传统的关闭了连接resulset结果集就无法使用了
    ![在这里插入图片描述](https://img-blog.csdnimg.cn/3fce5cf665f74ecd9095e46c43ded7f1.png)
    
    ```java
    public class DBUtils_Use {
        //使用apache-DBUtils工具类
        public void testQueryMany() throws SQLException {
            //1.得到连接(druid)
            Connection connection = JDBCUtilsByDruid.getConnection();
            //2.使用DBUtils类和接口,引入DBUTils相关的jar
            //3.创建QueryRunner
            QueryRunner queryRunner = new QueryRunner();
            //4.执行相关的方法,返回ArrayList集合
            //query方法就是执行sql语句,得到ResultSet,封装到ArrayList集合中
            //new BeanListHandler<>(Actor.class)将Resultset->Actor对象->封装到Arraylist集合
            //1就是给sql语句中的?进行赋值,可以有多个值
            //如果返回的是单行记录,使用的是Beanhandler,返回的是对应的实体类
            //返回单行单列用Scalarhande,返回的是Object
            //底层得到的resultset,会在query关闭,关闭preparStatement
            String sql ="select * from actor where id >=?";
           List<Actor> list =  queryRunner.query(connection,sql,new BeanListHandler<>(Actor.class),1);
        for(Actor actor:list){
            System.out.println(actor);
        }
        //释放资源
            JDBCUtilsByDruid.close(null,null,connection);
    
        }
    }
    
    
    执行修改语句update和插入insert和删除delete的代码:
    		String sql = "update actor set name =?where id=? ";
            //执行dml操作的是queryRunner.update,返回的是受影响的行数
            int affectedRow = queryRunner.update(connection,sql,"张三丰",4);

### dao和增删改查通用方法-basicDao ###

方便快速查询

public class BasicDAO<T> {
        
       private QueryRunner qr = new QueryRunner();
       //开发通用的dml方法,针对任意的表
        public int update(String sql,Object... parameters) throws Exception {
        
            Connection connection =null;
            try {
        
                connection = JDBCUtilsByDruid.getConnection();
               int update = qr.update(connection,sql,parameters);
               return update;
            } catch (SQLException e) {
        
                throw new RuntimeException(e);//将一个编译异常转换为运行异常
            } finally {
        
                //关闭连接诶
               JDBCUtilsByDruid.close(null,null,connection);
            }
        }
    //返回多个对象,及查询的结果是多行
        //Class<T> clazz传入的是一个类的class对象,比如Actor.class
        public List<T> queryMulti(String sql,Class<T> clazz,Object...parameters) throws Exception {
        
            Connection connection =null;
            try {
        
                connection = JDBCUtilsByDruid.getConnection();
               return qr.query(connection,sql,new BeanListHandler<T>(clazz),parameters);
            } catch (SQLException e) {
        
                throw new RuntimeException(e);//将一个编译异常转换为运行异常
            } finally {
        
                //关闭连接诶
                JDBCUtilsByDruid.close(null,null,connection);
            }
    
        }
        //查询单行的结果的通用方法
        public T querySingle(String sql,Class<T> clazz,Object...parameters) throws Exception {
        
            Connection connection =null;
            try {
        
                connection = JDBCUtilsByDruid.getConnection();
                return qr.query(connection,sql,new BeanHandler<T>(clazz),parameters);
            } catch (SQLException e) {
        
                throw new RuntimeException(e);//将一个编译异常转换为运行异常
            } finally {
        
                //关闭连接诶
                JDBCUtilsByDruid.close(null,null,connection);
            }
        }
        //查询单行单列的方法
        public Object queryScalar(String sql,Object...parameters) throws Exception {
        
            Connection connection =null;
            try {
        
                connection = JDBCUtilsByDruid.getConnection();
                return qr.query(connection,sql,new ScalarHandler(),parameters);
            } catch (SQLException e) {
        
                throw new RuntimeException(e);//将一个编译异常转换为运行异常
            } finally {
        
                //关闭连接诶
                JDBCUtilsByDruid.close(null,null,connection);
            }
        }
    }

运用工具:

public class TestDAO {
        
        //测试ActorDAO对actor表crud的操作
        @Test
       public void testActorDAO() throws Exception {
        
           ActorDAO actorDAO = new ActorDAO();
           //1.查询
           List<Actor> actors = actorDAO.queryMulti("select *from actor where id >= ?", Actor.class, 1);
           System.out.println("查询结果");
           for(Actor actor:actors){
        
               System.out.println(actor);
           }
            //查询单行记录
            Actor actor = actorDAO.querySingle("select *from actor where id =?", Actor.class, 4);
            System.out.println("查询的单行结果:"+actor);
            //查询单行单列值
            Object o = actorDAO.queryScalar("select name from actor where id = ?",6);
            System.out.println(o);
        }
    
    
    
    }

[471576dd6b4643a0a23ebe94330b7f34.png]: https://img-blog.csdnimg.cn/471576dd6b4643a0a23ebe94330b7f34.png
[f2541afc16f94e39b93b869607771d27.png]: https://img-blog.csdnimg.cn/f2541afc16f94e39b93b869607771d27.png