思维导图
数据库连接池
1. 概念:其实就是一个容器(集合),存放数据库连接的容器。
当系统初始化好后,容器被创建,容器中会申请一些连接对象,当用户来访问
数据库时, 从容器中获取连接对象,用户访问完之后,会将连接对象归还给容器。
2. 好处:
1. 节约资源
2. 用户访问高效
C3P0连接池
C3P0开源免费的连接池!目前使用它的开源项目有:Spring、Hibernate(mybatis)等。
spring全家桶:spring、springmvc、springdata、springboot、springCloud
项目分成:(MVC模式)
Web:springmvc
业务层:service
dao层:专门和数据库打交道
使用第三方工具需要导入jar包,c3p0使用时还需要添加配置文件 c3p0-config.xml
01.导入jar包
02.配置文件引入
配置文件名称:c3p0-config.xml (固定)
配置文件位置:src (类路径)
03.编写连接池工具
package com.offcn.util;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Util {
private static DataSource ds;
static{
ds = new ComboPooledDataSource();
}
public static DataSource getDataSource(){
return ds;
}
public static Connection getConnection(){
Connection conn = null;
try {
conn= ds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
}
DBUtils工具
Student.java
package com.offcn.bean;
import java.io.Serializable;
import java.util.Date;
public class Student implements Serializable {
private int id;
private String name;
private int age;
private Date birthday;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
StudentDao.java
package com.offcn.dao;
import java.util.List;
import java.util.Map;
import com.offcn.bean.Student;
public interface StudentDao {
public int insertStudent(Student stu);
public List<Student> findAllStudent();
public Student findStudentById(int id);
public List<Student> fingStudentByName(String name);
public List<Map<String, Object>> findWithMapListHandler();
public long findStudentCount();
public int updateStudent(Student stu);
}
StudentDaoImpl.java
package com.offcn.dao.impl;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.offcn.bean.Student;
import com.offcn.dao.StudentDao;
import com.offcn.utils.C3P0Util;
public class StudentDaoImpl implements StudentDao {
@Override
public List<Student> findAllStudent() {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from student";
List<Student> list = null;
try {
list = qr.query(sql, new BeanListHandler<Student>(Student.class));
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
@Override
public int insertStudent(Student stu) {
/*
* //自动模式 QueryRunner qr = new QueryRunner(C3P0Util.getDataSource()); try {
* result = qr.update(sql, new Object[] { stu.getName(), stu.getAge(),
* stu.getBirthday() }); } catch (SQLException e) { e.printStackTrace(); }
*/
// 手动模式,用conn了
QueryRunner qr = new QueryRunner();
String sql = "insert into student values(null,?,?,?)";
int result = 0;
try {
Connection conn = C3P0Util.getConnection();
result = qr.update(conn, sql, new Object[] { stu.getName(), stu.getAge(), stu.getBirthday() });
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
@Override
public Student findStudentById(int id) {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from student where id = ?";
Student stu = null;
try {
stu = qr.query(sql, new BeanHandler<Student>(Student.class), id);
} catch (SQLException e) {
e.printStackTrace();
}
return stu;
}
@Override
public List<Student> fingStudentByName(String name) {
List<Student> list = null;
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from student where name like ?";
try {
list = qr.query(sql, new BeanListHandler<Student>(Student.class), "%" + name + "%");
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
@Override
public List<Map<String, Object>> findWithMapListHandler() {
List<Map<String, Object>> list = null;
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select * from student";
try {
list = qr.query(sql, new MapListHandler());
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
@Override
public long findStudentCount() {
long result = 0;
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "select count(*) from student";
try {
result = (long) qr.query(sql, new ScalarHandler());
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
@Override
public int updateStudent(Student stu) {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql = "update student set name = ?, age = ?, birthday =? where id = ?";
int result = 0;
try {
result = qr.update(sql, new Object[] { stu.getName(), stu.getAge(), stu.getBirthday(), stu.getId() });
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
}
DBUtilsTest.java
package com.offcn.test;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.junit.Test;
import com.offcn.bean.Student;
import com.offcn.dao.StudentDao;
import com.offcn.dao.impl.StudentDaoImpl;
public class DBUtilsTest {
StudentDao dao = new StudentDaoImpl();
@Test
public void test7() {
Student stu = new Student();
stu.setName("sb");
stu.setAge(23);
stu.setBirthday(new Date());
stu.setId(9);
int result = dao.updateStudent(stu);
System.out.println(result);
}
@Test
public void test6() {
long result = dao.findStudentCount();
System.out.println(result);
}
@Test
public void test5() {
List<Map<String, Object>> list = dao.findWithMapListHandler();
for (Map map : list) {
System.out.println(map);
}
}
@Test
public void test4() {
List<Student> list = dao.fingStudentByName("adcder");
for (Student stu : list) {
System.out.println(stu.getName() + "\t" + stu.getAge() + "\t" + stu.getBirthday());
}
}
@Test
public void test3() {
Student stu = dao.findStudentById(2);
System.out.println(stu.getName() + "\t" + stu.getAge() + "\t" + stu.getBirthday());
}
@Test
public void test2() {
List<Student> list = dao.findAllStudent();
for (Student s : list) {
System.out.println(s.getName() + "\t" + s.getAge() + "\t" + s.getBirthday());
}
}
@Test
public void test1() {
Student stu = new Student();
stu.setName("xyz");
stu.setAge(35);
stu.setBirthday(new Date());
int result = dao.insertStudent(stu);
System.out.println(result);
if (result > 0) {
System.out.println("添加成功"); // 红色的意思是没有被执行过的代码。。。
} else {
System.out.println("添加失败");
}
}
}
C3P0Util.java
package com.offcn.test;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.junit.Test;
import com.offcn.bean.Student;
import com.offcn.dao.StudentDao;
import com.offcn.dao.impl.StudentDaoImpl;
public class DBUtilsTest {
StudentDao dao = new StudentDaoImpl();
@Test
public void test7() {
Student stu = new Student();
stu.setName("sb");
stu.setAge(23);
stu.setBirthday(new Date());
stu.setId(9);
int result = dao.updateStudent(stu);
System.out.println(result);
}
@Test
public void test6() {
long result = dao.findStudentCount();
System.out.println(result);
}
@Test
public void test5() {
List<Map<String, Object>> list = dao.findWithMapListHandler();
for (Map map : list) {
System.out.println(map);
}
}
@Test
public void test4() {
List<Student> list = dao.fingStudentByName("adcder");
for (Student stu : list) {
System.out.println(stu.getName() + "\t" + stu.getAge() + "\t" + stu.getBirthday());
}
}
@Test
public void test3() {
Student stu = dao.findStudentById(2);
System.out.println(stu.getName() + "\t" + stu.getAge() + "\t" + stu.getBirthday());
}
@Test
public void test2() {
List<Student> list = dao.findAllStudent();
for (Student s : list) {
System.out.println(s.getName() + "\t" + s.getAge() + "\t" + s.getBirthday());
}
}
@Test
public void test1() {
Student stu = new Student();
stu.setName("xyz");
stu.setAge(35);
stu.setBirthday(new Date());
int result = dao.insertStudent(stu);
System.out.println(result);
if (result > 0) {
System.out.println("添加成功"); // 红色的意思是没有被执行过的代码。。。
} else {
System.out.println("添加失败");
}
}
}
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 默认配置,如果没有指定则使用这个配置 -->
<default-config >
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test</property>
<property name="user">root</property>
<property name="password">root</property>
</default-config>
</c3p0-config>
事务
事务的四大特性
原子性(Atomicity)
事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
一致性(Consistency)
事务前后数据的完整性必须保持一致
隔离性(Isolation)
是指多个用户并发访问数据库时,一个用户的事务不能被其它用户的事务所干扰,
多个 并发事务之间数据要相互隔离,不能相互影响。
持久性(Durability)
指一个事务一旦被提交(commit),它对数据库中数据的改变就是永久性的,接
下来即使数据库发 生故障也不应该对其有任何影响
conn.setAutoCommit(false); // 设置为手动提交事务
DbUtils.commitAndCloseQuietly(conn); //提交事务 ---保证同时成功
DbUtils.rollback(conn);// 回滚事务---保证同时失败