Java基础--JDBC

最新推荐文章于 2024-08-31 10:53:27 发布

打篮球的金鱼

最新推荐文章于 2024-08-31 10:53:27 发布

阅读量64

点赞数

分类专栏： JAVA基础文章标签： java 数据库 mysql

本文链接：https://blog.csdn.net/fengthon/article/details/128862194

版权

JAVA基础专栏收录该内容

13 篇文章 0 订阅

订阅专栏

文章目录

JDBC入门
JDBC工具类
文件附录
- propertise

JDBC入门

第一个JDBC程序

分为四个步骤

1.注册驱动
2.得到连接
3.通过statement执行sql
4.关闭连接资源

jdbc01.java

public class jdbc01 {
    public static void main(String[] args) throws SQLException {
        //1.注册驱动
        Driver diver = new Driver();

        //2.得到连接
        String url="jdbc:mysql://localhost:3306/fwj_db01";
        //将用户名和密码封装到properties中
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","123456");
        //连接对象
        Connection connect = diver.connect(url, properties);

        //3.通过statement执行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 rows = statement.executeUpdate(sql);
        System.out.println(rows>0?"成功":"失败");

        //4.关闭连接资源
        statement.close();
        connect.close();
    }
}

JDBC连接数据库的方式

public class WayToConnectMySQL {
    //方法1
    @Test
    public void connect01() throws SQLException {
        //1.注册驱动
        Driver driver = new Driver();

        //2.得到连接
        String url="jdbc:mysql://localhost:3306/fwj_db01";
        //将用户名和密码封装到properties中
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","123456");
        //连接对象
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
    }
    //方法2
    @Test
    public void connect02() throws Exception {
        //通过反射,动态加载，更加灵活
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver  =(Driver) aClass.newInstance();

        String url="jdbc:mysql://localhost:3306/fwj_db01";
        //将用户名和密码封装到properties中
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","123456");
        //连接对象
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
    }
    //方法3
    @Test
    public void connect03()throws Exception{
        //使用DriverManager代替Driver
        //通过反射,动态加载，更加灵活
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver  =(Driver) aClass.newInstance();

        //创建路径，用户名，密码
        String url="jdbc:mysql://localhost:3306/fwj_db01";
        String user="root";
        String password="123456";
        DriverManager.registerDriver(driver);//注册驱动

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }
    //方法4
    @Test
    public void connect04()throws  Exception{
        //自动完成驱动注册
        Class.forName("com.mysql.jdbc.Driver");
        //创建路径，用户名，密码
        String url="jdbc:mysql://localhost:3306/fwj_db01";
        String user="root";
        String password="123456";
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }
    //方法5
    @Test
    public void connect05()throws Exception{
        //读取配置文件
        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");

        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);

    }

练习：创建news表，添加5条数据，修改id=1的记录，删除id=3的记录

    @Test
    public void ex() throws Exception {
        //读取配置文件
        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");

        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);

        String sql="create table news( id int,name varchar(32));";
        String sql_insert="insert into news values (1,'tom'),(2,'jack'),(3,'marry'),(4,'james'),(5,'bob')";
        String sql_up="update  news set name='curry' where id=3";
        String sql_del="delete from news where id=3";
        Statement statement = connection.createStatement();
        int res = statement.executeUpdate(sql_del);
        System.out.println(res>0?"成功":"失败");
        //关闭资源
        statement.close();
        connection.close();
    }
}

ResultSet 结果集

public class ResultSet {
    @Test
    public void GetResultSet()throws  Exception{
        //读取配置文件
        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");

        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();

        String sql="select id,name,sex,borndate from actor";
        //得到结果集
        java.sql.ResultSet resultSet = statement.executeQuery(sql);
        //循环取出结果
        while(resultSet.next()){
            int id = resultSet.getInt(1); //该行第一列
            // int id = resultSet.getInt("id");通过label也可以
            String name = resultSet.getString(2);
            String sex = resultSet.getString(3);
            Date date = resultSet.getDate(4);
            System.out.println(id+' '+name+' '+sex+' '+date);
        }
        //关闭
        statement.close();
        connection.close();
        resultSet.close();
    }
}

