通用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 + "]";

	}

}

巴特西

通用JDBC-demo

最新文章

热门文章