通用JDBC-demo
1、JDBC 的工具包（utils）：包含获取数据库连接, 关闭数据库资源等方法
JDBCTools_pro.java
package com.app.utils;
import java.beans.PropertyVetoException;
import java.io.IOException;
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 com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* JDBC 的工具类
*
* 其中包含: 获取数据库连接, 关闭数据库资源等方法.
*/
public class JDBCTools_pro {
private static JDBCTools_pro instance;
public ComboPooledDataSource ds;
private static String c3p0Properties = "jdbc.properties";
//处理数据库事务的
//提交事务
public static void commit(Connection connection){
if(connection != null){
try {
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//回滚事务
public static void rollback(Connection connection){
if(connection != null){
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//开始事务
public static void beginTx(Connection connection){
if(connection != null){
try {
connection.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//private static DataSource dataSource = null;
//数据库连接池应只被初始化一次.
/*static{
dataSource = new ComboPooledDataSource("helloc3p0");
}
public static Connection getConnection() throws Exception {
return dataSource.getConnection();
}*/
public JDBCTools_pro() throws Exception {
Properties p = new Properties();
/*
* 同一包下
*
*/
// p.load(this.getClass().getClassLoader().getResourceAsStream(c3p0Properties));
/*
* 根目录下
*/
p.load(this.getClass().getClassLoader().getResourceAsStream(c3p0Properties));
ds = new ComboPooledDataSource();
ds.setUser(p.getProperty("jdbc.username"));
ds.setPassword(p.getProperty("jdbc.password"));
ds.setJdbcUrl(p.getProperty("jdbc.url"));
ds.setDriverClass(p.getProperty("jdbc.driverClassName"));
ds.setMinPoolSize(Integer.parseInt(p.getProperty("C3P0.minPoolSize")));
ds.setMaxPoolSize(Integer.parseInt(p.getProperty("C3P0.maxPoolSize")));
ds.setMaxIdleTime(Integer.parseInt(p.getProperty("C3P0.maxIdleTime")));
ds.setAcquireIncrement(Integer.parseInt(p.getProperty("C3P0.acquireIncrement")));
ds.setMaxStatements(Integer.parseInt(p.getProperty("C3P0.maxStatements")));
ds.setMaxStatementsPerConnection(Integer.parseInt(p.getProperty("C3P0.maxStatementsPerConnection")));
ds.setIdleConnectionTestPeriod(Integer.parseInt(p.getProperty("C3P0.idleConnectionTestPeriod")));
ds.setAcquireRetryAttempts(Integer.parseInt(p.getProperty("C3P0.acquireRetryAttempts")));
}
public static final JDBCTools_pro getInstance() {
if (instance == null) {
try {
instance = new JDBCTools_pro();
} catch (Exception e) {
e.printStackTrace();
}
}
return instance;
}
public synchronized final Connection getConnection() {
try {
return ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void releaseDB(ResultSet resultSet, Statement statement,
Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
//数据库连接池的 Connection 对象进行 close 时
//并不是真的进行关闭, 而是把该数据库连接会归还到数据库连接池中.
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2、JDBC数据库底层实现（dao）
2.1.1.底层实现
BaseDaoImpl.java
package com.app.dao.impl;
import java.sql.Connection;
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 org.apache.commons.dbutils.handlers.ScalarHandler;
import com.app.dao.BaseDao;
import com.app.utils.ReflectionUtils;
/**
* 使用 QueryRunner 提供其具体的实现
* @param <T>: 子类需传入的泛型类型.
*/
public class BaseDaoImpl<T> implements BaseDao<T> {
private QueryRunner queryRunner = null;
private Class<T> type;
public BaseDaoImpl() {
queryRunner = new QueryRunner();
type = ReflectionUtils.getSuperGenericType(getClass());
}
@Override
public void batch(Connection connection, String sql, Object[]... args) throws SQLException {
queryRunner.batch(connection, sql, args);
}
@SuppressWarnings("unchecked")
@Override
public <E> E getForValue(Connection connection, String sql, Object... args) throws SQLException {
return (E) queryRunner.query(connection, sql, new ScalarHandler(), args);
}
@Override
public List<T> getForList(Connection connection, String sql, Object... args)
throws SQLException {
return queryRunner.query(connection, sql,
new BeanListHandler<>(type), args);
}
@Override
public T get(Connection connection, String sql, Object... args) throws SQLException {
return queryRunner.query(connection, sql,
new BeanHandler<>(type), args);
}
@Override
public void update(Connection connection, String sql, Object... args) throws SQLException {
queryRunner.update(connection, sql, args);
}
}
2.1.2.实物类继承
UserDaoImpl.java
package com.app.dao.impl;
import java.sql.Connection;
import com.app.dao.UserDao;
import com.app.entity.User;
import com.app.utils.JDBCTools_xml;
public class UserDaoImpl extends BaseDaoImpl<User> implements UserDao{
@Override
public boolean isUserExisted(String id) {
Connection conn = null;
boolean flage = false;
try {
conn = JDBCTools_xml.getConnection();
String sql = "SELECT id from users where userId = ?";
String p = getForValue(conn, sql, id);
if(null!=p){
flage = true;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCTools_xml.releaseDB(null, null, conn);
}
return flage;
}
@Override
public boolean isUserpass(String id, String password) {
Connection conn = null;
boolean flage = false;
try {
conn = JDBCTools_xml.getConnection();
String sql = "SELECT id from users where id = ? and password = ?";
String p = getForValue(conn, sql, id);
if(null!=p){
flage = true;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCTools_xml.releaseDB(null, null, conn);
}
return flage;
}
@Override
public long getNumber() {
long num = 0;
Connection conn = null;
try {
conn = JDBCTools_xml.getConnection();
String sql = "SELECT count(*) from users";
num = getForValue(conn, sql, null);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCTools_xml.releaseDB(null, null, conn);
}
return num;
}
@Override
public User getUserById(String id) {
Connection conn = null;
User user = null;
try {
conn = JDBCTools_xml.getConnection();
String sql = "SELECT userId,userName,password "+
"from users where userId = ?";
user = get(conn,sql,id);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools_xml.releaseDB(null, null, conn);
}
return user;
}
@Override
public void insertUser(String userId, String userName, String password) {
Connection conn = null;
try {
conn = JDBCTools_xml.getConnection();
String sql = "INSERT INTO users VALUES(?,?,?)";
update(conn, sql, userId,userName,password);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCTools_xml.releaseDB(null, null, conn);
}
}
@Override
public void deleteUser(String id) {
Connection conn = null;
try {
conn = JDBCTools_xml.getConnection();
String sql = "DELETE from users where userId = ?";
update(conn, sql, id);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCTools_xml.releaseDB(null, null, conn);
}
}
@Override
public void updateUser(String userId, String userName, String password) {
Connection conn = null;
try {
conn = JDBCTools_xml.getConnection();
String sql = "UPDATE users set userName = ?,password = ? where userId = ?";
update(conn, sql, userName,password,userId);
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCTools_xml.releaseDB(null, null, conn);
}
}
}
2.2.1
BaseDao.java
package com.app.dao;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
* 访问数据的 DAO 接口.
* 里边定义好访问数据表的各种方法
* @param T: DAO 处理的实体类的类型.
*/
public interface BaseDao<T> {
void batch(Connection connection,
String sql, Object [] ... args) throws SQLException;
<E> E getForValue(Connection connection,
String sql, Object ... args) throws SQLException;
List<T> getForList(Connection connection,
String sql, Object ... args) throws SQLException;
T get(Connection connection, String sql,
Object ... args) throws SQLException;
void update(Connection connection, String sql,
Object ... args) throws SQLException;
}
2.2.2
UserDao.java
package com.app.dao;
import com.app.entity.User;
public interface UserDao extends BaseDao<User>{
boolean isUserExisted(String id);
boolean isUserpass(String id,String password);
long getNumber();
User getUserById(String id);
void updateUser(String Userid, String Username, String password);
void deleteUser(String id);
void insertUser(String Userid, String Username, String password);
}
3、JDBC实物类（entity）
User.java
package com.app.entity;
public class User {
private String userId;
private String userName;
private String password;
public User() {
// TODO Auto-generated constructor stub
}
public User(String userId, String userName, String password) {
super();
this.userId = userId;
this.userName = userName;
this.password = password;
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "user [userId=" + userId + ", userName=" + userName + ", password=" + password + "]";
}
}
