4天贯通JDBC技术

七、实现DAO及其实现类CustomerDAO的代码

【DAO.java】

//DAO：database access object

 

class ReflectionUtils{

//获取clazz对象对应的运行时类的父类的泛型

public static Class getSuperGeneric(Class clazz){

Type type = clazz.getGenericSuperclass();

ParameterizedType p = (ParameterizedType)type;

Type[] ts = p.getActualTypeArguments();

 

return (Class)ts[0];

}

}

 

public class DAO<T> {

private Class<T> clazz = null;

//this.getClass()在这个问题中，就是CustomerDAO

public DAO(){

clazz = ReflectionUtils.getSuperGeneric(this.getClass());

}

//获取数据库的标准的特定含义的值

public <E> E getValue(Connection conn,String sql,Object...args){

PreparedStatement ps = null;

ResultSet rs = null;

try{

ps = conn.prepareStatement(sql);

 

for(int i = 0;i < args.length;i++){

ps.setObject(i + 1, args[i]);

}

 

rs = ps.executeQuery();

 

if(rs.next()){

return (E)rs.getObject(1);

}

 

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(rs, ps, null);

}

 

return null;

}

 

//返回多个对象，以集合的形式返回

public List<T> getForList(Connection conn,String sql,Object ...args){

PreparedStatement ps = null;

ResultSet rs = null;

List<T> list = new ArrayList<>();

try{

//1.预编译sql语句，获取PreparedStatement对象

ps = conn.prepareStatement(sql);

//2.填充占位符

for(int i = 0;i < args.length;i++){

ps.setObject(i + 1, args[i]);

}

//3.返回一个结果集

rs = ps.executeQuery();

ResultSetMetaData rsmd = rs.getMetaData();

int columnCount = rsmd.getColumnCount();

while(rs.next()){

T t = clazz.newInstance();

 

//给t对象的相应属性赋值

for(int i = 0;i < columnCount;i++){

Object columnVal = rs.getObject(i + 1);

String columnLabel = rsmd.getColumnLabel(i + 1);

PropertyUtils.setProperty(t, columnLabel, columnVal);

}

list.add(t);

}

 

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(rs, ps, null);

}

//System.out.println(clazz);

return list;

}

 

//返回一个对象

public T get(Connection conn,String sql,Object ...args){

PreparedStatement ps = null;

ResultSet rs = null;

try{

//1.预编译sql语句，获取PreparedStatement对象

ps = conn.prepareStatement(sql);

//2.填充占位符

for(int i = 0;i < args.length;i++){

ps.setObject(i + 1, args[i]);

}

//3.返回一个结果集

rs = ps.executeQuery();

ResultSetMetaData rsmd = rs.getMetaData();

int columnCount = rsmd.getColumnCount();

if(rs.next()){

T t = clazz.newInstance();

 

//给t对象的相应属性赋值

for(int i = 0;i < columnCount;i++){

Object columnVal = rs.getObject(i + 1);

String columnLabel = rsmd.getColumnLabel(i + 1);

PropertyUtils.setProperty(t, columnLabel, columnVal);

}

return t;

}

 

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(rs, ps, null);

}

//System.out.println(clazz);

return null;

}

 

//通用的增删改的操作

public void update(Connection conn,String sql,Object ... args){

PreparedStatement ps = null;

try{

ps = conn.prepareStatement(sql);

 

for(int i = 0;i < args.length;i++){

ps.setObject(i + 1, args[i]);

}

 

ps.executeUpdate();

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, ps, null);

}

}

}

【CustomerDAO.java】

//CustomerDAO类是用来操作Customer类的

public class CustomerDAO extends DAO<Customer>{

 

// @Test

// public void testGeneric(){

// Class clazz = CustomerDAO.class;

// Type type = clazz.getGenericSuperclass();

// ParameterizedType p = (ParameterizedType)type;

// Type[] ts = p.getActualTypeArguments();

// System.out.println(ts[0]);

// }

/**

 * 获取对应的表中的记录的个数

 */

public long getCount(Connection conn){

String sql = "select count(*) from customers";

return (long)getValue(conn, sql);

}

 

/**

 * 返回customers表中的所有数据

 * @param conn

 * @return

 */

public List<Customer> getAll(Connection conn){

String sql = "select id,name,email,birth from customers";

return getForList(conn, sql);

}

 

/**

 * 根据指定的id返回相应的对象

 * @param conn

 * @param customerId

 */

public Customer getInstance(Connection conn,int customerId){

String sql = "select id,name,email,birth from customers where id = ?";

return get(conn, sql, customerId);

}

 

/**

 * 删除指定customerId的数据表中的记录

 * @param conn

 * @param customerId

 */

public void delete(Connection conn,int customerId){

String sql = "delete from customers where id = ?";

update(conn, sql, customerId);

}

 

 

/**

 * 向数据表中修改指定id的信息为Customer对象的信息

 * @param conn

 * @param cust

 */

public void update(Connection conn,Customer cust){

String sql = "update customers set name = ?,email = ?,birth = ? where id = ?";

update(conn, sql, cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());

}

 

 

/**

 * 向数据表中插入一条数据

 * @param conn  数据库的连接

 * @param cust 要插入的Customer对象

 */

public void insert(Connection conn,Customer cust){

String sql = "insert into customers(name,email,birth)values(?,?,?)";

update(conn, sql, cust.getName(),cust.getEmail(),cust.getBirth());

}

 

}

