数据持久层可分为数据连接层和数据操作层。(自己定义的)
数据连接层解决方案:
1.JDBC1.0版本JdbcUtils工具类(驱动注册方式,获取Connection)
2.JDBC2.0版本JdbcUtils工具类(连接池方式,获取database)
使用DataBase连接池方式:
DBCP
C3P0
数据操作层解决方案:
1.JDBC1.0版本BaseDao工具类(connection+元数据+beanUtils组件)
2.JDBC2.0版本BaseDao工具列(DBUtils组件)
3.JdbcTemplate组件
组合:连接池+DBUtils组件
一、数据持久层解决方案1.0
JDBC1.0版本JdbcUtils工具列(驱动注册方式)
JDBC1.0版本BaseDao工具列(BeanUtils组件+元数据)
1.dao
BaseDao.java
/**
* JDBC1.0版本BaseDao工具类(元数据+BeanUtils组件)
*
* @author BGS
*
*/
public class BaseDao {
private Connection con;
private PreparedStatement pst;
private ResultSet rs;
public void update(String sql,Object...params) {
try {
con=JdbcUtil.getConn();
pst = con.prepareStatement(sql);
ParameterMetaData pst_metada = pst.getParameterMetaData();
int count = pst_metada.getParameterCount();
for(int i=0;i<count;i++) {
pst.setObject(i+1, params[i]);
}
pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.close(con, pst);
}
}
public <T> List<T> query(String sql,Class<T> clazz,Object...params) {
List<T> result=new ArrayList<T>();
try {
con=JdbcUtil.getConn();
pst = con.prepareStatement(sql);
ParameterMetaData pst_metada = pst.getParameterMetaData();
int count = pst_metada.getParameterCount();
//设置参数
for(int i=0;i<count;i++) {
pst.setObject(i+1, params[i]);
}
ResultSet rs = pst.executeQuery();
ResultSetMetaData rs_metada = rs.getMetaData();
int columnCount = rs_metada.getColumnCount();
while(rs.next()) {
T t=clazz.newInstance();
for(int i=0;i<columnCount;i++) {
String columnName = rs_metada.getColumnName(i+1);
Object value = rs.getObject(columnName);
//设置属性值
BeanUtils.setProperty(t, columnName, value);
}
result.add(t);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.close(con, pst);
}
return result;
}
}
MemberDao .java
public class MemberDao extends BaseDao{
public List<MemberEntity> selectAll() {
String sql="select * from member";
return super.query(sql, MemberEntity.class, null);
}
}
2.util
JdbcUtil.java
/**
* JDBC1.0版本JdbcUtils工具类(驱动注册方式)
*
* @author BGS
*
*/
public class JdbcUtil {
private static Properties db;
static {
try {
//读取配置文件
db=new Properties();
db.load(JdbcUtil.class.getResourceAsStream("/db.properties"));
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接对象
* @return
*/
public static Connection getConn() {
try {
return DriverManager.getConnection(db.getProperty("url"), db.getProperty("user"), db.getProperty("password"));
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 关闭连接
*/
public static void close(Connection conn,Statement st,ResultSet rs) {
try {
if(rs!=null) {
rs.close();
}
if(st!=null) {
st.close();
}
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 关闭连接
*/
public static void close(Connection conn,Statement st) {
try {
if(st!=null) {
st.close();
}
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3. 实体
MemberEntity.java
public class MemberEntity {
private String id;
private String name;
private String password;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
3. 配置文件
db.properties
url=jdbc:mysql://localhost:3306/day11
user=root
password=root
4. 测试
App.java
public class App {
@Test
public void test1() {
MemberDao dao=new MemberDao();
List<MemberEntity> result = dao.selectAll();
System.out.println(result);
}
}
二、数据持久层解决方案2.0
JDBC2.0版本JdbcUtils工具类(连接池方式)
dbcp连接池
c3p0连接池
JDBC2.0版本BaseDao工具列(DBUtils组件)
1)使用C3P0连接池
1.dao
BaseDao.java
/**
* JDBC2.0版本BaseDao工具类(元数据+BeanUtils组件)
*
* @author BGS
*
*/
public class BaseDao {
public void update(String sql,Object...params) {
try {
QueryRunner qr = JdbcUtil.getQr();
qr.update(sql, params);
} catch (SQLException e) {
e.printStackTrace();
}
}
public <T> List<T> query(String sql,Class<T> clazz,Object...params) {
try {
QueryRunner qr = JdbcUtil.getQr();
return qr.query(sql, new BeanListHandler<T>(clazz), params);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
MemberDao .java
public class MemberDao extends BaseDao{
public List<MemberEntity> selectAll() {
String sql="select * from member";
return super.query(sql, MemberEntity.class, null);
}
}
2.util
JdbcUtil.java
/**
* JDBC2.0版本JdbcUtils工具类(连接池-C3P0)
*
* @author BGS
*
*/
public class JdbcUtil {
private static ComboPooledDataSource dataSource;
static {
dataSource=new ComboPooledDataSource();
}
/**
* 获取DBUtils核心工具类
* 无须关闭连接
* @return
*/
public static QueryRunner getQr() {
return new QueryRunner(dataSource);
}
}
3. 实体
MemberEntity.java
public class MemberEntity {
private String id;
private String name;
private String password;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
3. 配置文件
c3p0-config.xml
放在src根目录下
<c3p0-config>
<!-- 默认数据库 -->
<default-config>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/day11</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">3</property>
<property name="maxPoolSize">6</property>
<property name="maxIdleTime">1000</property>
</default-config>
<!-- 可以使用多数据源
<named-config name="db2">
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">3</property>
<property name="maxPoolSize">6</property>
<property name="maxIdleTime">1000</property>
</named-config>
-->
</c3p0-config>
4. 测试
App.java
public class App {
@Test
public void test1() {
MemberDao dao=new MemberDao();
List<MemberEntity> result = dao.selectAll();
System.out.println(result);
}
}
1)使用DBCP连接池
1.dao
BaseDao.java
/**
* JDBC2.0版本BaseDao工具类(元数据+BeanUtils组件)
*
* @author BGS
*
*/
public class BaseDao {
public void update(String sql,Object...params) {
try {
QueryRunner qr = JdbcUtil.getQr();
qr.update(sql, params);
} catch (SQLException e) {
e.printStackTrace();
}
}
public <T> List<T> query(String sql,Class<T> clazz,Object...params) {
try {
QueryRunner qr = JdbcUtil.getQr();
return qr.query(sql, new BeanListHandler<T>(clazz), params);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
MemberDao .java
public class MemberDao extends BaseDao{
public List<MemberEntity> selectAll() {
String sql="select * from member";
return super.query(sql, MemberEntity.class, null);
}
}
2.util
JdbcUtil.java
/**
* JDBC2.0版本JdbcUtils工具类(连接池-DBCP)
*
* @author BGS
*
*/
public class JdbcUtil {
private static DataSource dataSource;
static {
try {
Properties p=new Properties();
p.load(JdbcUtil.class.getResourceAsStream("/db.properties"));
dataSource = BasicDataSourceFactory.createDataSource(p);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取DBUtils核心工具类
* 无须关闭连接
* @return
*/
public static QueryRunner getQr() {
return new QueryRunner(dataSource);
}
}
3. 实体
MemberEntity.java
public class MemberEntity {
private String id;
private String name;
private String password;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
3. 配置文件
db.properties
url=jdbc:mysql://localhost:3306/day11
driverClassName=com.mysql.jdbc.Driver
username=root
password=root
initialSize=3
maxActive=6
maxIdle=3000
4. 测试
App.java
public class App {
@Test
public void test1() {
MemberDao dao=new MemberDao();
List<MemberEntity> result = dao.selectAll();
System.out.println(result);
}
}