day40_学习总结
连接池
使用阿里的Druid来创建连接池
使用连接池的好处:
避免了Connection对象重复创建,可以通过连接池来获取数据源(连接对象),使用完毕后通过close()归还到连接池中
使用步骤:
1.导入阿里提供的Druid的jar包
2.定义配置文件
在当前的类目录下,定义一个druid.properites文件
内容包括:
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/student
username=root
password=root
3.加载配置文件
3.1 获取属性集合类对象
Properties prop = new Properties();
3.2 获取输入流,通过Druid包中的DruidDataSourceFactory(连接池数据源的工厂类)的字节码文件对象的getResourceAsStream方法来获取输入流
InputStream inputStream = DruidDataSourceFactory.class.getResourceAsStream("druid.properties");
3.3 加载配置文件
prop.load(inputStream);
3.4 获取数据源对象,通过连接池数据源工厂类的createDataSource(Properties prop)来获取数据源对象
DataSource dataSource = DruidDataSourceFactory(prop);
4.获取连接对象
Connection conn = dataSource.getConnection();
ThreadLocal—本地线程
可以存储任意的java类
可以配合连接池来使用,将获取到的连接对象存放到本地线程当中,
对于一个连接池来说,不同用户对连接池进行操作时,消耗资源较大,可能会发生内存溢出,因此将获取到的连接对象存储到本地线程中,不仅能减少连接池的资源消耗,并且只能操作本地线程中的连接对象,提高了安全性
ThreadLocal的方法
构造方法:
ThreadLocal()
方法:
T get()//返回线程中的值
void set(T t)//添加线程中的值
void remove()//移除线程中的值
连接池创建连接对象的工具类
配置文件–druid.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/student
username=root
password=root
initialSize=5
maxActive=10
maxWait=3000
minIdle=3
工具类
package jdbcutils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils {
private static DataSource dataSource = null ;
private static ThreadLocal<Connection> t = new ThreadLocal<Connection>() ;
static {
try {
//创建属性集
Properties prop = new Properties();
//获取通道内的输入流
InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
//加载文件
prop.load(inputStream);
//数据源对象
dataSource = DruidDataSourceFactory.createDataSource(prop);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection conn ;
try {
conn = t.get();
if(conn == null){
conn = dataSource.getConnection();
t.set(conn);
return conn ;
}
} catch (SQLException e) {
e.printStackTrace();
}
return null ;
}
//获取数据源
public static DataSource getDataSource(){
return dataSource;
}
public static void close(ResultSet rs, Statement stmt,Connection conn){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//从本地线程ThreadLocal移除:解绑
t.remove();
}
}
}
public static void close(Statement stmt,Connection conn){
close(null,stmt,conn);
}
}
MVC架构思想
M:Model
V:View
C:Controller
MVC三层架构思想:
数据访问层（DAL）(dao层)
表现层（UI）(web层)、
业务逻辑层（BLL）(service层)
entity:实体类
Service业务层中有主要的业务接口,以及实现业务接口的实现类,实现类中调用数据访问层接口的方法
dao层(数据访问层)中有数据访问接口层,以及实现数据访问接口的实现类
练习----通讯录的增删查改
实体类
package com.pj.entity;
/**
* Concat:联系人
* * id
* * name
* * gender
* * phone
* * address
*/
public class Concat {
private int id ;
private String name ;
private String gender ;
private String phone ;
private String address ;
public Concat() {
}
public Concat(String name, String gender, String phone, String address) {
this.name = name;
this.gender = gender;
this.phone = phone;
this.address = address;
}
public Concat(int id, String name, String gender, String phone, String address) {
this.id = id;
this.name = name;
this.gender = gender;
this.phone = phone;
this.address = address;
}
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 getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "Concat{" +
"id=" + id +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", phone='" + phone + '\'' +
", address='" + address + '\'' +
'}';
}
}
业务接口层service
package com.pj.service;
import com.pj.entity.Concat;
import java.sql.SQLException;
import java.util.List;
/**
* 1 增加联系人 ----insert into
* * 2 删除联系人 --- delete
* * 3 修改联系人 根据id编号 --- update
* * 4)查询所有联系人 --- select
*/
public interface ConcatService {
int add(Concat concat) throws SQLException;//增加联系人
int deleteById(int id) throws SQLException;//删除联系人
int updateConcat(int id,Concat concat);//修改联系人
List<Concat> findAll();//获取联系人列表
}
业务接口层实现类—serviceImpl
package com.pj.service.impl;
import com.pj.dao.ConcatDao;
import com.pj.dao.impl.ConcatDaoImpl;
import com.pj.entity.Concat;
import com.pj.service.ConcatService;
import java.sql.SQLException;
import java.util.List;
public class ConcatServiceImpl implements ConcatService {
ConcatDao cd = new ConcatDaoImpl();
@Override
public int add(Concat concat) {
try {
return cd.insertConcat(concat);
} catch (SQLException e) {
e.printStackTrace();
}
return 0 ;
}
@Override
public int deleteById(int id) {
try {
return cd.deleteByIdConcat(id);
} catch (SQLException e) {
e.printStackTrace();
}
return 0 ;
}
@Override
public int updateConcat(int id,Concat concat) {
try {
return cd.updateConcat(id,concat);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public List<Concat> findAll() {
try {
return cd.findALL();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
数据访问接口层—dao
package com.pj.dao;
import com.pj.entity.Concat;
import java.sql.SQLException;
import java.util.List;
public interface ConcatDao {
int insertConcat(Concat concat) throws SQLException;
int deleteByIdConcat(int id) throws SQLException;
int updateConcat(int id,Concat concat) throws SQLException;
List<Concat> findALL() throws SQLException;
}
数据访问接口实现类—daoImpl
package com.pj.dao.impl;
import com.pj.dao.ConcatDao;
import com.pj.entity.Concat;
import jdbcutils.JdbcUtils;
import javax.management.StandardEmitterMBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class ConcatDaoImpl implements ConcatDao {
/**
* 插入通讯录
* @param concat
* @return
* @throws SQLException
*/
@Override
public int insertConcat(Concat concat) throws SQLException {
//获取连接对象
Connection conn = JdbcUtils.getConnection();
//准备sql语句
String sql = "insert into concat(name,gender,phone,address) values(?,?,?,?)" ;
//获取预编译对象
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1,concat.getName());
stmt.setString(2,concat.getGender());
stmt.setString(3,concat.getPhone());
stmt.setString(4,concat.getAddress());
//更新
int count = stmt.executeUpdate();
return count ;
}
/**
* 通过id删除通讯录的人
* @param id
*/
@Override
public int deleteByIdConcat(int id) throws SQLException {
//获取连接对象
Connection conn = JdbcUtils.getConnection();
//准备sql语句
String sql = "delete from concat where id = ? " ;
//获取预编译对象
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1,id);
//更新
int count = stmt.executeUpdate();
return count ;
}
/**
* 更改通讯录
* @param id
* @return
*/
@Override
public int updateConcat(int id ,Concat concat) throws SQLException {
//获取连接对象
Connection conn = JdbcUtils.getConnection();
//准备sql语句
String sql = "update concat set name =?,gender=?,phone=?,address=? where id = ?" ;
//获取预编译对象
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1,concat.getName());
stmt.setString(2,concat.getGender());
stmt.setString(3,concat.getPhone());
stmt.setString(4,concat.getAddress());
stmt.setInt(5,id);
//更新
int count = stmt.executeUpdate();
return count ;
}
/**
* 获取全部列表
* @return
* @throws SQLException
*/
@Override
public List<Concat> findALL() throws SQLException {
//定义一个List集合
List<Concat> list = new ArrayList<Concat>();
Concat concat = null;
//获取连接对象
Connection conn = JdbcUtils.getConnection();
//准备sql语句
String sql = "select * from concat " ;
//获取预编译对象
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
while(rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
String gender = rs.getString(3);
String phone = rs.getString(4);
String address = rs.getString(5);
concat = new Concat(id,name,gender,phone,address);
list.add(concat);
}
return list;
}
}
测试类
package com.pj.concat;
import com.pj.entity.Concat;
import com.pj.service.ConcatService;
import com.pj.service.impl.ConcatServiceImpl;
import java.sql.SQLException;
import java.util.List;
import java.util.Scanner;
public class ConcatTest {
public static void main(String[] args) {
ConcatService concatService = new ConcatServiceImpl();
while(true){
System.out.println("请输入您需要的业务编号:");
System.out.println("1 插入通讯录");
System.out.println("2 删除");
System.out.println("3 更改通讯录");
System.out.println("4 获取通讯录中所有人");
System.out.println("5 退出");
Scanner sc = new Scanner(System.in) ;
int num = sc. nextInt() ;
switch (num){
case 1:
System.out.println("欢迎使用插入功能!!");
Concat concat = new Concat("鸣人","男","13322224444","七班");
Concat concat1 = new Concat("雏田","女","15688888888","五班");
Concat concat2 = new Concat("佐助","男","15688886666","七班");
try {
concatService.add(concat2);
} catch (SQLException e) {
e.printStackTrace();
}
break;
case 2:
System.out.println("请输入要删除的id号:");
int id = sc.nextInt();
try {
concatService.deleteById(id);
} catch (SQLException e) {
e.printStackTrace();
}
break;
case 3:
System.out.println("请输入要修改的id号:");
int id1 = sc.nextInt();
Concat concat4 = new Concat("路飞","男","15536368855","航海王");
concatService.updateConcat(id1,concat4);
break;
case 4:
List<Concat> list = concatService.findAll();
if(list != null){
for(Concat c :list){
System.out.println(c.getId()+"\t"+c.getName()+"\t"+c.getGender()+"\t"+c.getPhone()+"\t"+c.getAddress());
}
}
break;
case 5:
System.exit(0);
}
}
}
}