数据库框架DBUtils
2024-10-08 12:54:33
数据库有关框架
1.框架：提高开发效率。按部就班
2.数据库框架：
ORM：Object Relation Mapping 对象关系映射。JavaBean --Object数据库----Relation
知名框架：Hibernate ，MyBatis，JPA（Java Persist API：JavaEE技术之一，ORM标准）
-----------------------------------------------------------------------------------------------------------------------
DBUtils，Spring JDBCTemplate不能算是ORM框架，只是对JDBC编码进行简化处理
DBUtils框架
QueryRunner：
1.构造方法：
QueryRunner（）默认构造方法；
QueryRunner（DataSource ds）需要一个数据源；
2.具体方法：
batch：批处理
int[] batch(Connection conn, String sql, Object[][] params) // Execute a batch of SQL INSERT, UPDATE, or DELETE queries.使用默认构造方法时
int[] batch(String sql, Object[][] params) // Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
Object[][]params:高维：执行的语句条数，低维：每条语句需要的参数
query：查询
public <T> T query(String sql,ResultSetHandler<T> rsh,Object... params)throws SQLException ////Executes the given SELECT SQL query and returns a result object. The Connection is retrieved from the DataSource set in the constructor. public <T> T query(Connection conn，String sql,ResultSetHandler<T> rsh, Object... params)throws SQLException ////Executes the given SELECT SQL query and returns a result object. The Connection is retrieved from the DataSource set in the constructor.
update：DML INSERT UPDATE DELETE
int update(String sql, Object... params) Executes the given INSERT, UPDATE, or DELETE SQL statement. update(Connection conn,String sql, Object... params) Executes the given INSERT, UPDATE, or DELETE SQL statement.
注意：多次调用update方法，需要在一个事物中，用update(Connection conn,String sql, Object... params)，用默认构造方法。
例子：
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.SQLException;
import java.util.Date; import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialClob; import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test; import com.itheima.util.DBCPUtil; /*
create table student(
id int primary key,
name varchar(100),
birthday date
);
*/
public class DBUtilDemo1 {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
@Test
public void testAdd() throws SQLException{
qr.update("insert into student values(?,?,?)", 1,"wf",new Date());
}
// @Test
// public void testAdd1() throws SQLException{
// qr.update("insert into student values(?,?,?)", 2,"qhs","1930-09-08");
// } /*
* create table t1(id int primary key,content longblob);
*/
@Test
public void testBlob() throws Exception{
// InputStream in = new FileInputStream("src/20.jpg");
// qr.update("insert into t1 values(?,?)", 1,in); InputStream in = new FileInputStream("src/20.jpg");
byte b[] = new byte[in.available()];
in.read(b);
in.close();
Blob blob = new SerialBlob(b);
qr.update("insert into t1 values(?,?)", 2,blob);
}
/*
* create table t2(id int primary key,content longtext);
*/
@Test
public void testClob() throws Exception{
File file = new File("src/jpm.txt");
Reader r = new FileReader(file);
char ch[] = new char[(int)file.length()];
r.read(ch);
r.close();
Clob c = new SerialClob(ch);
qr.update("insert into t2 values(?,?)", 1,c);
}
/*
* create table t3(id int,name varchar(100));
*/
@Test
public void testBatch()throws Exception{ Object params[][] = new Object[10][];
for(int i=0;i<params.length;i++){
params[i] = new Object[]{i+1,"aaa"+(i+1)};
}
qr.batch("insert into t3 values(?,?)", params);
}
}
DBUtilDemo
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; public class DBCPUtil {
private static DataSource dataSource;
static{
try {
InputStream in = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties props = new Properties();
props.load(in);
dataSource = BasicDataSourceFactory.createDataSource(props);
} catch (Exception e) {
throw new ExceptionInInitializerError("error");
}
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException("error");
}
}
public static DataSource getDataSource(){
return dataSource;
} }
DBCPtil
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day17
username=root
password=123456
initialSize=10
maxActive=50
maxIdle=20
minIdle=5
maxWait=60000
connectionProperties=useUnicode=true;characterEncoding=utf8
defaultAutoCommit=true
defaultReadOnly=
defaultTransactionIsolation=REPEATABLE_READ
dbcpconfig.properties
DBUtils所有的结果处理器（查询返回的结果集）
封装的结果是什么样的：
ArrayHandler：适用于结果集只有一条结果的情况。返回Obejct[],数组中的元素就是记录的每列数据。 ArrayListHandler：适用于结果集中有多条结果的情况。返回的是一个List<Object[]>,List封装了记录，Object[]每条记录的每列数据。 BeanHandler, BeanListHandler, ColumnListHandler:适用于取某一列的值。返回的是List<Object>，集合中就是该列中的数据。 KeyedHandler:适用于结果中有多条的情况。返回的是一个Map<Object,Map<String,Object>>。
MapHandler:适用于结果只有一条的情况。Map<String,Object>,key是字段名，value，字段值。 MapListHandler：适用于结果有多条的情况。List<Map<String,Object>>.List封装了所有的记录，每条记录封装到Map中,key是字段名，value，字段值。 ScalarHandler：适用于结果中只有一行和只有一列的情况。返回的是一个Object。
例子：
import java.util.List;
import java.util.Map; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test; import com.itheima.util.DBCPUtil; public class DBUtilDemo2 {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource()); @Test
public void test1() throws Exception {
Object values[] = qr.query("select * from t3", new ArrayHandler());
for (Object obj : values)
System.out.println(obj);
} @Test
public void test2() throws Exception {
List<Object[]> records = qr.query("select * from t3",
new ArrayListHandler());
for (Object[] values : records) {
System.out.println("------------------");
for (Object obj : values)
System.out.println(obj);
}
} @Test
public void test3() throws Exception {
List<Object> values = qr.query("select * from t3",
new ColumnListHandler("name"));
for (Object obj : values)
System.out.println(obj);
} @Test
public void test4() throws Exception {
Map<Object, Map<String, Object>> bmap = qr.query("select * from t3",
new KeyedHandler("id"));
for (Map.Entry<Object, Map<String, Object>> bme : bmap.entrySet()) {
System.out.println("----------------");
for (Map.Entry<String, Object> sme : bme.getValue().entrySet()) {
System.out.println(sme.getKey() + "=" + sme.getValue());
}
}
} @Test
public void test5() throws Exception {
Map<String, Object> map = qr
.query("select * from t3", new MapHandler());
for (Map.Entry<String, Object> sme : map.entrySet()) {
System.out.println(sme.getKey() + "=" + sme.getValue());
}
} @Test
public void test6() throws Exception {
List<Map<String, Object>> list = qr.query("select * from t3",
new MapListHandler());
for (Map<String, Object> map : list) {
System.out.println("----------------");
for (Map.Entry<String, Object> sme : map.entrySet()) {
System.out.println(sme.getKey() + "=" + sme.getValue());
}
}
}
@Test
public void test7() throws Exception {
Object obj = qr.query("select count(*) from t3",new ScalarHandler(1));
System.out.println(obj.getClass().getName());
System.out.println(obj);
}
}
DBUtilDemo
1
aaa1
------------------
1
aaa1
------------------
2
aaa2
------------------
3
aaa3
------------------
4
aaa4
------------------
5
aaa5
------------------
6
aaa6
------------------
7
aaa7
------------------
8
aaa8
------------------
9
aaa9
------------------
10
aaa10
aaa1
aaa2
aaa3
aaa4
aaa5
aaa6
aaa7
aaa8
aaa9
aaa10
----------------
name=aaa1
id=1
----------------
name=aaa2
id=2
----------------
name=aaa3
id=3
----------------
name=aaa4
id=4
----------------
name=aaa5
id=5
----------------
name=aaa6
id=6
----------------
name=aaa7
id=7
----------------
name=aaa8
id=8
----------------
name=aaa9
id=9
----------------
name=aaa10
id=10
name=aaa1
id=1
----------------
name=aaa1
id=1
----------------
name=aaa2
id=2
----------------
name=aaa3
id=3
----------------
name=aaa4
id=4
----------------
name=aaa5
id=5
----------------
name=aaa6
id=6
----------------
name=aaa7
id=7
----------------
name=aaa8
id=8
----------------
name=aaa9
id=9
----------------
name=aaa10
id=10
java.lang.Long
10
运行结果
利用DBUtils进行事物有关操作：
import java.sql.Connection;
import java.sql.SQLException; import org.apache.commons.dbutils.QueryRunner; import com.itheima.dao.AccountDao;
import com.itheima.util.DBCPUtil; public class AccountDaoImpl implements AccountDao { private QueryRunner qr=new QueryRunner();
/* (non-Javadoc)
* @see com.itheima.dao.impl.AccountDao#transfer(java.lang.String, java.lang.String, float)
*/
@Override
public void transfer(String sourceAccount,String targetAccount,float money){
Connection conn=null;
try {
conn=DBCPUtil.getConnection();
conn.setAutoCommit(false);
qr.update(conn,"update account set money=money-? where name=?", money,sourceAccount);
qr.update(conn,"update account set money=money+? where name=?", money,targetAccount);
// int x=1/0;
conn.commit();
int x=1/0;
} catch (SQLException e) {
// TODO Auto-generated catch block
// throw new RuntimeException(e);
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
finally {
if(conn!=null)
try {
conn.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
} }
DAO
package com.itheima.service.impl; import com.itheima.dao.AccountDao;
import com.itheima.dao.impl.AccountDaoImpl;
import com.itheima.domain.Account;
import com.itheima.service.BusinessService; public class BusinessServiceImpl implements BusinessService {
private AccountDao dao=new AccountDaoImpl(); public void transfer(String sourceAccount,String targetAccount,float money){
dao.transfer(sourceAccount, targetAccount, money);
} }
service
package com.itheima.test01; import com.itheima.service.BusinessService;
import com.itheima.service.impl.BusinessServiceImpl;
import com.itheima.service.impl.*; public class Client { public Client() {
// TODO Auto-generated constructor stub
} public static void main(String[] args) {
// TODO Auto-generated method stub
BusinessService s=new BusinessServiceImpl();
s.transfer("bbb","aaa",10);
} }
Client
事物管理
import java.sql.SQLException; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler; import com.itheima.dao.AccountDao;
import com.itheima.domain.Account;
import com.itheima.util.TransactionManager; public class AccountDaoImpl implements AccountDao {
private QueryRunner qr = new QueryRunner(); public Account findByName(String accountName) {
try {
return qr.query(TransactionManager.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(TransactionManager.getConnection(),"update account set money=? where name=?", account.getMoney(),account.getName());
} catch (SQLException e) {
throw new RuntimeException(e);
}
} }
DAO层
import com.itheima.dao.AccountDao;
import com.itheima.dao.impl.AccountDaoImpl;
import com.itheima.domain.Account;
import com.itheima.service.BusinessService;
import com.itheima.util.TransactionManager; public class BusinessServiceImpl implements BusinessService {
private AccountDao dao = new AccountDaoImpl();
public void transfer(String sourceAccount,String targetAccount,float money){
try{
TransactionManager.startTransaction();
Account sAccount = dao.findByName(sourceAccount);
Account tAccount = dao.findByName(targetAccount); sAccount.setMoney(sAccount.getMoney()-money);
tAccount.setMoney(tAccount.getMoney()+money); dao.updateAccount(sAccount); // int i=1/0; dao.updateAccount(tAccount);
TransactionManager.commit();
}catch(Exception e){
TransactionManager.rollback();
e.printStackTrace();
}finally{
TransactionManager.release();
}
}
}
Service层
import com.itheima.service.BusinessService;
import com.itheima.service.impl.BusinessServiceImpl; public class Client {
//方法内的多次方法调用都处在同一个线程中
public static void main(String[] args) {
BusinessService s = new BusinessServiceImpl();
s.transfer("bbb", "aaa", 100);
} }
Client
ThreadLocal类
模拟ThreadLocal功能伪代码
public class ThreadLocal {
//容器
private Map<Runnable,Object> map=new HashMap<Runnable,Object>();
public void set(Object value){//向Map中存放数据
map.put(Thread.currentThread(),value)
}
public Object get(){//从Map中取数据
return map.get(Thread.currentThread());
}
}
特点：一个线程放的东西，除了自己谁也拿不到。线程局部变量
线程中的方法调用栈
借助ThreadLocal管理事物：
特点：1.方法内的多次方法调用都处在同一线程中，所以可以考虑，将Connection对象放入ThreadLocal中，这样只要在同一线程中，就是同一个对象调用的方法。
代码撸上：
import java.sql.SQLException; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler; import com.itheima.dao.AccountDao;
import com.itheima.domain.Account;
import com.itheima.util.TransactionManager; public class AccountDaoImpl implements AccountDao {
private QueryRunner qr = new QueryRunner(); public Account findByName(String accountName) {
try {
return qr.query(TransactionManager.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(TransactionManager.getConnection(),"update account set money=? where name=?", account.getMoney(),account.getName());
} catch (SQLException e) {
throw new RuntimeException(e);
}
} }
AccountDAOImpl
package com.itheima.util; import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; public class TransactionManager {
private static DataSource dataSource;
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
static{
try {
InputStream in = TransactionManager.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties props = new Properties();
props.load(in);
dataSource = BasicDataSourceFactory.createDataSource(props);
} catch (Exception e) {
throw new ExceptionInInitializerError("初始化数据源失败");
}
}
public static Connection getConnection(){
try {
Connection conn = tl.get();//从当前线程中取连接
if(conn==null){
conn = dataSource.getConnection();
tl.set(conn);
}
return conn;
} catch (SQLException e) {
throw new RuntimeException("获取数据库连接失败");
}
}
public static void startTransaction(){
try {
Connection conn = getConnection();
conn.setAutoCommit(false);
} catch (SQLException e) {
throw new RuntimeException("开启事务失败");
}
}
public static void rollback(){
try {
Connection conn = getConnection();
conn.rollback();
} catch (SQLException e) {
throw new RuntimeException("回滚事务失败");
}
}
public static void commit(){
try {
Connection conn = getConnection();
conn.commit();
} catch (SQLException e) {
throw new RuntimeException("提交事务失败");
}
}
public static void release(){
try {
Connection conn = getConnection();
conn.close();
tl.remove();//从当前线程中解绑。服务器有关：用到了线程池。
} catch (SQLException e) {
throw new RuntimeException("关闭连接失败");
}
}
}
TransactionManager
import com.itheima.dao.AccountDao;
import com.itheima.dao.impl.AccountDaoImpl;
import com.itheima.domain.Account;
import com.itheima.service.BusinessService;
import com.itheima.util.TransactionManager; public class BusinessServiceImpl implements BusinessService {
private AccountDao dao = new AccountDaoImpl();
public void transfer(String sourceAccount,String targetAccount,float money){
try{
TransactionManager.startTransaction();
Account sAccount = dao.findByName(sourceAccount);
Account tAccount = dao.findByName(targetAccount); sAccount.setMoney(sAccount.getMoney()-money);
tAccount.setMoney(tAccount.getMoney()+money); dao.updateAccount(sAccount); // int i=1/0; dao.updateAccount(tAccount);
TransactionManager.commit();
}catch(Exception e){
TransactionManager.rollback();
e.printStackTrace();
}finally{
TransactionManager.release();
}
}
}
BusinessServiceImpl
import com.itheima.service.BusinessService;
import com.itheima.service.impl.BusinessServiceImpl; public class Client {
//方法内的多次方法调用都处在同一个线程中
public static void main(String[] args) {
BusinessService s = new BusinessServiceImpl();
s.transfer("bbb", "aaa", 100);
} }
Client
AOP思想控制事物
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.util.HashSet;
import java.util.Set; import com.itheima.service.BusinessService;
import com.itheima.service.impl.BusinessServiceImpl; public class BeanFactory {
private static Set<String> includeMethod = new HashSet<String>();//需要控制事务的方法 static{
includeMethod.add("transfer");
} public static BusinessService getBusinessSerivce(){
final BusinessService s = new BusinessServiceImpl();
BusinessService proxyS = (BusinessService)Proxy.newProxyInstance(s.getClass().getClassLoader(),
s.getClass().getInterfaces(),
new InvocationHandler() {
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable { String methodName = method.getName();
if(includeMethod.contains(methodName)){ Object rtValue = null;
try{
TransactionManager.startTransaction();
rtValue = method.invoke(s, args);
TransactionManager.commit();
}catch(Exception e){
TransactionManager.rollback();
e.printStackTrace();
}finally{
TransactionManager.release();
}
return rtValue;
}else{
return method.invoke(s, args);
}
}
});
return proxyS;
}
}
BeanFactory
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; public class TransactionManager {
private static DataSource dataSource;
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
static{
try {
InputStream in = TransactionManager.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties props = new Properties();
props.load(in);
dataSource = BasicDataSourceFactory.createDataSource(props);
} catch (Exception e) {
throw new ExceptionInInitializerError("初始化数据源失败");
}
}
public static Connection getConnection(){
try {
Connection conn = tl.get();//从当前线程中取连接
if(conn==null){
conn = dataSource.getConnection();
tl.set(conn);
}
return conn;
} catch (SQLException e) {
throw new RuntimeException("获取数据库连接失败");
}
}
public static void startTransaction(){
try {
Connection conn = getConnection();
conn.setAutoCommit(false);
} catch (SQLException e) {
throw new RuntimeException("开启事务失败");
}
}
public static void rollback(){
try {
Connection conn = getConnection();
conn.rollback();
} catch (SQLException e) {
throw new RuntimeException("回滚事务失败");
}
}
public static void commit(){
try {
Connection conn = getConnection();
conn.commit();
} catch (SQLException e) {
throw new RuntimeException("提交事务失败");
}
}
public static void release(){
try {
Connection conn = getConnection();
conn.close();
tl.remove();//从当前线程中解绑。服务器有关：用到了线程池。
} catch (SQLException e) {
throw new RuntimeException("关闭连接失败");
}
}
}
TransactionManager
import com.itheima.dao.AccountDao;
import com.itheima.dao.impl.AccountDaoImpl;
import com.itheima.domain.Account;
import com.itheima.service.BusinessService; public class BusinessServiceImpl implements BusinessService {
private AccountDao dao = new AccountDaoImpl();
public void transfer(String sourceAccount,String targetAccount,float money){
Account sAccount = dao.findByName(sourceAccount);
Account tAccount = dao.findByName(targetAccount); sAccount.setMoney(sAccount.getMoney()-money);
tAccount.setMoney(tAccount.getMoney()+money);
dao.updateAccount(sAccount);
// int i=1/0; dao.updateAccount(tAccount);
}
}
BusinessServiceImpl
import java.sql.SQLException; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler; import com.itheima.dao.AccountDao;
import com.itheima.domain.Account;
import com.itheima.util.TransactionManager; public class AccountDaoImpl implements AccountDao {
private QueryRunner qr = new QueryRunner(); public Account findByName(String accountName) {
try {
return qr.query(TransactionManager.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(TransactionManager.getConnection(),"update account set money=? where name=?", account.getMoney(),account.getName());
} catch (SQLException e) {
throw new RuntimeException(e);
}
} }
AccountDAOImpl
import com.itheima.service.BusinessService;
import com.itheima.service.impl.BusinessServiceImpl;
import com.itheima.util.BeanFactory; public class Client {
//方法内的多次方法调用都处在同一个线程中
public static void main(String[] args) {
BusinessService s = BeanFactory.getBusinessSerivce();
s.transfer("bbb", "aaa", 100);
} }
Client
利用DBUtils进行事物有关操作：
在同一
利用DBUtils进行多表操作
DAO层：
package com.itheima.dao.impl; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler; import com.itheima.domain.IdCard;
import com.itheima.domain.Person;
import com.itheima.util.DBCPUtil; public class PersonDaoImpl {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
public void addPerson(Person p){
try{
qr.update("insert into person values(?,?)", p.getId(),p.getName());
IdCard idcard = p.getIdCard();
if(idcard!=null){
qr.update("insert into id_card values(?,?)", p.getId(),idcard.getNum());
}
}catch(Exception e){
throw new RuntimeException(e);
}
}
//关联的idcard要不要查：建议查出来
public Person findPersonById(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 id_card where id=?", new BeanHandler<IdCard>(IdCard.class),personId);
p.setIdCard(idcard);
}
return p;
}catch(Exception e){
throw new RuntimeException(e);
}
}
}
PersonDaoImpl
import java.sql.SQLException;
import java.util.List; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler; import com.itheima.domain.Department;
import com.itheima.domain.Employee;
import com.itheima.util.DBCPUtil; //以部门为出发点进行操作
public class DepartmentDaoImpl {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
public void addDepartment(Department d){
try {
//保存部门的基本信息
qr.update("insert into department values(?,?)", d.getId(),d.getName());
//判断部门下面是否有员工
List<Employee> emps = d.getEmps();
if(emps!=null&&emps.size()>0){
//有：保存员工信息。depart_id就是部门的id
for(Employee e:emps){
qr.update("insert into employee values(?,?,?,?)", e.getId(),e.getName(),e.getSalary(),d.getId());
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//部门关联的员工要不要查：看需求
public Department getDepartmentById(int departmentId){
try {
Department d = qr.query("select * from department where id=?", new BeanHandler<Department>(Department.class), departmentId);
if(d!=null){
//查询该部门下的员工
List<Employee> emps = qr.query("select * from employee where depart_id=?", new BeanListHandler<Employee>(Employee.class), departmentId);
d.setEmps(emps);
}
return d;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
DepartmentDaoImpl
import java.sql.SQLException;
import java.util.List; import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler; import com.itheima.domain.Student;
import com.itheima.domain.Teacher;
import com.itheima.util.DBCPUtil; public class TeacherDaoImpl {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource()); public void addTeacher(Teacher t) {
try {
// 保存老师信息
qr.update("insert into teacher values(?,?,?)", t.getId(),
t.getName(), t.getSalary());
// 看老师有没有关联的学生
List<Student> students = t.getStudents();
if (students != null && students.size() > 0) {
// 如果有：
for (Student s : students) {
// 看看学员在数据库中存在吗
Student stu = qr.query("select * from student where id=?",
new BeanHandler<Student>(Student.class), s.getId());
if (stu == null) {
// 不存在才插入学生信息
qr.update("insert into student values(?,?,?)",
s.getId(), s.getName(), s.getGrade());
}
// 不管学生存在还是不存在，都得维护关系
qr.update("insert into teacher_student values(?,?)",
t.getId(), s.getId());
}
}
} catch (SQLException e) {
e.printStackTrace();
} } // 学生要不要查：看需求
public Teacher findTeacherById(int teacherId) {
try {
Teacher t = qr.query("select * from teacher where id=?",new BeanHandler<Teacher>(Teacher.class), teacherId);
if(t!=null){
//查学生信息
// String sql = "select * from student where id in (select s_id from teacher_student where t_id=?)";
// String sql = "select s.* from student s,teacher_student ts where s.id=ts.s_id and ts.t_id=?";
String sql = "select s.* from student s inner join teacher_student 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);
}
}
}
TeacherDaoImpl
实体类：
package com.itheima.domain; import java.util.ArrayList;
import java.util.List;
//一对多：one2many
public class Department {
private int id;
private String name;
private List<Employee> emps = new ArrayList<Employee>();
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 List<Employee> getEmps() {
return emps;
}
public void setEmps(List<Employee> emps) {
this.emps = emps;
}
@Override
public String toString() {
return "Department [id=" + id + ", name=" + name + ", emps=" + emps
+ "]";
} }
Department
package com.itheima.domain;
//多对一:many 2 one
public class Employee {
private int id;
private String name;
private float salary;
private Department department;
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 getSalary() {
return salary;
}
public void setSalary(float salary) {
this.salary = salary;
}
public Department getDepartment() {
return department;
}
public void setDepartment(Department department) {
this.department = department;
}
@Override
public String toString() {
return "Employee [id=" + id + ", name=" + name + ", salary=" + salary
+ ", department=" + department + "]";
} }
Employee
package com.itheima.domain;
public class IdCard {
private int id;
private String num;
private Person person;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getNum() {
return num;
}
public void setNum(String num) {
this.num = num;
}
public Person getPerson() {
return person;
}
public void setPerson(Person person) {
this.person = person;
}
@Override
public String toString() {
return "IdCard [id=" + id + ", num=" + num + ", person=" + person + "]";
}
}
IdCard
package com.itheima.domain; import java.util.ArrayList;
import java.util.List;
//many 2 many
public class Student {
private int id;
private String name;
private String grade;
private List<Teacher> teachers = new ArrayList<Teacher>();
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 String getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = grade;
}
public List<Teacher> getTeachers() {
return teachers;
}
public void setTeachers(List<Teacher> teachers) {
this.teachers = teachers;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", grade=" + grade
+ ", teachers=" + teachers + "]";
} }
student
package com.itheima.domain; import java.util.ArrayList;
import java.util.List;
//many 2 many
public class Teacher {
private int id;
private String name;
private float salary;
private List<Student> students = new ArrayList<Student>();
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 getSalary() {
return salary;
}
public void setSalary(float salary) {
this.salary = salary;
}
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "Teacher [id=" + id + ", name=" + name + ", salary=" + salary
+ ", students=" + students + "]";
} }
Teacher
package com.itheima.domain;
public class Person {
private int id;
private String name;
private IdCard idCard;
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 IdCard getIdCard() {
return idCard;
}
public void setIdCard(IdCard idCard) {
this.idCard = idCard;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", idCard=" + idCard
+ "]";
}
}
Person
油条类：
package com.itheima.util; import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; public class DBCPUtil {
private static DataSource dataSource;
static{
try {
InputStream in = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties props = new Properties();
props.load(in);
dataSource = BasicDataSourceFactory.createDataSource(props);
} catch (Exception e) {
throw new ExceptionInInitializerError("初始化数据源失败");
}
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException("获取数据库连接失败");
}
}
public static DataSource getDataSource(){
return dataSource;
} }
DBCPutil
测试类：
package com.itheima.util; import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; public class DBCPUtil {
private static DataSource dataSource;
static{
try {
InputStream in = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties props = new Properties();
props.load(in);
dataSource = BasicDataSourceFactory.createDataSource(props);
} catch (Exception e) {
throw new ExceptionInInitializerError("初始化数据源失败");
}
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException("获取数据库连接失败");
}
}
public static DataSource getDataSource(){
return dataSource;
} }
TeacherDaoImplTest
package com.itheima.test; import org.junit.Test; import com.itheima.dao.impl.DepartmentDaoImpl;
import com.itheima.domain.Department;
import com.itheima.domain.Employee; public class DepartmentDaoImplTest {
private DepartmentDaoImpl dao = new DepartmentDaoImpl();
@Test
public void testAdd(){
Department d = new Department();
d.setId(1);
d.setName("公关部"); Employee e1 = new Employee();
e1.setId(1);
e1.setName("王斐");
e1.setSalary(10000); Employee e2 = new Employee();
e2.setId(2);
e2.setName("苑明星");
e2.setSalary(10000); //建立关联关系：从部门角度出发
d.getEmps().add(e1);
d.getEmps().add(e2); dao.addDepartment(d);
}
@Test
public void testQuery(){
Department d = dao.getDepartmentById(1);
System.out.println(d);
for(Employee e:d.getEmps())
System.out.println(e);
}
}
DepartmentDaoImplTest
package com.itheima.test; import static org.junit.Assert.*; import org.junit.Test; import com.itheima.dao.impl.PersonDaoImpl;
import com.itheima.domain.IdCard;
import com.itheima.domain.Person; public class PersonDaoImplTest {
private PersonDaoImpl dao = new PersonDaoImpl();
@Test
public void testAddPerson() {
Person p = new Person();
p.setId(1);
p.setName("wzt"); IdCard idcard = new IdCard();
// idcard.setId(2);//设置应该无效
idcard.setNum("3701XXX"); p.setIdCard(idcard); dao.addPerson(p);
} @Test
public void testFindPersonById() {
Person p = dao.findPersonById(1);
System.out.println(p);
IdCard idcard = p.getIdCard();
System.out.println(idcard);
} }
PersonDaoImplTest
最新文章
- C# 5.0 异步编程
- 产生NaN
- Node.js的核心与红利(zz)
- Spark-1.0.1 的make-distribution.sh编译、SBT编译、Maven编译 三种编译方法
- Remoting首次用时偏长问题
- shuffle过程中的信息传递
- Eclipse环境下JBoss调试，解决引用的工程不被部署的问题
- 关于” fatal error C1010: unexpected end of file while looking forprecompiled header directive”问题
- java项目组会议纪要
- macvim打造python IDE
- Open-Falcon第六步安装Dashboard（小米开源互联网企业级监控系统）
- 一次隐蔽的while死循环
- Bootstrap switch 切换状态踩坑
- hadoop_随笔二_参数
- python学习第11天 迭代器
- Git永久删除文件和历史记录
- js條件結構和循環結構
- mysql创建用户并给用户分配权限
- linux命令学习之：cd
- 【转】如何用css限制文字长度,使溢出的内容用省略号…显示