JDBC API

在这里插入图片描述

PreparedStatement

在这里插入图片描述
增删改操作

public class Use_PreparedStatementDML {
    public static void main(String[] args) throws  Exception{
        Scanner sc=new Scanner(System.in);
        System.out.print("请输入管理员姓名：");
        String admin_name=sc.nextLine();
        System.out.print("请输入新密码：");
        String admin_pwd=sc.nextLine();
        //读取配置文件
        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");

        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);

        //插入用户
        String sql_insert="insert into admin values(?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql_insert);
        preparedStatement.setString(1,admin_name);
        preparedStatement.setString(2,admin_pwd);


        /*修改密码
        String sql_update="update admin set pwd=? where name=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql_update);
        preparedStatement.setString(1,admin_pwd);
        preparedStatement.setString(2,admin_name);
        */

        /*删除用户
        String sql_update="delete from admin where name=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql_update);
        preparedStatement.setString(1,admin_name);
        */

        /*
            查询使用executeQuery
            增删改使用executeUpdate
         */
        int rows = preparedStatement.executeUpdate();
        System.out.println(rows>0?"成功":"失败");
        //关闭
        preparedStatement.close();
        connection.close();
    }
}

查询操作

public class Use_PreparedStatementSelect {
    public static void main(String[] args)throws  Exception {
        Scanner sc=new Scanner(System.in);
        System.out.println("请输入管理员姓名：");
        String admin_name=sc.nextLine();
        System.out.println("请输入密码：");
        String admin_pwd=sc.nextLine();
        //读取配置文件
        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");

        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);

        String sql="select name,pwd from admin where name=? and pwd=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,admin_name);
        preparedStatement.setString(2,admin_pwd);
        /*
            查询使用executeQuery
            增删改使用executeUpdate
         */
        ResultSet resultSet = preparedStatement.executeQuery();

        if(resultSet.next()){
            System.out.println("登录成功");
        }else {
            System.out.println("登录失败");
        }
        //关闭
        preparedStatement.close();
        connection.close();
        resultSet.close();
    }

Transaction 事务

让A的账户转账100给B，防止自动提交和意外阻断，启动事务

public class UseTransaction {
    public static void main(String[] args) {
        //连接
        Connection connection=null;
        //sql语句
        String sql_sub="update account set balance=balance-100 where id=?";
        String sql_add="update account set balance=balance+100 where id=?";
        //创建statement
        PreparedStatement preparedStatement=null;
        try {
            connection= JDBCUtils.getConnection();
            //开启事务，防止自动提交
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql_sub);
            preparedStatement.setInt(1,1);
            preparedStatement.executeUpdate();

            //异常阻断测试
            //int i=1/0;

            preparedStatement=connection.prepareStatement(sql_add);
            preparedStatement.setInt(1,2);
            preparedStatement.executeUpdate();

            //提交事务
            connection.commit();
        } catch (SQLException e) {
            System.out.println("异常中断");
            //设置回滚,默认回滚到原点
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
}

批处理

在这里插入图片描述

/**
*   批处理插入5000条数据的用时对比
*/
public class TestBatchTime {
    @Test
    public void Batch() throws Exception {
        Connection  connection= JDBCUtils.getConnection();
        String sql_insert="insert into admin2 values(null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql_insert);
        System.out.println("开始执行");
        long start=System.currentTimeMillis();
        for (int i = 0; i <5000 ; i++) {
            preparedStatement.setString(1,"user"+i);
            preparedStatement.setString(2,"123");
            //打包
            preparedStatement.addBatch();
            //打包1000条
            if((i+1)/1000==0){
                preparedStatement.executeBatch();
                //执行完后清空处理下一批
                preparedStatement.clearBatch();
            }
        }
        long end=System.currentTimeMillis();
        System.out.println("执行耗时: "+(end-start));

        JDBCUtils.close(null,preparedStatement,connection);
    }
    
