类库
dbcpconfig.properties(src目录)
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=sorry
initialSize=10
maxActive=50
maxIdle=20
minIdle=5
maxWait=60000
connectionProperties=useUnicode=true;characterEncoding=utf8
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读(read-only)状态。
#如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_COMMITTED
QueryRunner
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
@Test
public void test1() throws SQLException {
qr.update("insert into person values (?,?,?)",1,"zxn","1998-09-08");
}
@Test
public void test11() throws SQLException{
qr.update("insert into person values(?,?,?)", 2,"zxnn",new Date());
}
@Test
public void test2() throws SQLException{
qr.update("update person set name=? where id=?", "lxj",2);
}
@Test
public void test3() throws SQLException{
qr.update("delete from person where id=?", 2);
}
//批处理
@Test
public void test4() throws SQLException{
//高维:插入的记录条数 低维:每条记录的参数
Object [][]params = new Object[10][];
for(int i=0;i<10;i++){
params[i] = new Object[]{i+1,"aaa"+(i+1),new Date()};
}
qr.batch("insert into person values(?,?,?)", params);
}
test1
test11
test2
test3
test4
ResultSetHandler
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
//ArrayHandler:适合结果只有一条的情况。把记录中的每个字段的值封装到了一个Object[]中。
@Test
public void test1() throws SQLException{
Object[] objs = qr.query("select * from person", new ArrayHandler());
for(Object obj:objs)//每个元素就是列的值
System.out.println(obj);
}
//ArrayListHandler:多条结果。把每条记录的列值封装到Object[],再把Object[]封装到List中
@Test
public void test2() throws SQLException{
List<Object[]> list = qr.query("select * from person", new ArrayListHandler());
for(Object[] objs:list){
System.out.println("----------------------");
for(Object obj:objs)//每个元素就是列的值
System.out.println(obj);
}
}
//ColumnListHandler:封装某列的值。把取出的列值封装到List中。
@Test
public void test3() throws SQLException{
List<Object> list = qr.query("select * from person", new ColumnListHandler("name"));
for(Object obj:list){
System.out.println(obj);
}
}
//KeyedHandler:适合有多条记录的情况
@Test
public void test4() throws SQLException{
Map<Object, Map<String,Object>> bmap = qr.query("select * from person", new KeyedHandler("id"));
for(Map.Entry<Object, Map<String,Object>> bme:bmap.entrySet()){
System.out.println("------------------");
for(Map.Entry<String, Object> lme:bme.getValue().entrySet()){
System.out.println(lme.getKey()+":"+lme.getValue());
}
}
}
//MapHandler:适合结果有一条的情况。把记录的列明作为key,列值作为value封装到一个Map中
@Test
public void test5() throws SQLException{
Map<String,Object> map= qr.query("select * from person", new MapHandler());
for(Map.Entry<String, Object> me:map.entrySet()){
System.out.println(me.getKey()+":"+me.getValue());
}
}
//MapListHandler:适合多条记录的结果。把记录的列明作为key,列值作为value封装到一个Map中。再把Map放到List中
@Test
public void test6() throws SQLException{
List<Map<String,Object>> list= qr.query("select * from person", new MapListHandler());
for(Map<String,Object> map:list){
System.out.println("------------------");
for(Map.Entry<String, Object> me:map.entrySet()){
System.out.println(me.getKey()+":"+me.getValue());
}
}
}
//ScalarHandler:适合结果只有一条且只有一列情况。
@Test
public void test7() throws SQLException{
Object obj = qr.query("select count(*) from person", new ScalarHandler(1));
System.out.println(obj.getClass().getName());
System.out.println(obj);
}
test1
test2
test3
test4
test5
test6
test7
ThreadLocal
- ThreadLocal是一个线程局部变量
模拟ThreadLocal
public ThreadLocal{
private Map<Runnable,Object>map = new HashMap<Runnable,Object>();
public void set(Object obj){
map.put(Thread.currentThread(),obj);
}
public void remove(){
map.remove(Thread.currentThread());
}
public Object get(){
return map.get(Thread.currentThread())
}
}
使用
public class Thread1 extends Thread {
private ThreadLocal tl;
public Thread1(ThreadLocal tl) {
// TODO Auto-generated constructor stub
this.tl = tl;
}
@Override
public void run() {
// TODO Auto-generated method stub
System.out.println(Thread.currentThread().getName()+":"+tl.get());
}
}
public static void main(String[] args) {
// TODO Auto-generated method stub
ThreadLocal t1 = new ThreadLocal();
t1.set("p");
Object obj = t1.get();
Thread1 tt =new Thread1(t1);
tt.start();
System.out.println(Thread.currentThread().getName()+":"+obj);
}
事务控制(第一版)
domain
public class Account implements Serializable {
private int id;
private String name;
private float money;
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 float getMoney() {
return money;
}
public void setMoney(float money) {
this.money = money;
}
}
DAO
public interface AccountDao {
void transfer(String sourceAccountName,String targetAccountName,float money);
}
//DAO:只负责CRUD。事务要求都是业务上有要求
public class AccountDaoImpl implements AccountDao {
private QueryRunner qr = new QueryRunner();
public void transfer(String sourceAccountName, String targetAccountName,
float money) {
Connection conn = null;
try {
conn = DBCPUtil.getConnection();
conn.setAutoCommit(false);//开启事务
qr.update(conn,"update account set money=money-? where name=?", money,sourceAccountName);
// int i=1/0;
qr.update(conn,"update account set money=money+? where name=?", money,targetAccountName);
conn.commit();
} catch (SQLException e) {
if(conn!=null){
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
throw new RuntimeException(e);
}finally{
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
service
public interface BusinessService {
/**
* 转账
* @param sourceAccountName 转出账户
* @param targetAccountName 转入账户
* @param money 涉及金额
*/
void transfer(String sourceAccountName,String targetAccountName,float money);
}
public class BusinessServiceImpl implements BusinessService{
private AccountDao dao = new AccountDaoImpl();
public void transfer(String sourceAccountName, String targetAccountName,
float money) {
dao.transfer(sourceAccountName, targetAccountName, money);
}
}
public static void main(String[] args) {
BusinessService s = new BusinessServiceImpl();
s.transfer("aaa", "bbb", 100);
}
第二版(分层)
DAO
//DAO:只负责CRUD。事务要求都是业务上有要求
public class AccountDaoImpl implements AccountDao {
private Connection conn;
public AccountDaoImpl(Connection conn){
this.conn = conn;
}
private QueryRunner qr = new QueryRunner();
public Account findAccountByName(String accountName) {
try {
return qr.query(conn,"select * from account where name=?", new BeanHandler<Account>(Account.class),accountName);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void updateAccount(Account account) {
try {
qr.update(conn,"update account set money=? where id=?", account.getMoney(),account.getId());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
service
public class BusinessServiceImpl implements BusinessService{
public void transfer(String sourceAccountName, String targetAccountName,
float money) {
Connection conn = null;
try{
conn = DBCPUtil.getConnection();
conn.setAutoCommit(false);//开启事务
AccountDao dao = new AccountDaoImpl(conn);
Account sAccount = dao.findAccountByName(sourceAccountName);
Account tAccount = dao.findAccountByName(targetAccountName);
sAccount.setMoney(sAccount.getMoney()-money);
tAccount.setMoney(tAccount.getMoney()+money);
dao.updateAccount(sAccount);
// int i=1/0;
dao.updateAccount(tAccount);
conn.commit();
} catch (SQLException e) {
if(conn!=null){
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
throw new RuntimeException(e);
}finally{
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
第三版(ThreadLocal控制事务)
TransationManager
//事务管理器
public class TransationManager {
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
public static Connection getConnection(){
Connection conn = tl.get();//从当前线程上获得链接
if(conn==null){
conn = DBCPUtil.getConnection();
tl.set(conn);//把链接绑定到当前线程上
}
return conn;
}
public static void startTransaction(){
Connection conn = getConnection();
try {
conn.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void commit(){
Connection conn = getConnection();
try {
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void rollback(){
Connection conn = getConnection();
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void release(){
Connection conn = getConnection();
try {
conn.close();
tl.remove();//与线程池有关,解除关系
} catch (SQLException e) {
e.printStackTrace();
}
}
}
DAO
//DAO:只负责CRUD。事务要求都是业务上有要求
public class AccountDaoImpl implements AccountDao {
private QueryRunner qr = new QueryRunner();
public Account findAccountByName(String accountName) {
try {
return qr.query(TransationManager.getConnection(),"select * from account where name=?", new BeanHandler<Account>(Account.class),accountName);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void updateAccount(Account account) {
try {
qr.update(TransationManager.getConnection(),"update account set money=? where id=?", account.getMoney(),account.getId());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
第四版(APO思想)
BeanFactory
public class BeanFactory {
/**
* 产生BusinessService的实例
* @param isProxy ture,返回代理类。false,原来的类
* @return
*/
public static BusinessService getBusinessService(boolean isProxy){
final BusinessService s = new BusinessServiceImpl();
if(isProxy){
//返回实现的代理类
BusinessService proxyS = (BusinessService)Proxy.newProxyInstance(s.getClass().getClassLoader(),
s.getClass().getInterfaces(), new InvocationHandler() {
@Override
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
Object rtValue = null;
try{
long time = System.currentTimeMillis();
TransationManager.startTransaction();
rtValue = method.invoke(s, args);
TransationManager.commit();
System.out.println(method.getName()+" cost time "+(System.currentTimeMillis()-time) +" millis second");
} catch (Exception e) {
TransationManager.rollback();
throw new RuntimeException(e);
}finally{
TransationManager.release();
}
return rtValue;
}
});
return proxyS;
}else{
return s;
}
}
}
BusinessService s = BeanFactory.getBusinessService(true);
System.out.println(s.getClass().getName());
s.transfer("aaa", "bbb", 100);
多表操作
一对多
create table customers(
id int primary key,
name varchar(100),
address varchar(255)
);
create table orders(
ordernum int primary key,
amount float(8,2),
customerId int,
constraint customer_id_fk foreign key(customerId) references customers(id)
);
public class Customer {
private int id;
private String name;
private String address;
//One2Many
private List<Order> os = new ArrayList<Order>();
}
public class Order {
private int ordernum;
private float amount;
//Many2One
private Customer customer;
}
public class CustomerDaoImpl implements CustomerDao {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
public void addCustomer(Customer c) {
try {
//保存客户的基本信息
qr.update("insert into customers (id,name,address) values (?,?,?)", c.getId(),c.getName(),c.getAddress());
//查看有没有订单,保存订单的信息
List<Order> os = c.getOs();
if(os.size()>0){
// for(Order o:os){
// qr.update("insert into orders (ordernum,amount,customerId) values (?,?,?)", o.getOrdernum(),o.getAmount(),c.getId());
// }
Object params[][] = new Object[os.size()][];
for(int i=0;i<os.size();i++){
Order o = os.get(i);
params[i] = new Object[]{o.getOrdernum(),o.getAmount(),c.getId()};
}
qr.batch("insert into orders (ordernum,amount,customerId) values (?,?,?)", params);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//客户对应的订单要不要查?看需求
//查询客户时,把订单查询出来,但订单中只有id的信息。延迟加载机制。懒
public Customer findCustomer(int customerId) {
//饿汉子:不懒的 lazy=false的
try {
Customer c = qr.query("select * from customers where id=?", new BeanHandler<Customer>(Customer.class), customerId);
if(c!=null){
//把定单查询出来
List<Order> os = qr.query("select * from orders where customerId=?", new BeanListHandler<Order>(Order.class), customerId);
c.setOs(os);
}
return c;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
多对多
create table teachers(
id int primary key,
name varchar(100),
salary float(8,2)
);
create table students(
id int primary key,
name varchar(100),
grade varchar(10)
);
create table teachers_students(
t_id int,
s_id int,
primary key(t_id,s_id),
constraint teacher_id_fk foreign key(t_id) references teachers(id),
constraint student_id_fk foreign key(s_id) references students(id)
);
public class Teacher {
private int id;
private String name;
private float salary;
//Many2Many
private List<Student> students = new ArrayList<Student>();
}
public class Student {
private int id;
private String name;
private String grade;
//Many2Many
private List<Teacher> teachers = new ArrayList<Teacher>();
}
public class TeacherDaoImpl implements TeacherDao {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
public void addTeacher(Teacher t) {
try {
//保存老师的基本信息
qr.update("insert into teachers(id,name,salary) values (?,?,?)", t.getId(),t.getName(),t.getSalary());
//看看老师有没有关联的学员:
List<Student> students = t.getStudents();
if(students.size()>0){
//遍历学员:
for(Student s:students){
//看看学员信息数据库有没有
Object obj = qr.query("select id from students where id=?", new ScalarHandler(1), s.getId());
if(obj==null){
//没有:插入学员的基本信息
qr.update("insert into students(id,name,grade) values (?,?,?)",s.getId(),s.getName(),s.getGrade());
}
//插入关系
qr.update("insert into teachers_students(t_id,s_id) values (?,?)", t.getId(),s.getId());
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public Teacher findTeacher(int teacherId) {
try {
Teacher t = qr.query("select * from teachers where id=?", new BeanHandler<Teacher>(Teacher.class), teacherId);
if(t!=null){
//去查教过哪些学员
// String sql = "select * from students where id in (select s_id from teachers_students where t_id=?)";
// String sql = "select s.* from students s,teachers_students ts where s.id=ts.s_id and ts.t_id=?";
String sql = "select s.* from students s inner join teachers_students ts on s.id=ts.s_id where ts.t_id=? ";
List<Student> students = qr.query(sql, new BeanListHandler<Student>(Student.class), teacherId);
t.setStudents(students);
}
return t;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
一对一
create table person(
id int primary key,
name varchar(100),
date birthday
);
create table idcard(
id int primary key,
num varchar(18),
constraint personId_fk foreign key(id) references person(id)
);
//按照外键关联:通过一对多模拟的一对一
create table idcard(
id int primary key,
num varchar(18),
personId int unique,
constraint personId_fk foreign key(personId) references person(id)
);
public class Person {
private int id;
private String name;
private Date birthday;
private IdCard idCard;
}
public class IdCard {
private int id;
private String num;
private Person p;
}
public class PersonDaoImpl implements PersonDao {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
public void savePerson(Person p) {
try {
qr.update("insert into person (id,name,birthday) values (?,?,?)", p.getId(),p.getName(),p.getBirthday());
IdCard idcard = p.getIdCard();
if(idcard!=null){
qr.update("insert into idcard (id,num) values (?,?)", p.getId(),idcard.getNum());
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public Person findPerson(int personId) {
try {
Person p = qr.query("select * from person where id=?",new BeanHandler<Person>(Person.class), personId);
if(p!=null){
IdCard idCard = qr.query("select * from idcard where id=?",new BeanHandler<IdCard>(IdCard.class), personId);
p.setIdCard(idCard);
}
return p;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}