Java基础入门day46

day46

druid连接池

概念

在程序初始化时，预先创建指定数量的数据库连接对象存储在“池”中，当需要连接数据库时，从连接池中取出现有连接对象，使用完毕后，也不会进行关闭，而是放回池中，实现复用，节省资源

使用步骤

创建druid的配置文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/saas
user=root
pass=Abc@1234

引入druid的第三方jar包文件

连接池工具类

package com.saas.util;
​
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
​
import javax.sql.DataSource;
import java.util.Properties;
​
public class DruidUtil {
​
 private static DruidDataSource dataSource = null;
​
 private static Env p = Env.getInstance();
​
 static {
     try {
         dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(p);
​
         dataSource.setUrl(p.getProperty("url"));
         dataSource.setUsername(p.getProperty("user"));
         dataSource.setPassword(p.getProperty("pass"));
         dataSource.setDriverClassName(p.getProperty("driver"));
​
     } catch (Exception e) {
         throw new RuntimeException(e);
     }
 }
​
 /**
     * 获取数据源对象
     * @return 数据库连接的数据源对象
     */
    public static DataSource getDataSource(){
        return dataSource;
    }
}

apache的DBUtil使用

概念

DbUtils是Java编程中操作实用小工具，小巧，简单，使用

• 对于数据表的DQL查询操作，可以把结果转换为List，Array，Set等集合便于操作

• 对于数据表的DML增删改操作，也变得很简单（只需要写SQL语句即可）

使用

package com.saas.util;
​
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
​
import javax.sql.DataSource;
import java.util.Properties;
​
public class DruidUtil {
​
 private static DruidDataSource dataSource = null;
​
 private static Env p = Env.getInstance();
​
 static {
     try {
         dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(p);
​
         dataSource.setUrl(p.getProperty("url"));
         dataSource.setUsername(p.getProperty("user"));
         dataSource.setPassword(p.getProperty("pass"));
         dataSource.setDriverClassName(p.getProperty("driver"));
​
     } catch (Exception e) {
         throw new RuntimeException(e);
     }
 }
​
 /**
     * 获取数据源对象
     * @return 数据库连接的数据源对象
     */
    public static DataSource getDataSource(){
        return dataSource;
    }
}

这个工具类当中只需要一个连接池对象的获取即可

DbUtils工具类中需要这个连接池对象

工具类的使用

package com.saas.dao.impl;
​
import com.saas.dao.IStudentDao;
import com.saas.entity.Student;
import com.saas.util.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
​
import java.sql.SQLException;
import java.util.List;
​
public class StudentDaoImpl implements IStudentDao{
​
    private QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
​
    @Override
    public List<Student> getAllStudents() {
        try {
            return qr.query("select * from student", new BeanListHandler<Student>(Student.class));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
​
    @Override
    public List<Student> getStudentsByPage(int cp, int ps) {
        int si = (cp - 1) * ps;
​
        try {
            return qr.query("select * from student limit ?, ?", new BeanListHandler<Student>(Student.class), si, ps);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
​
    @Override
    public Student getStudentBySid(int sid) {
        try {
            return qr.query("select * from student where sid = ?", new BeanHandler<Student>(Student.class), sid);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
​
    @Override
    public int saveStudent(Student stu) {
        return 0;
    }
​
    @Override
    public int deleteStudent(int sid) {
        return 0;
    }
​
    @Override
    public int updateStudent(Student stu) {
        try {
            return qr.update("update student set name = ?, sex = ?, score = ?, cid = ?, age = ?  where sid = ?",
                    stu.getName(), stu.getSex(), stu.getScore(), stu.getCid(), stu.getAge(), stu.getSid());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

在dao的实现类中只需要一个QueryRunner对象，里面需要连接池对象

使用QueryRunner对接即可完成所有的crud功能

package com.saas.test;
​
import com.saas.dao.IStudentDao;
import com.saas.dao.impl.StudentDaoImpl;
import com.saas.entity.Student;
​
import java.util.List;
​
public class TestStudentDao {
​
    public static void main(String[] args) {
​
        IStudentDao isd = new StudentDaoImpl();
​
        List<Student> list = isd.getAllStudents();
​
        for (Student s : list) {
            System.out.println(s);
        }
​
        System.out.println("--------------");
​
        List<Student> studentList = isd.getStudentsByPage(1, 4);
​
        for (Student s : studentList) {
            System.out.println(s);
        }
​
        System.out.println("--------------");
​
        System.out.println(isd.getStudentBySid(200));
​
        System.out.println("--------------");
​
        Student s = new Student();
​
        s.setSid(100);
        s.setName("dasheng");
        s.setSex("male");
        s.setScore(90);
        s.setCid(3);
        s.setAge(500);
​
        System.out.println(isd.updateStudent(s) > 0);
    }
}

运行结果如下:

Student{sid=100, name='wukong', sex='male', score=99.0, cid=1, age=0}
Student{sid=101, name='wuneng', sex='male', score=59.0, cid=1, age=0}
Student{sid=102, name='wujing', sex='male', score=60.0, cid=1, age=0}
Student{sid=103, name='tangtang', sex='male', score=100.0, cid=1, age=0}
Student{sid=104, name='baoyu', sex='male', score=17.0, cid=2, age=0}
Student{sid=105, name='daiyu', sex='female', score=16.0, cid=2, age=0}
Student{sid=106, name='baichai', sex='female', score=16.0, cid=2, age=0}
Student{sid=107, name='xifeng', sex='female', score=27.0, cid=2, age=0}
Student{sid=108, name='liubei', sex='male', score=20.0, cid=3, age=0}
Student{sid=109, name='sunquan', sex='male', score=20.0, cid=3, age=0}
Student{sid=110, name='caocao', sex='male', score=20.0, cid=3, age=0}
Student{sid=111, name='songjiang', sex='male', score=20.0, cid=4, age=0}
Student{sid=112, name='likui', sex='male', score=22.0, cid=4, age=0}
Student{sid=113, name='sunerniang', sex='female', score=20.0, cid=4, age=0}
Student{sid=115, name='wuerlang', sex='male', score=88.0, cid=5, age=22}
Student{sid=116, name='zhiduoxing', sex='male', score=101.0, cid=3, age=22}
Student{sid=117, name='wangpengzhan', sex='boy', score=99.0, cid=5, age=19}
--------------
Student{sid=100, name='wukong', sex='male', score=99.0, cid=1, age=0}
Student{sid=101, name='wuneng', sex='male', score=59.0, cid=1, age=0}
Student{sid=102, name='wujing', sex='male', score=60.0, cid=1, age=0}
Student{sid=103, name='tangtang', sex='male', score=100.0, cid=1, age=0}
--------------
null
--------------
true

运行完成