    @Test
    public void NoBatch() throws Exception {
        Connection  connection= JDBCUtils.getConnection();
        String sql_insert="insert into admin2 values(null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql_insert);
        System.out.println("开始执行");
        long start=System.currentTimeMillis();
        for (int i = 0; i <5000 ; i++) {
            preparedStatement.setString(1,"user"+i);
            preparedStatement.setString(2,"123");
            preparedStatement.executeUpdate();
        }
        long end=System.currentTimeMillis();
        System.out.println("执行耗时: "+(end-start));

        JDBCUtils.close(null,preparedStatement,connection);
    }
}

JDBC工具类

JDBCUtils

public class JDBCUtils {
    //定义属性
    private static String user; //用户名
    private static String password; //密码
    private static String url; //url
    private static String driver; //驱动

    //在静态代码块中初始化
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src/mysql.properties"));
            //读取属性值
            user=properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver=properties.getProperty("driver");

        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    //连接数据库，返回connection
    public static Connection getConnection(){
        try {
            //注册驱动，否则可能会找不到驱动
            Class.forName(driver);
            return DriverManager.getConnection(url,user,password);
        } catch (SQLException | ClassNotFoundException e) {
            //将编译异常转化为运行异常，调用者可以选择捕获该异常
            throw new RuntimeException(e);
        }
    }