【TestCustomerDAO.java】

public class TestCustomerDAO {

CustomerDAO customerDAO = new CustomerDAO();

 

@Test

public void testGetCount(){

Connection conn = null;

try{

conn = JDBCUtils.getConnection();

long count = customerDAO.getCount(conn);

System.out.println(count);

 

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, null, conn);

}

}

 

@Test

public void testGetAll(){

Connection conn = null;

try{

conn = JDBCUtils.getConnection();

List<Customer> list = customerDAO.getAll(conn);

//System.out.println(list);

 

Iterator<Customer> iterator = list.iterator();

while(iterator.hasNext()){

System.out.println(iterator.next());

}

 

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, null, conn);

}

}

 

@Test

public void testQuery(){

Connection conn = null;

try{

conn = JDBCUtils.getConnection();

Customer cust = customerDAO.getInstance(conn, 13);

System.out.println(cust);

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, null, conn);

}

}

 

@Test

public void testDelete(){

Connection conn = null;

try{

conn = JDBCUtils.getConnection();

customerDAO.delete(conn, 10);

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, null, conn);

}

}

 

@Test

public void testUpdate(){

Connection conn = null;

try{

conn = JDBCUtils.getConnection();

Customer cust = new Customer(10, "张卫健", "zwj@gmail.com",new Date(new java.util.Date().getTime()));

customerDAO.update(conn, cust);

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, null, conn);

}

}

 

@Test

public void testInsert(){

Connection conn = null;

try{

conn = JDBCUtils.getConnection();

Customer cust = new Customer(10, "张卫健", "zwj@gmail.com",new Date(new java.util.Date().getTime()));

customerDAO.insert(conn, cust);

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.close(null, null, conn);

}

}

}

 

八、数据库连接池

C3P0数据库连接池

// 保证在所有的通过C3P0获取的连接中，只有一个DataSource的对象。（推荐）

private static DataSource source = null;

static {

source = new ComboPooledDataSource("helloc3p0");

}

 

// 获取数据库的连接方式3:使用c3p0数据库连接池获取数据库的连接,使用配置文件

public static Connection getConnection3() throws Exception {

return source.getConnection();

}

对应的配置文件：c3p0-config.xml

<c3p0-config>

 

<named-config name="helloc3p0">

<!-- 提供数据库连接的4个基本信息 -->

<property name="jdbcUrl">jdbc:mysql:///test</property>

<property name="driverClass">com.mysql.jdbc.Driver</property>

<property name="user">root</property>

<property name="password">123456</property>

 

<!-- 当连接池中的数量不足时，c3p0连接一次性向数据库服务器申请的连接数 -->

<property name="acquireIncrement">5</property>

<!-- 初始化数据库连接池时，池中存在的连接数 -->

<property name="initialPoolSize">10</property>

<!-- 数据库连接池中最少容纳的连接数 -->

<property name="minPoolSize">5</property>

<!-- 数据库连接池中最大容纳的连接数 -->

<property name="maxPoolSize">100</property>

<!-- 连接池中，最多允许存在的Statement的数量 -->

<property name="maxStatements">10</property>

<!-- 一次连接中，最多容纳的Statement的个数 -->

<property name="maxStatementsPerConnection">5</property>

 

 

</named-config>

</c3p0-config>

 

 

DBCP数据库连接池

//随着类的加载，使用BasicDataSourceFactory的静态方法createDataSource()返回一个

//DataSource的对象

private static DataSource source1 = null;

static {

Properties info = new Properties();

// info.load(new FileInputStream("dbcp.properties"));

InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream(

"com/atguigu/java/dbcp.properties");

try {

info.load(is);

source1 = BasicDataSourceFactory.createDataSource(info);

} catch (Exception e) {

e.printStackTrace();

}

}

 

// 获取数据库的连接方式4:使用DBCP数据库连接池获取数据库的连接（推荐）

public static Connection getConnection5() throws Exception {

 

return source1.getConnection();

}

 

配置文件dbcp.properties：

username=root

password=123456

url=jdbc:mysql://127.0.0.1:3306/test

driverClassName=com.mysql.jdbc.Driver

 

initialSize=10

maxActive=100

 

本教程由尚硅谷教育大数据研究院出品，如需转载请注明来源。