java数据库连接

package com.jynine;

import java.io.IOException;

/**
 * 数据库连接类
 * @author jynine
 *
 */
public class MyConnection {
	private static String driver = null;
	private static String url = null;
	private static String user = null;
	private static String password = null;
	private static Connection connection = null;
	private static PreparedStatement ps = null;
	private static ResultSet rs = null;
	/**
	 * 初始化数据库连接信息
	 * @param jdbc 数据库资源文件 必须和当前文件在同一路径下
	 *  e.g  jdbc.properties
	 */
	public static void initPro(String jdbc){
		InputStream is = new MyConnection().getClass().getResourceAsStream(jdbc);
		Properties pro = new Properties();
		try {
			pro.load(is);
			driver = pro.getProperty("driver");
			url = pro.getProperty("url");
			user = pro.getProperty("user");
			password = pro.getProperty("password");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 获取数据库连接
	 * @param driver
	 * @param url
	 * @param user
	 * @param password
	 * @return
	 */
	public static void setConnection(String jdbc){
		if(!isEmpty(jdbc)){
			initPro(jdbc);
			try {
				Class.forName(driver);
				connection = DriverManager.getConnection(url, user, password);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 得到预编译的SQL语句的对象
	 * @param sql
	 */
	public static void setPerStatement(String sql){
		if(!isEmpty(sql) && connection != null){
			try {
				ps = connection.prepareStatement(sql);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 得到结果集
	 * @param sql
	 * @param jdbc
	 * @return
	 */
	public static void setResultSet(){
		if(ps != null){
			try {
				rs = ps.executeQuery();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 初始化连接
	 * @param jdbc
	 * @param sql
	 */
	public static void initConn(String jdbc,String sql){
		setConnection(jdbc);
		setPerStatement(sql);
		setResultSet();
	}
	/**
	 * list根据提供的字段添加元素
	 * @param rs
	 * @param column
	 * @param list
	 * @throws SQLException
	 */
	public static void listAddElement(ResultSet rs,String[] column,List<String[]> list) throws SQLException{
		if(rs != null){
			String[] s = null;
			while (rs.next()) {
				s = new String[column.length];
				for (int i = 0; i < column.length; i++) {
					s[i] = rs.getString(column[i]);
				}
				list.add(s);
			}
		}
	}
	/**
	 * 动态拼接sql语句 并执行
	 * @param table 表名
	 * @param column 要返回字段
	 * @param jdbc 数据库资源文件
	 * @param orderby 排序
	 * @param ascOrDesc 升序或降序
	 * @return List<String>
	 */
	public static List<String[]> executeSql(String table,String[] column,String orderby,String ascOrDesc,String jdbc){
		List<String[]> list = new ArrayList<String[]>();
		try {
			String sql = splitJointSql(table, column, orderby, ascOrDesc);
			initConn(jdbc, sql);
			listAddElement(rs, column, list);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			clean(rs, ps, connection);
		}
		return list;
	}
	/**
	 * 执行sql查询 返回查询的第一个字段
	 * @param sql
	 * @param jdbc
	 * @return
	 */
	public static List<String> executeSql(String sql,String jdbc){
		List<String> list = new ArrayList<String>();
		try {
			initConn(jdbc, sql);
			if(rs != null){
				while (rs.next()) {
					list.add(rs.getString(1));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			clean(rs, ps, connection);
		}
		return list;
	}
	/**
	 * 执行sql查询 
	 * @param sql
	 * @param jdbc
	 * @return
	 */
	public static List<String[]> executeSql(String sql,String[] column,String jdbc){
		List<String[]> list = new ArrayList<String[]>();
		try {
			initConn(jdbc, sql);
			listAddElement(rs, column, list);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			clean(rs, ps, connection);
		}
		return list;
	}
	
	/**
	 * 拼接sql
	 * @param table 表名
	 * @param column 要返回字段
	 * @param jdbc 数据库资源文件
	 * @param orderby 排序
	 * @param ascOrDesc 升序或降序
	 * @return
	 */
	public static String splitJointSql(String table,String[] column,String orderby,String ascOrDesc){
		if(!isEmpty(table) && !isEmptyArray(column)){
			StringBuffer sb = new StringBuffer("select ");
			for (int i = 0; i < column.length; i++) {
				sb.append(column[i]);
				if(i < column.length - 1){
					sb.append(",");
				}
			}
			sb.append(" from ");
			sb.append(table);
			if(!isEmpty(orderby)){
				sb.append("order by ");
				sb.append(orderby);
			}
			if(!isEmpty(ascOrDesc)){
				sb.append(ascOrDesc);
			}
			return sb.toString();
		}else{
			return null;
		}
	}
	/**
	 * 判断字符串是否为空  如果为空返回true
	 * @param str
	 * @return
	 */
	public static boolean isEmpty(String str){
		if(str != null && !str.equals("")){
			return false;
		}else{
			return true;
		}
	}
	/**
	 * 判断数组是否为空
	 * @param str
	 * @return
	 */
	public static boolean isEmptyArray(String[] str){
		if(str != null && str.length >= 1){
			return false;
		}else{
			return true;
		}
	}
	/**
	 * 释放连接资源
	 * @param rs
	 * @param ps
	 * @param connection
	 */
	public static void clean(ResultSet rs, PreparedStatement ps, Connection connection){
		try {
			if(rs != null){
				rs.close();
			}
			if(ps != null){
				ps.close();
			}
			if(connection != null){
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		List<String[]>  strs = executeSql("user", new String[]{"username"}, null, null, "jdbc.properties");
		for (int i = 0; i < strs.size(); i++) {
			System.out.println(strs.get(i)[0]+"=======");
		}
	}
}

jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/ssm3
user=root
password=root