欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

java数据库连接

程序员文章站 2022-04-06 09:45:59
...
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