package cn.tongdun.kraken.neo4j.pool;
import org.springframework.beans.factory.annotation.Autowired;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class JdbcConnectionPoolFactory {
/**
* @Field: pool
* 数据库连接池
*/
@Autowired
private static JdbcConnectionsPool pool;
/**
* @Method: getConnection
* @Description: 从数据库连接池中获取数据库连接对象
* @return Connection数据库连接对象
* @throws SQLException
*/
public static Connection getConnection() throws SQLException{
return pool.getConnection();
}
/**
* @Method: release
* @Description: 释放资源，
* 释放的资源包括Connection数据库连接对象，负责执行SQL命令的Statement对象，存储查询结果的ResultSet对象
* @param conn
* @param st
* @param rs
*/
public static void release(Connection conn,Statement st,ResultSet rs){
if(rs!=null){
try{
//关闭存储查询结果的ResultSet对象
rs.close();
}catch (Exception e) {
e.printStackTrace();
}
rs = null;
}
if(st!=null){
try{
//关闭负责执行SQL命令的Statement对象
st.close();
}catch (Exception e) {
e.printStackTrace();
}
}
if(conn!=null){
try{
//关闭Connection数据库连接对象
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
public static List<Object> executeSql(String sql,Class c) throws SQLException, IllegalAccessException, InstantiationException {
List<Object> objects = new ArrayList<>();
Field[] fields = c.getDeclaredFields();
Connection conn = JdbcConnectionPoolFactory.getConnection();
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
Object o=c.newInstance();
for (Field f : fields) {
f.setAccessible(true);
if (f.getType() == java.util.List.class) {
// 如果是List类型，得到其Generic的类型
Type genericType = f.getGenericType();
// 如果是泛型参数的类型
if (genericType instanceof ParameterizedType) {
ParameterizedType pt = (ParameterizedType) genericType;
//得到泛型里的class类型对象
Class<?> genericClazz = (Class<?>) pt.getActualTypeArguments()[0];
}
} else {
}
System.out.println(resultSet.getString("n"));
}
//关闭所有的数据库资源
JdbcConnectionPoolFactory.release(conn, statement, resultSet);
}
return null;
}
public static List<Map<String,String>> executeSql(String sql,String...returnKeys) throws SQLException, IllegalAccessException, InstantiationException {
List<Map<String,String>> maps=new ArrayList<>();
Connection conn = JdbcConnectionPoolFactory.getConnection();
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
Map<String,String> map=new HashMap();
for (String key:returnKeys){
map.put(key,resultSet.getString(key));
}
maps.add(map);
}
//关闭所有的数据库资源
JdbcConnectionPoolFactory.release(conn, statement, resultSet);
return maps;
}
}
package cn.tongdun.kraken.neo4j.pool; //import org.neo4j.jdbc.Connection; import javax.sql.DataSource; import java.io.IOException; import java.io.InputStream; import java.io.PrintWriter; import java.lang.reflect.InvocationHandler; import java.lang.reflect.Method; import java.lang.reflect.Proxy; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.SQLFeatureNotSupportedException; import java.util.LinkedList; import java.util.Properties; import java.util.logging.Logger; public class JdbcConnectionsPool implements DataSource { /* * 使用静态块代码，初始化连接池，创建连接池的中最小链接数量连接， * 创建linkedlist集合，将这些连接放入集合中 */ //创建linkedlist集合 private static LinkedList<Connection> linkedlist1=new LinkedList<Connection>(); private static String driver;// private static String url;// private static String username;//数据库登陆名 private static String password;//数据库的登陆密码 private static int jdbcConnectionInitSize;//最小连接数量 private static int max=1; //当前最大连接数量=max*jdbcConnectionInitSize static{ //通过反射机制获取访问db.properties文件 InputStream is=JdbcConnectionsPool.class.getResourceAsStream("/db.properties"); Properties prop=new Properties(); try { //加载db.properties文件 prop.load(is); //获取db.properties文件中的数据库连接信息 driver=prop.getProperty("driver"); url=prop.getProperty("url"); username=prop.getProperty("username"); password=prop.getProperty("password"); jdbcConnectionInitSize=Integer.parseInt(prop.getProperty("jdbcConnectionInitSize")); Class.forName(prop.getProperty("driver")); //创建最小连接数个数据库连接对象以备使用 for(int i=0;i<jdbcConnectionInitSize;i++){ Connection conn= (Connection) DriverManager.getConnection(url, username, password); System.out.println("获取到了链接" + conn); //将创建好的数据库连接对象添加到Linkedlist集合中 linkedlist1.add(conn); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } @Override public PrintWriter getLogWriter() throws SQLException { // TODO Auto-generated method stub return null; } @Override public void setLogWriter(PrintWriter out) throws SQLException { // TODO Auto-generated method stub } @Override public void setLoginTimeout(int seconds) throws SQLException { // TODO Auto-generated method stub } @Override public int getLoginTimeout() throws SQLException { // TODO Auto-generated method stub return 0; } @Override public Logger getParentLogger() throws SQLFeatureNotSupportedException { // TODO Auto-generated method stub return null; } @Override public <T> T unwrap(Class<T> iface) throws SQLException { // TODO Auto-generated method stub return null; } @Override public boolean isWrapperFor(Class<?> iface) throws SQLException { // TODO Auto-generated method stub return false; } /* * 实现数据库连接的获取和新创建 */ @Override public Connection getConnection() throws SQLException { //如果集合中没有数据库连接对象了，且创建的数据库连接对象没有达到最大连接数量，可以再创建一组数据库连接对象以备使用 if(linkedlist1.size()==0&&max<=5){ try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } for(int i=0;i<jdbcConnectionInitSize;i++){ Connection conn=DriverManager.getConnection(url, username, password); System.out.println("获取到了链接" + conn); //将创建好的数据库连接对象添加到Linkedlist集合中 linkedlist1.add(conn); } max++; } if(linkedlist1.size()>0){ //从linkedlist集合中取出一个数据库链接对象Connection使用 Connection conn1=linkedlist1.removeFirst(); System.out.println("linkedlist1数据库连接池大小是" + linkedlist1.size()); /*返回一个Connection对象，并且设置Connection对象方法调用的限制， *当调用connection类对象的close()方法时会将Connection对象重新收集放入linkedlist集合中。 */ Class<?>[] interfaces=new Class[]{Connection.class}; return (Connection) Proxy.newProxyInstance(Thread.currentThread().getContextClassLoader(),//这里换成JdbcConnectionsPool.class.getClassLoader();也行 interfaces, new InvocationHandler() { @Override public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { if(!method.getName().equalsIgnoreCase("close")){ return method.invoke(conn1, args); }else{ linkedlist1.add(conn1); System.out.println(conn1+"对象被释放，重新放回linkedlist集合中！"); System.out.println("此时Linkedlist集合中有"+linkedlist1.size()+"个数据库连接对象！"); return null; } } }); }else{ System.out.println("连接数据库失败！"); } return null; } @Override public Connection getConnection(String username, String password) throws SQLException { return null; } }
package cn.tongdun.kraken.neo4j.pool; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestJdbcPool { public static void main(String[] args) throws SQLException { //实例化封装了有关数据库类方法类的对象 JdbcConnectionPoolFactory jcpt=new JdbcConnectionPoolFactory(); //获得数据库连接对象 Connection conn= JdbcConnectionPoolFactory.getConnection(); //下面代码是存储过程的调用 String s="match (n:RelaNode) return n limit 1 "; Statement statement=conn.createStatement(); ResultSet resultSet= statement.executeQuery(s); while (resultSet.next()){ System.out.println(resultSet.getString("n")); } //关闭所有的数据库资源 JdbcConnectionPoolFactory.release(conn, statement, resultSet); } }
版权声明：本文为weixin_35555139原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。