    //关闭资源
    public static void close(ResultSet set, Statement statement,Connection connection){
        try {
            if(set!=null){
                set.close();
            }
            if(statement!=null){
                statement.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

增删查改操作：

public class JDBCDMLS {
    public static void main(String[] args) {
        TestDML();
        TestSelect();
    }
	
	//查询操作
    public static void TestSelect(){
        //连接
        Connection connection=null;
        //sql语句
        String sql="select * from actor ";
        //创建statement
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            connection=JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            /*
            查询特定的
            String sql="select * from actor where id=?";
            preparedStatement.setInt(1,2); //查询id为2的人的信息
             */
            //结果集
            resultSet= (ResultSet) preparedStatement.executeQuery();
            //返回结果
            while(resultSet.next()){
                int id=resultSet.getInt("id");
                String name=resultSet.getString("name");
                String sex=resultSet.getString("sex");
                Date borndate=resultSet.getDate("borndate");
                String phone=resultSet.getString("phone");
                System.out.println(id+'\t'+name+'\t'+sex+'\t'+
                        borndate+'\t'+phone);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
    }

	//增删改操作
    public static void TestDML(){
        //连接
        Connection connection=null;
        //sql语句
        String sql="update actor set name=? where id=?";
        //创建statement
        PreparedStatement preparedStatement=null;
        try {
            connection=JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //修改
            preparedStatement.setString(1,"吴彦祖");
            preparedStatement.setInt(2,1);
            //执行语句
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.close(null,preparedStatement,connection);
        }
    }
}

JDBC连接池

在这里插入图片描述
Druid_pool

public class Druid_pool {
    @Test
    public  void testDruid() throws Exception {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));
        //创建数据源
        DataSource dataSource=
                DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        System.out.println("连接成功~");
        connection.close();
    }
}

C3P0_pool

public class C3P0_Pool {

    //1.通过user,pwd,url建立连接
    @Test
    public void TestC3P0_01()throws Exception{
        //得到数据源对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        //获取配置文件
        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.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);
        //初始化连接数
        comboPooledDataSource.setInitialPoolSize(10);
        //设置最大连接数
        comboPooledDataSource.setMaxPoolSize(50);
        //连接
        Connection connection = comboPooledDataSource.getConnection();
        System.out.println("连接成功~");
        connection.close();
    }

    @Test
    //2.使用配置文件连接
    public void testC3P0_02() throws Exception {
        ComboPooledDataSource comboPooledDateSource = new ComboPooledDataSource("fwj_db");
        Connection connection = comboPooledDateSource.getConnection();
        System.out.println("连接成功~");
    }
}

Druid实现的工具类

public class JDBCUtilsByDruid {
    private static DataSource ds;
    //初始化
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\druid.properties"));
            ds= DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    //连接方法
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    //关闭连接，将Connection对象放回连接池
    public static void close(ResultSet set, Statement statement, Connection connection){
        try {
            if(set!=null){
                set.close();
            }
            if(statement!=null){
                statement.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

Druid查询使用

/**
*   Druid查询应用实例
*/
public class JDBCUtilsByDruid_Use {
    @Test
    public  void TestSelect(){
        //连接
        Connection connection=null;
        //sql语句
        String sql="select * from actor ";
        //创建statement
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            connection=JDBCUtilsByDruid.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            /*
            查询特定的
            String sql="select * from actor where id=?";
            preparedStatement.setInt(1,2); //查询id为2的人的信息
             */
            //结果集
            resultSet= preparedStatement.executeQuery();
            //返回结果
            while(resultSet.next()){
                int id=resultSet.getInt("id");
                String name=resultSet.getString("name");
                String sex=resultSet.getString("sex");
                Date borndate=resultSet.getDate("borndate");
                String phone=resultSet.getString("phone");
                System.out.println(id+'\t'+name+'\t'+sex+'\t'+
                        borndate+'\t'+phone);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);
        }
    }

    //结果集封装到集合中
    @Test
    public  void ToArrayslist(){
        //连接
        Connection connection=null;
        //sql语句
        String sql="select * from actor ";
        //创建statement
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            connection=JDBCUtilsByDruid.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            /*
            查询特定的
            String sql="select * from actor where id=?";
            preparedStatement.setInt(1,2); //查询id为2的人的信息
             */
            //结果集
            resultSet= preparedStatement.executeQuery();
            ArrayList<Actor>list=new ArrayList<>();
            //返回结果
            while(resultSet.next()){
                int id=resultSet.getInt("id");
                String name=resultSet.getString("name");
                String sex=resultSet.getString("sex");
                Date borndate=resultSet.getDate("borndate");
                String phone=resultSet.getString("phone");
                list.add(new Actor(id,name,sex,borndate,phone));
            }
            System.out.println(list);
            for(Actor actor:list){
                System.out.println(actor.getName());
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);
        }
    }

}

Apache-DBUtils

在这里插入图片描述

Actor类

public class Actor {
    private Integer id;
    private String name;
    private String sex;
    private Date borndate;
    private String phone;
    public Actor(){} //用于反射

    public Actor(Integer id, String name, String sex, Date borndate, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.borndate = borndate;
        this.phone = phone;
    }
}

/**
*   Apache-DBUtils对表actor的crud操作
*/
public class Apache_DBUtils {

    //返回单条结果
    @Test
    public void textQuerySingal()throws Exception{
        //连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //创建query
        QueryRunner queryRunner = new QueryRunner();
        String sql="select *from actor where id=?";
        /*
            connection:连接对象
            sql:执行语句
            BeanHandler<>(Actor.class):通过反射获取类的信息
            1:给问好赋值
         */
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 1);
        //遍历输出结果
        System.out.println(actor);
        //关闭资源
        JDBCUtilsByDruid.close(null,null,connection);
    }

    //返回多行结果
    @Test
    public void textQueryMany()throws Exception{
        //连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //创建query
        QueryRunner queryRunner = new QueryRunner();
        String sql="select *from actor where id>=?";
        /*
            得到结果集合list
            connection:连接对象
            sql:执行语句
            BeanListHandler<>(Actor.class):通过反射获取类的信息
            1:给问好赋值
         */
        List<Actor> list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
        //遍历输出结果
        for(Actor a:list){
            System.out.println(a);
        }
        //关闭资源
        JDBCUtilsByDruid.close(null,null,connection);
    }

    //单行单列查询
    @Test
    public void testScalar() throws SQLException {
        //连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //创建query
        QueryRunner queryRunner = new QueryRunner();
        String sql="select name from actor where id=?";
        Object query = queryRunner.query(connection, sql, new ScalarHandler<>(), 1);
        System.out.println(query);
        //关闭资源
        JDBCUtilsByDruid.close(null,null,connection);
    }

    //DML语句
    @Test
    public void testDML() throws SQLException {
        //连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //创建query
        QueryRunner queryRunner = new QueryRunner();
        //修改
        String sql1="update actor set name=? where id=?";
        int affectedRows1 = queryRunner.update(connection, sql1, "张三丰",2 );
        System.out.println(affectedRows1>0?"成功":"失败");
        //插入
        String sql2="insert into actor values(null,?,?,?,?)";
        int affectedRows2 = queryRunner.update(connection, sql2, "林青霞", "女","1999-2-2","116");
        System.out.println(affectedRows2>0?"成功":"失败");
        //删除
        String sql3="delete from actor where id=?";
        int affectedRows3 = queryRunner.update(connection, sql3, 2);
        System.out.println(affectedRows3>0?"成功":"失败");

        //关闭资源
        JDBCUtilsByDruid.close(null,null,connection);
    }
}

DAO

在这里插入图片描述

通过DAO完成对表actor的crud操作

BasicDAO

/**
*   BasicDAO的使用
*/
@SuppressWarnings({"all"})
public class BasicDAO<T> { //指定具体类型

    private QueryRunner qr=new QueryRunner();

    public int update(String sql,Object...parameters){
        /**
         * @Description 通用的dml方法，针对任意的表
         * @param sql sql语句
         * @param parameters 参数
         * @return: int
         */
        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);
        }
    }

    public List<T> queryMulti(String sql, Class<T>clazz,Object...parameters){
        /**
         * @Description 返回多个对象（查询多条结果)
         * @param sql 语句
         * @param parameters  参数
         * @param clazz 类的Class对象
         * @return: java.util.List<T>
         */
        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){
        /**
         * @Description 返回单行查询结果
         * @param sql
         * @param clazz
         * @param parameters
         * @return: T
         */
        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){
        /**
         * @Description 返回单行单列
         * @param sql
         * @param parameters
         * @return: java.lang.Object
         */
        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);
        }
    }
}

