Java用C3P0、DBUtils连接操作数据库综合实例

编辑工具：Eclipse

数据库：MySQL

实例内容：操作数据库中的Student表

 

 

 

 

需要导入的jar包：

 

 

 

 

Student的属性文件：存放DbUtils连接数据库和C3P0配置连接池的信息：

C3P0.properties:

USER=root
       
PASSWORD=root
       
Driver=com.mysql.cj.jdbc.Driver
       
URL=jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
#当连接池中的连接耗尽的时候c3p0一次同时获取的连接数，default：3
c3p0.acquireIncrement=3
#没60秒检查所有连接池中的空闲      连接。default：0
c3p0.idleConnectionTestPeriod=60
c3p0.initialPoolSize=10
#最大空闲时间，60秒内未连接被丢弃，默认为0，永不丢弃，default：0
c3p0.maxIdleTime=60
c3p0.maxPoolSize=20
c3p0.minPoolSize=5

Student的domain类：

private String stu_name;
       private int stu_age;
       private String stu_class;
       private String stu_course;
       private int stu_score;     
       public Student() {} 
       public Student(String stu_name, int stu_age, String stu_class, String stu_course, int stu_score) {
             super();
             this.stu_name = stu_name;
             this.stu_age = stu_age;
             this.stu_class = stu_class;
             this.stu_course = stu_course;
             this.stu_score = stu_score;
       }
       @Override
       public String toString() {
             return "Student [stu_name=" + stu_name + ", stu_age=" + stu_age + ", stu_class=" + stu_class + ", stu_course="
                           + stu_course + ", stu_score=" + stu_score + "]";
       }
       public String getStu_name() {
             return stu_name;
       }
       public void setStu_name(String stu_name) {
             this.stu_name = stu_name;
       }
       public int getStu_age() {
             return stu_age;
       }
       public void setStu_age(int stu_age) {
             this.stu_age = stu_age;
       }
       public String getStu_class() {
             return stu_class;
       }
       public void setStu_class(String stu_class) {
             this.stu_class = stu_class;
       }
       public String getStu_course() {
             return stu_course;
       }
       public void setStu_course(String stu_course) {
             this.stu_course = stu_course;
       }
       public int getStu_score() {
             return stu_score;
       }
       public void setStu_score(int stu_score) {
             this.stu_score = stu_score;
       }      
}

C3P0的配置工具类：

public class DBPoolUtils {
       
       //取得属性文件中的信息
       private static ResourceBundle bundle=ResourceBundle.getBundle("com/demo4/C3P0");
       //数据库的连接池
       private static ComboPooledDataSource dataSource=new ComboPooledDataSource();   
       static
       {
             
             try {
                    //配置数据库的驱动
                    dataSource.setDriverClass(bundle.getString("Driver"));
                    //设置数据库的url
                    dataSource.setJdbcUrl(bundle.getString("URL"));
                    //设置数据库的用户名
                    dataSource.setUser(bundle.getString("USER"));
                    //设置数据库的密码
                    dataSource.setPassword(bundle.getString("PASSWORD"));
                    //设置连接池中耗尽的时候c3p0一次同时获取的连接数
                    dataSource.setAcquireIncrement(Integer.parseInt(bundle.getString("c3p0.acquireIncrement")));
                    //设置检查连接池的空闲连接的时间
                    dataSource.setIdleConnectionTestPeriod(Integer.parseInt(bundle.getString("c3p0.idleConnectionTestPeriod")));
                    dataSource.setInitialPoolSize(Integer.parseInt(bundle.getString("c3p0.initialPoolSize")));
                    //设置最大的空闲时间
                    dataSource.setMaxIdleTime(Integer.parseInt(bundle.getString("c3p0.maxIdleTime")));
                    //设置最大的连接池数量
                    dataSource.setMaxPoolSize(Integer.parseInt(bundle.getString("c3p0.maxPoolSize")));
                    //设置最小的连接池数量
                    dataSource.setMinPoolSize(Integer.parseInt(bundle.getString("c3p0.minPoolSize")));
             } catch (PropertyVetoException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
             }
       }
       public static Connection getConnection() throws SQLException
       {
             return dataSource.getConnection();
       }
       
}

数据操作Dao接口：用于规定操作的方法

public interface StudentDao {
       //增加一个学生
       boolean addStudent(Student student);
       //更新一个学生
       boolean updateStudent(Student student);
       //删除一个学生
       boolean deleteStudent(String name);
       //查看一个学生
       Student viewStudentByName(String name);
       //得到一个学生的所有信息
       Map<String,Object> viewMapBuName(String name);
       //得到多个的学生
       List<Student> queryMultiStudent(String name);
       //得到多个学生的所有信息
       List<Map<String,Object>>queryMultiMapForStudent(int score);
       
}

数据操作DAO接口的实现类：用于具体的实现操作

public class StudentDaoImpl implements StudentDao{
       
       public StudentDaoImpl() {}
       
