java基础--JDBC

于 2023-03-23 18:01:40 发布
阅读量211 收藏
点赞数
文章标签： java mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_52830421/article/details/129682548
版权

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();

四:## 事务
在这里插入图片描述

 //操作转帐的业务
//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

连接池

传统的连接不能连接太多,否则会崩溃
在这里插入图片描述
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);
    }



}
夜夜夜ye
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值