ActorDAO

public class ActorDAO extends BasicDAO<Actor>{
    //继承方法
    //编写特殊的方法
}

domain

public class Actor {
    private Integer id;
    private String name;
    private String sex;
    private Date borndate;
    private String phone;
    public Actor(){} //用于反射

    public Actor(Integer id, String name, String sex, Date borndate, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.borndate = borndate;
        this.phone = phone;
    }
 }

testActorDAO

/**
*   通过DAO对actor表的crud操作
*/
public class testActorDAO {
    @Test
    public void test(){
        /**
         * @Description crud操作
         * @param null
         * @return: void
         */
        ActorDAO actorDAO = new ActorDAO();

        //多行查询
        List<Actor> actors = actorDAO.queryMulti("select *from actor where id>=?", Actor.class, 1);
        for(Actor actor:actors){
            System.out.println(actor);
        }
        //单行查询
        Actor actor = actorDAO.querySingle("select *from actor where id=?", Actor.class, 1);
        System.out.println(actor);

        //单行单列
        Object o = actorDAO.queryScalar("select name from actor where id=?",  1);
        System.out.println(o);

        //dml操作
        int update = actorDAO.update("insert into actor values(null,?,?,?,?)", "张无忌", "男", "2003-1-1", 119);
        System.out.println((update>0?"成功":"失败"));

    }
}

JDBCUtilsByDruid

public class JDBCUtilsByDruid {
    private static DataSource ds;
    //初始化
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\druid.properties"));
            ds= DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    //连接方法
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    //关闭连接，将Connection对象放回连接池
    public static void close(ResultSet set, Statement statement, Connection connection){
        try {
            if(set!=null){
                set.close();
            }
            if(statement!=null){
                statement.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

文件附录

propertise

mysql.properties

user=root
password=123456
url=jdbc:mysql://localhost:3306/fwj_db01?  rewriteBatchedStatements=true
driver=com.mysql.jdbc.Driver

druid.properties

# druid.properties?????
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/fwj_db01
username=root
password=123456
# ???????
initialSize=10
# ?????
maxActive=50
# ??????
maxWait=5000