       @Override
       public boolean addStudent(Student student) {
             //使用DBUtils工具类，操作数据库
             QueryRunner queryRunner=new QueryRunner();
             Connection connection=null;
             String sql="insert into student values(?,?,?,?,?)";
             boolean flag=false;
                    //使用JDBC连接池
                    try {
                           connection=DBPoolUtils.getConnection();//得到数据库连接
                           int result=queryRunner.update(connection,sql,new Object[] {student.getStu_name(),student.getStu_age(),student.getStu_class(),student.getStu_course(),student.getStu_score()});//使用DButils工具执行更新操作
                           flag=result>0?true:false;
                    } catch (SQLException e) {
                           e.printStackTrace();
                    }finally
                    {
                           try {
                                 DbUtils.close(connection);//关闭数据库连接
                           } catch (SQLException e) {
                                 // TODO Auto-generated catch block
                                 e.printStackTrace();
                           }
                    }                   
                    return flag;
       }
       @Override
       public boolean updateStudent(Student student) {
             QueryRunner queryRunner =new QueryRunner();
             Connection connection=null;
             String sql="update student set stu_age=?,stu_class=?,stu_course=?,stu_score=? where stu_name=?";
             boolean flag=false;
             try {
                    connection=DBPoolUtils.getConnection();
                    int result=queryRunner.update(connection,sql,new Object[] {student.getStu_age(),student.getStu_class(),student.getStu_course(),student.getStu_score(),student.getStu_name()});
                    flag=result>0?true:false;
             } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
             }finally
             {
                    try {
                           DbUtils.close(connection);//关闭数据库连接
                    } catch (SQLException e) {
                           // TODO Auto-generated catch block
                           e.printStackTrace();
                    }
             }
             return flag;
       }
       @Override
       public boolean deleteStudent(String name) {
             QueryRunner queryRunner =new QueryRunner();
             Connection connection=null;
             String sql="delete from  student  where stu_name=?";
             boolean flag=false;
             try {
                    connection=DBPoolUtils.getConnection();
                    int result=queryRunner.update(connection,sql,new Object[] {name});
                    flag=result>0?true:false;
             } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
             }finally
             {
                    try {
                           DbUtils.close(connection);//关闭数据库连接
                    } catch (SQLException e) {
                           // TODO Auto-generated catch block
                           e.printStackTrace();
                    }
             }
             return flag;
       }
       @Override
       public Student viewStudentByName(String name) {
             
             QueryRunner queryRunner =new QueryRunner();
             Connection connection=null;
             String sql="select * from  student  where stu_name=?";
             try {
                    connection=DBPoolUtils.getConnection();
                    return (Student) queryRunner.query(connection,sql,new BeanHandler<>(Student.class),new Object[] {name});                 
             } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
             }finally
             {
                    try {
                           DbUtils.close(connection);//关闭数据库连接
                    } catch (SQLException e) {
                           // TODO Auto-generated catch block
                           e.printStackTrace();
                    }
             }
             return null;
       }
       @Override
       public Map<String, Object> viewMapBuName(String name) {
             QueryRunner queryRunner =new QueryRunner();
             Connection connection=null;
             String sql="select * from  student  where stu_name=?";
             try {
                    connection=DBPoolUtils.getConnection();
                    return  queryRunner.query(connection,sql,new MapHandler(),new Object[] {name});                   
             } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
             }finally
             {
                    try {
                           DbUtils.close(connection);//关闭数据库连接
                    } catch (SQLException e) {
                           // TODO Auto-generated catch block
                           e.printStackTrace();
                    }
             }
             return null;
       }
       @Override
       public List<Student> queryMultiStudent(String name) {
             QueryRunner queryRunner =new QueryRunner();
             Connection connection=null;
             String sql="select * from  student  where stu_name like ?";
             try {
                    connection=DBPoolUtils.getConnection();
                    return  queryRunner.query(connection,sql,new BeanListHandler<>(Student.class),new Object[] {"%"+name+"%"});                  
             } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
             }finally
             {
                    try {
                           DbUtils.close(connection);//关闭数据库连接
                    } catch (SQLException e) {
                           // TODO Auto-generated catch block
                           e.printStackTrace();
                    }
             }
             return null;
       }
       @Override
       public List<Map<String, Object>> queryMultiMapForStudent(int score) {
             QueryRunner queryRunner =new QueryRunner();
             Connection connection=null;
             String sql="select * from  student  where stu_score > ?";
             try {
                    connection=DBPoolUtils.getConnection();
                    return  queryRunner.query(connection,sql,new MapListHandler(),new Object[] {score});              
             } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
             }finally
             {
                    try {
                           DbUtils.close(connection);//关闭数据库连接
                    } catch (SQLException e) {
                           // TODO Auto-generated catch block
                           e.printStackTrace();
                    }
             }
             return null;
       }
       
}

下面是简单的一个添加学生功能的测试：

public class TestStudent {
       
       public static void main(String[] args) {
             StudentDaoImpl test=new StudentDaoImpl();
             Student student=new Student("李白",23,"B3","物理",89);                                                     
             if(test.addStudent(student))
             {
                    System.out.println("插入成功");
             }else
             {
                    System.out.println("插入失败");
             }
       }
}